December 20, 2011 at 2:09 am
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
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 table1left 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
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
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
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
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
+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
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
That's the estimated plan, we need the actual plan.
December 20, 2011 at 8:02 am
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
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
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
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
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
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