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.

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.

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.
>
>

"Not4u" <Not4u@chez.com> wrote in message
news:43a93f68$0$19247$626a54ce@news.free.fr...
> 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.


Not really. You're hitting a query plan cache issue. The original query
plan probably used the first select statement and then something came along
that caused the second statement to be executed for which there was no query
plan.

Your solution is the usual one.

>
> 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.
> >
> >

if you want to speed it up, and the table is large, do a compound index
on the fields Day and Stock.

Hi,

That what i did, the primary key is composed by :
day,Provider,Id_prod

Thanks

Doug wrote:
> if you want to speed it up, and the table is large, do a compound index
> on the fields Day and Stock.
>

that is not what i said.
i said to use day and stock.