Increasing performance

[Jordan]

I have just moved my back end from an MDB file on the file server to a new
SQL 2000 SP3 server that has:

Dual 64 bit Processors
Dual GB Nics
2 gig RAM
800BG Raid 5

The performance is terrible. Before, copying 250,000 records from one MDB
table to another only took about a minute, now it takes 5+. When I open a
table via an ODBC attach the screen will pause after every 15 or so records
for about half a second. It took 20 minutes to do a mass update to 9000 of
the 250,000 records when doing the SELECT only took 6 seconds. When this
was in the MDB file it only took about a second to get the 9000 record
SELECT and about 4 seconds to do the UPDATE.

Obviously since I am new to SQL server, I must be doing something wrong. I
pretty much chose all the defaults when installing SQL and the Service Pack,
and I used the SQL import wizard to suck my MDB file in to become the SQL
tables. The only thing I did to my Access 2002 front end was change the
ODBC attached tables to point to the SQL server.

Any tips

 

[aaron.kempf]

give us more information

you're doing 100% MDB against ODBC?

you need to use ADP instead.. it is much better

 

[Philipp Stiefel]

you need to use ADP instead.. it is much better

No it isn't better in general and it's definitely not the only
solution to improve performance.

But you are certainly right that one needs more information to
be more specific.

Cheers
Phil