August 26, 2010 at 12:49 pm
I have a procedure with 4 parameters all set to = NULL. When I try to run the proc it just hangs. If I remove the = NULL and pass it values it runs fine. If I change any of the parameters to = NULL the proc hangs. I didn't write the code but was charged with finding out why it was hanging and this is what I found. Odd thing is that if the user waited long enough, sometimes 15-30 minutes the report would run. The code that follows runs great. As soon as I add = NULL to one or more of the parameters the proc hangs.
Sword_Calendar is a table of all dates with our fiscal year, quarter, month, week, beginning and ending dates for year, month, week, etc. one record for every day of the year for years to come and years in the past.
Code:
ALTER PROCEDURE [dbo].[Daily_Sales_GOB]
@Param_Year int
,@Param_Week int
,@Param_DOW int
AS
BEGIN
Declare @Today datetime,
@Year int,
@Week int,
@DOW int
Set @Today = GETDATE();
set @Today = @Today - 1
Set @Year = (select YR_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @Week = (select WK_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @DOW = (select DAY_CODE from sword_calendar where @Today between greg_date and greg_date + 1)
if @Param_Year is null
begin
set @Param_Year = @Year
set @Param_Week = @Week
set @Param_DOW = @DOW
end
August 26, 2010 at 1:18 pm
I don't see anything that would cause that kind of delay. Is this the entire procedure?
Do you have an index on greg_date?
But:
Set @Year = (select YR_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @Week = (select WK_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @DOW = (select DAY_CODE from sword_calendar where @Today between greg_date and greg_date + 1)
This could all be done in one call:
SELECT @Year = YR_NUMBER,
@Week = WK_NUMBER,
@DOW = DAY_CODE
FROM sword_calendar
WHERE @Today BETWEEN greg_date AND greg_date + 1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2010 at 1:34 pm
No indexes on Sword_Calendar (ought to be one on Greg_Date) but there is less than 10,000 records (rows - I'm an old mainframe programmer) in the table.
I agree with your comment on the code. Like I said, I didn't write it but if I did I surely would have done it as suggested as I do in all the programs I write.
This problem is very strange. I also have problems with other procs run from SSRS where some times the report will run in just a few seconds, soon later it will take 3-4 minutes and other times 10-15 minutes. Now this is not a heavily used box. No transaction processing. Just a warehouse. We have 8 processors and 16GB memory. Pretty powerful but sometimes very slow. Like when opening up tables in a db or columns in a table. I get that loading... message sometimes for a few minutes. I've rebooted the server, sql, insured there was a fair amount of disk space empty, etc. Just a frustrated DBA/developer.
I appreciate your interest.
August 26, 2010 at 1:37 pm
The rest of the procedure:
SELECT DWSTORE
,@Param_Year as Param_Year
,@Param_Week as Param_Week
,@Param_DOW as Param_DOW
, sum (dw_sales_units) as [Total Units]
, sum (dw_sales_rtl) as [Total Sales Retail]
, sum (dw_mkd_promo) as [Total Mkdwn Promo]
, sum (dw_mkd_in_str) as [Total Mkdwn In Str]
, sum (dw_mkd_promo + dw_mkd_in_str) as [Total Mkdwn]
FROM [DW_PROD].[dbo].[SPF053_SALES]
where dwyear = @Param_Year
and dwweek = @Param_week
and dw_dow = @Param_DOW
and dwstore in (1890, 3028, 1648, 1878, 2480, 3180, 1190)
-- and dwstore in (1040,1383,1693,1778,1795,1888,2403,2830,2968)
group by DWSTORE
order by dwstore
Like I say, if I send the proc the parameter values this procedure runs in seconds every time.
August 26, 2010 at 1:37 pm
parameter sniffing.
an execution plan is created when the procedure is created, and the SQL engine makes an assumption that since the default values are NULL, the best execution plan should assume the NULL values are in place.
when the values are not defaulted to null, it uses statistics to see how granular the fields are for uniqueness, and creates a different plan based on that info.
but the problem is that when you call the proc with real parameters, the cached plan is not suitable to ge tthe data, and SQL goes off on a tangent trying to get the data; personally i always assumed that a bad plan due to parameter sniffing ended up using the bad plan on a per-row basis, so big MillionRowTables get the same plan a million times, hence the huge time difference...whether that is true or not, the results are the same... it takes too long.
search for parameter sniffing to learn more, but the two general fixes are:
use the WITH RECOMPILE option so the proc recomiles each time it is called... or assign local variables to the actual values being passe dto the procedure.
Lowell
August 26, 2010 at 1:49 pm
Thanks a lot. I'll look up parameter sniffing. And yes the table has over 900,000,000 rows.
August 26, 2010 at 1:51 pm
Great explanation Lowell!
jnichols - what is the intent if a null is passed in for one of the parameters? To get all values, or to get values where that field is actually null? Because the way that the query is written, I'd be surprised if you get anything back from the query if a null is passed in for any these parameter.
Is is safe to assume that the values for the parameters will be:
@Param_Year (what are valid starting/ending year values for your data?)
@Param_Week 1-52?
@Param_DOW int 1-7?
Does the [DW_PROD].[dbo].[SPF053_SALES] table have an index on dwstore, dwyear, dwweek, dw_dow?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 6:51 am
As you can see, if the @Param_Year is null the default values, which are the current date - 1 day, captured earlier in the procedure are loaded into the params and used in the select statement. And yes, there are those indexes on the table. What with 900 million rows no query would run in an acceptable time. This query runs in about a minute when parameters are passsed to the proc. The problem is that it is called from a SSRS report and the author didn't want the user to have to enter yesterday's date as that is how it is generally used. I modified the report to default to the values of yesterday's date.
August 27, 2010 at 7:39 am
I think we can get around the parameter sniffing and make this work properly.
First, change this code:
Set @Today = GETDATE();
set @Today = @Today - 1
Set @Year = (select YR_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @Week = (select WK_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @DOW = (select DAY_CODE from sword_calendar where @Today between greg_date and greg_date + 1)
if @Param_Year is null
begin
set @Param_Year = @Year
set @Param_Week = @Week
set @Param_DOW = @DOW
end
to:
SET @Today = DateAdd(day, -1, GetDate());
SELECT @Year = IsNull(@Param_Year, YR_NUMBER),
@Week = IsNull(@Param_Week, WK_NUMBER),
@DOW = IsNull(@Param_DOW, DAY_CODE )
FROM sword_calendar
WHERE @Today BETWEEN greg_date AND greg_date + 1;
Then change your select to use these new values:
SELECT DWSTORE
,@Year as Param_Year
,@Week as Param_Week
,@DOW as Param_DOW
, sum (dw_sales_units) as [Total Units]
, sum (dw_sales_rtl) as [Total Sales Retail]
, sum (dw_mkd_promo) as [Total Mkdwn Promo]
, sum (dw_mkd_in_str) as [Total Mkdwn In Str]
, sum (dw_mkd_promo + dw_mkd_in_str) as [Total Mkdwn]
FROM [DW_PROD].[dbo].[SPF053_SALES]
WHERE dwyear = @Year
AND dwweek = @week
AND dw_dow = @DOW
AND dwstore in (1890, 3028, 1648, 1878, 2480, 3180, 1190)
GROUP BY DWSTORE
ORDER BY dwstore
How does this work for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 12:37 pm
I appreciate the help, all. The problem is fixed.
Thanks
August 27, 2010 at 3:18 pm
Great! but it's only fair to tell us how.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 7:28 am
WayneS (8/27/2010)
Great! but it's only fair to tell us how.
According to an article I read on parameter sniffing I see that the first time the procedure is run if the parameters are null SQL sets up an execution plan that does a table scan. Why I don't understand. If SQL waas all that smart it should have seen I filled the parameters with data if they were null. Now when you have a table with 900 million rows table scanning takes some time. I guess I could force SQL not to use the stored execution plan and build a new one with the WITH RECOMPILE option but I am afraid the same result will happen if the parameters are null then. So I changed the SSRS that calls this procedure to default to the same values the procedure builds if the parameters are null. So the code remains the same. And I appreciate the recode WanyeS offered but my mantra is "don't fix it if it's not broken". Now obviously that does not hold true when a proecedure works but is very slow like this one. But the quick fix is to change the SSRS.
code:
ALTER PROCEDURE [dbo].[Daily_Sales_GOB]
@Param_Year int
,@Param_Week int
,@Param_DOW int
AS
BEGIN
Declare @Today datetime,
@Year int,
@Week int,
@DOW int
Set @Today = GETDATE();
set @Today = @Today - 1
Set @Year = (select YR_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @Week = (select WK_NUMBER from sword_calendar where @Today between greg_date and greg_date + 1)
Set @DOW = (select DAY_CODE from sword_calendar where @Today between greg_date and greg_date + 1)
if @Param_Year is null
begin
set @Param_Year = @Year
set @Param_Week = @Week
set @Param_DOW = @DOW
end
SELECT DWSTORE
,@Param_Year as Param_Year
,@Param_Week as Param_Week
,@Param_DOW as Param_DOW
, sum (dw_sales_units) as [Total Units]
, sum (dw_sales_rtl) as [Total Sales Retail]
, sum (dw_mkd_promo) as [Total Mkdwn Promo]
, sum (dw_mkd_in_str) as [Total Mkdwn In Str]
, sum (dw_mkd_promo + dw_mkd_in_str) as [Total Mkdwn]
FROM [DW_PROD].[dbo].[SPF053_SALES] -- 900 million record table
where dwyear = @Param_Year
and dwweek = @Param_week
and dw_dow = @Param_DOW
and dwstore in (1890, 3028, 1648, 1878, 2480, 3180, 1190)
group by DWSTORE
order by dwstore
August 30, 2010 at 9:01 pm
What you need is a clusted index on [DW_PROD].[dbo].[SPF053_SALES] (dwyear, dwweek, dw_dow, dwstore)
Then your query will return results instantly.
But schedule index creation for weekend: on a table with 900 mil rows it will take whole day.
And make sure you've got plenty of space for LOG file. "Plenty" means 2-3 times more than the table occupies.
_____________
Code for TallyGenerator
September 2, 2010 at 3:20 pm
Sergiy (8/30/2010)
What you need is a clusted index on [DW_PROD].[dbo].[SPF053_SALES] (dwyear, dwweek, dw_dow, dwstore)Then your query will return results instantly.
But schedule index creation for weekend: on a table with 900 mil rows it will take whole day.
And make sure you've got plenty of space for LOG file. "Plenty" means 2-3 times more than the table occupies.
Yep, but the SPF053_Sales table is not the largest in our warehouse. We have table with 1.220 BILLION rows. Also a few more with over 200 million rows. This is a retail chain and the TLOG is pretty big. (1.220 billion rows). I agree with what you are saying but we can't take that table or any other of the directly related tables off line, much less the space needed for clustered indexes on these and more tables that need them. Thanks for the suggestion though.
September 2, 2010 at 5:18 pm
It only confirms that such a lame database design is not good enough for you.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply