Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: Need help fine-tuning a query

    Hello,

    I need help fine-tuning a query.
    The tables, existing indexes, default indexes, and query itself include below,
    along with the Query Plan (the current I/O Cost is 753680).
    A restriction is that I cannot change the table design.
    I can force indexes, and add indexes.
    Having tested with all of the existing indexes, I've found that
    the default ones are the most efficient.
    They question is: what would be the best index to add and enforce?
    Please note the table sizes. They are very large (hundreds of millions).
    The query typically takes 70+ seconds to run as is.

    Thanks very much, chassegs

    Table: Accounts..Accounts (Size 766K+)
    ----------------------------------------
    AccountId (int)
    State (tinyint)
    AccountClassInd (char(1))

    Table: ProductSynonyms (Size 322M+)
    -------------------------------------
    ProductId (int)
    ProductSynonymType (tinyint)
    ProductSynonym (varchar(32))

    Table: ForexItems (Size 104M+)
    --------------------------------
    ValueDate (smalldatetime)
    ProductTypeId (tinyint)
    AccountId (int)
    ProductId (int)
    PSCode (char1)
    SpecialRecordId varchar(31)
    ProductId (int)
    ExternalItemId
    StructureId (int)
    ExternalItemType (tinyint),
    CurrencyCode1 (char(3))
    CurrencyAmount1 (money)
    CurrencyCode2 (char(3))
    CurrencyAmount2 (money)
    ExchangeRate (tinyint)
    ProductDate (smalldatetime)
    ValueDate (smalldatetime)

    SELECT PSCode, SpecialRecordId, t1.ProductId, t2.ProductSynonym, t3.AccountId, t3.AccountName, StructureId, ExternalItemType,
    CurrencyCode1, CurrencyAmount1 = convert( float, CurrencyAmount1 ),
    CurrencyCode2, CurrencyAmount2 = convert( float, CurrencyAmount2 ),
    ExchangeRate, ProductDate, ValueDate
    FROM ForexItems t1, ProductSynonyms t2, Accounts..Accounts t3
    WHERE t1.ValueDate >= '2011-03-15 12:00:00:00 AM'
    AND t1.State = 0
    AND t1.ProductTypeId != 4
    AND t1.AccountId IN (201, 10505, 74443, 6870, 142745, 95959)
    AND t1.ProductId = t2.ProductId
    AND t2.ProductSynonymType = 3
    AND t1.AccountId = t3.AccountId
    AND t3.State = 0
    AND t3.AccountClassInd != 'I'
    AND t2.ProductSynonym NOT LIKE 'TD%' `

    sp_helpindex Accounts..Accounts

    index_name index_description index_keys
    -------------------- --------------------------------------- -------------------
    AccountsIndex1 clustered, unique located on default AccountId, Version
    AccountsIndex2 nonclustered located on default AccountId, State
    AccountsIndex4 nonclustered located on default AccountName, State

    sp_helpindex ProductSynonyms

    index_name index_description index_keys
    --------------------- --------------------------------------- -----------------------------
    ProductSynonymsIndex2 nonclustered located on default ProductSynonym, ProductSynonymType
    ProductSynonymsIndex3 nonclustered, unique located on default ProductId, ProductSynonymType
    ProductSynonymsIndex1 clustered, unique located on default ProductSynonymId

    sp_helpindex ForexItems

    index_name index_description index_keys
    ----------------- ------------------------------------ --------------------------------------------------
    ProductIdIndex2 nonclustered located on default ValueDate, CompanyId, CurrencyCode, Version, State
    ProductIdIndex3 nonclustered located on default ProductDate, CompanyId, CurrencyCode, Version, State
    ForexItemsIndex4 nonclustered located on default CompanyId, RowCreation, ValueDate, State
    ForexItemsIndex5 nonclustered located on default RowCreation, State
    ForexItemIndex6 nonclustered located on default AccountId
    ProductIdIndex1 clustered, unique located on default ProductId, Version, State

    The default indexes being used (on the 3 tables respectively):

    index_name index_description index_keys
    ------------------ --------------------------------------- --------------------------------------------------
    AccountsIndex1 clustered, unique located on default AccountId, Version
    ProductSynonymsIndex3 nonclustered, unique located on default ProductId, ProductSynonymType
    ProductIdIndex2 nonclustered located on default ValueDate, CompanyId, CurrencyCode, Version, State

    QUERY PLAN:

    set showplan on
    set noexec on

    Command was executed successfully

    Warnings: --->
    W (1):
    W (2): QUERY PLAN FOR STATEMENT 1 (at line 1).
    W (3): Executed in parallel by coordinating process and 15 worker processes.
    W (4):
    W (5):
    W (6): STEP 1
    W (7): The type of query is SELECT.
    W (8): Executed in parallel by coordinating process and 15 worker processes.
    W (9):
    W (10): FROM TABLE
    W (11): ForexItems
    W (12): t1
    W (13): Nested iteration.
    W (14): Index : ProdIdIndex2
    W (15): Forward scan.
    W (16): Positioning by key.
    W (17): Keys are:
    W (18): ValueDate ASC
    W (19): Executed in parallel with a 15-way hash scan.
    W (20): Using I/O Size 16 Kbytes for index leaf pages.
    W (21): With LRU Buffer Replacement Strategy for index leaf pages.
    W (22): Using I/O Size 16 Kbytes for data pages.
    W (23): With LRU Buffer Replacement Strategy for data pages.
    W (24):
    W (25): FROM TABLE
    W (26): ProdSynonyms
    W (27): t2
    W (28): Nested iteration.
    W (29): Index : ProdSynonymsIndex3
    W (30): Forward scan.
    W (31): Positioning by key.
    W (32): Keys are:
    W (33): ProdId ASC
    W (34): ProdSynonymType ASC
    W (35): Using I/O Size 2 Kbytes for index leaf pages.
    W (36): With LRU Buffer Replacement Strategy for index leaf pages.
    W (37): Using I/O Size 2 Kbytes for data pages.
    W (38): With LRU Buffer Replacement Strategy for data pages.
    W (39):
    W (40): FROM TABLE
    W (41): Accounts..Accounts
    W (42): t3
    W (43): Nested iteration.
    W (44): Using Clustered Index.
    W (45): Index : AccountsIndex1
    W (46): Forward scan.
    W (47): Positioning by key.
    W (48): Keys are:
    W (49): AccountId ASC
    W (50): Using I/O Size 2 Kbytes for data pages.
    W (51): With LRU Buffer Replacement Strategy for data pages.
    W (52):
    W (53): Parallel network buffer merge.
    W (54):
    W (55): Total estimated I/O cost for statement 1 (at line 1): 753680.
    W (56):
    <---
    [Executed: 3/17/11 11:10:56 AM EDT] [Execution: 0ms]

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It's difficult withoiut having the data infront of you but try this:
    Code:
    select *
    into   #fx
    FROM   ForexItems t1
    WHERE  AccountId IN (201, 10505, 74443, 6870, 142745, 95959)
           and ProductTypeId != 4
           and State = 0
           and ValueDate >= '2011-03-15 12:00:00:00 AM'
    
    SELECT PSCode, SpecialRecordId, #fx.ProductId, t2.ProductSynonym, 
           t3.AccountId, t3.AccountName, StructureId, ExternalItemType,
           CurrencyCode1, CurrencyAmount1 = convert( float, CurrencyAmount1 ),
           CurrencyCode2, CurrencyAmount2 = convert( float, CurrencyAmount2 ),
           ExchangeRate, ProductDate, ValueDate
    FROM   Accounts..Accounts t3, ProductSynonyms t2, #fx
    WHERE  t2.ProductId = #fx.ProductId
           AND t2.ProductSynonymType = 3
           AND t2.ProductSynonym NOT LIKE 'TD%' `
           AND t3.AccountId = #fx.AccountId
           AND t3.State = 0
           AND t3.AccountClassInd != 'I'
    
    drop table #fx

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Hey thanks, mike_bike_kite!
    That reduces the I/O cost by roughly 75%!
    You rock!

    chassegs

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Just noticed you are using float for money values. It is not accurate and can produce unexpected results.

    Please read this article.

    Using the float Datatype Technote: Mobile Enterprise, Database Management - Sybase Inc

  5. #5
    Join Date
    Apr 2011
    Posts
    3

    Thumbs up

    The Query show that ur are using " not like ", " != " , " IN " function. These functions takes more lead time to find the extract data in the page.

    Temp tables are the best solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •