Re: SQL Server Dual Processors INSERTS take long time
From: FandCDev2 (FandCDev2_at_discussions.microsoft.com)
Date: 01/14/05
- Next message: Chris V.: "Re: Backup Question"
- Previous message: Hoof Hearted: "Backup Question"
- In reply to: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Next in thread: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Reply: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 Jan 2005 08:17:05 -0800
I have looked at SQL profiler and perfance monitors, and the only thing I can
find is that the transactions per sec, is a lot lower on the server. I have
checked file I/O on the server versus on my desktop, and it is much faster on
the server. So I'm fairly certain it isn't the disk. The other thing I
noticed was that in the profiler every eight or so inserts the duration time
is about 16 on the server, where on the desktop they are all much lower.
None of the other times/columns are abnormal. I have reinstalled sql and the
OS, changed from 2k3 Server to 2k Server.
"Andrew J. Kelly" wrote:
> 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.
> >>
> >>
> >>
>
>
>
- Next message: Chris V.: "Re: Backup Question"
- Previous message: Hoof Hearted: "Backup Question"
- In reply to: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Next in thread: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Reply: Andrew J. Kelly: "Re: SQL Server Dual Processors INSERTS take long time"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
