Here is a question, if you select 5mm or selecting 10Kx500 won't the time it takes to do the select equal in any case?

If so what is the point in...

Ok, thanks to everyone’s help!

I’ll just continue trying to speed it up

Well I periodically run maintenace scripts on it (like re-indexing etc)

My largest involved table is:

2.440Gb in dataspace, 1.213Gb in index space, 19 307 075 total rows

Second largest involved table:

881.711 MB...

Ok, we are using RAID1 6x500Gb into 3x500Gb drives (C,D,E)

TempDB on C, mdf on D and ldf on E (file growth set to 500mb)

External SAN as backup, just for interest...

Nope no triggers or any additional processing on the tables.

I used SQL Profiler and the log is allowed for autogrowth by 10% limited to 10Gb, last time I checked.

Well, let me list some more stats:

average IO write ops 4~47 / sec, avg 188434 bytes/ sec (running raid thus using averages) , half way through it starts to peak...

Thanks for the feedback guys, but unfortunately I only upload 400K entries at a time never more using 1 Transaction and commit at end.

Also Isolation level are set to minimal...

The script looks like this:

INSERT INTO [OPR_SUPER_BOX] (SBox_No,Job_ID) VALUES ('000000000000000',152)

INSERT INTO [OPR_BOX] (Box_No,Job_ID,SBox_No) VALUES ('000000000000000000000',152,'0000000000000000')

INSERT INTO [OPR_BATCHES] (Batch_No,Box_No,Job_ID) VALUES ('00000000000000000000000','00000000000000000000000',152)

INSERT INTO [OPR_SERIALS] (Serial,Job_ID,Batch_No,Exp_Date,Misc,SCID)VALUES('0000000000000',152,'00000000000000000000000','','','')

INSERT INTO [OPR_PINS] (Serial,Job_ID,Pin_Num,Pin_Set,Pin_Serial) VALUES ('000000000000',152,'0000000000000000000000000000000000000000000000000000000000000000',1,'000000000000')

INSERT INTO [OPR_PINS]...

We did use batch processing originally, can't remember why, but found that row-by-row approach worked more efficiently back then when we were still using SQL2000 (now SQL 2005 Std).

We run...

I checked the script durations and went like this:

The first 10min duration 0, at 10min it drops to 35, at 20min to 110 and after 30min it goes to 500...

Makes sense, I'll take a look at script execution times.

Thanks

Well let’s elaborate the situation:

Technologies used:

Server is a Quad Xeon 2.45Ghz (or something like that) 16Gb Ram, 1.5Tb total storage divided into 4 drives 1 for Windows, 1 for mdf...