Indexing a Large Table

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

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

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

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

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

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

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

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

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


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

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

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

  • 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 34 total)

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