Most Efficient Way to Insert 10 - 15 Rows

[Jonathan Wood]

I'm an experienced programmer with limited database development. Most of my
database work involves using query strings or stored procedures or to update
data or populate a SqlDataReader object.

Now, I need to update 10 - 15 rows. I know I can execute 10 - 15 separate
queries but this is in a Web applications and now I'm wondering if there
might be a more efficient way.

I've virtually never used data tables, and I'm wondering if it's worth
trying to add rows to a table and then update the table to the server. Would
that even make much difference in performance?

Thanks for any tips.

Jonathan

 

[Jonathan Wood]

After thinking about this, I'm deciding the best way is probably to just
create one long query string with each INSERT statement separated with a
semicolon. This will reduce the number of round trips to 1.

In fact, SQL 2008 even has a shortcut to make this every more efficient. For
more info about that, see:

http://blog.sqlauthority.com/2008/0...-one-insert-statement-use-of-row-constructor/

Thanks.

 

[sloan]

Sql Server 2000 ...one method, ship down Xml and use OPENXML.

Sql Server 2005,...one method ship down Xml and "shred" the xml. Either go
directly to the tables, or "shred" them into @variable or #temp tables and
update the real tables from those.
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html

Sql Server 2008, Table Value Parameters
http://www.google.com/#hl=en&q=sql+...08+TAble+VAlue+P&gs_rfai=&fp=1dc62da33e2ff469
(or you can still use the 2005 method in 2008)


You need to think "set based" for this.

 

[Gregory A. Beamer]

I'm an experienced programmer with limited database development. Most of
my database work involves using query strings or stored procedures or to
update data or populate a SqlDataReader object.

Now, I need to update 10 - 15 rows. I know I can execute 10 - 15 separate
queries but this is in a Web applications and now I'm wondering if there
might be a more efficient way.

I've virtually never used data tables, and I'm wondering if it's worth
trying to add rows to a table and then update the table to the server.
Would that even make much difference in performance?

Thanks for any tips.

There are many ways to handle this.

One is a bulk load, but a bulk load will require using systems outside of
..NET that may be restricted due to partially trust (esp. with web apps). If
this is a windows application, and you can set up the bulk load, it works
great. It is the fastest, perf wise.

Another is to format the insert as XML and then use the XML capabilities of
SQL Server. The XML can be fed to a stored procedure that then uses it as a
'table' in an insert/update type of query. This is not as fast, but the
looping is done on the SQL Server side.

You can, as you mentioned, chain SQL Statements. you can also fire one by
one with a same connection. Or you can dispose the connection and
re-instantiate. The performance difference between all three will be
negligible, as the connection object will not really be disposed, it will go
back into pool and simply be reassigned to your "new" connection.

The downside of chaining statements, is you will build your insert
statements using a stringbuilder, or similar, which can lead to issues like
SQL injection. So be careful of building insert statements on the fly
without parameterizing them.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

 

[sloan]

SQL Injection is one con as you mention.

Another con of the "chain" method is that **index rebuilding** will happen
after each statement in the chain.

And that can be drastically bad, depending on the destination database of
course.

...

And the above reason (the index rebuilding ) is why I prefer the "one shot"
INSERT or UPDATE (or UPSERT(merge) if you have 2008).......namely, the index
rebuilding will happen once.


Index rebuilding is sometimes the "forgotten child" in these type of
discussions, from past conversations (in my limited conversation
participation that is).

 

[LovalvoB]

Your approach of using the multi-value insert in SS2008 is a good one. It's
a single action, a single round-trip, fast to execute, and very clear.

You might also want to consider this syntax, which will work in older
versions of SS as well:

create table #test(one int not null ,two int not null)
insert into #test( one ,two )
select 1 ,2
union all select 2 ,3
union all select 4 ,5

The 'union all' makes it a single action.
The select with constants (variables, functions, etc) doesn't require a
'from' clause.

 

[Gregory A. Beamer]

SQL Injection is one con as you mention.

Another con of the "chain" method is that **index rebuilding** will happen
after each statement in the chain.

This depends on fill factor on the insert, what index is clustered, and a
variety of other factors. In fact, if you insert with cluster on primary
key, you may have 0% index rebuilding. Also, the index rebuild is generally
an issue with any form of insert.

And that can be drastically bad, depending on the destination database of
course.

Definitely. And if you chain quite a few, you may time out. ;-)

And the above reason (the index rebuilding ) is why I prefer the "one
shot" INSERT or UPDATE (or UPSERT(merge) if you have 2008).......namely,
the index rebuilding will happen once.

XML is nice for this, as it bulk loading, but sometimes, with enough
inserts, you end up having to drop and rebuild indexes, as it is faster than
inserting

Index rebuilding is sometimes the "forgotten child" in these type of
discussions, from past conversations (in my limited conversation
participation that is).

Definitely not something we think about in the standard SQL scenario, where
the primary key is an IDENTITY field and clustered.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************