Slow Insert in SQL Server

[Volks]

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.

[DA Morgan]

Re: Slow Insert in SQL Server

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
damorgan@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group

https://www.psoug.org

[Roy Harvey]

Re: Slow Insert in SQL Server

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" <patrick.simard @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.

[Jack Vamvas]

Re: Slow Insert in SQL Server

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" <patrick.simard @isac-inc.comwrote in message news:1155756964 .614937.145000@ h48g2000cwc.goo glegroups.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.
>

[Dan Guzman]

Re: Slow Insert in SQL Server

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" <patrick.simard @isac-inc.comwrote in message
news:1155756964 .614937.145000@ h48g2000cwc.goo glegroups.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.
>