Question
Consider the following (using dapper) Example 1:
var query = @"INSERT INTO FOO (ID, Name) VALUES (1, 'Test')
INSERT INTO FOO (ID, Name) VALUES (2, 'ABC')";
using(var con = connection)
{
con.Execute(query);
}
Example 2:
var queryOne = "INSERT INTO FOO (ID, Name) VALUES (1, 'Test')";
var queryTwo = "INSERT INTO FOO (ID, Name) VALUES (2, 'ABC')";
using(var con = connection)
{
con.Execute(queryOne);
con.Execute(queryTwo);
}
I am trying to understand if it matters which way I execute my 2 inserts. From my understanding con gets opened on the using statement and gets closed at the exit of the using statement.
Does it matter how many times I call con.Execute()? Is it better to just have it under one query? I am asking this for purposes of determining the design of my application so that I know if I should go the extra mile to try and get everyone under a single execute.
Not sure if it matters but I intend to use SQL Server.
Popular Answer
No one can really answer your question because no one really knows how you evaluate "better".
One advantage of the single execute approach is that your application makes fewer round-trips to the server. In other words, there is less overhead and less waiting. If your app does not have a fast and reliable connection to the instance, that might matter a lot. One disadvantage is that your app might not be able to identify which statement causes an error if one occurs.
In addition, you can also structure a single statement to insert multiple rows - rather than creating multiple statements in the one string. E.g.,
insert dbo.FOO (ID, Name) values (1, 'Test'), (2, 'ABC');
Does that make a difference? A slight one, but you be the judge. These are simplistic examples and there is no single answer/approach that is "best" for all situations. And you do need to consider your transaction boundaries - do you want both rows to insert as a single unit of work (so if one fails the other is not inserted too)?
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow