Inserts taking longer time

Hello All,

I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.

Declare @.i int
Set @.i=0
While (@.i<1500)
Begin
Insert into test2 values (@.i)
Set @.i=@.i+1
End

Here goes the table definition,

CREATE TABLE [dbo].[test2](
[int] NULL
) ON [PRIMARY]

Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds.
Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful

Thanks in advance,
Mitesh

Although this can′t be the possible cause, which recovery models are you using on the databases ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hey Jens,
Thanks for the pointer but we are using the Full Recovery model on both of my database. I think there is some problem with the server configuration as I have tried this Insert on multiple databases of my server (where the time taken is more) and have got the same output
|||

You machine is probably caching disk writes so the commits are not actually hardening.Servers are usually configured not to do that so your data is not at risk.

You should wrap your loop with a BEGIN TRAN and COMMIT TRAN.

Declare @.i int
Set @.i=0

Begin Tran
While (@.i<1500)
Begin
Insert into test2 values (@.i)
Set @.i=@.i+1
End
Commit Tran

|||

Hi Mitesh,

I am having a very similar problem:

* I have a crappy testing machine (P3 at 0.5 GHz on Windows NT Server) with SQL 7,

* I have a testing environment running on a Virtual Machine on a pretty descent laptop (Laptop = Celeron @. 1.59 GHz on Windows XP - sorry, low budget -). The Virtual Machine runs Windows 2003 with SQL 2005 Express, with 400 MBytes assigned in RAM.

* The database I work with is created running an SQL script. It has about 15 tables, and as a whole is a quite normalized database.

* I have this application that I did, that updates the database through ODBC.

Now here is the interesting data:

* On my SQL 7, it takes between 2 to 3 seconds to update the database (we are talking about 50 to 100 insert statements) the first time I click on the "save" button. After that is just a matter of a little bit less than a second to update the database every time I click on the "save" button.

* However, on my SQL 2005 environment, it takes about 1 minute. The amount of time does not change much the next time I click "save".

Notes aside:

* Both are empty databases. They only have the table structure.

* The tables are quite normalized. It is ok, it is not a database that it is intended to grow beyond 100 Mbytes.

* "De-normalization" is not an option.

* I changed the size of the database log file from 1 Mbyte to 20 to see if my problem were excesive "check points"... but the behaviour was just the same.

I learned to live with it, after all, Windows XP does not support VM's. In fact, I was rather impressed by the Celeron chip.

But the fact that caught my attention is that, after time went by and I continued using my Virtual Machine, the time got reduced as my database grew up in size. After about 100 "save" operations, It now takes around 10 seconds to save. This suggests me that there is something going on with the indexes...

It is ok, after all, it is just a testing environment.

Hope it helps!!!

Tark Ik

PS: This posting has been posted "AS IS"

|||Hey Peter,
Thanks a Ton for your reply. There would have been no way in the world through which i could have figured this out . The other thing I want to ask is that how can we disable the caching on our server. I mean could not exactly figure out how did the begin tran and commit solved the purpose. Will be really helpful if you can tell how to disable the caching and hardening and is this safe.