Aanvik
asked on
SQL Server Query Help
Hello,
I am running 1 query against SQL Server 2005 and Its taking too much time to return and Eating up all the CPU. The problem with the SQL is we have 2 different database on 1 server and doing a Join and query looks something like this...
and seems like A.TNId = B.NPA + B.DBB + B.RDV is causing all the trouble.
Can someone recommend any better approach to handle this without making changes to db structure? B is really big table with some million records in it.
I am running 1 query against SQL Server 2005 and Its taking too much time to return and Eating up all the CPU. The problem with the SQL is we have 2 different database on 1 server and doing a Join and query looks something like this...
and seems like A.TNId = B.NPA + B.DBB + B.RDV is causing all the trouble.
Can someone recommend any better approach to handle this without making changes to db structure? B is really big table with some million records in it.
Select Distinct TNid from db1.dbo.Table A , Db2.dbo.Table1 B where
A.CreateDate > b.CreateDate
And A.TNId = B.NPA + B.DBB + B.RDV
Bob Hoffman
do you have indexes built on the fields in your where clause?
ASKER
yes, I have non clustered index on these columns.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It doesn't appear that you are using a Join statement. This can really cause things to slow down.
Try the code in the attached snippet.
Try the code in the attached snippet.
Select Distinct TNid from db1.dbo.Table A
JOIN Db2.dbo.Table1 B ON And A.TNId = B.NPA + B.DBB + B.RDV
where A.CreateDate > b.CreateDate
Oop, I left the And in. Let's try that again.
Select Distinct TNid from db1.dbo.Table A
JOIN Db2.dbo.Table1 B ON A.TNId = B.NPA + B.DBB + B.RDV
where A.CreateDate > b.CreateDate
Does TNid appear more then once in table 'A'? If not get rid of the "Distinct". If it does can they have a different CreateDate?
ASKER
Thx