CODEWITHSUNDEEP
c#sqlperformanceoptimizationdatabase-performance
Ricardo Polo Jaramillo
Ricardo Polo Jaramillo

Reputation: 12318

Check before, or manage the exception?

This is an inmediattly need but I woul like to know what would you recommend in terms of Performance and in terms of Design.

When you need to do something, what would be faster and better? Check if you can, and If you can do it. Or Try it directly, and in case if it fails you understand the execption.

This is my particular case: I have a database table that I will fill using a foreach. The table has a primary key that cannot be repeated in the tabale.

So..

foreach(object in Objects)
{
   bool exists = //SQL SELECT to check if exists.
   if (exists == false)
   {
      //SQL INSERT
      return INSERTED
    }
   else 
    {
     return failed
    }
}

OR

    foreach(object in Objects)
    {
       try 
          {
          //SQL INSERT
          return INSERTED
           }
       catch
         {
        return FAILED
         }

    }

I would like to know the best practice of this in another things, like creating files without override propetry and managing the execpiton or checking if the file exist before try.

Update: Based on the answers I know that I should not make a transaction for each insert because performance. I should make a bulk insert. In my code I have somehting like this

//SQL Start Transaction
foreach
{
//SQL INSERT
}
//SQL Commit transaction

My question is about inside the transaction, if I should check before insert or simply try.

Upvotes: 1

Views: 114

Answers (3)

Mathieson
Mathieson

Reputation: 1948

Exceptions have a much higher construction cost than a boolean.

Some general guidelines are to only use exceptions for behavior that is actually exceptional (you don't expect it as a normal event), and avoid using exceptions for program control.

Upvotes: 3

MgSam
MgSam

Reputation: 12803

If you want to do this in C#, I'd suggest making a HashSet of the primary keys and then checking the objects against that before trying to add them.

Note: You should make sure you aren't sending off a query to the database with each insert, as that will be far slower than any difference between try/catch and if.

The fastest way to do the inserts would be to batch them into a single database call.

Upvotes: 0

Andrew
Andrew

Reputation: 7778

Large amount of database operations via C# is very ineffective and time consuming. When I approach this, instead of checks, I usually structure inserts into one query; for example:

"INSERT INTO sitemaps (link, site) 
 (SELECT ('" + NAME2 + "') as link, '" + SITE + "' as site 
   WHERE NOT EXISTS 
 (SELECT link FROM sitemaps 
    WHERE link = '" + NAME2 + "' and site='" + SITE + "')"

This takes load off from database and cuts the coding.

Another option, a very-very good method to increase insertions performance (literally hundreds of times) is to use BULK INSERT (MS SQL only) into temporary database table (very fast for thousands of records at once); and then, on the database side, use MERGE command and write if-else scenarios how to handle updates, inserts, deletions etc. It s always a good practice to take the back-and-fourth part and move it to the database procedural end.

Upvotes: 1

Related Questions