2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks