Friday, March 30, 2012

poor preformance usinng sql 2005 with access 2003 application

we have run access 2003 application using SQL 2005 server
and the data transfer rate is extreamly slow.
we are using ODBC connection
and the access apllication works very fast with SQL 2000.

we have the tested the comptabilty of the application to SQL 2005 with Microsoft wizard and it had no problems.
even when i run simple application on the sql 2005 server, it runs very slowly.

i am using the MDAC clients ODBC component and the SQL Native Clients one.
which eliminates the possibilty of network problem.

what could be the problem?

Most likely the problem is improper indexing on the SQL Server side. Typically the driver used is not going to have a huge impact on overall performance. The simplest way to verify if you have index issues is run your application against SQL 2005 and capture a SQL Profiler trace of the activity. Then take the profiler trace and run it though the "Database Engine Tuning Advisor" tool that comes with SQL 2005 and it will analyze the trace and make recommendations as to what indexes to build.

No comments:

Post a Comment