April 23, 2003 at 2:59 am
Hi, I'm curious to know which is faster(and by what %), the insert or update query. Both affect the same amount of columns and only one row.
April 23, 2003 at 3:32 am
There are many outstanding possibilities that affect either. Primary amoung them is indexes. If the update causes a column that is involved in an index to change it may have to remove the record and place somewhere else in the index.
Overall thou Insert would be faster simply because all index values are inserted and not moved. Also, the underlying engine doesn't have to keep a value in the delete table as there was no prexisitng value and only one value is written to the log.
But again due to influences of other objects such as triggers, constraints, indexes, etc., it will boil done to your system.
April 23, 2003 at 5:59 am
I'd agree with that. Decent generalization is to say that inserts will be faster.
Andy
April 23, 2003 at 6:14 am
thanks for the responses.
BTW is there a place where I can do more comparisons like the one above (i.e. subqueries vs joins) without having to create them in query Analyser and them comparing.
April 23, 2003 at 6:43 am
Pretty much you have to test to be sure. So many factors go into determining performance.
Andy
April 23, 2003 at 2:57 pm
April 24, 2003 at 4:30 am
Only speed was mentioned here. Is faster better? I have a practice of using Updates before Inserts whenever constraints and/or triggers are used on the table. Any comments on best practice rather than speed?
Tchau
Tchau
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply