Posted by Rocky on 12/21/05 08:19

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]