Batch SQL Commands, Performance?

[Robert Zurer]

Hello All,

In a large production environment, is there be a marked difference in
performance when executing a batch of commands and a single command?

My VERY simple profiling came up with no difference at all between the
following two procedures.


public void Update()
{
StringBuilder sb = new StringBuilder();

sb.Append("UPDATE RECORDINGS SET discID='3001' WHERE id=2001;");

sb.Append("UPDATE RECORDINGS SET title='The Freewheelin'''
WHERE id=2001 ;");

sb.Append("UPDATE RECORDINGS SET artist='Bob Dylan' WHERE id=2001;");

//command is a SqlCommand object and the database is SQLServer
command.ExecuteNonQuery(sb.ToString());
}

public void BatchUpdate()
{
string commandText = UPDATE RECORDINGS SET discID='3001',
title='The Freewheelin''', artist='Bob Dylan' WHERE id=2001");
command.ExecuteNonQuery(sb.ToString());
}

I'm creating a persistence mechanism which in which one field of an
object might map to TableA in DatabaseA and another might map to TableB
in DatabaseB, or, for that matter, to a node in an xml file or some
other storage. I would rather use these granular updates in batch rather
than write code to combine them before execution.


Thanks


Robert Zurer

 

[Miha Markic]

Hi Robert,

There certainly is a difference (batch is faster).
It can be small but there is.
Try running your procedures in loop many times...
However, if the difference isn't that important to you go for granular
updates if they suite you better - you have to decide.

 

[Robert Zurer]

I mistakenly switched the way the procedures should be named. What was
called Update should have been BatchUpdate and vice versa. I will try
profiling in a loop and see if there is any significant difference.


Thank you


Robert Zurer

 

[Robert Zurer]

I profiled in a loop and running a single statement is more than twice
as fast as three statements in batch.