Increasing performace on SQL back end

[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

 

[Rick Brandt]

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

Are both tables that you are copying between ON the SQL Server or are you
copying between a server table and a local table? The latter will always be
slower because you are moiving the data over the LAN. If the former then
you should use a pass-through query to ensure that all of the transfer is
taking place on the server.

Access/Jet is pretty forgiving of bad designs on tables and queries. When
you move to a server back end you often have to "fix" or redesign quite a
bit to take advantage of a client/server architecture.

 

[Guest]

Hi Jordan,

I had a similar problem, and the way around it for me was to move to an ADP,
and create views and stored procedures on the server. Doing that resolved my
issues.

John