Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    9

    Unanswered: Regarding Index utilization

    Hi,

    I am facing one problem in the following Query. Following query is not utilizing the full index. we have index on AbcValues (dtEffective, lCategoryId, lOrg, lPortfolioId, sSource, sCcyCode) table. It is only utilizing dtEffective. But not all index columns.
    When I do force plan the query is utlizing full index. Is there any way the following query can utilize the full index without forcing the index. Any help is appreciated.

    CREATE TABLE #abc
    (
    lOrg int NULL,
    sCcyCode char(3) NULL,
    lCategoryId int NULL,
    sSourceAdj varchar(10) NULL
    )

    INSERT INTO #MaxRowId
    (
    lOrg,
    lCategoryId,
    sSourceAdj,
    sCcyCode,
    RowID
    )
    SELECT
    ABV.lOrg,
    ABV.lCategoryId,
    ABV.sSource,
    ABV.sCcyCode2,
    MAX(RowID)
    FROM
    AbcValues ABV,
    #abc ABC
    WHERE
    ABV.dtEffective >= @dtPrevMe
    AND ABV.dtEffective <= @dtCob
    AND ABV.sSource = ABC.sSourceAdj
    AND ABV.lCategoryId = ABC.lCategoryId
    AND ABV.lOrg = ABC.lOrg
    AND ABV.sCcyCode = ABC.sCcyCode
    AND ABV.bIsAdjustment = 1
    AND ABV.bIsManual = 1
    AND ABV.lPortfolioId = -1
    GROUP BY
    ABV.lOrg,
    ABV.lCategoryId,
    ABV.sSource,
    ABV.sCcyCode2

    Thanks,
    Arun

  2. #2
    Join Date
    Jan 2004
    Posts
    19

    Question

    Please correct me if i am wrong but IMHO either a query will utilize an index or not utilize it(no question of column level utilisation).So if it is utilising dtEffective (which i assume is first column of the index) so it is utilising the defined index.Please correct me if i am missing something here.

  3. #3
    Join Date
    Feb 2004
    Posts
    9

    Regarding Index utilization

    Originally posted by Andy2004
    Please correct me if i am wrong but IMHO either a query will utilize an index or not utilize it(no question of column level utilisation).So if it is utilising dtEffective (which i assume is first column of the index) so it is utilising the defined index.Please correct me if i am missing something here.
    Andy,

    You are correct. This query is picking only the first column of the defined index. Becuase of this I am facing performance issue. If I force the same index this is only taking 2000ms elapsed time. But if I remove forcing it is taking more than one hour. Could you please tell what could be the problem. Should I include RowId also in the index. Will is solve my problem. or is there any way to change this range query.

    Thanks,
    Arun

  4. #4
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97

    Re: Regarding Index utilization

    Originally posted by arrunkumaar
    I am facing one problem in the following Query. Following query is not utilizing the full index. we have index on AbcValues (dtEffective, lCategoryId, lOrg, lPortfolioId, sSource, sCcyCode) table. It is only utilizing dtEffective. But not all index columns.
    When I do force plan the query is utlizing full index. Is there any way the following query can utilize the full index without forcing the index. Any help is appreciated.

    SELECT
    ... FROM
    AbcValues ABV,
    #abc ABC
    WHERE
    ABV.dtEffective >= @dtPrevMe
    AND ABV.dtEffective <= @dtCob
    AND ABV.sSource = ABC.sSourceAdj
    AND ABV.lCategoryId = ABC.lCategoryId
    AND ABV.lOrg = ABC.lOrg
    AND ABV.sCcyCode = ABC.sCcyCode
    AND ABV.bIsAdjustment = 1
    AND ABV.bIsManual = 1
    AND ABV.lPortfolioId = -1
    ...
    Arun
    If dtEffective does not appear in the conditions
    with an equal operator (=), it will be the only
    index column used.

    Mi advice is to review every SQL statement
    accessing AbcValues, then decide whether you
    can afford altering the order of the columns in
    the index. The new order should have one or
    more columns used in equality comparisons
    (lCategoryId, lOrg, lPortfolioId, sSource, sCcyCode)
    placed before dtEffective.

    Regards,
    Mariano Corral

  5. #5
    Join Date
    Jan 2004
    Posts
    19
    Arun,
    What i meant to say was as per my knowledge sybase only takes the index into consideration if it is able to compare on the first column of the index defined.The problem here appears that your query is not utilising the index itself if you are not forcing it(you can check this by executing set showplan on).Can you run update statistics on the table and then check if the problem is due to incorrect stats.If you include rowid also then also it will not help(won't be able to use index covering) as there are other columns in the select statement as well as SARGS which are not part of index.
    Cheers

Posting Permissions

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