performance tuning - 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 tuning

Author

Topic

nandan
Starting Member

10 Posts

Posted - 2007-08-23 : 16:41:09

i just wanna know how to reduce the performance of the query
can any one pls help me in the gaining the performance of query
SELECT
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number

INTO #ActiveBK

FROM FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND tblReferral.CloseDate IS NULL
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)

GROUP BY
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 16:43:53

Do you have any indexes on tblBankruptcyInfo.MasterID and tblBankruptcyInfo.bk_Case_Number?
Are all the columns you are joining on Primary Keys or at least indexed?

IS the list of SuperClientVendorID coming from some other table or is it always hardcoded?

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

nandan
Starting Member

10 Posts

Posted - 2007-08-23 : 16:53:49

No Indexes are placed on the columns but index scans and index seeks are used when i came across execution plan when i executed the query.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

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

Where is it doing index scan? when was the last time all the tables in question were reindexed?
try doing an

UPDATE STATISTICS TableNAme WITH FULLSCAN

on each of the tables and see if it helps. Index scan is basically a table scan. so you want to avoid it. Index seek is good.

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

nandan
Starting Member

10 Posts

Posted - 2007-08-23 : 17:13:19

how to do update statistics and index scan is basically done ON FNFSDATAMART.COMPANIESID.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

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

I provides sample code i my previous post.

UPDATE STATISTICS TableName WITH FULLSCAN

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

nandan
Starting Member

10 Posts

Posted - 2007-08-23 : 17:40:05

ok dinakar i will try it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources