分享程序员开发的那些事...
更新时间：2023-02-26 20:22:01
>>>>>>>>>>>>>>>>>>>>>>>
" David Berry" ; <分贝**** @ mvps.org>在留言中写道
新闻：O0 ************* @ tk2msftngp13.phx.gbl ...
大家好。我在ASP页面上有一个只返回4条记录的SQL语句。
当我在SQL Server或查询分析器中运行它时，它运行不到一秒钟。
我从我的ASP页面运行它得到：
Microsoft OLE DB提供程序用于SQL Server错误''80040e31''
超时已过期
错误行指向rs.Open sql，con line。
SQL语句是：
SELECT DISTINCT CUST_SERVICE.ACCTNO As Acct，CONT_DATE，SALES_REP_NAME，
DESCRIP，PROD_TYPE，MODE FROM CUST_SERVICE CROSS JOIN CUSTOMER CROSS JOIN
订单在哪里（CUST_SERVICE.ACCTNO NOT IN（SELECT DISTINCT acctno FROM orders
WHERE oredertype =''Blowout Print''或oredertype =''Print''））和CONT_DATE
BETWEEN''12 / 23 / 03''AND''12 / 24 / 03''AND SALES_REP_NAME =''Anne''和PROD_TYPE =
''P''和CUSTOMER.BILLST =''ct''AND ORDERS.oredertype = ''打印''订购'
CUST_SERVICE.ACCTNO
任何想法可能导致这种情况？我提高了Server.ScriptTimeout，
Con.ConnectionTimeout和comCommand.CommandTimeout并没有任何帮助。
Dave
<< ;<<<<<<<<<<<<<<<<<<<<<<<<< ;<
你真的需要DISTINCT吗？您是否可能使用完全相同的信息获得多个记录
？
WHERE子句中的字段是否有索引？
-
John Blessing
http://www.LbeHelpdesk.com - 价格适合所有
商家的服务台软件
http://www.free-helpdesk.com - 完全免费的帮助台软件！
http://www.lbetoolbox.com - 从MS Outlook中删除重复项
约翰，
是的，我需要他们，不，他们没有索引。我无法控制
数据库。它属于客户，他们不想改变它。
John Blessing < JB @ ** ** REMOVE THIS ** LbeHelpdesk.com>在消息中写道
news：yO ****************** @ newsfep4-glfd.server.ntli.net ...>>>>>>>>>>>>>>>>" David Berry" <分贝**** @ mvps.org>在消息中写道
新闻：O0 ************* @ tk2msftngp13.phx.gbl ...
大家好。我在ASP页面上有一个SQL语句，它只返回4个
记录。当我在SQL Server或查询分析器中运行它时，它的运行时间不到
秒。当我从我的ASP页面运行它时，我得到：
Microsoft OLE DB Provider for SQL Server错误''80040e31''
超时已过期
错误行指向rs.Open sql，con line。
SELECT语句是：
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（选择DISTINCT acctno FROM
命令WHERE oredertype =''Blowout Print''或oredertype =''打印''））和CONT_DATE
BETWEEN''12 / 23 / 03''AND''12 / 24/03''和SALES_REP_NAME =''安妮''和PROD_TYPE
=''P'' AND CUSTOMER.BILLST =''ct''和ORDERS.oredertype =''打印''订购者来自
CUST_SERVICE.ACCTNO
任何想法可能导致这种情况？我提升了Server.ScriptTimeout，
Con.ConnectionTimeout和comCommand.CommandTimeout，没有任何帮助。
Dave
<<<<<<<<< ;<<<<<<<<<<<<<<<<<<<<<<<
你真的需要DISTINCT吗？您是否可以使用完全相同的信息获得多于
的记录？
WHERE子句中的字段是否有索引？
-
John Blessing
http://www.LbeHelpdesk.com - 价格适合所有
业务的服务台软件
http：// www .free-helpdesk.com - 完全免费的帮助台软件！
http：/ /www.lbetoolbox.com - 从MS Outlook中删除重复项
Dave，
你可以将CommandTimeout增加到一个很大的数字，但是你必须增加
以增加ASP脚本超时。但是，如果执行
查询的时间太长，您的浏览器可能会放弃。看起来这个表是很大的，并且正如John建议的那样，查询的列没有编入索引。
你可以做的一件事就是创建一个异步查询 - 查询的结果
进入表等等。每个查询都可以用数字标识。
然后你实现一个查询执行，请等待..."检查的页面
查看查询是否已完成。
-
Manohar Kamath
编辑，.netWire
www.dotnetwire.com
David Berry <分贝**** @ mvps.org>在消息中写道
news：ep ************** @ TK2MSFTNGP11.phx.gbl ...嗨John，
是的，我需要它们，不，它们没有索引。我无法控制数据库。它属于客户，他们不想改变
。
John Blessing < JB @ ** ** REMOVE THIS ** LbeHelpdesk.com>在消息中写道
新闻：yO ****************** @ newsfep4-glfd.server.ntli.net ...>>>>>>>>>>>>>>>>>>> 大卫贝瑞 <分贝**** @ mvps.org>在消息中写道
新闻：O0 ************* @ tk2msftngp13.phx.gbl ...
大家好。我在ASP页面上有一个SQL语句，它只返回4个
记录。当我在SQL Server或查询分析器中运行它时，它运行的时间不到
第二。当我从我的ASP页面运行它时，我得到：
用于SQL Server的Microsoft OLE DB提供程序错误''80040e31''>
超时过期
错误行指向rs.Open sql，con line。
SQL语句是：
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
WHERE oredertype =''Blowout Print''或oredertype =''打印''））和CONT_DATE
BETWEEN''12 / 23 / 03''AND'' 12/24/03''和SALES_REP_NAME =''安妮''和e>
PROD_TYPE =''P''和CUSTOMER.BILLST =''ct''和ORDERS.oredertype =''打印''ORDER BY
CUST_SERVICE.ACCTNO
任何想法可能导致这个？我提升了Server.ScriptTimeout，
Con.ConnectionTimeout和comCommand.CommandTimeout，没有任何帮助。
Dave
<<<<<<<<< ;<<<<<<<<<<<<<<<<<<<<<<<
你真的需要DISTINCT吗？您是否可能使用完全相同的信息获取多个
记录？
WHERE子句中的字段是否具有索引？
-
John Blessing
http ：//www.LbeHelpdesk.com - 价格适合所有商家的服务台软件
http://www.free-helpdesk.com - 完全免费的帮助台软件！
http://www.lbetoolbox.com - 从MS Outlook中删除重复项
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
>>>>>>>>>>>>>>>>>>
"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
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
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
ordersWHERE 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
recordwith 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