Results 1 to 3 of 3

Thread: Index Scanning

  1. #1
    Join Date
    Aug 2002
    Posts
    7

    Post Unanswered: Index Scanning

    Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

    The Following things are already done.

    DBCC DBREINDEX
    Sp_updatestats
    update statistics

    It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

    Is there anybod can explain this and please post a solution. ?

    Raj Sankar




    Statement 1.
    select * from rx_control where store_id = @store_id and date_added between '08/01/02' and '08/20/02'
    -------------------------------------------------------------------------------
    statement 2.
    ----------------------------------------
    declare @bdate as datetime
    declare @edate as datetime
    declare @rxid as int


    set @store_id = '52'
    set @rxid = '158315'
    set @bdate = '08/01/02'
    set @edate = '08/10/02'

    select * from rx_control where store_id = @store_id and date_added between @bdate and @edate
    -------------------------------------------------------------------------------

  2. #2
    Join Date
    Aug 2002
    Location
    india
    Posts
    41
    check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

    if still the problem persists send me the showcontig report for the respectve table if u can.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I had problem like this or very similar.
    Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
    The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
    How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
    You can try to use this idea.

    Good luck

Posting Permissions

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