Slow Query
-
select distinct a.empid
from ViewEmpployee a inner join #Empdates b on a.empid=b.empid,#researchtab
where EmmJobCode in( '435','767','974') or
(Job1 = '5' or Job2 = '5'or Job3 ='5' or Job4 = '5' or Job5 = '5')
is there other t-sql methods to get this query faster ?
-
Tara-1044200
To receive some help please help us to help you. Read both the articles in my signature block and post the requested information. Other wise the only answer that can be given to you is "That depends"
-
Tara-1044200 (10/16/2009)
select distinct a.empid
from ViewEmpployee a inner join #Empdates b on a.empid=b.empid,#researchtab
where EmmJobCode in( '435','767','974') or
(Job1 = '5' or Job2 = '5'or Job3 ='5' or Job4 = '5' or Job5 = '5')
is there other t-sql methods to get this query faster ?
How does #researchtab relate to #Empdates or ViewEmpployee? If it doesn't you'll get a cross join. Can you post some DDL and sample data.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Tara-1044200 (10/16/2009)
select distinct a.empid
from ViewEmpployee a inner join #Empdates b on a.empid=b.empid,#researchtab
where EmmJobCode in( '435','767','974') or
(Job1 = '5' or Job2 = '5'or Job3 ='5' or Job4 = '5' or Job5 = '5')
is there other t-sql methods to get this query faster ?
Even without properly aliasing the columns so we know which columns go with which table, from what I can see, you have a non-declared CROSS JOIN in the code which will, of course, eat the face right off any performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff , could you please breif me about non declared cross join. how could i declare in my query?
-
I was in a hurry so I didn't read Mark's post. He basically said the same thing that I did before I did.
Here's the code you posted...
select distinct a.empidfrom ViewEmpployee a inner join #Empdates b on a.empid=b.empid,#researchtab
where EmmJobCode in( '435','767','974') or
(Job1 = '5' or Job2 = '5'or Job3 ='5' or Job4 = '5' or Job5 = '5')
Notice that you have a nice join between "a" and "b"... no problem there. But what are you joining on #researchtab? Even if the EmmJobCode or the various Jobx columns are in the #researchtab table, they're all constants that you refer to... not columns in other tables.
What the means is that you have a "cross join" on the #researchtab. In other words, for whatever result set the join on "a" and "b" returns is, every row will be matched up with all the rows in #researchtab. If the "a" and "b" join returns 100 rows and the #researchtab has 200 rows, the entire result will be the Cartesian Product or 100x200 rows which will be 20,000 rows with lots and lots of duplicates on a.empid... that's why you ended up having to use DISTINCT which only masks the problem in this case.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply