468,538 Members | 1,744 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,538 developers. It's quick & easy.

Timeout Error Question

Hi All. I have a SQL Statement on an ASP page that only returns 4 records. When I run it in SQL Server or Query Analyzer it runs in less than a second. When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME, DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM orders WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND PROD_TYPE = 'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY CUST_SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout, Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
Jul 19 '05 #1
11 7246
>>>>>>>>>>>>>>>>>>
"David Berry" <db****@mvps.org> wrote in message
news:O0*************@tk2msftngp13.phx.gbl...
Hi All. I have a SQL Statement on an ASP page that only returns 4 records.
When I run it in SQL Server or Query Analyzer it runs in less than a second.
When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME,
DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN
ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM orders
WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE
BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND PROD_TYPE =
'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY
CUST_SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout,
Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Do you really need the DISTINCTs? Are you likely to get more than one record
with exactly the same info?

Do the fields in your WHERE clause have indexes?

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
Jul 19 '05 #2
Hi John,

Yes, I need them and no, they don't have indexes. I have no control over
the database. It belongs to the customer and they don't want to change it.
"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:yO******************@newsfep4-glfd.server.ntli.net...
>>>>>>>>>>>>>>>
"David Berry" <db****@mvps.org> wrote in message
news:O0*************@tk2msftngp13.phx.gbl...
Hi All. I have a SQL Statement on an ASP page that only returns 4

records. When I run it in SQL Server or Query Analyzer it runs in less than a second. When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME,
DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN
ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM orders WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE
BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND PROD_TYPE = 'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY
CUST_SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout,
Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Do you really need the DISTINCTs? Are you likely to get more than one record with exactly the same info?

Do the fields in your WHERE clause have indexes?

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook

Jul 19 '05 #3
Dave,

You can increase the CommandTimeout to a large number, but then you have to
increase ASP script timeout as well. But then, if the time to execute the
query is too long, your browser might just give up. Looks like the table is
large, and as John suggested, the queried columns are not indexed.

One thing you could do is to create a asyncronous query -- where the results
of the query go into a table, etc. Each query can be identified by a number.
You then implement a "Query executing, please wait..." page that checks to
see if the query has completed.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:ep**************@TK2MSFTNGP11.phx.gbl...
Hi John,

Yes, I need them and no, they don't have indexes. I have no control over
the database. It belongs to the customer and they don't want to change it.

"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:yO******************@newsfep4-glfd.server.ntli.net...
>>>>>>>>>>>>>>>>> "David Berry" <db****@mvps.org> wrote in message
news:O0*************@tk2msftngp13.phx.gbl...
Hi All. I have a SQL Statement on an ASP page that only returns 4

records.
When I run it in SQL Server or Query Analyzer it runs in less than a

second.
When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME,
DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM

orders
WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE
BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND

PROD_TYPE =
'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY
CUST_SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout,
Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Do you really need the DISTINCTs? Are you likely to get more than one

record
with exactly the same info?

Do the fields in your WHERE clause have indexes?

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook


Jul 19 '05 #4
The CommandTimeout is the relevant property to be setting. Only, you need to
be setting it at the Connection level instead of the Command - you aren't
using the Command object to open the recordset so setting its
CommandProperty will have no effect on the rs.open statement. Do
con.commandtimeout=<whatever you need> before opening the recordset. You may
have to up the scripttimeout if the commandtimeout exceeds that value.

Bob Barrows
David Berry wrote:
Hi John,

Yes, I need them and no, they don't have indexes. I have no control
over the database. It belongs to the customer and they don't want to
change it.
"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:yO******************@newsfep4-glfd.server.ntli.net...
>>>>>>>>>>>>>>>>

"David Berry" <db****@mvps.org> wrote in message
news:O0*************@tk2msftngp13.phx.gbl...
Hi All. I have a SQL Statement on an ASP page that only returns 4
records. When I run it in SQL Server or Query Analyzer it runs in
less than a second. When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

Any ideas what could be causing this? I upped the
Server.ScriptTimeout, Con.ConnectionTimeout and
comCommand.CommandTimeout and nothing helps.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5
I have run into the same problem. I'm not sure of the root of the
problem but I have found a workaround I believe.

More than likely I'm going to assume that SERVICE.ACCTNO is an index
value. If you add another non-index value into your query in the
order by clause it should run faster on the web.

So for example just add CONT DATE at the end of your order by clause.

How that helps.

Kevin


"David Berry" <db****@mvps.org> wrote in message news:<O0*************@tk2msftngp13.phx.gbl>...
Hi All. I have a SQL Statement on an ASP page that only returns 4
records. When I run it in SQL Server or Query Analyzer it runs in less
than a second. When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST SERVICE.ACCTNO As Acct, CONT DATE, SALES REP NAME,
DESCRIP, PROD TYPE, MODE FROM CUST SERVICE CROSS JOIN CUSTOMER CROSS
JOIN ORDERS WHERE (CUST SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno
FROM orders WHERE oredertype='Blowout Print' or oredertype='Print'))
AND CONT DATE BETWEEN '12/23/03'AND'12/24/03' AND SALES REP NAME =
'Anne' AND PROD TYPE = 'P' AND CUSTOMER.BILLST = 'ct' AND
ORDERS.oredertype = 'Print' ORDER BY CUST SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout,
Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
--

Jul 19 '05 #6
Hi Manohar. I changed the code to:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "file name=e:\micro.udl"
cn.ConnectionTimeout = 6000
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandTimeout = 6000

Server.ScriptTimeout = 6000

cmd.CommandText = "SELECT ........... "

Set rs = cmd.Execute(cmd.CommandText)

And now I'm getting an error that the operation isn't allowed while the
object is open. The line it points to is

cn.ConnectionTimeout = 6000

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:Oj**************@tk2msftngp13.phx.gbl...
Dave,

You can increase the CommandTimeout to a large number, but then you have to increase ASP script timeout as well. But then, if the time to execute the
query is too long, your browser might just give up. Looks like the table is large, and as John suggested, the queried columns are not indexed.

One thing you could do is to create a asyncronous query -- where the results of the query go into a table, etc. Each query can be identified by a number. You then implement a "Query executing, please wait..." page that checks to
see if the query has completed.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:ep**************@TK2MSFTNGP11.phx.gbl...
Hi John,

Yes, I need them and no, they don't have indexes. I have no control over
the database. It belongs to the customer and they don't want to change

it.


"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:yO******************@newsfep4-glfd.server.ntli.net...
>>>>>>>>>>>>>>>>>>
"David Berry" <db****@mvps.org> wrote in message
news:O0*************@tk2msftngp13.phx.gbl...
Hi All. I have a SQL Statement on an ASP page that only returns 4

records.
When I run it in SQL Server or Query Analyzer it runs in less than a

second.
When I run it from my ASP page I get:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

the error line points to the rs.Open sql,con line.

The SQL Statement is:

SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME, DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS

JOIN ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM

orders
WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE
BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND

PROD_TYPE
=
'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY CUST_SERVICE.ACCTNO

Any ideas what could be causing this? I upped the Server.ScriptTimeout, Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps.

Dave
<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Do you really need the DISTINCTs? Are you likely to get more than one

record
with exactly the same info?

Do the fields in your WHERE clause have indexes?

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook



Jul 19 '05 #7
Dave,

Swap the lines to:

cn.ConnectionTimeout = 6000
cn.Open "file name=e:\micro.udl"

Actually, the connection timeout won't help you much here, since the
connection seems to be fine, and time out happens when query is issued.

Also, move the line Server.ScriptTimeout to the top of the code. It should
ideally be the topmost line. Again, 6000 seconds (100 mins) is way too long
before browser gives up. You might shorten it to say 600 seconds.

I still feel you need to query the database asynchronously, and poll it to
see if the results have been returned.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:eL**************@TK2MSFTNGP10.phx.gbl...
Hi Manohar. I changed the code to:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "file name=e:\micro.udl"
cn.ConnectionTimeout = 6000
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandTimeout = 6000

Server.ScriptTimeout = 6000

cmd.CommandText = "SELECT ........... "

Set rs = cmd.Execute(cmd.CommandText)

And now I'm getting an error that the operation isn't allowed while the
object is open. The line it points to is

cn.ConnectionTimeout = 6000

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:Oj**************@tk2msftngp13.phx.gbl...
Dave,

You can increase the CommandTimeout to a large number, but then you have

to
increase ASP script timeout as well. But then, if the time to execute the
query is too long, your browser might just give up. Looks like the table

is
large, and as John suggested, the queried columns are not indexed.

One thing you could do is to create a asyncronous query -- where the

results
of the query go into a table, etc. Each query can be identified by a

number.
You then implement a "Query executing, please wait..." page that checks to see if the query has completed.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:ep**************@TK2MSFTNGP11.phx.gbl...
Hi John,

Yes, I need them and no, they don't have indexes. I have no control over the database. It belongs to the customer and they don't want to change it.


"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:yO******************@newsfep4-glfd.server.ntli.net...
> >>>>>>>>>>>>>>>>>>
> "David Berry" <db****@mvps.org> wrote in message
> news:O0*************@tk2msftngp13.phx.gbl...
> Hi All. I have a SQL Statement on an ASP page that only returns 4
records.
> When I run it in SQL Server or Query Analyzer it runs in less than a
second.
> When I run it from my ASP page I get:
>
> Microsoft OLE DB Provider for SQL Server error '80040e31'
>
> Timeout expired
>
> the error line points to the rs.Open sql,con line.
>
> The SQL Statement is:
>
> SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE, SALES_REP_NAME, > DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS

JOIN
> ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno
FROM orders
> WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE > BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND

PROD_TYPE
=
> 'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER

BY > CUST_SERVICE.ACCTNO
>
> Any ideas what could be causing this? I upped the Server.ScriptTimeout, > Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps. >
> Dave
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<
>
> Do you really need the DISTINCTs? Are you likely to get more than one record
> with exactly the same info?
>
> Do the fields in your WHERE clause have indexes?
>
>
>
> --
> John Blessing
>
> http://www.LbeHelpdesk.com - Help Desk software priced to suit all
> businesses
> http://www.free-helpdesk.com - Completely free help desk software !
> http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
>
>



Jul 19 '05 #8
Hi Manohar,

Thanks! That works now, although it still takes a long time to bring the
page up. I didn't notice I had the lines reversed.

As for "query the database asynchronously, and poll it to see if the results
have been returned. "

Unfortunately I don't know how you do that. Do you have a sample? Also, the
query is being dynamically built based on what they enter into 5 search
criteria fields and I need to do it that way. I'd prefer to use a Stored
Procedure but I have no access to the actual SQL Server box (just a backup
copy locally) and they don't want me creating any Stored Procedures (no idea
why).

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:Oq**************@TK2MSFTNGP10.phx.gbl...
Dave,

Swap the lines to:

cn.ConnectionTimeout = 6000
cn.Open "file name=e:\micro.udl"

Actually, the connection timeout won't help you much here, since the
connection seems to be fine, and time out happens when query is issued.

Also, move the line Server.ScriptTimeout to the top of the code. It should
ideally be the topmost line. Again, 6000 seconds (100 mins) is way too long before browser gives up. You might shorten it to say 600 seconds.

I still feel you need to query the database asynchronously, and poll it to
see if the results have been returned.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:eL**************@TK2MSFTNGP10.phx.gbl...
Hi Manohar. I changed the code to:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "file name=e:\micro.udl"
cn.ConnectionTimeout = 6000
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandTimeout = 6000

Server.ScriptTimeout = 6000

cmd.CommandText = "SELECT ........... "

Set rs = cmd.Execute(cmd.CommandText)

And now I'm getting an error that the operation isn't allowed while the
object is open. The line it points to is

cn.ConnectionTimeout = 6000

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:Oj**************@tk2msftngp13.phx.gbl...
Dave,

You can increase the CommandTimeout to a large number, but then you have
to
increase ASP script timeout as well. But then, if the time to execute the query is too long, your browser might just give up. Looks like the
table
is
large, and as John suggested, the queried columns are not indexed.

One thing you could do is to create a asyncronous query -- where the results
of the query go into a table, etc. Each query can be identified by a

number.
You then implement a "Query executing, please wait..." page that
checks to see if the query has completed.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:ep**************@TK2MSFTNGP11.phx.gbl...
> Hi John,
>
> Yes, I need them and no, they don't have indexes. I have no control

over
> the database. It belongs to the customer and they don't want to change it.
>
>
> "John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in
message > news:yO******************@newsfep4-glfd.server.ntli.net...
> > >>>>>>>>>>>>>>>>>>
> > "David Berry" <db****@mvps.org> wrote in message
> > news:O0*************@tk2msftngp13.phx.gbl...
> > Hi All. I have a SQL Statement on an ASP page that only returns 4
> records.
> > When I run it in SQL Server or Query Analyzer it runs in less than a > second.
> > When I run it from my ASP page I get:
> >
> > Microsoft OLE DB Provider for SQL Server error '80040e31'
> >
> > Timeout expired
> >
> > the error line points to the rs.Open sql,con line.
> >
> > The SQL Statement is:
> >
> > SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE,

SALES_REP_NAME,
> > DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN
> > ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno

FROM > orders
> > WHERE oredertype='Blowout Print' or oredertype='Print')) AND CONT_DATE > > BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND
PROD_TYPE
> =
> > 'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY
> > CUST_SERVICE.ACCTNO
> >
> > Any ideas what could be causing this? I upped the

Server.ScriptTimeout,
> > Con.ConnectionTimeout and comCommand.CommandTimeout and nothing helps. > >
> > Dave
> > <<<<<<<<<<<<<<<<<<<<<<<<<<<<
> >
> > Do you really need the DISTINCTs? Are you likely to get more than one > record
> > with exactly the same info?
> >
> > Do the fields in your WHERE clause have indexes?
> >
> >
> >
> > --
> > John Blessing
> >
> > http://www.LbeHelpdesk.com - Help Desk software priced to suit all
> > businesses
> > http://www.free-helpdesk.com - Completely free help desk software

! > > http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
> >
> >
>
>



Jul 19 '05 #9
David Berry wrote:
and they don't want me
creating any Stored Procedures (no idea why).


It must be because they want you to be creating ad hoc queries that will
bring their server to its knees .... ;-)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #10
Dave,

Don't have any ready samples, but here's one approach (of many):

1. Set Response.Buffer=True. Display a DIV with "Working on the query..."
message.
2. Use the adAsyncExecute option of the Connection.Execute to execute your
query.
3. Loop to find out if the Connection.State is adStateExecuting, loop until
this changes.
4. While in the loop, check to see if the client is still connected
(IsClientConnected of Request object). If not, do a Connection.Cancel to
cancel the query. At the same time, write out something back to the
Response, and flush it. This lets the browser know the server is
responsive.
5. Once the Connection.State changes, write out a Javascript to hide the
DIV, and do a Response.Flush
6. Display the results.
--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi Manohar,

Thanks! That works now, although it still takes a long time to bring the
page up. I didn't notice I had the lines reversed.

As for "query the database asynchronously, and poll it to see if the results have been returned. "

Unfortunately I don't know how you do that. Do you have a sample? Also, the query is being dynamically built based on what they enter into 5 search
criteria fields and I need to do it that way. I'd prefer to use a Stored
Procedure but I have no access to the actual SQL Server box (just a backup
copy locally) and they don't want me creating any Stored Procedures (no idea why).

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:Oq**************@TK2MSFTNGP10.phx.gbl...
Dave,

Swap the lines to:

cn.ConnectionTimeout = 6000
cn.Open "file name=e:\micro.udl"

Actually, the connection timeout won't help you much here, since the
connection seems to be fine, and time out happens when query is issued.

Also, move the line Server.ScriptTimeout to the top of the code. It should
ideally be the topmost line. Again, 6000 seconds (100 mins) is way too long
before browser gives up. You might shorten it to say 600 seconds.

I still feel you need to query the database asynchronously, and poll it to see if the results have been returned.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"David Berry" <db****@mvps.org> wrote in message
news:eL**************@TK2MSFTNGP10.phx.gbl...
Hi Manohar. I changed the code to:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "file name=e:\micro.udl"
cn.ConnectionTimeout = 6000
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandTimeout = 6000

Server.ScriptTimeout = 6000

cmd.CommandText = "SELECT ........... "

Set rs = cmd.Execute(cmd.CommandText)

And now I'm getting an error that the operation isn't allowed while the object is open. The line it points to is

cn.ConnectionTimeout = 6000

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message news:Oj**************@tk2msftngp13.phx.gbl...
> Dave,
>
> You can increase the CommandTimeout to a large number, but then you have to
> increase ASP script timeout as well. But then, if the time to execute the
> query is too long, your browser might just give up. Looks like the table is
> large, and as John suggested, the queried columns are not indexed.
>
> One thing you could do is to create a asyncronous query -- where the
results
> of the query go into a table, etc. Each query can be identified by a
number.
> You then implement a "Query executing, please wait..." page that checks
to
> see if the query has completed.
>
> --
> Manohar Kamath
> Editor, .netWire
> www.dotnetwire.com
>
>
> "David Berry" <db****@mvps.org> wrote in message
> news:ep**************@TK2MSFTNGP11.phx.gbl...
> > Hi John,
> >
> > Yes, I need them and no, they don't have indexes. I have no
control over
> > the database. It belongs to the customer and they don't want to

change
> it.
> >
> >
> > "John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in

