You can improve performance of your MDAC application by using native OLEDB providers instead of OLEDB provider for ODBC. MDAC 2.0 contains the native OLEDB providers for three SQL data stores: SQL Server, Access, and Oracle. If you go through the OLE DB Provider for ODBC Data, then it internally uses the appropriate ODBC driver to access the data store. With MDAC 2.0, you can use these native OLE DB providers to access your data faster and with lower disk and memory footprint. Usage of native OLEDB provider improves performance by removing the extra ODBC layer. Native providers connect more quickly to the data stores then the ODBC counterparts. Apart from this you should try to specify details in the connection string instead of just specifying the DSN because specifying DSN results in registry lookups for the connection details.
If you are using native OLEDB provider to connect to a SQL Server then your connection string would like:
Provider=SQLOLEDB; Data Source=MyDatabaseServer; Initial Catalog=MyDatabase; User Id="sa"; Password="" If you are using OLEDB provider for ODBC to connect to a SQL Server then your connection string would like:
Driver={SQL Server}; Server=MyDatabaseServer; Database=MyDatabase; UID="sa"; PWD="" If you are just using the DSN style of connecting string then it would look like:
DSN=MyAppDSN
