Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance

Author

Topic

nandan
Starting Member

10 Posts

Posted - 2007-08-23 : 17:38:15

i need help in gaining the performance of this query
SELECT
tblSuperClientFile.ClientRefNo,
tblReferral.RefID,
tblRail.RailDescr,
tblReferral.SuperClientVendorID,
tblVendor.VendorName AS Client,
tblReferral.AssignedVendorID,
tblReferral.ReferralDate,
tblSpikeDate.DateCompleted AS PlanRevCompleted,
tblReferral.CloseDate,
tblCloseReason.CloseReason,
tblBankruptcyInfo.BK_Filing_State,
tblBankruptcyInfo.BK_Case_Number

INTO #PlanRev

FROM FNFBSDataMart.dbo.tblSpikeDate tblSpikeDate WITH (NOLOCK)
INNER JOIN #ActiveBK
ON tblSpikeDate.MasterID = #ActiveBK.MasterID
AND tblSpikeDate.FID = 3160
AND tblSpikeDate.DateCompleted <= GetDate()-5
INNER JOIN FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
ON tblReferral.RefID = tblSpikeDate.RefID
AND tblReferral.ReferralDate >= GetDate()-180
AND tblReferral.AssignedVendorID NOT IN (188,1721)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND #ActiveBK.bk_Case_Number = tblBankruptcyInfo.bk_Case_Number
INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK)
ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID
AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867)
INNER JOIN FNFBSDataMart.dbo.tblRail tblRail WITH (NOLOCK)
ON tblReferral.RailID = tblRail.RailID
INNER JOIN FNFBSDataMart.dbo.tblVendor tblVendor WITH (NOLOCK)
ON tblReferral.SuperClientVendorID = tblVendor.VendorID
INNER JOIN FNFBSDataMart.dbo.tlkpState tlkpState WITH (NOLOCK)
ON tblSuperClientFile.StateID = tlkpState.StateID
AND (tblSuperClientFile.SuperClientVendorID <> 1820
OR tlkpState.Abbrev NOT IN ('AZ','AK','CA','HI','ID','NV','OR','TX','UT','WA'))
LEFT OUTER JOIN FNFBSDataMart.dbo.tblCloseReason tblCloseReason WITH (NOLOCK)
ON tblReferral.CloseReaID = tblCloseReason.CloseReaID

can anyone have a look at it and give me a feed back asap

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 17:48:57

The suggestion over here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88344
still holds good. You might want to update stats on the tables then check the query plan again.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources