March 8, 2007 at 4:59 am
I've got a problem with a specific query, which when running on the exact database, is super quick on SQL 2000, and takes forever on SQL 2005.
The problem seems to arise from the concatenation done in the where statement.
select *
from [users]
where
[users].[name] + '|' + convert(nvarchar,[users].[age])
not in (select [listing].[name] + '|' + convert(nvarchar,[listing].[age]) from [listing])
This happens on all MSSQL 2005 servers I've tested it on.
Any ideas?
Robert
March 8, 2007 at 6:40 am
Robert,
That query is never going to run that nicely you are better off rewriting it slightly. I persume you want all of the users in users (primary key name, age) who have no entries in listing?
Somthing like this is likely to run better:
SELECT *
FROM [users]
LEFT JOIN [listing]
ON [users].[name] = [listing].[name]
AND [users].[age] = [listing].[age]
WHERE [users].[name] IS NULL
- James
--
James Moore
Red Gate Software Ltd
March 8, 2007 at 7:14 am
Ok, that is probably the better way of doing it.
Probelm solved.
Thanks for your help.
Robert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply