Timeout Error Question

[David Berry]

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.AC CTNO As Acct, CONT_DATE, SALES_REP_NAME, DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN ORDERS WHERE (CUST_SERVICE.A CCTNO NOT IN (SELECT DISTINCT acctno FROM orders WHERE oredertype='Blo wout Print' or oredertype='Pri nt')) 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.orederty pe = 'Print' ORDER BY CUST_SERVICE.AC CTNO

Any ideas what could be causing this? I upped the Server.ScriptTi meout, Con.ConnectionT imeout and comCommand.Comm andTimeout and nothing helps.

Dave

[Manohar Kamath [MVP]]

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.Exec ute to execute your
query.
3. Loop to find out if the Connection.Stat e is adStateExecutin g, loop until
this changes.
4. While in the loop, check to see if the client is still connected
(IsClientConnec ted of Request object). If not, do a Connection.Canc el 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.Stat e 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.or g> wrote in message
news:%2******** ********@TK2MSF TNGP11.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*****@TAKETH ISOUTkamath.com > wrote in message
news:Oq******** ******@TK2MSFTN GP10.phx.gbl...

Dave,

Swap the lines to:

cn.ConnectionTi meout = 6000
cn.Open "file name=e:\micro.u dl"

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.ScriptTi meout 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.or g> wrote in message
news:eL******** ******@TK2MSFTN GP10.phx.gbl...

Hi Manohar. I changed the code to:

Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.Open "file name=e:\micro.u dl"
cn.ConnectionTi meout = 6000
Set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveConne ction = cn
cmd.CommandTime out = 6000

Server.ScriptTi meout = 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.ConnectionTi meout = 6000

Dave

"Manohar Kamath [MVP]" <mk*****@TAKETH ISOUTkamath.com > wrote in message news:Oj******** ******@tk2msftn gp13.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.or g> wrote in message
> news:ep******** ******@TK2MSFTN GP11.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**T HIS**LbeHelpdes k.com> wrote in

message > > news:yO******** **********@news fep4-glfd.server.ntl i.net...
> > > >>>>>>>>>>>>>>> >>>
> > > "David Berry" <db****@mvps.or g> wrote in message
> > > news:O0******** *****@tk2msftng p13.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.AC CTNO As Acct, CONT_DATE,
SALES_REP_NAME,
> > > DESCRIP, PROD_TYPE, MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS > JOIN
> > > ORDERS WHERE (CUST_SERVICE.A CCTNO NOT IN (SELECT DISTINCT acctno FROM

> > orders
> > > WHERE oredertype='Blo wout Print' or oredertype='Pri nt')) 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.orederty pe = 'Print' ORDER BY
> > > CUST_SERVICE.AC CTNO
> > >
> > > Any ideas what could be causing this? I upped the
Server.ScriptTi meout,
> > > Con.ConnectionT imeout and comCommand.Comm andTimeout 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
> > >
> > >
> >
> >
>
>

[Chris Hohmann]

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.AC CTNO 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='Blo wout Print' OR
oredertype='Pri nt'
) AS ORDERS_BPP ON
CUST_SERVICE.AC CTNO = ORDERS_BPP.acct no
WHERE
ORDERS_BPP.acct no 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.orederty pe = 'Print'
ORDER BY
CUST_SERVICE.AC CTNO

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