Slow Sales Order form

[Ben]

Hi all!

Several weeks ago, we successfully transferred our database from SQL Server
2000 to 2005. Below are our steps in transferring the database from 2000 to
2005:

a) Backuped our SQL Server 2000 database
b) Created an empty database in SQL Server 2005
c) Restored our SQL Server 2000 database to the empty SQL Server 2005
database

At first, after the transfer, all functions of the application went very
slow. Once I performed the update statistics, the speed of all the
functions went back to normal except for some. This is what is causing me
headaches right now - these functions that are still slow because all
functions worked fine in the SQL Server 2000. I did not change any code.

Each workstation is using the SQL Server 2005 ODBC driver.

Below is the issue that needs to be solved:
1) Some functionalities take so long to execute or finish executing.
examples:
a) "Find" function - After finding the specific customer number
or phone number
and clicking the "Done" button, which should take the user
back to the customer
form, it takes like 3 to 6 seconds wherein before it took
only 1-2 seconds.

b) "Sales Order" form - After clicking the "Done" button, which
would take the user
back to the main menu, it takes from 4 - 9 seconds to
finish. Before it took only
1-2 seconds.

Below are the things that I have tried so far, which did not work:
1) Rebuilt all the indexes
2) Took out the "Refresh" lines that were not needed in the app code
3) Set the compatibility level from 80 to 90
4) Dropped the "dtproperties" system table, which are not being used
anymore in SQL Server 2005

Any ideas would be great! Thanks!

 

[Arvin Meyer [MVP]]

It would seem that this is a SQL-Server issue since your front-end didn't
change at all.

To check on the form speed use the GetTickCount api:

Private Declare Function apiGetTickCount Lib "kernel32" Alias "GetTickCount"
() As Long

and a snippet of the code would be:

Dim lngTimeElapsed As Long
Dim lngStart As Long
Dim lngEnd As Long

lngStart = apiGetTickCount()

'Do something

lngEnd = apiGetTickCount()
lngTimeElapsed = lngEnd - lngStart


I'd check it with both SQL 2000 and 2005

 

[Ben]

By the way, I noticed one thing:

If I run the MS Access in the server computer itself, the slow performance
issues are not visible. But then again the hardware has two Intel Quad-Core
Xeon processors, 10GB of RAM, and 15,000RPM SATA hard drives. I just wanted
to point it out here maybe it tells something. I am still leaning towards
network connection problem or ODBC configuration.

It is still very slow.

 

[Ben]

An update on this issue, I am still experiencing this slowness problem. I
have tried from executing the maintenance processes, to checking the codes
of the MS Access 2003 application.

One new thing that we found is that if we access the MS Access application
from one of the workstations via remote desktop, the speed is very fast.
So, I do not know what is the relationship between remote desktop, the
network, and SQL Serve 2005 and the application being faster or accessing
the database faster.

Does anyone have an idea?

 

[Ben]

The main problem in this issue was the packet size. The packet size setting
of SQL Server 2005 required it to be increased. So, after the update
statistics and reindexing, the packet size just had to be increased and that
solved the slowness problem.