Access 97 and SQL 2000

Question

Access 97 and SQL 2000

Zero One

Old Hand

Points: 337
More actions
February 15, 2007 at 2:06 pm

#95160

Hi Guys,
Shortly after starting a new DBA position I have inherited a funky access/sql 2000 combination and am running into a wall.
Here is the Situation:
Past:
The old server specs.
OS Windows 2003 32bit
SQL Server 2000 32bit
An Access 97 db had been accessing a single table on one of our servers for some time through some sort of pass through query joining the remote table with the local access ones. It would take around 5 seconds to retrieve a record.
Current:
OS Windows 2003 64bit
SQL Server 2000 32bit
Now the access application takes five minutes to pull the one record and seems to running differently. Instead of making a remote join and grabbing only the 1 record it is pulling the whole table over before running the join.
Supposedly the people using the access app say they have never altered any of the setting aside from the connection string pointing it to the new server.
Assuming they didn't alter anything on the access side, what could account for this change in behavior? OS went from 32 -> 64bit. SQL server is still 32 bit. The old server is gone so I can't reference any settings unfortunately.
Any and all ideas of what else I could check for are welcome.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

"I did not change anything" is something always being heard from develpers. There must be something has been changed whatever that is.

I suggest to trace through SQL Profiler. You will see what SQL statements are triggered from Access.

Lowell SSC Guru Points: 323518 More actions · Answer 2

could it be something as simple as statistics or something?

I had a situation where a developer turned off automatic statistics in his test database, and over time his machine seemed to get slower and slower when running an app that was full of queries;

some investigation was done, found that was the issue, turned auto statistics on and manually updated statistics, and it was quick again.

That of course, doesn't explain why if the app was gabbing one record, and is now grabbing the qhole table.

also, did you restore the database from a backup, or did you use Enterprise Manager to "copy" the tables....if that is it, maybe you are missing a lot of indexes that were created that would have beent here previously?

That's about all I can think of at the moment.

HTH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Actually the reason is very simple Access even 2003 is not even 40 percent ANSI SQL compliant in types the pointer Arithmetic of the 64bits is different so Access is confused. Quick solution move the queries and tables to SQL Server tables or views and your problem will go away. If the above is not an option then run the code and click on show execution cost and you maybe surprised what you see. Hope this helps.

Kind regards,
Gift Peddie

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

The pass-thru query to SQL Server should still run the same, since Jet does not touch that. But joining the resulting data to local Access tables will always be a bottleneck, using "heterogenous joins". A better approach might be to populate a local table from the pass-thru, and then joining that to the other local table.

Zero One Old Hand Points: 337 More actions · Answer 5

"I suggest to trace through SQL Profiler. You will see what SQL statements are triggered from Access." -Terry

Good suggestion, we had run it through a trace and found that the query is a select of the one table with no where clause which answers the obvious question, it is grabbing the whole table of 600k records.

"could it be something as simple as statistics or something?

I had a situation where a developer turned off automatic statistics in his test database, and over time his machine seemed to get slower and slower when running an app that was full of queries;

some investigation was done, found that was the issue, turned auto statistics on and manually updated statistics, and it was quick again."-Lowell

Nope, auto-statistics are enabled I am unsure of the means the used to move from one server to another.

"Quick solution move the queries and tables to SQL Server tables or views and your problem will go away. If the above is not an option then run the code and click on show execution cost and you maybe surprised what you see. Hope this helps."-Gift Peddie

That would be a good suggestion. Moving the pointers to another server running like the original one (32bit/32bit) would definitely eliminate whether the 62bit OS is causing any problems.

Thank you all very much for your helpful suggestions.

This is a case of C.Y.A. We as the DBA group are just trying to make sure that we have left no stone unturned in researching this so we can come back with firm answer as to what need to be done to correct this problem