SQL Question – Use Case Statement or Cursors?

Are you split over whether to use a CASE statement or CURSORS in your SQL code?

torn_photo

This article will help clarify your dilemma.

First of all let’s agree that both methodologies are useful for performing actions on a database table based on processing one SQL data row at a time.

Now let’s look at the main characteristics of each methodology.

CURSORS

Looks more complicated. Support costs are higher.

Best when working with multiple tables and complicated algorithms.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 45 seconds.

cursor_sql

CASE STATEMENT

Looks simpler.  Easier to maintain.

Best with select and where clauses used on a single table.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 137 seconds.

case_sql

CONCLUSION

Each methodology has its advantages and each can help you get where you are going.

Good SQL, good night.

Advertisements

Please leave a reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s