June 16, 2014 at 10:23 am
My query pulls data from two separate databases, and it runs extremely slow. The queries run well when they are pulling from their own database. When I join them to pull from both databases, it is sluggish. Any tips?
June 16, 2014 at 10:28 am
areed (6/16/2014)
My query pulls data from two separate databases, and it runs extremely slow. The queries run well when they are pulling from their own database. When I join them to pull from both databases, it is sluggish. Any tips?
Quick questions, are the databases on the same server instance? Are the same network protocols configured for both servers (if not on the same)? Is one reachable from the other? What is being used for pulling the data?
Any detail helps!
😎
June 16, 2014 at 10:30 am
They are on the same instance and easily linked together.
June 16, 2014 at 10:30 am
They also have indexes on the keys that I am linking them with too.
June 16, 2014 at 10:32 am
seequill (6/16/2014)
They also have indexes on the keys that I am linking them with too.
What are you using for pulling the data?
😎
June 16, 2014 at 10:35 am
Do you mean what tool? Mgt studio and via a sql job. Sorry if that is a really dumb answer.
June 16, 2014 at 10:59 am
seequill (6/16/2014)
Do you mean what tool? Mgt studio and via a sql job. Sorry if that is a really dumb answer.
No worries, perfect answer.
Can you share more information, query, execution plan etc.?
😎
June 16, 2014 at 11:09 am
Here is my query with identifying information removed.
SELECT'01' as [Record Type]
,fv.AccountNumber as [Account Number]
,g.GuarantorNameLast + ', ' + g.GuarantorNameFirst as [Guarantor Name]
,g.GuarantorAddress1 as [Guarantor Address 1]
,g.GuarantorAddress2 as [Guarantor Address 1]
,g.GuarantorCity as [Guarantor City]
,g.GuarantorState_MisStateProvID as [Guarantor State]
,g.GuarantorZip as [Guarantor Zip]
,g.GuarantorHomePhone as [Guarantor Phone]
,NULL as [Guarantor Date of Birth]
,NULL as [Guarantor SSN]
,''
,''
,''
,''
,''
,''
FROM [DatabaseA].[dbo].[FinData] as fd
LEFT JOIN [DatabaseA].[dbo].[FinVisit] as fv
ON fv.BillingID = fd.BillingID AND fv.SourceID = fd.SourceID
LEFT JOIN [DatabaseB].[dbo].[RegMain] as rm
ON (rm.VisitID = fv.VisitID OR rm.AccountNumber = fv.AccountNumber) AND (rm.SourceID = fv.SourceID)
LEFT JOIN [DatabaseB].[dbo].[Guarantor] as g
ON g.PatientID = rm.PatientID AND g.SourceID = rm.SourceID
WHERE fd.Balance > 0
AND fv.AccountNumber IS NOT NULL
AND fd.CollectionAgency = 'TEST'
ORDER BY fv.AccountNumber
June 16, 2014 at 11:16 am
I have it in a Stored Procedure that pulls 7 days of data. I'm thinking that my best bet is to populate two temp tables and pull from the temp tables. What do you think? Thank you so much for your time!
June 16, 2014 at 4:57 pm
seequill (6/16/2014)
They also have indexes on the keys that I am linking them with too.
Verify that those indexes are actually being used; an estimated query plan is enough for
You could also move one condition from the WHERE to the join, which might filter rows out more quickly giving you better overall performance:
that.
SELECT '01' as [Record Type]
,fv.AccountNumber as [Account Number]
,g.GuarantorNameLast + ', ' + g.GuarantorNameFirst as [Guarantor Name]
,g.GuarantorAddress1 as [Guarantor Address 1]
,g.GuarantorAddress2 as [Guarantor Address 1]
,g.GuarantorCity as [Guarantor City]
,g.GuarantorState_MisStateProvID as [Guarantor State]
,g.GuarantorZip as [Guarantor Zip]
,g.GuarantorHomePhone as [Guarantor Phone]
,NULL as [Guarantor Date of Birth]
,NULL as [Guarantor SSN]
,''
,''
,''
,''
,''
,''
FROM [DatabaseA].[dbo].[FinData] as fd
LEFT JOIN [DatabaseA].[dbo].[FinVisit] as fv
ON fv.BillingID = fd.BillingID AND fv.SourceID = fd.SourceID
LEFT JOIN [DatabaseB].[dbo].[RegMain] as rm
ON (rm.VisitID = fv.VisitID OR rm.AccountNumber = fv.AccountNumber)
AND fv.AccountNumber IS NOT NULL /*moved to join from WHERE*/
AND (rm.SourceID = fv.SourceID)
LEFT JOIN [DatabaseB].[dbo].[Guarantor] as g
ON g.PatientID = rm.PatientID AND g.SourceID = rm.SourceID
WHERE fd.Balance > 0
AND fd.CollectionAgency = 'TEST'
ORDER BY fv.AccountNumber
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply