473,218 Members | 1,730 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

SQL/ASP - Timout Problem w/ Particular Statement

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.

Dec 20 '05 #1
6 1703
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.


Dec 21 '05 #2
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.


Dec 21 '05 #3

"Not4u" <No***@chez.com> wrote in message
news:43***********************@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.


Dec 21 '05 #4
if you want to speed it up, and the table is large, do a compound index
on the fields Day and Stock.

Dec 21 '05 #5
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.

Dec 21 '05 #6
that is not what i said.
i said to use day and stock.

Dec 22 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Matt | last post by:
I am having trouble with the HttpWebRequest.GetRequestStream method. I am posting data to my web server running NT4 iis4 using a loop. So every time the loop executes I want it to post data to...
7
by: Yannick Turgeon | last post by:
Hello all, I'm using SS2K on W2k. I'v got a table say, humm, "Orders" with two fields in the PK: OrderDate and CustomerID. I would like to add an "ID" column which would be auto-increment...
3
by: dba2adm | last post by:
The SNAPSHOT_STATEMENT table function gives the CPU used rows read etc information. How do I know the total number of time the particular statement was executed (as in snapshot_dyn_sql's...
4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
0
by: Eyal Sharabi Horwitz | last post by:
im trying to use the sqlhelper.filldataset on a few tables with querys that return num of rows ranging from 16 to just shy of 6000, and it always fails to load my last (and largest table) because...
0
by: Perecli Manole | last post by:
I have a need for logged in users to be timed out after 20 min of inactivity. This works well with the built in functionality that ASP.NET provides except for a certain situation. I have a reminder...
2
by: Bernie Yaeger | last post by:
I'm getting the following error - not always, only randomly - when I run a certain routine (the routine is a simple executenonquery and it appears to complete despite the message) against sql...
0
by: Agnes | last post by:
the application run in my office is very well. However, as it move to the client's company, Timeout and general network error always happend. We found that the firewall had a default setting "if it...
8
by: surya | last post by:
hi all i hava eetable that is , eename sal _______ ________ suresh 100000 ramsesh 100000 raja 100000 susjssj 100000 dkddkd ...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.