SQL Server Performance Forum – Threads Archive

Faster "select" and "or" from 3 tables

How can I make this query run faster? No indexes is created.
SELECT DISTINCT P.Category, p.subcategory, p.subcategory2, C.Category, sc.subcategory, sc2.subcategory2
FROM Products P, Category C, subcategory sc, subcategory2 sc2
WHERE ((p.category=c.id and p.subcategory=0)
OR (p.category=c.id and p.subcategory=sc.id and p.subcategory2=0)
OR (p.category=c.id and p.subcategory=sc.id and p.subcategory2=sc2.id) )
ORDER BY C.Category, sc.subcategory, sc2.subcategory2 The query takes about 20 seconds to run and 567 rows is returned.
The tables:
products
id | name | … | category | subcategory | subcategory2
1 | A product with a category only | … | 1 | 0 | 0
2 | A product with a category and a subcategory | … | 1 | 1 | 0
3 | A product with a category and another subcategory | … | 1 | 2 | 0
4 | A product with a category, a subcategory as product #2, and a subcategory2 | … | 1 | 1 | 1 7378 rows in total
category
id | category
1 | Clothes 40 rows in total subcategory
id | subcategory
1 | Jackets
2 | Socks 699 rows in total subcategory2
id | subcategory2
1 | Winter 1 row Thanks.

A start might be to run the Index Tuning Wizard and see what it suggests.
Am I reading right, that there are even no indexes on the joined columns? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Ooh, I forgot.
Be explicite in your JOINs and move the conditions away from the WHERE cluase into a JOIN…ON… —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

And Also you have said that there are no indexes. run the ITW and create indexes

remove the order by clause and do the sorting at the client.
But as said by others, 1st step is to run ITW.

Actually I think RDBMS like SQL Server are very strong at sorting. So I would rather leave this to the db than the client. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

quote:Originally posted by FrankKalis Actually I think RDBMS like SQL Server are very strong at sorting. So I would rather leave this to the db than the client.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃÂ²â€œÃÂºÂ¥ PASS Deutschland e.V. http://www.sqlpass.de)

I feel frank there can be improvemenet by removing order by especially when order is on varchar fields.
i have seen many times significant boost by removing order by clause.

Sure, you have to decide if you really *need* a sorted resultset or not. If you simply want to take the resultset and display it at the client, I would say, do the sorting at the server. However, when you need to process the resultset at the client first before you display it, there is really no need for a sorting on the server. So, I’d say this depends on your special situation and cannot be generalized. But I think, we should now wait for the original questioner to reply. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>