May 9, 2010 at 8:55 pm
Hi there,
I have 2 tables. Ons is the fact table and the other one is the calendar dimension table. When I hardcode the where clause to return rows between 2 dates, the query runs fast. As soon as I change the where clause to use variables in stead, my query takes ages to complete. Can anyone tell me the reason behind this.
Here is an example of my queries
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SET @StartDate = '1 may 2010'
SET @EndDate = '20 may 2010'
--This runs fast
SELECTF.Column_1,F.Column_2
FROM dbo.Fact F
JOIN dbo.DimCalendar C
ON F.DateKey = C.DateKey
WHERE C.SQLDateTime = @StartDate
--This runs fast
SELECTF.Column_1,F.Column_2
FROM dbo.Fact F
JOIN dbo.DimCalendar C
ON F.DateKey = C.DateKey
WHERE C.SQLDateTime between '1 may 2010' and '20 may 2010'
--This runs slow
SELECTF.Column_1,F.Column_2
FROM dbo.Fact F
JOIN dbo.DimCalendar C
ON F.DateKey = C.DateKey
WHERE C.SQLDateTime between @StartDate and @EndDate
May 9, 2010 at 9:18 pm
This is because of parameter sniffing and / or safe plans.
The server has the assume that start date and end date will change. Also once the tipping point is reached (I forget the exact nmber but it's freakyshly low like 0.3%)... of data to be returned, a scan is assumed to be faster than seek + bookmark lookup (because the lookup will add up to 3 reads per row rather than multiple rows per read).
Gail has a great article on this but I didn't save the link... plz anyone post it if you know what I'm talking about.
Solution : use with recompile option to make sure the plan is rebuild on all runs and that no bad plan is used. Plan guides may be of help here but I've never used them so that would be plan Z.
May 10, 2010 at 7:51 am
Ninja's_RGR'us (5/9/2010)
Gail has a great article on this but I didn't save the link... plz anyone post it if you know what I'm talking about.
is this it?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
May 10, 2010 at 7:54 am
SQL Server does not know what the values of your variables are going to be when it generates an execution plan. Since you are using between it will assume that 9% (30%*30%) of the rows in the table will be returned. If you use only > or < it will assume that 30% of the rows are returned.
For values where the to and from date are very close to each other, resulting in very few rows, a seek might be the most efficient way to execute your query, but if the date range results in a large portion of the rows to be returned, a scan will most probably be the best solution. The problem is that SQL Server does not know this in advance, so it generates a plan based on the percentage rules i have listed.
If you know that only a small portion of the rows will be returned, meaning that the date values will be close to each other, you can instruct SQL Server to build a plan based on specific values. This is accomplished by using the option(optimize for(...)) query hint and "force" SQL Server to use a seek or a scan. SQL Server will then cache a plan which means that it does not have to use CPU to compile the next time the query is executed.
If you can't guarantee that the date values supplied will be efficient for a specific plan, meaning that one time a seek might be efficient and another time a scan will be more efficient, you can tell SQL Server to recompile the statements each time it is executed. This is done by using option (recompile).
May 10, 2010 at 8:16 am
lemsip (5/10/2010)
Ninja's_RGR'us (5/9/2010)
Gail has a great article on this but I didn't save the link... plz anyone post it if you know what I'm talking about.is this it?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Well yes but I was thinking about another article but I can't find it (forgot the title).
It was about that % of rows returned where a scan was faster than a seek.
May 10, 2010 at 8:19 am
May 10, 2010 at 1:51 pm
Thank you all for your replies. I was hoping that it was not paramater sniffing. Now I know how to fix it as well.:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply