Menu
I'm considering an optimisation in a particularly heavy part of my code. It's task is to insert statistical data into a table. This data is being hit a fair amount by other programs. Otherwise I would consider using SQL Bulk inserts etc.
So my question is...
Is it ok to try and insert some data knowing that it might (not too often) throw a SqlException for a duplicate row?
Is the performance hit of an exception worse than checking each row prior to insertion?
338
To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.
SELECT'. Because the 'INSERT … SELECT' inserts data into an existing table, it is slower and requires more resources due to the higher number of logical reads and greater transaction log usage. However, providing the query hint to lock the entire destination table, the two statements perform exactly the same.
To handle exception in Sql Server we have TRY.. CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.
A TRY... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
First, my advice is to err on the side of correctness, not speed. When you finish your project and profiling shows that you lose significant time checking that rows exist before inserting them, only then optimize it.
Second, I think there's syntax for inserting and skipping if there are duplicates in all RDBMS, so this shouldn't be a problem in the first place. I try to avoid exceptions as part of the normal application flow and leave them for truly exceptional cases. That is, don't count on exceptions in the DB to work around logic in your code. Maintain as much consistency on your end (code), and let DB exceptions indicate only true bugs.
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
