473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Slow Insert in SQL Server

Hi,

It takes 4 minutes to insert 40 000 records on a SQL Server and 40 sec
on an other SQL Server. The slower run on windows 2000 terminal server
and the faster on windows 2000. The slower The slower have 2 Go of
ram, the faster 512 Mo!!!

How to diagnostic what's going wrong on the slower server? I can't
change the application who do the insertions.

Aug 16 '06 #1
4 13642
Volks wrote:
Hi,

It takes 4 minutes to insert 40 000 records on a SQL Server and 40 sec
on an other SQL Server. The slower run on windows 2000 terminal server
and the faster on windows 2000. The slower The slower have 2 Go of
ram, the faster 512 Mo!!!

How to diagnostic what's going wrong on the slower server? I can't
change the application who do the insertions.
That is amazingly slow. Using a different commercial database product,
on my slightly anemic IBM ThinkPad (2GB RAM) I just id 40,000 inserts
in 0.14sec.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Aug 16 '06 #2
Compare the hard drive configurations. SQL Server is, after all, a
database system, and disk performance is overwhemlingly important.
More important than CPU or memory for a single load process. Factors
that could slow such a process down include logs not isolated on their
own drives and RAID 5, always slow on writes. Of course combining
those two could be particularly bad.

Next, where is the data coming from? Over the network? From another
hard drive on the same server? From the same hard drive as the
database files? Is the location of the data the same for both
servers?

Roy Harvey
Beacon Falls, CT

On 16 Aug 2006 12:36:04 -0700, "Volks" <pa************@isac-inc.com>
wrote:
>Hi,

It takes 4 minutes to insert 40 000 records on a SQL Server and 40 sec
on an other SQL Server. The slower run on windows 2000 terminal server
and the faster on windows 2000. The slower The slower have 2 Go of
ram, the faster 512 Mo!!!

How to diagnostic what's going wrong on the slower server? I can't
change the application who do the insertions.
Aug 16 '06 #3
1.Run Processor -% Processor Time
SQLServer:SQL Statistics -Batch Requests/sec
over the period of the INSERTS

2.Are these INSERTS coming from an app over a network?


----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Volks" <pa************@isac-inc.comwrote in message news:11**********************@h48g2000cwc.googlegr oups.com...
Hi,

It takes 4 minutes to insert 40 000 records on a SQL Server and 40 sec
on an other SQL Server. The slower run on windows 2000 terminal server
and the faster on windows 2000. The slower The slower have 2 Go of
ram, the faster 512 Mo!!!

How to diagnostic what's going wrong on the slower server? I can't
change the application who do the insertions.
Aug 17 '06 #4
It takes 4 minutes to insert 40 000 records on a SQL Server

This performance is exactly what I would expect when each insert is done in
an individual transaction and there is no write caching. A physical
transaction log write is required during COMMIT to guarantee that data are
permanently persisted and a disk can typically sustain only 150-200 I/Os per
second.

Server grade hardware usually has write-caching that greatly improves write
performance while ensuring persistence (e.g. controller cache battery
backup). However, you can probably improve performance greatly with your
current configuration by performing the inserts in a single transaction.
This ought allow you to achieve the kind of performance Daniel alluded do.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Volks" <pa************@isac-inc.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi,

It takes 4 minutes to insert 40 000 records on a SQL Server and 40 sec
on an other SQL Server. The slower run on windows 2000 terminal server
and the faster on windows 2000. The slower The slower have 2 Go of
ram, the faster 512 Mo!!!

How to diagnostic what's going wrong on the slower server? I can't
change the application who do the insertions.

Aug 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around...
4
by: Bri | last post by:
Hi, First let me explain the process I have going on, then I'll address the problems I'm having: 1) Insert records in a temp table using a query 2) Using a query that joins the temp table with...
1
by: Noman | last post by:
i am developing a system using VB 6 and Ms Access 2000. overall the system is working fine but at few instances the program get stuck for few minutes like 2 to 3 minutes i.e it is very slow. the...
29
by: Geoff Jones | last post by:
Hi All I hope you'll forgive me for posting this here (I've also posted to ado site but with no response so far) as I'm urgently after a solution. Can anybody help me? I'm updating a table on...
20
by: John Bailo | last post by:
I have a c# program that loops through a table on a DB2 database. On each iteration it assigns data to values in the SqlParameter collection. The command text is an INSERT statement to a Sql...
9
by: dan | last post by:
within a loop i am building a sql insert statement to run against my (programatically created) mdb. it works but it seems unreasonably SLOW! Sorry, dont have the code here but the jist is very...
21
by: lesperancer | last post by:
I've got an access97 reporting mdb that pulls data (77,000 rows) from a sql server table into a local table to run reports if the local table is part of the reporting MDB, the insert statement...
7
by: eselk | last post by:
I'm doing some speed tests. I created a brand-new table, with just one "Long Integer" field. I'm testing adding 1000 records. If I use the "Export" feature in Access, it takes only a few seconds...
7
by: Don Li | last post by:
Hi, Env is ms sql server 2000. ddl: create table srchPool(tid int primary key, taid int, s tynyint, uid tynyint); -- and sql server automatically creates a clustered index for the pk dml:
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.