Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i have a strange issue with a windows application (in delphi) running on a win7 client. The application has a loops and inserts rows on a SQL Server table. It's simply an insert statement, repeated on the same table. For the first 9-10 inserts, the performance is good (approx 10 milliseconds per insert) From the 10th insert, the time suddenly increase to approx 400-500 ms. It's not a deterioration... if you go ahead, the performance is constant, always 400-500 ms. The performance changes only from the 9th to 10th insert. And it pass from 10 to 500 ms. During this time, the CPUs of client and server seems not to be overloaded, and the network too.

The server is an HP ML 370 G6 with Windows Server 2008R2, and multiple sql installed: 2005, 2008 and 2008R2

If i attach the DB to 2005, 2008 or 2008R2 it's the same. If i move the DB files from the SAN to a local SAS disk, it's the same If i change client, it's the same

but.... If i run the application directly on the server, the performance is excellent, straight to end of the loop.

So, i decided to use another server... well, it's not a server, at all. It's just a pc (dual core).. but has the same operating system (srv2008r2), and SQL server 2008 installed. If i move the Database to this "server", and launch the application from the same client, i have an excellent performance, not so different from running the application directly on the HP server.

What else can i do? Am i missing something?




migration rejected from Sep 20 '13 at 14:37

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Max Vernon, RolandoMySQLDBA, dezso, ypercubeᵀᴹ, Mark Storey-Smith Sep 20 '13 at 14:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RolandoMySQLDBA, dezso, ypercubeᵀᴹ, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

Are you opening a connection for every insert? If yes, are you using connection pooling? – rivarolle Apr 11 '13 at 20:51
No, i open a single connection. the whole loop is under the same transaction. – Liscio Apr 11 '13 at 22:08
Did you try wrap each INSERT in it's own database transaction, while still using just the one connection? – RoKa Apr 12 '13 at 8:47
No, but i have to keep all them under the same transaction... anyway, i don't think it can be a transaction-related issue. If i move the DB to a different server, i don't have the issue... i also tried to "detach-attach" the DB to a different server, so i have the exact same data, and it has excellent performance. So, i'm thinking about sql server configuration... or something about the server's network interface configuration. – Liscio Apr 12 '13 at 9:51
I'd start with a comparison between (particularly the Connections and Advanced Page) under server properties. – RoKa Apr 12 '13 at 10:05

Browse other questions tagged or ask your own question.