Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: table scan using getdate()

    I'm working on the following query on Sybase ASE 12.5.4:

    select distinct t2.pay_method_number "Cnumber",
    t1.order_number,
    t4.ship_company_name,
    t2.method_of_payment,
    t5.amount 'GC Amt$'

    from order_header t1, payment_header t2, shipping_detail t3, shipping_header t4, payment_detail t5

    where t1.order_number = t2.order_number
    and t1.order_number = t4.order_number
    and t2.order_number=t5.order_number
    and t2.payment_number=t5.payment_number
    and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
    and t3.date_shipped < dateadd(dd,0,"11/15/11")
    and t1.order_number=t3.order_number
    and t1.source_key = "RNWHUMC"
    and t1.order_status not in ("H", "SH", "DE")
    and t2.method_of_payment = "GC"
    and t1.customer_number != XXXXXX

    when in this form it returns the proper record in 1 sec.

    if I change the following from:
    and t3.date_shipped >= dateadd(dd,-1,"11/15/11")

    and make it:
    and t3.date_shipped >= dateadd(dd,-1,getdate())

    the query never returns. Show plan using the "11/15/11" show indexes being used on all the tables. When I change the date to getdate() the plan shows a table scan on a very large table. And it's not the t3 table, but the t1 table.

    Any insight is greatly appreciated.
    Eric

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    When last did you update statistics?
    Try running "update index statistics" on the tables

    PS. Your query might not work as expected as getdate() include time, I suggest truncate the time with
    dateadd(dd, datediff(dd, '' ,getdate()), '')
    dateadd(dd, datediff(dd, '' ,getdate())-1, '')

  3. #3
    Join Date
    Nov 2011
    Posts
    2

    table scan using getdate()

    Thanks for the information. I did find out that running it on 12.5.4 using a function or variable is most likely to get a table scan.

    I ran the query using getdate() on 15.5 (test server) and it ran very fast, and did not perform the scan.

    I will try the update index stats since I have to get this into production on 12.5.4.

    Thanks again,
    eric

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Using a function should not be a problem as long as it is not on the indexed column
    i.e. this is OK
    Code:
    SELECT mydate
    FROM mytable 
    WHERE mydate=dateadd(dd,datediff(dd,'',getdate())-1,'')
    But this can't use an index
    Code:
    SELECT mydate
    FROM mytable 
    WHERE dateadd(dd,datediff(dd,'',mydate)+1,'')='20111118'
    I still suggest you update index statistics on ALL tables involved.
    As a 2nd option try recreating the indexes or reorg rebuild the indexes.

Tags for this Thread

Posting Permissions

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