What’s in what, now?

December 1, 2010 § Leave a comment

I’ve been working with a client that is working on an Oracle to SQL Server conversion project, and part of it involves poring over stored procedures to fix inefficiencies. This type of thing keeps coming up, and since this isn’t the only client I’ve seen it at…

This is slow and inefficient:
SELECT @var1 = tab1.col2
FROM tab1
WHERE tab1.col1 =
  (
    SELECT tab2.col2
    FROM tab2
    WHERE tab2.col1 =
    (
      SELECT tab3.col2
      FROM tab3
      WHERE tab3.col1 = @par1
    )
  )

This is better and much, much faster:
SELECT @var1 = tab1.col2
FROM tab1
INNER JOIN tab2
  ON tab1.col1 = tab2.col2
INNER JOIN tab3
  ON tab2.col1 = tab3.col2
WHERE tab3.col1 = @par1

Advertisements

Tagged:

Leave a Reply

Fill in your details below or click an icon to log in:

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

What’s this?

You are currently reading What’s in what, now? at Mike Vallotton's Blog.

meta

%d bloggers like this: