Post reply

Optimize query in 2005

  • May 12, 2010 at 9:21 am

    #386915

    Hi,

    I am running query in sql 2005 and its taking tool ong to finish.

    I have to run query for my monthly report and its pulling data from four table which i am joining by pk/fk.

    I have currently PK Index on PK columns.

    Could you please guide me how can i optimize the query as i am joining table just simply by PK/FK columns without any other where conditions?

    Should I create on Index on FK columns which will boost up the query performance?

    We are mostly inserting data everyday.

    Please let me know if you want query definition or table structures but i am joining tables by PK/FK relationship and i am selecting all the columns from all the tables.

    Appreciate your help and feedback!

    Thanks,

    Poratips

  • May 12, 2010 at 9:26 am

    #1166315

    Please see this article

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


    Clear Sky SQL
    My Blog[/url]

  • May 12, 2010 at 9:33 am

    #1166322

    There is very simple answer to your question:

    To increase your query performance you just optimize it using well known query optimisation techinques.

    I do hope my detailed answer will help you to solve the issues with your query.

    If you would like more "general" answer, please provide the table structure (incl. indexes), query you're runing and the actual query execution plan you got.

    Ok, you've mentioned that you do a lot of inserts into the table used in the query. Try to UPDATE STATISTICS [TableName] after inserting large chunks of data and before running any query on it.

    Cheers,

    Me

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 12, 2010 at 7:12 pm

    #1166593

    Query:

    select A.DTrackID, A.DVersion, A.Version, A.Locale, A.OS, A.OSLocale, A.CId,

    B.LId, B.LType, B.PLCount, B.PJCount,

    C.PId, C.Model, C.Local,

    D.SId, D.Starttime, D.Stoptime, D.Source

    from

    dbo.DTrack A, dbo.LTrack B,

    dbo.PTrack C, dbo.STrack D

    where A.DTrackID = D.DTrackID

    and D.SId = C.SId

    and C.PId = B.PId

    Table Structure:

    ===============

    Dtrack Table:

    -------------

    DtrackId - PK and Cluster Index

    DVersion

    Version

    Locale

    OS

    OSLocale

    CID

    LTrack Table:

    -------------

    LID - PK and Cluster Index

    PID - FK reference to Ptrack Table

    Ltype

    PLcount

    PJcount

    PTrack Table:

    -------------

    PID - PK and Cluster Index

    SID - FK reference to Strack Table

    Model

    Local

    Strack Table:

    --------------

    SId - PK and Cluster Index

    DtrackID - FK reference to Dtrack Table

    StartTime

    Stoptime

    Source

    I will post the execution plan later as i need to run the query again.

    It runs for almost 45 minutes.

    file size is approximate: 1066340 KB

    Table counts:

    Table_NameNumber_of_Rows

    Dtrack204198

    LTrack2362770

    PTrack2287311

    STrack2604331

    Thanks for your help!

  • May 13, 2010 at 4:40 am

    #1166754

    First of all. You should better use JOIN for joining tables:

    select A.DTrackID, A.DVersion, A.Version, A.Locale, A.OS, A.OSLocale, A.CId,

    B.LId, B.LType, B.PLCount, B.PJCount,

    C.PId, C.Model, C.Local,

    D.SId, D.Starttime, D.Stoptime, D.Source

    from dbo.DTrack A

    JOIN dbo.STrack D ON A.DTrackID = D.DTrackID

    JOIN dbo.PTrack C ON D.SId = C.SId

    JOIN dbo.LTrack B ON,C.PId = B.PId

    Second, and most important: your foreign keys are not automaticaly indices! Can you tell us please if you have indices created for your FK columns? If not then I can gurantee that you will have table scans in your execution plan and that makes your query slow a bit :-D. Don't forget UPDATE STATISTICS on tables when you insert a lot of data into them.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 13, 2010 at 5:14 am

    #1166764

    Full table definitions (the create table statement) and index definitions please (all indexes). See the article that Dave listed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 13, 2010 at 8:43 pm

    #1167282

    Thanks for your JOIN query suggetion and Index tips.

    I know from oracle that we should create index on FK columns but i was little confuse for Sql server.

    I will use your query for JOIN and let you know.

    I do agree with you that FK column index will help.

    What kind of index you are suggestion to create on FK columns?

    Thanks for your help!

  • May 14, 2010 at 4:07 am

    #1167404

    There is no straight answer to this. Its depends on many factors.

    For begining you can just add non-clustered indices for all these columns.

    And see if the query will start using them instead of table scans.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 21, 2010 at 5:09 am

    #1170715

    Thanks for your tips.

    Are you suggesting to add indexex on all the select columns or all the FK columns?

  • May 21, 2010 at 9:45 am

    #1170925

    1st create indices for you FK columns.

    Adding indices for all columns used in a query also would probably increase performance of your select, but think about insert operation... Read something about "covering index" for SQL Server

    Cheers,

    Me

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 24, 2010 at 3:59 pm

    #1171801

    Thanks.

    You are right, i don't want to create index on other columnd because it wil slow down my Inert as i have mostly Insert activity.

    Thanks for help!

  • May 24, 2010 at 11:20 pm

    #1171847

    poratips (5/24/2010)

    You are right, i don't want to create index on other columnd because it wil slow down my Inert as i have mostly Insert activity

    And you've tested and measured and found that the impact of an index on the inserts is unacceptable?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 24, 2010 at 11:40 pm

    #1171852

    Still waiting for FULL DDL and execution plan


    Clear Sky SQL
    My Blog[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply