Post reply

Problem with long running query

  • December 20, 2011 at 2:09 am

    #265206

    Hi there,

    first of all thank you all for the tips and hints here, ive got alot of tips out here for my daily work ! Thanks !

    Now my problem:

    Ive an SQL2005 Server running. From two tables i have to query records within a timerange but it takes horrible long, 50 to 80 seconds. How can i improve this one.

    select table1.* from table1

    left join table2 on table1.id=table2.id where table2.id is null and table1.date is not null and datediff(day,table1.date,getdate())<7

    The meaning behind this query is:

    Select all records from table1 which have no entries in table2 but have field date filled in table1 and are not older then 7 days

    Tables have close to 8.000.000 records, Table1 has NO clustered index but index on ID and date, same at table2

    Any help would be appreciated

    Chris

    I want to know all secrets about MS SQL Server :w00t:

  • December 20, 2011 at 2:20 am

    #1423491

    christian 92959 (12/20/2011)

    Hi there,

    first of all thank you all for the tips and hints here, ive got alot of tips out here for my daily work ! Thanks !

    Now my problem:

    Ive an SQL2005 Server running. From two tables i have to query records within a timerange but it takes horrible long, 50 to 80 seconds. How can i improve this one.

    select table1.* from table1

    left join table2 on table1.id=table2.id where table2.id is null and table1.date is not null and datediff(day,table1.date,getdate())<7

    The meaning behind this query is:

    Select all records from table1 which have no entries in table2 but have field date filled in table1 and are not older then 7 days

    Tables have close to 8.000.000 records, Table1 has NO clustered index but index on ID and date, same at table2

    Any help would be appreciated

    Chris

    I suggest that you have a look at the execution plan and see whether that gives you any clues.

    Here's an attempt at something which may be a little faster:

    declare @StartDate datetime

    set @StartDate = getdate() - 7

    select t1.f1, t1.f2, ...

    from table1 t1

    left join table2 t2 on t1.id = t2.id

    where table2.id is null

    and table1.date > @StartDate

    Notes:

    1) Use a finite list of field names, not *

    2) Take the DateDiff calc out of the query if possible

    3) The not null condition was superfluous

  • December 20, 2011 at 2:22 am

    #1423492

    Note: use

    datediff(dd,0,getdate()) - 7

    If you'd like to remove the time component from today - 7

  • December 20, 2011 at 4:36 am

    #1423552

    Hi Phil,

    thank you for the imediate response, i changed my query to your advise ... but its not faster at all.

    Investigating the execution plan gives me the information that there is/was a Table Scan which costs about 39% the scan is initiated by the date comparision t1.date > @StartDate.

    But i dont understand that because i have an index on the column t1.date and IMHO there should be an index scan

    On the other hand i got the hint to create another index on the t1.date column AND include all other fields of the table and this would improve the query 29%. The hint comes out of the execution plan

    Hmm, now im a little bit lost because i cant create the index now because of production 24/7 and i must wait for a m.window

    Thanks in advance

    Chris

    I want to know all secrets about MS SQL Server :w00t:

  • December 20, 2011 at 4:47 am

    #1423556

    christian 92959 (12/20/2011)

    Hi Phil,

    thank you for the imediate response, i changed my query to your advise ... but its not faster at all.

    Investigating the execution plan gives me the information that there is/was a Table Scan which costs about 39% the scan is initiated by the date comparision t1.date > @StartDate.

    But i dont understand that because i have an index on the column t1.date and IMHO there should be an index scan

    On the other hand i got the hint to create another index on the t1.date column AND include all other fields of the table and this would improve the query 29%. The hint comes out of the execution plan

    Hmm, now im a little bit lost because i cant create the index now because of production 24/7 and i must wait for a m.window

    Thanks in advance

    Chris

    29% is not bad, I guess. There are other people on this forum who know more about query optimisation than I do & I hope that they will offer their input.

    Surely you can do your optimisation on a dev copy of the DB? Adding a clustered index is generally a pretty good idea, by the way.

    You should also check the tables for fragmentation.

  • December 20, 2011 at 4:58 am

    #1423560

    select t1.f1, t1.f2, ...

    from table1 t1

    left join table2 t2 on t1.id = t2.id

    where table2.id > 0 --table2.id is null

    and table1.date > @StartDate

    Try this & let me know if hit the index. I will explain it later.

    p.s. for better assistance, you should provide sqlplan, DDLs & sample data.

  • December 20, 2011 at 6:03 am

    #1423587

    +1 on the clustered index benefits, especially on such a large table.

    We'd need the actual execution plan to help any further.

  • December 20, 2011 at 6:37 am

    #1423610

    Thx,

    @Phil, yes i will try to get a dev copy, for sure its better to work with

    @dev thx for answering but this does not work because the table1.id is a string value and with a comparison like <>'' it gives me wrong results (the whole data from table1) so i switched back.

    Attached you will find the execution plan. Maybe you find something if you look at it

    (DDL is a bit difficult because i have to modify because of speaking fieldnames, but if needed i will do it)

    On table Asend i have Non-Unique, non-Clustered indexes on:

    fz_senddat

    fz_id

    fz_datuhr (not relevant at query)

    fz_vvvnum (not relevant at query)

    fz_sidnr (not relevant at query)

    on table Arueck i have Non-Unique, non-Clustered indexes on:

    fr_id

    fr_rmdat (not relevant at query)

    fr_datuhr (not relevant at query)

    fz_id and fr_id are defined as varchar(23)

    Thx

    Chris

    I want to know all secrets about MS SQL Server :w00t:

  • December 20, 2011 at 6:58 am

    #1423619

    That's the estimated plan, we need the actual plan.

  • December 20, 2011 at 8:02 am

    #1423652

    Oh, im sorry for that i thought its the same.

    Attached the actual plan

    Thanks

    Chris

    I want to know all secrets about MS SQL Server :w00t:

  • December 20, 2011 at 8:39 am

    #1423682

    Actual plan has WAY more info about what's going on in that query. 1 of the most important is actual rows vs estimates.

  • December 20, 2011 at 8:43 am

    #1423692

    The estimates are WAY off.

    I'd try with not exists and make this the clustered index fz_senddat.

    That should improve this immensely.

  • December 20, 2011 at 9:26 am

    #1423749

    Thank you Ninja,

    i should try to dig deeper in analyzing execution plans 🙂

    Did you mean something like this ? (not proofed so far because im at home at the momen)

    declare @StartDate datetime

    set @StartDate = getdate() - 7

    select fz_ky,fz_.....,.....

    from Asend fz

    where not exists (select fr_ky from Arueck fr where fr.fr_ky=fz.fz_ky)

    and fz.fz_senddat > @StartDate

    Thanks again

    Chris

    I want to know all secrets about MS SQL Server :w00t:

  • December 20, 2011 at 5:44 pm

    #1423967

    Something like that.

    If you want 7 days ago midnight you can use something like this instead

    SET @Start = DATEADD(D, -7, DATEDIFF(D, 0, GETDATE()))

  • December 21, 2011 at 9:00 am

    #1424306

    Ok, it works ...

    i had the chance to get a copy of the DB on a DEV Server. I took a clustered index on fz_senddat as you advised, and now i have a response time close to 5 secs.

    Now i have to find out a maintainence window for the production server to create the clustered index,

    Thank you all for your support

    Chris

    I want to know all secrets about MS SQL Server :w00t:

Viewing 15 posts - 1 through 15 (of 20 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy