Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
0

ODBC timeout errors

Guest
May 08, 2007 May 08, 2007

Copy link to clipboard

Copied

I have a site I created some time ago in DW connecting to a SQL database using an ODBC connection. Some of the queries are now taking so long that I have started getting these timeout errors;
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired

The underlying problem is that the queries on the SQL db are now taking very much longer than they used to! I have got them 'tuned' as much as I can but the timeouts still occur - there is just so much data in the tables!
Looking around various ASP/ADO tutorials would seem to indicate I should be adjusting the 'connection.commandtimeout' value but DW (I'm using MX v6) isn't being very obvious with where I put in this value!
I have a connection file, connPathData.asp, that looks like this;
<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_connPathData_STRING
MM_connPathData_STRING = "dsn=pathnet_data;"
%>
... although I could have used;
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_connPathData_STRING
MM_connPathData_STRING = "PROVIDER=SQLOLEDB;DATA SOURCE=PATHOLOGY1;UID=user;PWD=pass;DATABASE=pathnet_data"
%>

Then the actual page that uses the data looks like this;
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/connPathData.asp" -->
<%
set rsDept_Totals = Server.CreateObject("ADODB.Recordset")
rsDept_Totals.ActiveConnection = MM_connPathData_STRING
rsDept_Totals.Source = "SELECT DEPT, Year, Month etc..."
rsDept_Totals.CursorType = 0
rsDept_Totals.CursorLocation = 2
rsDept_Totals.LockType = 1
rsDept_Totals.Open()
rsDept_Totals_numRows = 0
%>

My problem is that DW doesn't seem to be *explicitly* using the connection object, so I can't see where to include the commandtimeout value.

Apologies in advance for X-posting this to the 'general' forum initially, upon reflection I thought folks round here would be more versed in this kind of thing (-;

Simon Harris
TOPICS
Server side applications

Views

603
Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 08, 2007 May 08, 2007

Copy link to clipboard

Copied

You'd be better off sticking with OLEDB, it's much faster than ODBC. If
you're getting timeouts, you be best advised to look into database
optimisation rather than code optimisation.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004


Votes

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 08, 2007 May 08, 2007

Copy link to clipboard

Copied

Jules,
Thanks for the pointer. So what does my 'connection string' look like with your suggestion in the above example? Would the code
Dim MM_connPathData_STRING
MM_connPathData_STRING = "PROVIDER=SQLOLEDB;DATA SOURCE=PATHOLOGY1;UID=user;PWD=pass;DATABASE=pathnet_data"
be significantly quicker?

I *have* looked at the database but my problem is there is nothing I can do with the underlying structure - I'm just working with what I'm given )-; What I need is a way of connecting to the data regardless of the response time. What I am giving the users is a view on *their* data - essentially, if it's slow then that is their problem!!
Again, thanks for all and any input,

Simon

Votes

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 08, 2007 May 08, 2007

Copy link to clipboard

Copied

Yes, that string should be quicker. Also, try using:

Server.ScriptTimeout=123456

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004


Votes

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 09, 2007 May 09, 2007

Copy link to clipboard

Copied

LATEST
Also look at indices. No indiceds or insufficient indices can really
slow down a query.

Votes

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines