Very Computer

by **Rene** » Wed, 07 May 2003 07:33:25

This sounds really weird and I don't have an explanation for this:

I'm running an import program, written in VB, reading the same textfile over
and over again and writing the information in several tables (master with
many details). I want to write this way some 100.000.000 records to test
performance.

The program runs steady and for some days, writing between 60 en 80 record
per second in the mastertable. I check this with a simple query, the field
TrKey is increased by one and clustered indexed. I wait 3 seconds and print
the number of records written:

waitfor delay '00:00:03' -- wait 3 seconds

In the taskmanager I see SQL is using avg 17% CPU time, the import avg 15%.

Sometimes I noticed then the import writes around 150 records per second,
and be accident I found out that Windows Media player was the trigger (as
programmers we need some music at night, not?).

When I played a MP3 file with the Windows Media Player I noticed that around
150 per second are written per second.
When I check the taskmanager SQL takes around 45% and the import program
around 35% cpu time. Media player jumps between 0 and 2%.
When I stop playing the MP3 file or when the file is finished, I'm still
doing a 150 records per second.
When I terminate Media player immediately the number of records per seconds
drops between 60 and 80, and the cpu time of SQL and my application drops to
the old values again.
When I open a new MP3 file immediately the import process boosts.

Test configuration:
Pentium 2,8 with 512 MB
2xSATA 120Gb harddrives
Windows 2000 Proffesional SP3
MSSQL 2000 developer edition SP3 (from MSDN Subscriptions)
VB6 SP5

Configuration: Boost SQL Server priority switched on, other default

Someone has an explanation for this?

Rene

by **Robert Carneg** » Thu, 08 May 2003 23:58:53

> This sounds really weird and I don't have an explanation for this:

> I'm running an import program, written in VB, reading the same textfile over
> and over again and writing the information in several tables (master with
> many details). I want to write this way some 100.000.000 records to test
> performance.

> The program runs steady and for some days, writing between 60 en 80 record
> per second in the mastertable. I check this with a simple query, the field
> TrKey is increased by one and clustered indexed. I wait 3 seconds and print
> the number of records written:

> waitfor delay '00:00:03' -- wait 3 seconds

> In the taskmanager I see SQL is using avg 17% CPU time, the import avg 15%.

> Sometimes I noticed then the import writes around 150 records per second,
> and be accident I found out that Windows Media player was the trigger (as
> programmers we need some music at night, not?).

> When I played a MP3 file with the Windows Media Player I noticed that around
> 150 per second are written per second.
> When I check the taskmanager SQL takes around 45% and the import program
> around 35% cpu time. Media player jumps between 0 and 2%.
> When I stop playing the MP3 file or when the file is finished, I'm still
> doing a 150 records per second.
> When I terminate Media player immediately the number of records per seconds
> drops between 60 and 80, and the cpu time of SQL and my application drops to
> the old values again.
> When I open a new MP3 file immediately the import process boosts.

> Test configuration:
> Pentium 2,8 with 512 MB
> 2xSATA 120Gb harddrives
> Windows 2000 Proffesional SP3
> MSSQL 2000 developer edition SP3 (from MSDN Subscriptions)
> VB6 SP5

> Configuration: Boost SQL Server priority switched on, other default

> Someone has an explanation for this?

> Rene

Well, a guess: suppose your VB app reads text in a way that closes
the file between rows, and consequently closes the disk - or otherwise
obliges Windows to do file system housekeeping between operations?
And Media Player, when it's continually reading a file on the same
disk, prevents that housekeeping from happening - when it's probably
unnecessary anyway?

Btw, did the thing finish? I calculate 19.3 days to do 100,000,000
rows at 60 rows per second.

by **Rene** » Fri, 09 May 2003 18:24:36

Quote:
> Well, a guess: suppose your VB app reads text in a way that closes
> the file between rows, and consequently closes the disk - or otherwise
> obliges Windows to do file system housekeeping between operations?
> And Media Player, when it's continually reading a file on the same
> disk, prevents that housekeeping from happening - when it's probably
> unnecessary anyway?

The file is one big ASCII file (4Mb), it takes around 30 seconds to process
it. So there isn't too mucht open./close. Besides that, the media player is
not playing!

But the story continues: I upgraded my motherboard BIOS and got different
results:

Before the upgrade:
60-80 rows / with media player 150
After the upgrade:
90 rows steady / with media player 120

So I guess there is some threading issue here. My motherboard (Asus P4G8X,
do not buy this together with a Radeon 9700!!!!) supports all new features
like hyperthreading (with P4 it is not used) and maybe realplayer triggers
some MMX features that boostst threading.

Quote:> Btw, did the thing finish? I calculate 19.3 days to do 100,000,000
> rows at 60 rows per second.

I'm at 41mio now, so far the performance is excellent, requesting a few days
turnover only in 1,2 second! My customers will do a max. of 20mio in 10
years, it's just a test how far SQL can go but I'm afraid that I need to buy
extra diskspace to find that out ;-)

p.s. did you try the 'RealPlayer' trick?

