Can I stop this from Table scanning ?
-
This peice of code is Table scanning because os the variables @yesterday_from and @yesterday_to.
Of course it is fine if I put in the dates hardcoded ? but that would make the Stored Procedure pretty useless?
Any takes .... Ahsen
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
select 'DAP_Inbound' as SuppliersName,
@yesterday_str as Surveydate,
c.Title,
c.forename as firstname,
from dialaphone..customer as c
left join dialaphone..corders as o on c.uniqueid = o.custid
left join dialaphone..coitems as i on o.orderid = i.orderid
left join dap2..customer as c2 on replace(c.postcode,' ','') = c2.pcodefind and c.surname = c2.surname and c2.uniqueid > c.uniqueid
where i.orderid is null
and c2.UniqueID is null
and isnull(c.hometel,'') <> ''
and c.datetime between @yesterday_from and @yesterday_to
option ( loop join )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-
What is the range of @yesterday_from and @yesterday_to?
What is the range / distribution of dates in c.datetime?
If the optimizer thinks that just a few of your records will match the range, it will most likely use an index, otherwise a scan.
In a prior thread, a forum user suggested comparing the datetime column to itself in the WHERE clause prior to the BETWEEN. They said this had a dramatic effect. The thread kind of died out without "proof", while others tried against their own data without performance improvement. Perhaps it will help yours.
and c.datetime = c.datetime and c.datetime between @yesterday_from and @yesterday_to
Once you understand the BITs, all the pieces come together
-
Do you have a Clustered index on the datetime column? how many rows are in the table? are you sure is THAT Column the reason for the scan?
* Noel -
my 2€ct
- post ddl ! tables and indexes
- remove functions from your predicates if you can (they wil make the predicat nonseargeble!)
- replace the left join for i and c2 to "exists"-subqueries in your where clause
- check what's the effect when "isnull(c.hometel,'') <> ''" is replaced with (c.hometel is null or c.hometel = '')
- remove the option(loop join) when you want to do optimizations.
- aling indexes (i.e. provide indexes that are composed the same way and the same direction (asc/desc).
from dialaphone..customer as c
left join dialaphone..corders as o on c.uniqueid = o.custid
left join dialaphone..coitems as i on o.orderid = i.orderid
left join dap2..customer as c2 on replace(c.postcode,' ','') = c2.pcodefind and c.surname = c2.surname and c2.uniqueid > c.uniqueid
where i.orderid is null
and c2.UniqueID is null
and isnull(c.hometel,'') <> ''
and c.datetime between @yesterday_from and @yesterday_to
option ( loop join )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I recall having read that using BETWEEN automatically forces a table scan. I don't recall where, but I'd break it up using two >= and <= statements and see if that works.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Hi Wayne,
BETWEEN in itself does not force a table scan. You can check that in Query Analyzer - if the column is indexed, and enough values lie outside the specified range, it should use Index scan.
Ahsen,
Sometimes there are problems with the usage of indexes when you have several conditions on the same table (here two conditions - for c.hometel and c.datetime - plus a join on c.uinqueid). Depending on the structure of the table, and number of rows, you probably could juggle around a bit with the indexes and find out, whether a composite or clustered index would help. However, in my experience table scan issues can sometimes be solved also by optimization of the database (e.g. DBCC INDEXDEFRAG). It is also possible to specify a hint "WITH (INDEX(index_name))" in the query, but IMHO that should only be used as a last resort.
Good luck!
-
Thanks, Vladan, I remember what it was now. It was LIKE and IN, IIRC, that forced a table scan. My bad. I blame it on being sick since last Wednesday.
And that was under 6.5/7.0, I haven't confirmed whether or not that holds true under 2K.-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply