Indexing a Large Table

  • Rofty

    SSCrazy

    Points: 2585

    Hey you all

    Sorry about the delay, internet problem.

    Right!

    To answer all your questions:

    Gail: I have attached screenshot of the table structure, and execution plan for the following query

    [font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4[/font]

    Shriji: Due to the design of the table, queries will always only use datekey and instrumentkey for where clauses.

    Stef: There are statistics running for the composite key (datekey, instrumentkey).

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

  • Rofty

    SSCrazy

    Points: 2585

    Just to add to all the info

    Me and my System Administrator had a look at the server that runs SQL Server.

    The Virtual Memory is set to 2GB out of the 4GB physical and the process of SQLServer is allready running at 1.7GB under normal circumstances.

    In SQL Server itself the Max Memory usage is set to 2.4GB

    FYI

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

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    Plan looks okay to me and the query also looks like to have completed in 5 seconds. Looks like ur server as a whole isn't healthy..If you want optimize this query further we need to see the udf used

    But as I said earlier, this query is unlikely to kill ur server.

    Please start your investigations as mentioned on my earlier post.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    roelofsleroux (2/2/2010)


    Gail: I have attached screenshot of the table structure, and execution plan for the following query

    A picture of the execution plan is near-useless. It's just a pretty picture without all the information that's in the properties and tooltips, which is what is really important. The article I referred you to explains exactly how to post an execution plan.

    I also can't copy-paste text from a screenshot of a table structure. The Create table and Create index statements would be nice.

    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
  • Rofty

    SSCrazy

    Points: 2585

    dbo.fnLKPInstrumentExchangeKey is used to find the ExchangeKey of an instrument whether it is JSE, LSE, NYSE.

    Its basically to avoid JOIN statements. I have a suspicion that it could have and effect, but even if I change the query to the following it still has no better affect.

    [font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE InstrumentKey >= 1001

    [/font]or[font="Courier New"]

    SELECT tblDailyData.* FROM DATAS.tblDailyData

    INNER JOIN DATAS.tblInstrument ON ...

    WHERE ExchangeKey = 4[/font]

    [font="Courier New"]ALTER FUNCTION [dbo].[fnLKPInstrumentExchangeKey]

    (@InstrumentKey INT)

    /****** Returns the specied Instrument's Exchange Key ******/

    RETURNS INT

    AS

    BEGIN

    DECLARE @ExchangeKey INT

    SELECT @ExchangeKey = ExchangeKey FROM DATAS.tblInstrument WHERE InstrumentKey = @InstrumentKey

    RETURN ISNULL(@ExchangeKey,0)

    END[/font]

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

  • Rofty

    SSCrazy

    Points: 2585

    GilaMonster (2/2/2010)


    roelofsleroux (2/2/2010)


    A picture of the execution plan is near-useless. It's just a pretty picture without all the information that's in the properties and tooltips, which is what is really important. The article I referred you to explains exactly how to post an execution plan.

    I also can't copy-paste text from a screenshot of a table structure. The Create table and Create index statements would be nice.

    I'm running query now and will post the SAVED execution plan as soon as its done.

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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Why are the queries using SELECT *? Do you really need every single column from the table?

    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
  • Rofty

    SSCrazy

    Points: 2585

    Not necessarily. Usually the CL column is the only one 60% of our users are interested in. But the more advanced users prefer to use Op, Cl, Hi, Lo, VWAP and Vol for statistical analysis.

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

  • Rofty

    SSCrazy

    Points: 2585

    Execution Plan for Query:

    SELECT * FROM DATAS.tblDailyData

    WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4 AND DateKey = 20100101

    I had to to make the where clause tak date into account otherwise it would have ran for over 20min.

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

  • Shriji

    SSCommitted

    Points: 1881

    roelofsleroux (2/2/2010)


    dbo.fnLKPInstrumentExchangeKey is used to find the ExchangeKey of an instrument whether it is JSE, LSE, NYSE.

    Its basically to avoid JOIN statements. I have a suspicion that it could have and effect, but even if I change the query to the following it still has no better affect.

    [font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE InstrumentKey >= 1001

    [/font]or[font="Courier New"]

    SELECT tblDailyData.* FROM DATAS.tblDailyData

    INNER JOIN DATAS.tblInstrument ON ...

    WHERE ExchangeKey = 4[/font]

    [font="Courier New"]ALTER FUNCTION [dbo].[fnLKPInstrumentExchangeKey]

    (@InstrumentKey INT)

    /****** Returns the specied Instrument's Exchange Key ******/

    RETURNS INT

    AS

    BEGIN

    DECLARE @ExchangeKey INT

    SELECT @ExchangeKey = ExchangeKey FROM DATAS.tblInstrument WHERE InstrumentKey = @InstrumentKey

    RETURN ISNULL(@ExchangeKey,0)

    END[/font]

    By looking at the function code, to me, the ISNULL step is the killer. I may be wrong but we are talking about running a condition millions of times....

  • Jeff Moden

    SSC Guru

    Points: 997353

    roelofsleroux (2/2/2010)


    Its basically to avoid JOIN statements.

    It may be convenient to use but all you've done is avoid having to write JOIN statements. It's still doing a join of sorts but with the extreme overhead of a scalar function that accesses data in a table. It's a form a RBAR that you could do without.

    I would modify the code to do a proper join and remove this function from my DB as a start. I'd also be on the lookout for other such RBAR problems.

    --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)

  • Rofty

    SSCrazy

    Points: 2585

    You are right Jeff. removing the RBAR UDF from the query and using a simple join does make the query almost 60% faster.

    Just to clarify the use of those look-up UDF's, their mostly used in T-SQL procedures to look-up values from tables with writing a repetitive SQL to look-up values. They are not normally used in queries.

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

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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    roelofsleroux (2/2/2010)


    Execution Plan for Query:

    SELECT * FROM DATAS.tblDailyData

    WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4 AND DateKey = 20100101

    I had to to make the where clause tak date into account otherwise it would have ran for over 20min.

    There's nothing that can be done for this query as it's written. The clustered index is on date and the function's not SARGable, hence there's no index in existence that will help this further as it's currently written.

    What does that function do and can it be removed and replaced with appropriate joins and SARGable conditions?

    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
  • Rofty

    SSCrazy

    Points: 2585

    Gila like I stated to Jeff, the function is not a must. See it as n lazy and resource expensive way of writing the query. The join is faster than the rbar function ... clearly.

    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.

    But i still have to know:

    Will increasing the Memory and Processor settings of a SQL Server Instance by any means help prevent the stalling of SQL Server due to a query based on a table with millions of records. A table that will probably increase in size on a daily basis.

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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    roelofsleroux (2/2/2010)


    Will increasing the Memory and Processor settings of a SQL Server Instance by any means help prevent the stalling of SQL Server due to a query based on a table with millions of records. A table that will probably increase in size on a daily basis.

    Possibly a little, however...

    Hardware upgrades only fix performance problems if the root cause of those problems is hardware bottleneck. If it is poor queries and/or poor indexes, hardware upgrades will help minimally or even not at all. In fact, I've seen a DB decrease in performance after a hardware upgrade.

    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

Viewing 15 posts - 16 through 30 (of 35 total)

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