Posted by Greg D. Moore \(Strider\) on 12/21/05 14:26

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

[Back to original message]