by **Robert Carneg** » Fri, 09 May 2003 22:23:30

> > Well, a guess: suppose your VB app reads text in a way that closes
> > the file between rows, and consequently closes the disk - or otherwise
> > obliges Windows to do file system housekeeping between operations?
> > And Media Player, when it's continually reading a file on the same
> > disk, prevents that housekeeping from happening - when it's probably
> > unnecessary anyway?

> The file is one big ASCII file (4Mb), it takes around 30 seconds to process
> it. So there isn't too mucht open./close. Besides that, the media player is
> not playing!

> But the story continues: I upgraded my motherboard BIOS and got different
> results:

> Before the upgrade:
> 60-80 rows / with media player 150
> After the upgrade:
> 90 rows steady / with media player 120

> So I guess there is some threading issue here. My motherboard (Asus P4G8X,
> do not buy this together with a Radeon 9700!!!!) supports all new features
> like hyperthreading (with P4 it is not used) and maybe realplayer triggers
> some MMX features that boostst threading.

> > Btw, did the thing finish? I calculate 19.3 days to do 100,000,000
> > rows at 60 rows per second.

> I'm at 41mio now, so far the performance is excellent, requesting a few days
> turnover only in 1,2 second! My customers will do a max. of 20mio in 10
> years, it's just a test how far SQL can go but I'm afraid that I need to buy
> extra diskspace to find that out ;-)

> p.s. did you try the 'RealPlayer' trick?

Sorry, no; this office isn't media-friendly - and, come to think, I don't
know where the server physically is ;-)

This /is/ crazy. You'd wonder if Windows is just rigged to run the
PC more slowly when Media Player isn't running, but then they wouldn't
win SQL benchmarks with it.

by **Rene** » Sat, 10 May 2003 01:02:58

Quote:
> This /is/ crazy. You'd wonder if Windows is just rigged to run the
> PC more slowly when Media Player isn't running, but then they wouldn't
> win SQL benchmarks with it.

BTW if you want real speeds use the dblib. My record a 38.000 record insert
per second, but for complex situations I preffer VB because it's easier to
program.

by **Craig Kell** » Sat, 10 May 2003 07:04:40

I can see a motherboard change helping things: perhaps something to do with
bus transfer, interrupt handling, the hardware timer, etc.

However, I would guess that the performance improvement you're seeing from
Media Player is actually *not* an improvement. As has been pointed out, MP
could be doing something to keep the hard drive and/or OS driver "primed",
but my guess would be that the additional strain of MP is causing the line

waitfor delay '00:00:03' -- wait 3 seconds

To actually wait longer than when you run the tests without it. It would
only need to change your sample time from 3 to, say, 3.4 seconds to make the
kind of impact you're seeing. Add in the fact that MP is probably also
altering the time for "select max(key) from table" and I think what's really
going on is that Media Player isn't so much helping performance as hindering
metrics.

Craig

> > Well, a guess: suppose your VB app reads text in a way that closes
> > the file between rows, and consequently closes the disk - or otherwise
> > obliges Windows to do file system housekeeping between operations?
> > And Media Player, when it's continually reading a file on the same
> > disk, prevents that housekeeping from happening - when it's probably
> > unnecessary anyway?

> The file is one big ASCII file (4Mb), it takes around 30 seconds to
process
> it. So there isn't too mucht open./close. Besides that, the media player
is
> not playing!

> But the story continues: I upgraded my motherboard BIOS and got different
> results:

> Before the upgrade:
> 60-80 rows / with media player 150
> After the upgrade:
> 90 rows steady / with media player 120

> So I guess there is some threading issue here. My motherboard (Asus P4G8X,
> do not buy this together with a Radeon 9700!!!!) supports all new features
> like hyperthreading (with P4 it is not used) and maybe realplayer triggers
> some MMX features that boostst threading.

> > Btw, did the thing finish? I calculate 19.3 days to do 100,000,000
> > rows at 60 rows per second.

> I'm at 41mio now, so far the performance is excellent, requesting a few
days
> turnover only in 1,2 second! My customers will do a max. of 20mio in 10
> years, it's just a test how far SQL can go but I'm afraid that I need to
buy
> extra diskspace to find that out ;-)

> p.s. did you try the 'RealPlayer' trick?

by **Erland Sommarsko** » Sat, 10 May 2003 07:28:15

> BTW if you want real speeds use the dblib. My record a 38.000 record
> insert per second, but for complex situations I preffer VB because it's
> easier to program.

Should be that much difference - at least not if you use the bulk-
copy interface. I would expect bulk copy to be equally fast, no matter
if it is DB-Lib, ODBC or OLE DB.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

by **Rene** » Sun, 11 May 2003 01:37:44

Quote:> However, I would guess that the performance improvement you're seeing from
> Media Player is actually *not* an improvement. As has been pointed out,
MP
> could be doing something to keep the hard drive and/or OS driver "primed",
> but my guess would be that the additional strain of MP is causing the line

> waitfor delay '00:00:03' -- wait 3 seconds

> To actually wait longer than when you run the tests without it. It would
> only need to change your sample time from 3 to, say, 3.4 seconds to make
the
> kind of impact you're seeing. Add in the fact that MP is probably also
> altering the time for "select max(key) from table" and I think what's
really
> going on is that Media Player isn't so much helping performance as
hindering
> metrics.

Craig,

I've tested a lot of things and the number of records written where doubled
over a longer period. The media player is *not* playing , and if it is
playing it takes 1-2%. Anyway, the number of records are almost doubled, so
this .4 second shouldn't make a difference. At this moment my program insert
a 120 record per second (with my previous BIOS 160) and the whole process is
slower.

I still believe it has something to do with the interrupts handling of the
machine. After installing a motherboard BIOS update the whole test behaves
different, so at least I have one clue.

Rene

- Show quoted text -

> Craig

> > > Well, a guess: suppose your VB app reads text in a way that closes
> > > the file between rows, and consequently closes the disk - or otherwise
> > > obliges Windows to do file system housekeeping between operations?
> > > And Media Player, when it's continually reading a file on the same
> > > disk, prevents that housekeeping from happening - when it's probably
> > > unnecessary anyway?

> > The file is one big ASCII file (4Mb), it takes around 30 seconds to
> process
> > it. So there isn't too mucht open./close. Besides that, the media player
> is
> > not playing!

> > But the story continues: I upgraded my motherboard BIOS and got
different
> > results:

> > Before the upgrade:
> > 60-80 rows / with media player 150
> > After the upgrade:
> > 90 rows steady / with media player 120

> > So I guess there is some threading issue here. My motherboard (Asus
P4G8X,
> > do not buy this together with a Radeon 9700!!!!) supports all new
features
> > like hyperthreading (with P4 it is not used) and maybe realplayer
triggers
> > some MMX features that boostst threading.

> > > Btw, did the thing finish? I calculate 19.3 days to do 100,000,000
> > > rows at 60 rows per second.

> > I'm at 41mio now, so far the performance is excellent, requesting a few
> days
> > turnover only in 1,2 second! My customers will do a max. of 20mio in 10
> > years, it's just a test how far SQL can go but I'm afraid that I need to
> buy
> > extra diskspace to find that out ;-)

> > p.s. did you try the 'RealPlayer' trick?

by **Rene** » Sun, 11 May 2003 01:42:30

Quote:> > BTW if you want real speeds use the dblib. My record a 38.000 record
> > insert per second, but for complex situations I preffer VB because it's
> > easier to program.

> Should be that much difference - at least not if you use the bulk-
> copy interface. I would expect bulk copy to be equally fast, no matter
> if it is DB-Lib, ODBC or OLE DB.

I used the DB-Lib BCP interface directly, faster you can't go. The BCP
program is always slower because it has some overhead reading/converting the
file.

OLE DB and ODBC are much much slower.

Rene

by **Vlad Vissoultche** » Sun, 11 May 2003 01:51:40

i would really want to know what is your experience with IRowsetFastLoad
proprietary interface -- the "bulk insert extension" to SQLOLEDB provider.
is it really *that* slow compared to dblib?

cheers,
</wqw>

> > > BTW if you want real speeds use the dblib. My record a 38.000 record
> > > insert per second, but for complex situations I preffer VB because
it's
> > > easier to program.

> > Should be that much difference - at least not if you use the bulk-
> > copy interface. I would expect bulk copy to be equally fast, no matter
> > if it is DB-Lib, ODBC or OLE DB.

> I used the DB-Lib BCP interface directly, faster you can't go. The BCP
> program is always slower because it has some overhead reading/converting
the
> file.

> OLE DB and ODBC are much much slower.

> Rene

by **Rene Balver** » Tue, 13 May 2003 16:04:45

Quote:> i would really want to know what is your experience with IRowsetFastLoad
> proprietary interface -- the "bulk insert extension" to SQLOLEDB provider.
> is it really *that* slow compared to dblib?

I do not have expierence with the IRowsetFastLoad, but it could use the same
methods. All I know is that inserting records with the tradiodional .AddNew
and .Update or .Execute "insert bla blah" is much slower.

Rene

by **<n..** » Thu, 15 May 2003 00:59:27

Rene, dblib is completely deprecated, and T-SQL bulk insert is the fastest,
period. We find db-lib to be the slowest in the chain - in order of speed,
slowest to fastest db-lib, odbc bulk insert, oledb bulk insert (dts), bcp,
t-sql bulk insert. This is across a variety of tools, not just one custom
program. We easily get a million rows a minute, what do you get with your
proprietary db-lib app?

> > i would really want to know what is your experience with IRowsetFastLoad
> > proprietary interface -- the "bulk insert extension" to SQLOLEDB
provider.
> > is it really *that* slow compared to dblib?

> I do not have expierence with the IRowsetFastLoad, but it could use the
same
> methods. All I know is that inserting records with the tradiodional
.AddNew
> and .Update or .Execute "insert bla blah" is much slower.

> Rene

Very Computer

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?

How can Windows media player boosts MSSQL and import application?