Indexing a Large Table

  • Gail Shaw

    SSC Guru

    Points: 1004484

    roelofsleroux (2/2/2010)

    As far as your opinion on the indexes goes, I'm pretty much happy with your statement that the Index on the composite key should be sufficient.

    I did not say that.

    I said that the cluster is on the date, the query is doing a clustered index seek and with the current way the query is written there is no way to get it better. That does not in the slightest imply that the index is sufficient, just that no indexing is going to help unless the query is rewritten.

    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
  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104


    You say, if you don't use the date parameter query takes longer time. It means query takes the data set correctly to get the rows. So, the reason for taking about 5 minutes to process 8000 rows could be the function you are using. Is that function in turn, access much data? If so, you will make 8000 calls to determine the outcome. You may have to rewrite the function or if those values are static per stock, move to a table and join with that table.

    Prithiviraj Kulasingham

  • Rofty


    Points: 2585

    Sorry GilaMonster, miss use of words. But i do get what you said.

    Happens when I have 100s of things to do at the same time. 😀

    [font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
    😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀

  • Jeff Moden

    SSC Guru

    Points: 997111

    roelofsleroux (2/2/2010)

    Thank for the head-up. But will this explain the reason why my SQL server over-consumed hardware resources and ended up not responding?

    If you have a lot of code or code that is used often written in the same fashion, you bet'cha.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • arr.nagaraj


    Points: 6518

    /*Thank for the head-up. But will this explain the reason why my SQL server

    over-consumed hardware resources and ended up not responding? */

    I guess I answered that sometime back.

    Any investigation would start in this order -> Task manager->Perfmon->Sysprocesses->Queries.

Viewing 5 posts - 31 through 35 (of 35 total)

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