message > > news:yO******************@newsfep4-glfd.server.ntli.net...
> > > >>>>>>>>>>>>>>>>>>
> > > "David Berry" <db****@mvps.org> wrote in message
> > > news:O0*************@tk2msftngp13.phx.gbl...
> > > Hi All. I have a SQL Statement on an ASP page that only returns 4 > > records.
> > > When I run it in SQL Server or Query Analyzer it runs in less than a
> > second.
> > > When I run it from my ASP page I get:
> > >
> > > Microsoft OLE DB Provider for SQL Server error '80040e31'
> > >
> > > Timeout expired
> > >
> > > the error line points to the rs.Open sql,con line.
> > >
> > > The SQL Statement is:
> > >
> > > SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct, CONT_DATE,
SALES_REP_NAME,
> > > DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS > JOIN
> > > ORDERS WHERE (CUST_SERVICE.ACCTNO NOT IN (SELECT DISTINCT acctno FROM
> > orders
> > > WHERE oredertype='Blowout Print' or oredertype='Print')) AND

CONT_DATE
> > > BETWEEN '12/23/03'AND'12/24/03' AND SALES_REP_NAME = 'Anne' AND
> PROD_TYPE
> > =
> > > 'P' AND CUSTOMER.BILLST = 'ct' AND ORDERS.oredertype = 'Print' ORDER BY
> > > CUST_SERVICE.ACCTNO
> > >
> > > Any ideas what could be causing this? I upped the
Server.ScriptTimeout,
> > > Con.ConnectionTimeout and comCommand.CommandTimeout and nothing

helps.
> > >
> > > Dave
> > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<
> > >
> > > Do you really need the DISTINCTs? Are you likely to get more
than one
> > record
> > > with exactly the same info?
> > >
> > > Do the fields in your WHERE clause have indexes?
> > >
> > >
> > >
> > > --
> > > John Blessing
> > >
> > > http://www.LbeHelpdesk.com - Help Desk software priced to suit

all > > > businesses
> > > http://www.free-helpdesk.com - Completely free help desk

software ! > > > http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
> > >
> > >
> >
> >
>
>



Jul 19 '05 #11
Dave-

Other more capable minds have addressed the timeout issue so I will not
rehash it. However, you may want to consider reworking the query itself.
I suspect it's the subquery that's degrading your performance. Here's
what I believe would be a functionally equivalent query that may perform
better. Obviously, the only way to know for sure is to test it.

SELECT DISTINCT
CUST_SERVICE.ACCTNO As Acct,
CONT_DATE,
SALES_REP_NAME,
DESCRIP,
PROD_TYPE,
MODE
FROM
ORDERS,
CUSTOMER,
CUST_SERVICE LEFT JOIN
(
SELECT DISTINCT
acctno
FROM
orders
WHERE
oredertype='Blowout Print' OR
oredertype='Print'
) AS ORDERS_BPP ON
CUST_SERVICE.ACCTNO = ORDERS_BPP.acctno
WHERE
ORDERS_BPP.acctno IS NULL AND
CONT_DATE > '20031223' AND
CONT_DATE < '20031224' AND
SALES_REP_NAME = 'Anne' AND
PROD_TYPE = 'P' AND
CUSTOMER.BILLST = 'ct' AND
ORDERS.oredertype = 'Print'
ORDER BY
CUST_SERVICE.ACCTNO

Notes:
1. Could you provide some details on the relationship between these
tables. The cross product joins seem out of place here. Guessing from
the names of the tables, I would suspect an ERD(Entity Relationship
Diagram) something like this:

ORDERS (+)---(1) CUSTOMER (1)---(+) CUST_SERVICE

If this is the case, your query could and should be taking advantage of
these relationships.

2. The difference between the above query and your original is the
placement of the Blowout Print/Print Orders subquery. In the original
query, the subquery is instantiated for each row of the main query. This
is unnecessary since the subquery is not dependant on the row data. This
is probably the reason for the poor performance.

3. You may want to consider using table aliases and using fully
qualified field references. It improves the legibility of the query and
eliminates ambiguity. For example, looking at the above query, I have no
idea to which table "DESCRIP" belongs.

4. You may want to consider using an ISO standard date format, i.e.
YYYYMMDD

5. You may want to consider replacing "BETWEEN" with less-than and
greater-than operators. Most modern databases engines have built-in
optimizations to do this conversion for you, but it is an unnecessary
extra step. The BETWEEN operator can also be somewhat confusing as it is
not clear whether it's inclusive or exclusive of the limits.

6. You may want to consider formatting your query text to improve their
legibility.

HTH
-Chris Hohmann
Jul 19 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by DarthMacgyver | last post: by
1 post views Thread by Tommy | last post: by
1 post views Thread by Steve | last post: by
1 post views Thread by Mani | last post: by
3 posts views Thread by news | last post: by
7 posts views Thread by =?Utf-8?B?Sm9obiBTdGFnZ3M=?= | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.