Want to Optimize the query - 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

Want to Optimize the query

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-18 : 00:55:20

Hi all,

i am selecting details from 5 tables, from first table i have select all details and from other tables some of the columns only.. i have written query like this i want to optimize it...

SELECT
Jou.Journal_ID,
Jou.Abbreviation,
Jou.About,
Jou.Created_By,
Jou.Created_Date,
Jou.Front_Code,
Jou.IsDeleted,
Jou.IsRejected,
Jou.ISSN,
Jou.ISSN_Electronic,
Jou.IsSuggested,
Jou.Journal_DOI,
Jou.Journal_Image,
Jou.Journal_Logo,
Jou.Journal_Name,
Jou.Journal_No,
Jou.Journal_Type_ID,
Jou.JournalStatus,
Jou.Lang_ID,
Jou.Login_User_ID,
Jou.Mission_Statement,
Jou.Modifed_Date,
Jou.Modified_By,
Jou.NLMID,
Jou.Pub_Type_ID,
Jou.Published_Year,
Jou.Short_Name,
Jou.Start_Year,
DFS.Field_ID,
DFS.Journal_DFS_ID,
DFS.Login_User_ID,
DFS.Speciality_ID,
DFS.Status,
DFS.Tax_ID,
DFS.Tier_ID,
ArtTyp.Article_Type_ID,
ArtTyp.Journal_Article_Type_ID,
ART.Article_Type_Name,
rep.Journal_Rep_ID,
rep.Repository_ID,
DFS.Domain_ID,
ArtTyp.Article_Type_ID,
rep.Journal_Rep_ID

FROM
FIS_Journal as Jou
INNER JOIN FIS_Journal_DFS AS DFS ON DFS.Journal_ID= Jou.Journal_ID
INNER JOIN FIS_Journal_ArticleType AS ArtTyp ON ArtTyp.Journal_ID= Jou.Journal_ID
INNER JOIN FIS_Journal_Repository as rep ON rep.Journal_ID= Jou.Journal_ID
INNER JOIN FIS_ArticleType AS ART ON ART.Article_Type_ID =ArtTyp.Article_Type_ID
WHERE
Jou.Journal_ID= @Journal_ID

Regards,
Divya

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 02:59:25

here's what i do:

1) Check the number of Logical I/Os. Then then you can compare any changes to make to see if this reduces - then you know you have made an improvement.

2) Look at the Query Plan. Check where INDEX SCAN is being used instead of INDEX SEEK, and also where TABLE SCAN is being used.

For each INDEX SCAN look at the Index which is being chosen. Is that the one you would expect? (Usually for poorly performing queries you will find that the Clustered PK has been used).

3) Try adding Indexes when they are not currently present / being used

4) Try changing indexes so that they better "cover" the query.

In particular try adding Indexes to all columns used in JOINs, and crieteria in the WHERE clause that are suitable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources