SQL Server Performance Forum – Threads Archive

How to tune this query?

Hi, I try to tune a slow query: SELECT 1 FROM tab1
WHERE col1 = ?
AND col2 = ?
AND col3 = ?
AND (SELECT COUNT(col1) FROM tab2 a
WHERE a.col4 <> ’10’
AND a.col5 IN (‘AB’, ‘BC’, ‘CD’)
AND a.col1 <> tab1.col1
AND a.col5 = (SELECT b.col5 FROM tab2 b WHERE b.col1 = tab1.col1)
AND EXISTS (SELECT 1 FROM tab1 c
WHERE c.col1 = a.col1
AND c.col2 = 1)
) = 0 tab2 is parent of tab1 (1 to many) The problem is the first part of the query: SELECT 1 FROM tab1
WHERE col1 = ?
AND col2 = ?
AND col3 = ? is fixed and can’t be changed, in the rest of the query, I have to use tab2 two times and tab1 another time, how can I make this query runs faster?

I’ve moved to relevant forum.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Can you be a bit more specific what "slow" means?
How many rows are we talking about?
What are your indexes? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Hi You can create index CREATE INDEX tab1_Index on tab1 (col1,col2,col3) ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

Give SQL Optimizer for Visual Studio a Try. It will rewrite your SQL and find the best performing method to run the SQL. You can download a trial at http://www.extensibles.com/modules.php?name=Products TheRelentlessOne
The Relentless One
No Bug says alive http://www.extensibles.com

]]>