Re: SQL/ASP - Timout Problem w/ Particular Statement

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.

.

Relevant Pages

  • Re: Poor performance when executing stored procedure
    ... If SQL Server ... has to re-compile the SP, it takes shorter time to compile a smaller stored ... Poor performance when executing stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures v Views
    ... Build a stored procedure, ... The goal here will be to not use any dynamic sql like you used in Access. ... If the query is very complex, but frankly, if you have to do this, you are ... > they make are then used to create the SQL statement which is then used to ...
    (microsoft.public.sqlserver.programming)
  • RE: Class Module Choices
    ... SQL Server has Stored Procedures. ... Perhaps you meant Stored Query with is different. ... When I said stored procedure, ... I don't know how to assign a value to Param1 within the execute command. ...
    (microsoft.public.access.formscoding)
  • RE: How to simple retrieve autonumber valuees or identity from data so
    ... you can write the equivalent of a stored procedure in Access. ... Simply write your SQL in a query and save it. ... > I use the access database in my simple application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SELECT MAX(...) performance in SQL Server/ODBC
    ... I tried the Profiler and compared the results and execution plans in the two ... executing the query from C++ code by calling SQLExecDirect. ... > Have you tried using SQL Profiler to profile the events on the server? ...
    (microsoft.public.data.odbc)