Help with Try/Catch block

Question

Help with Try/Catch block

andegre

SSCertifiable

Points: 5875
More actions
May 17, 2010 at 11:46 am

#386942

Does a Try/Catch block cause statements to execute like a Transaction (ie begin Tran, commit/rollback)?
I have 1 Try/Catch block, inside that, there are multiple Insert Into statements. The 2nd and 3rd Insert Into statements depend on the table that is in the first Insert Into statement. When it gets to the 3rd Insert Into statement, it hangs and SQL Activity Monitor says there are 2 transactions that are open and it's just sitting there doing nothing.
Here's an example of the code:
Begin Try
Insert Into archiveA
(
archiveA_ID, archiveA_val
)
Select a.col1, a.col2
From tableA a
Insert Into archiveB
(
archiveB_ID, archiveA_ID, archiveB_val
)
Select b.archiveB_ID, b.archiveA_ID, b.archiveB_val
From tableB b
Where b.archiveA_ID In (Select archiveA_ID From archiveA)
And b.archiveB_ID Not In (Select archiveB_ID From archiveB)
End Try
Begin Catch
-- Catch Statement stuff here
End Catch
I can't tell if this code isn't doing anything (like locking or waiting for a lock to release) or if it is just taking a long time.
tableA has about 16 million rows that get inserted, and tableB should have about 11 million rows.
Any help is great, thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hi,

you mention problems with third INSERT INTO, but there are only 2 in your code as far as I can see. Since the query seems to be a simplified form of the real one, maybe you deleted part of it by mistake? Another thing that you didn't mention is, whether both tables (normal and archive) in your real scenario are in the same DB and on the same server - it would seem they are, but I'm not sure... archives often tend to be placed somewhere else.

Also, I wouldn't use WHERE IN (select ...) if there are several millions of rows in both tables. It seems that performance of such clauses increases with newer versions of SQLServer, but still I would prefer to avoid it.

BOL of SQLS2005 has some information about transactions - please read "Using TRY...CATCH in Transact-SQL", especially the part "Uncommittable Transactions", and consider whether that could apply to your problem.

andegre SSCertifiable Points: 5875 More actions · Answer 2

Hi Vladen, thanks for the reply.

The tables are in different databases, but the same SQL Server Instance. There is an "Archive" database, and also an "IADS" database. I am moving them from the primary table in the "IADS" database over to the "Archive" database which is just going to be the backup (with a retention period).

Yes, this was a cleaned up version and I left out the second "Insert Into" statement.

Should I do a Left Outer Join then on my destination table and then just check to make sure that the common field in both tables is blank (null) after the join? (In order to remove the "IN" and "NOT IN" statements).

Thanks

UMG Developer SSChampion Points: 13482 More actions · Answer 3

gregory.anderson (5/18/2010)Should I do a Left Outer Join then on my destination table and then just check to make sure that the common field in both tables is blank (null) after the join? (In order to remove the "IN" and "NOT IN" statements).

I think most people prefer to use the EXISTS and NOT EXISTS clauses something like this:

Select b.archiveB_ID, b.archiveA_ID, b.archiveB_val

From tableB b

Where EXISTS (SELECT NULL FROM archiveA a where a.archiveA_ID = b.archiveA_ID)

And NOT EXISTS (SELECT NULL FROM archiveB c where c.archiveB_ID = b.archiveB_ID)

Vladan SSC-Insane Points: 22072 More actions · Answer 4

I personally prefer the syntax with LEFT JOIN and NULL check in WHERE, but also the variant with EXISTS (SELECT ... FROM table WHERE <condition>) mentioned in previous post should be OK. As far as I know, EXISTS clause is evaluated in such a way, that it does not play any role whether you write SELECT NULL, SELECT 1 or SELECT * (so this is one of the few situations where * is generally not considered bad practice).

UMG Developer SSChampion Points: 13482 More actions · Answer 5

One of the advantages of using EXISTS over the LEFT JOIN/NULL check is that it can't introduce a cartesian, and I think as a result of that the query optimize can sometimes make different choices that perform better. (Of course there are times that you want the cartesian...)