Posted by Not4u on 12/21/05 13:41

Hi,

Thanks for your reply, i split my stored procedure and the problem seem
to be resolve.
In my asp code i added a test:
if end>begin then
execute sp_1
else
execute sp_2
end if

sp_1 look like simply
SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day >= @begin

And sp_2
SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day>=@begin OR
day<=@end

Very strange problem.

Happy Christmas


Rocky wrote:
> Hi there,
> I ran into a similar issue, where I had a IF THEN ELSE
> inside the stored proc and i was executing one query or the other based
> on the parameters,
>
> Can you create two stored procs(i know not the most efficient way), but
> just for test, try that and see if you can get a consistent
> performance,
>
> other idea would be to use complete dates and doing a between, that
> will take care of dec 29 and jan 2nd issue.
> (not sure how much data are you talking about)
>
> also check out some articles on parameter sniffing,
>
> HTH,
> R.
>
> Not4u wrote:
>
>>Hello
>>
>>Config : SQL 2000 on WIN 2000 (IIS 5.0)
>>
>>In my ASP page for some queries i have this error :
>>
>> Microsoft OLE DB Provider for SQL Server error '80040e31'
>> Timeout expired
>>
>>
>>My asp page calls a stored procedure passing many parameters.
>>I used the SQL profiler to get the exact stored procedure with all
>>parameters.
>>
>>If i execute stored procedure in the Query Analyzer, it's execute
>>successfully in 3-4 seconds.
>>After executing 2-3 times the stored procedure in the Query Analyzer,
>>the error disapear from the ASP Page which runs fine and quickly.
>>
>>My procedure is too long to be detailled here, but to do short, it's
>>look for the availabilities (the stock) of different products for a
>>desired length of time, with different parameters (color,size,etc..).
>>My main table look like :
>>
>>Day | Id_prod | Provider | Stock | Price
>> 1 1 1 2 3
>> 1 2 1 1 2
>> 1 1 2 4 5
>> 1 2 2 0 4
>> 2 1 1 1 9
>> 2 2 1 3 7
>> 2 1 2 1 7
>> 2 2 2 4 6
>>..
>>....
>>366 1 1 4 4
>>366 2 1 1 5
>>366 1 2 2 8
>>366 2 2 0 9
>>
>>The primary key is : day,Provider,Id_prod
>>And the main sql statment in my stored procedure :
>>
>>IF @end>@begin
>> INSERT INTO #tmptable
>> SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day >= @begin
>>AND day <=@end
>>ELSE
>> INSERT INTO #tmptable
>> SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day>=@begin OR
>>day<=@end)
>>
>>Note : I use a temporary table to use paging, i just display 10
>>results/pages.
>>
>>The problem only appears if @end<@begin (exemple; which product is
>>available from dec 29th to jan 2nd in blue color)
>>
>>Any help would be much appreciated.
>>Thank and happy Christmas.
>>
>>
>>
>>PS:If i set the timeout CommandTimeout = 9999 the problem is resolve but
>>it's not a solution for me.
>
>

[Back to original message]