-2
\$\begingroup\$
        SELECT BR.IDObject AS IDObject,
               B.imgName AS imgName, 
               BK.imgPath AS imgPath,
               BR.imgType AS imgType

        FROM ImageReferences BR
        INNER JOIN BDB_WebImages B ON BR.IDWebImage = B.ID
        INNER JOIN BDB_WebImageCategory BK ON BR.IDImageCategory = BK.ID
        LEFT OUTER JOIN DB_IMAGES BDB ON BDB.IDImage = b.IDOriginalBild     

        LEFT OUTER JOIN Multilingual_Translation MLSignature1 ON MLSignature1.IDObject = BR.ID 
                         AND MLSignature1.IDObjectType = 36
                         AND MLSignature1.IDMultilingualAttribute = (SELECT TOP 1 ID FROM Multilingual_Attribute WHERE Tablename = 'BDB_WebImageReference' AND Columnname = 'titel')
                         AND MLSignature1.IDLanguage = 1
        LEFT OUTER JOIN Multilingual_Translation MLSignature2
                       ON MLSignature2.IDObject = BR.ID 
                         AND MLSignature2.IDObjectType = 36
                         AND MLSignature2.IDMultilingualAttribute = (SELECT TOP 1 ID FROM Multilingual_Attribute WHERE Tablename = 'BDB_WebImageReference' AND Columnname = 'Description')
                         AND MLSignature2.IDLanguage = 1
        LEFT OUTER JOIN Multilingual_Translation MLSignature3
                       ON MLSignature3.IDObject = BR.ID 
                         AND MLSignature3.IDObjectType = 36
                         AND MLSignature3.IDMultilingualAttribute = (SELECT TOP 1 ID FROM Multilingual_Attribute WHERE Tablename = 'BDB_WebImageReference' AND Columnname = 'Signature3')
                         AND MLSignature3.IDLanguage = 1
         LEFT OUTER JOIN SEO_Permalink SEODataWebImage
                ON SEODataWebImage.IDObjekt = BR.ID
               AND SEODataWebImage.IDType = 36
               AND SEODataWebImage.IDLanguage = 1

       LEFT OUTER JOIN SEO_Permalink SEODataBild
                ON SEODataBild.IDObjekt = BDB.IDImage
               AND SEODataBild.IDType = 33
               AND SEODataBild.IDLanguage = 1

WHERE BR.IDObjekt IN (25,26,38,40,45)

AND BR.IDObjectType = 36
\$\endgroup\$
  • \$\begingroup\$ what SQL database are you using? \$\endgroup\$ – Malachi Nov 20 '13 at 21:53
  • \$\begingroup\$ also please give a little bit more about what you are asking about and what you think is bogging down the query \$\endgroup\$ – Malachi Nov 20 '13 at 21:54
6
\$\begingroup\$

This query is horribly over-working. I can only believe that the query has been 'simplified' to put here on CR. Otherwise, as far as I can tell, the query can be rewritten as:

    SELECT BR.IDObject AS IDObject,
           B.imgName AS imgName, 
           BK.imgPath AS imgPath,
           BR.imgType AS imgType

    FROM ImageReferences BR
    INNER JOIN BDB_WebImages B ON BR.IDWebImage = B.ID
    INNER JOIN BDB_WebImageCategory BK ON BR.IDImageCategory = BK.ID
    WHERE BR.IDObjekt IN (25,26,38,40,45)
     AND BR.IDObjectType = 36

This is because all the other tables were OUTER joins, and their values are never 'selected' or used for 'projection'. Thus, they were just redundant ....

\$\endgroup\$
  • \$\begingroup\$ what about the IDMultilingualAttribute? that is different in each of those joins. you gotta scroll all the way over \$\endgroup\$ – Malachi Nov 21 '13 at 15:40
  • \$\begingroup\$ Yeah, so? what if it is different. If it matches a record, where is that record used (it's not selected anywhere). If it does not match a record, then it's an outer join, so no problem. \$\endgroup\$ – rolfl Nov 21 '13 at 15:49
  • 1
    \$\begingroup\$ @Malachi - put a different way, where are the table aliases MLSignature[123] actually used? nowhere ! \$\endgroup\$ – rolfl Nov 21 '13 at 15:52
  • 1
    \$\begingroup\$ I get what you are saying, I see it now. Forest, trees, running, look at grass, hit tree \$\endgroup\$ – Malachi Nov 21 '13 at 15:56
  • \$\begingroup\$ The original query would likely have returned the result set that this simpler query yields, but with each row possibly repeated. Therefore, this query is not identical to the original — it's probably better, assuming you only want distinct rows. \$\endgroup\$ – 200_success Jan 1 '14 at 9:23
3
\$\begingroup\$

Queries involving a large amount of data blobs on a SQL Server version that does not support streams will be pretty slow.

Try using a common table expression to build up your reference query before hitting the actual image data (I'm assuming you're using data blobs to store your image data - which should be as far down the query as possible).

Also use variables, or more CTE's, to hit your Multilingual_Attribute table.

If you're using an environment where CTEs are not available, you can use temp tables and index on IDWebImage, IDImageCategory, and IDImage.

\$\endgroup\$
1
\$\begingroup\$

The part of your query where you have several AND conditions on your joins is a bit messy, I would put those into your where statement, as it would be cleaner.

You should only join tables ON their Key columns and not ON other columns, it slows down your query because these other columns aren't indexed like a Primary or Foreign key.

You should also use Temp Tables, so that you don't have to call a SELECT inside of the table joining on the main query. This will also make the query faster because you can add indexes and keys where you need to in the temp table.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.