Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
llahiff
Starting Member
6 Posts |
 Posted - 2005-08-29 : 15:50:05
|
| Hi,I have a 16 columns table with approximately 10 million rows. Each time the application saves the data, approximately 100 rows are inserted into this table. The strange thing is the first row usually takes a minute or longer to insert. Then rest of the rows will take a few seconds to insert each row. Then all subsequence inserts into that table appears to insert just as fast. I have used Profiler to find out which part of the first insert is hanging on. When I look at the Profiler results, the first insert produces over 300 rows of Profiler trace information. Whereas subsequence inserts only produce around 30 rows for each insert. I do not understand why the first insert into this table always takes 10 times longer than the rest. This problem has been baffling me for months. Does anyone know why this is the case?Thanks,Lorinda |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-29 : 16:48:07
|
| Are You doing the 100 inserts in one transaction and locks are acquired during the first insert ?rockmoose |
 |
|
|
llahiff
Starting Member
6 Posts |
Posted - 2005-08-29 : 17:07:40
|
| They are all separate transactions. I have done this test in Query Analyzer with 3 insert statements in 3 separate transactions. The first insert is still slower than the other 2 inserts. If you create a new table and run Profiler, you will notice when you run the first insert, profiler will produce almost 10 times the number of rows then subsequence inserts into the same table. Does it some how need to "read" part of the table into memory before the insert takes place?Lorinda |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-29 : 17:28:02
|
| Could it have something to do with establishing a connection to the sql server and authentication? Perhaps the subsequent inserts are using the existing, authenticated connection where as the initial insert needs to go through connecting and authenticating process. What is the application technology/architecture? Seems like you have too much profiler activity going on for a single insert. Can you copy paste an example of the profiler output?Be One with the OptimizerTG |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-30 : 05:09:11
|
| >> Does it some how need to "read" part of the table into memory before the insert takes place?It will have to read the appropriate data page(s) into the buffer cache if not already there.http://www.sql-server-performance.com/rd_data_cache.asphttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspxhttp://support.microsoft.com/default.aspx?scid=kb;en-us;230785The amount of activity in the trace seems surprising, post an example.You might consider running all the inserts in one transaction:Cons: extended transaction scope, lessened concurrency.Pros: fewer log flushes (1 iof 100), less physical I/O, better performance (probably).rockmoose |
|
|
|
llahiff
Starting Member
6 Posts |
Posted - 2005-08-30 : 12:30:10
|
| I have tried running the inserts as one transaction and separate transactions. Both produces the same results in the profiler activity. Below is the test I ran:--1)Open query analyzer and connect to the database--2)Create the table belowCreate table InsertTest (InsertId int not null, InsertName varchar(16) not null, InsertValue varchar(48) not null, CreateDate datetime not null)--3)Run Profiler using the SQLProfilerStandard template and be sure to include all Trace Properties--4)Run first insert belowInsert into InsertTest values (2,'1st insert','the first insert',getdate())--Profiler returns 159 rows--5)Save Profiler results--Profiler results for the first insert can be viewed at:-- http://csua.berkeley.edu/~lorinda/work/insertlong.txt--6)Clear Profiler window and Run second insert belowInsert into InsertTest values (4,'2nd insert','the second insert',getdate())--Profiler returns 27 rows--5)Save Profiler results--Profiler results for the 2nd insert can be viewed at:-- http://csua.berkeley.edu/~lorinda/work/insertshort.txtPlease let me know if I can provide any additional information.Thanks,Lorinda |
|
|
|
|
|
|
|
|
1st insert takes longer then subsequence inserts