sql server - inserting rows

[Jimbob]

when inserting a row into a sql table, is there much of a performance difference between using an update statement and using the good old fashioned

rs.addnew
rs!Fieldname = valuename
rs.update

[JHausmann]

Depends on the cursor location but the amount is likely to be marginal.

[Jimbob]

i'm using a server side cursor

the reason i'm asking is that i've rewritten an app that used to use an access db located on a server to now use sql 7

most things are faster (running big queries etc), but adding a new row to a table containg 10,000 + rows is slower than it was on the access db. i've got indexes running on the db, and i've optimised the code as much as i can, but it's still slow.

i'm using .addnew and .update at the moment, and was wondering if an insert would speed things up

the annoying thing is, it's really quick on my pc, but when compiled and installed on other machines it's slow as hell. i'm starting to think maybe i'm on a better part of the network.....

[JHausmann]

Do you have a DBA who preforms maintenace on your servers? It sounds like "Update Statistics" hasn't been run...

For those that are curious, over time the index pages for tables tend to contain lots of wasted space and multiple pointers to pointers in the b-tree. Update Statistics re-writes the index pages to make SQL Server more efficient. It needs to be run on a regular basis.

One other thing to consider, are you using varchars and, if so, are they increasing (in size) in your updates? If you are (and they are) your datapages will get re-written (by a delete insert and not an update in place) for every affected record.

[Jimbob]

we don't have anyone who does things like that, so i've addded a maitainence plan to update the statistics on a weekly basis, so I should know on monday if that's helped.

With regard to your other point, I am using VarChars, but i'm afraid to say I don't quite understand the rest of your question.

Having read up a bit on VarChar, would I be correct in the assumption that a varchar only takes up the space of the data stored, rather than being a fixed length field? if this is so, then yes - some of my varchars do increase (and decrease) in size. Would I be better off in using a different datatype for these fields.

I have also been told that the fact I am using a Bit datatype will slow things down, and I would be better off using an int instead. do you think this is right??

I think I might buy a book about SQL Server - can you reccomend a good one?

[honeybee]

I think using an 'Insert' command will work slightly faster. Mind, though: I have no prior experience on SQL Server, only Oracle. Usually commands given using Con.Execute execute faster, or so the books say.

[JHausmann]

Originally posted by Jimbob
we don't have anyone who does things like that, so i've addded a maitainence plan to update the statistics on a weekly basis, so I should know on monday if that's helped.

With regard to your other point, I am using VarChars, but i'm afraid to say I don't quite understand the rest of your question.

Having read up a bit on VarChar, would I be correct in the assumption that a varchar only takes up the space of the data stored, rather than being a fixed length field? if this is so, then yes - some of my varchars do increase (and decrease) in size. Would I be better off in using a different datatype for these fields.


The problem manifests itself with replication (but it's indicative of problems you will encounter). Updates come in two flavors, Update in place changes the data where it's stored and a delete/insert update deletes the old record before inserting the changed record. Update in place is, essentially, one transaction, delete inserts are two (IOW, the delete insert transaction will take longer). Where it can cause problems, as I indicated at the start, is with replication. If the table you're updating has referential integrity (it's a foreign key on some other table), then replication will fail when the distribution tables send the delete/insert commands. A delete/insert will always be generated if 1) the primary key has a varchar in it and/or 2) any varchar being updated in the table increases in size.Varchars, do indeed, only use the space they need. There 's a slight performance penalty in using them over chars (SQL Server has to figure out how long each varchar field is for every field) as opposed to a storage penalty for chars (it uses the same amount of bytes no matter what the record size is).

Originally posted by Jimbob
I have also been told that the fact I am using a Bit datatype will slow things down, and I would be better off using an int instead. do you think this is right??
[/B]

Dunno, Never used bit datatypes.

Originally posted by Jimbob

I think I might buy a book about SQL Server - can you reccomend a good one?

Two good ones for different reasons:

SAMS - SQL Server # Unleashed.
O'Reilly - Transact-SQL Programming

[Edited by JHausmann on 07-28-2000 at 12:01 PM]