Post reply

Query takes too long time

  • September 12, 2003 at 2:02 pm

    #81112

    Somebody please tell me why this query takes one our to finish...what I'm doing wrong?

    declare @date1 datetime

    declare @date2 datetime

    select @date1 = '02/01/2003 00:00:00'

    select @date2 = '02/28/2003 23:59:59'

    drop table explorer

    select siteid,callnbr,callid,callwhen,ldtime,ldamt,country into:explorer from billable_transactions with(index(callwhen))

    where (callnbr = '6808' or callnbr = '6809' or callnbr = '6876' or callnbr = '4202' or callnbr = '4203' or callnbr = '4204')

    and callwhen between @date1 and @date2

    order by callnbr,callwhen

    thanks

  • September 12, 2003 at 7:36 pm

    #474016

    Have you displayed the query plan to see what is the most resource-intensive bit?

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 12, 2003 at 8:32 pm

    #474017

    Which columns are used to create index(callwhen)? An index created on columns callnbr and callwhen should be used.

    From BOL.

    Table Hints

    "Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, <table_hint>, and <view_hint> only be used as a last resort by experienced developers and database administrators."

    The index hint in your query may help but it also could make the query performance even worse. As Mark suggested, you need post the query plan here for further investigation.

  • September 13, 2003 at 4:32 am

    #474040

    Agree with previous comments.

    Maybe you can try IN versus the ORs

  • September 15, 2003 at 2:02 am

    #474134

    How much data is in the "billable_transactions" table? And how much data is returned by the query?

    I seem to recall there are some locking issues with selecting into dynamically-created temporary tables. Is it possible that some other process is locking up the table while you're querying it?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • September 15, 2003 at 2:23 am

    #474136

    Nelson,

    As Allen already told you, whenever possible you should avoid forcing the indexes.

    Never the less to be able to help you for such kind of request you should give us the DDL (of the table and the indexes), the query plan, the nunber of records (in the table and returned)

    Without this dependig on the selectivity I would define an index on the callnbr column or a clustered index on the callwhen column (eventually a clustered index on the callwhen, callnbr columns)

    But I I've told you you have to test

    Bye

    Gabor

    Bye

    Gabor



    Bye
    Gabor

Viewing 6 posts - 1 through 5 (of 5 total)

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