Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jmillay
Starting Member
7 Posts |
 Posted - 2009-02-23 : 09:18:10
|
| on our 2000 production box, the following query:SELECT col1, col2, col3, [current] FROM tbl_stagingWHERE col3 = '0109' AND col1 + col2 + col3 NOT IN (SELECT col1 + col2 + col3 from tbl_FinanceValues)takes 10 seconds. on our 2005 dev box, it doesn't even finish. the longest i've let it run is up to 6 minutes. I realize this isn't the best way to be doing this (concatinating columns together to form a "key"). but, i'm just curious if anyone knows off hand why this would take so much longer. our 2005 box is much superior machine, higher processor, more RAM, so that's not it. the amount of records in the tables in question are the same. the index on the tables are the same (unless i'm missing some subtle difference). any ideas why this is happening? thanks for any help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-23 : 09:22:35
|
[code]SELECT s.Col1, s.Col2, s.Col3, s.[Current]FROM tbl_Staging AS sWHERE s.Col3 = '0109' AND NOT EXISTS (SELECT * tbl_FinanceValues AS i WHERE i.Col1 = s.Col1 AND i.Col2 = s.Col2 AND i.Col3 = s.Col3)[/code]
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
2000 subquery vs 2005 subquery