L
Loane Sharp
Hi there
I'm using an ADO 2.8 connection to a SQL Server 2000 database from a VBA
procedure in Excel 2003. Morever, I connect to the database server at the
office using a VPN link from home (both are connected to the internet using
broadband).
I have tried as far as possible to optimize my SQL statements (using
indexes, for example), and have set whatever properties for the ADO
recordset and connection objects that I can find (particularly the
CommandTimeout properties of the Connection and Command objects used to
create the recordsets, which I've set to 0 for "infinite", plus
resource-sparing AdForwardOnly, AdUseClient cursors etc.).
Still I get frequent timeouts and general network connection failures.
I seem to have isolated the problem to the database connection:
specifically, I declare the connection object only once (at the beginning of
the procedure) and then pass all the commands and recordsets over this
single connection, and then release the object at the end of the procedure.
Now, when I instantiate a different connection each time I pass a command or
return a recordset, I don't get the timeouts or general network connection
failures ... but my procedure runs incredibly slowly. About 90% of the time
taken to run my procedure (if SQL Profiler is a guide) is taken up by "Audit
Login" and "Audit Logout" events, clearly related to creating and destroying
a connection object so many times.
Is there anything I can do to speed things up a bit?
Best regards
Loane
I'm using an ADO 2.8 connection to a SQL Server 2000 database from a VBA
procedure in Excel 2003. Morever, I connect to the database server at the
office using a VPN link from home (both are connected to the internet using
broadband).
I have tried as far as possible to optimize my SQL statements (using
indexes, for example), and have set whatever properties for the ADO
recordset and connection objects that I can find (particularly the
CommandTimeout properties of the Connection and Command objects used to
create the recordsets, which I've set to 0 for "infinite", plus
resource-sparing AdForwardOnly, AdUseClient cursors etc.).
Still I get frequent timeouts and general network connection failures.
I seem to have isolated the problem to the database connection:
specifically, I declare the connection object only once (at the beginning of
the procedure) and then pass all the commands and recordsets over this
single connection, and then release the object at the end of the procedure.
Now, when I instantiate a different connection each time I pass a command or
return a recordset, I don't get the timeouts or general network connection
failures ... but my procedure runs incredibly slowly. About 90% of the time
taken to run my procedure (if SQL Profiler is a guide) is taken up by "Audit
Login" and "Audit Logout" events, clearly related to creating and destroying
a connection object so many times.
Is there anything I can do to speed things up a bit?
Best regards
Loane