ODBC timeout errors

[Guest]

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

[Newsgroup_User]

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

[Guest]

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

[Newsgroup_User]

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

[Newsgroup_User]

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