Re: SQL Server Dual Processors INSERTS take long time

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 09:34:48 -0500

There can be lots of differences between machines that may not be obvious
unless you monitor what is going on with tools like profiler and especially
perfmon. Look at the disk, memory, cpu etc. counters to see where the
bottleneck is so you can focus on the right things. Just because a server
has scsi disks does not in any way shape or form mean SQL Server activity
will be faster than on a laptop. Actually most of the time it is just the
opposite due to the large amounts of disk cache on modern IDE drives. Does
the scsi controller have caching and if so how is it set? It should be set
for 100% writeback and usually they come 50 / 50. If you have no indexes at
all you might try adding a clustered index on the table. If the data is
going to be inserted in a particular order of the clustered index you will
probably get better performance than inserting into a heap. Or you can add
(temporarily or not) an identity column and cluster on that. 

-- 
Andrew J. Kelly  SQL MVP
"FandCDev2" <FandCDev2@discussions.microsoft.com> wrote in message 
news:FC913F64-0B80-4277-8358-2B702B247B1D@microsoft.com...
>I am doing all of the inserts before creating any index, and I am the only
> user working with the db.  I have tried leaving the log file on the same
> drive as the db, and putting on the other drive, and seen no difference.
>
> I know using an ADO.NET object or a bulk insert would be the better
> solution, but I need to figure out why inserts take longer on this machine
> for other parts of the app, that need to be done in real time.
>
> "Ryan Hanisco" wrote:
>
>> In your C#, are you doing your inserts by index rather than the field 
>> names?
>> You'll see a huge improvement, though it will make your code much harder 
>> to
>> read.
>>
>> Why not handle all this in ADO.net.  While I would normally advocate 
>> better
>> planning of database utilization and interaction than doing the updates 
>> to
>> an ADO.net object and synching back to the DB, this is much better than 
>> 300K
>> inserts.
>> -- 
>> Ryan Hanisco
>> MCSE, MCDBA
>> Flagship Integration Services
>>
>> "FandCDev2" <FandCDev2@discussions.microsoft.com> wrote in message
>> news:F74B1117-A1B9-4A09-B5D9-0DB21DECEB05@microsoft.com...
>> > I've got a C# console app. that does around 300,000 inserts in a row. 
>> > I
>> know
>> > this isn't the best way to do inserts, but for the app. design it is 
>> > what
>> we
>> > need to do.  I've set the Recovery Model to simple.  When I run the 
>> > app.
>> on
>> > my desktop (3Gh, 3Gig Ram, Win XP, etc..) it takes about 25min to
>> complete.
>> > When I run the app. on our server(Dual 3Gh, 4Gig RAM, seperate SCSI 
>> > hard
>> > drives for db(RAID 0) and OS, Windows 2000 Server) it takes over 3 
>> > hours.
>> > I've verified every setting in SQL is configured the same, tried 
>> > limiting
>> SQL
>> > to only 1 CPU, but haven't found anything.  I've written small programs
>> that
>> > check file I/O times, and it is a lot faster on the server.  I've tried
>> doing
>> > a bunch of DB SELECTS followed by DB INSERTS,  and found that the 
>> > SELECT
>> > times are about par between my desktop and the server, but the INSERT
>> times
>> > are about 5 times higher.
>>
>>
>>

Relevant Pages

  • Re: version & instances
    ... SQL 2000 SP3a for Antivirus app ... that same server, to avoid breaking any of those apps. ... MSDE has a limited database size ...
    (microsoft.public.sqlserver.msde)
  • Re: Accessing SQL Server w/ Forms on Intranet
    ... on to the SQL server." ... launched the application from a shared drive really shouldn't affect SQL ... Sam Santiago ... > which Forms process information much better then an ASP app communicating ...
    (microsoft.public.dotnet.distributed_apps)
  • ?? unable to access SQL Server w/ sa account unless user is Administrator on local machine
    ... can tell it to pickup our SQL server using SQL ... app and SQL Server. ... >user belongs to the local Administrators group, ...
    (microsoft.public.sqlserver.security)
  • Re: Invalid Seek Offset continued...
    ... Well shifting to MS SQL will be a very very big step. ... > As Aashish mentioned, network cards drivers may be causing this problem, I ... > also see this error a lot at one customer where the server RAID Controller ... >>> We are deploying a VFP8 app to several beta sites to replace an FPW ...
    (microsoft.public.fox.programmer.exchange)
  • Re: sorted_data_reorg
    ... You need a clustered index to defragment the data pages initially. ... you may see some fragmentation if updates have caused SQL ... Server to split pages. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)