Toad World® Forums

Very slow TPC-C table loading with MS SQL


#1

Hi,
I’m currently running TPC-C (1000 userload) with MS SQL 2008. The whole mixed transaction testing only took 5 minutes, but the job (including re-loading all the tables) would take more than 3 hours. This is very frustrating.

I ran the same test on an Oracle 11g and it took about 40min. which is less than 1/3 of the time. It’s still a long time, but I can live with that.

As I searched thru the Knowledge Base, there’re some recommendations as:

  • Increasing the initial database size.
  • Using the SQL Server Native driver.

I’ve tried both of those and it didn’t help either.

Any other advise would be appreciated.

Thanks


#2

In my experience load is almost always the longest part of the test. There are a couple of things you can do to speed it up - mostly basic performance troubleshooting.

The most effective strategy I’ve found for running multiple test iterations is to separate the object creation section of the test from the load. Then you can create a backup of the test database before running the load, and restore that database before the next iteration.

Hope this helps.

John


#3

Hi John,
Does backup/restore database work for different userload test?

Thanks


#4

Not really. I simply replace any object deletion/creation steps in the BMF script file with a script to run a restore from my backup.

John