0

We experiment low performance with SQL-server 2008 when performing massive INSERT queries. These queries are enclosed into transactions by batches of 25, 50, 100, or 200 rows.

Different factors influences the DB performance:

  • the number of concurrent queries handle at the same time by the database

  • less obviously, the complexity of the INSERT (normal speed when 8 columns, but 60 times slower when 20 columns !)

  • and more unexpectedly, the client application: we have tested jTDS, SQSH (command line) and Toad (GUI).

The following graph shows how Toad is much faster and steady than the 2 other applications: enter image description here NB: we have also tested SQL Server Express and it's as fast as Toad.

How can Toad be more performance than the two other lighter application? How can we reproduce this performance with jTDS or SQSH? Any idea?

We offer a beer !

François

user2335044
  • 41
  • 4

1 Answers1

1

I have said this in previous questions but there are a few things you should look at.

Item one: Have/can you look at your execution plan, that will often point out where the root of the problem is. One misformed junction or conversion can kill a servers performance.

Item two: Look for implicit conversions, I recently reduced a query's performance time from 2 mins to about 10 sec by changing the filter variable from a datetime object to a smalldatetime object. That removed the implicit conversion on the 6 million rows I was comparing it to.

Item three: This is expensive, we found that it was cheaper to make a copy of a table (Then deal with constraints,permissions,indexes and triggers) with our transforms/inserts happening during the copy process than it was to update those rows.

orgtigger
  • 3,914
  • 1
  • 20
  • 22
  • Thanks OrgTigger but in this case, the _same_ SQL script (supposed optimized) is executed with _different_ performance depending on the client application. I don't believe that Toad, the fastest app, optimize my code before executing it. So how can we explain such differences? – user2335044 Oct 03 '13 at 07:49