ODBC timeout errors

Report · May 08, 2007

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"%>

<%
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

Report · May 08, 2007

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

Report · May 08, 2007

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

Report · May 08, 2007

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

Report · May 09, 2007

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