Are you still connecting to the database using an ODBC system or a file DSN? Replace the DSN connection database with a faster OLE DB provider technology, and you don't have to ask your ISP (or your database administrator/web Administrator) to create a System DSN for you, or to modify the configuration because you moved the file.
OLE DB is between the ODBC layer and the application. For ASP pages, ADO is an "application" above OLE DB. ADO calls are first sent to OLE DB before being sent to the ODBC layer. But you can also connect directly to the OLE DB layer, and if you do, you'll see a further improvement in performance. So, how do you connect directly to OLE DB?
If you are using SQL Server 7, use the following code as the connection string:
strconnstring = "dsn= ';D river={sql SERVER};" & _
"UID=MYUID; Pwd=mypwd; "& _
"DATABASE=MYDB; Server=myserver; "
If you want to bypass ODBC and use SQL Server via OLE DB, use the following syntax:
strconnstring = "Provider=SQLOLEDB.1; Password=mypassword; "& _
"Persist security info=true; User Id=myuid; "& _
"Initial catalog=mydbname;" & _
"Data Source=myserver; Connect timeout=15 "
Now you may want to know what is the benefit of learning this new connection method. Why not use the standard DSN method? According to the test results in the Wrox in ADO 2.0 Programmer's Reference, using the OLE DB connection and using the DSN connection performance variance is the following table:
<table>
<th><td>SQL</td><td>Access</td></th>
<tr><td> Project OLE DB dsn</td><td> project OLE DB dsn</td></tr>
<tr><td> Connection time: 82</td><td> connection time: 99</td</tr>
<tr><td> Iteration 1000 record time: 2900 5400</td><td> Iteration 1000 record time: 950 </td></tr>
</table>
Note: The above results are in 232, 233 pages of the Book 2.0 Programmer ' s reference in ADO. Time in milliseconds, the time to iterate 1000 records is computed with a server-side cursor (the performance of OLE DB and DSN recordset iterations is not significantly different when using client cursors).
