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.
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

Open in new window

LVL 5
AanvikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob HoffmanDeveloperCommented:
do you have indexes built on the fields in your where clause?
0
AanvikAuthor Commented:
yes, I have non clustered index on these columns.
0
Aneesh RetnakaranDatabase AdministratorCommented:
test it after creating an include index   createDate Include (NPA, DBB, RDV)

or else if you can alter the table, add a computed column for  B.NPA + B.DBB + B.RDV and index createdDate (newComputedColumn )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TechSingerCommented:
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.
 

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

Open in new window

0
TechSingerCommented:
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

Open in new window

0
Bob HoffmanDeveloperCommented:
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?
0
AanvikAuthor Commented:
Thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.