Home > Database > Fun with subqueries

Fun with subqueries

Today I was working with an old Access database, on a program that’s essentially meant to move it out of access into another.

One query, ran several times:

SELECT Id, SubId, SomeTime, ColA, ColB...
FROM SomeTable
WHERE SomeTime BETWEEN %STARTTIME% AND %ENDTIME%;

Some code later actually filtered out some of the SubId’s… OK, fine, let’s add that:

SELECT Id, SubId, SomeTime, ColA, ColB...
FROM SomeTable
WHERE SomeTime BETWEEN %STARTTIME% AND %ENDTIME%
AND SubId IN (%LIST_OF_SUBIDS%);

Oops, it’s slowed the whole query. But why? Less rows are returned! Looking at the database design, I see that the SomeTime column is indexed, but not SubId. Can I change the schema? Hmm, not really, since I’m moving out of Access in the first place. And it’s already a completed program. Let’s not add to that. So why is Access making this slower? I don’t need a query plan, I can guess that the columns are all getting evaluated at the same time, and then all those that match all criteria are returned. Hmmm… But how can I make it do what I want, filtered to what I need, and faster or just about the same speed? Just a simple subquery:

SELECT Id, SubId, SomeTime, ColA, ColB...
FROM
(SELECT *
FROM SomeTable
WHERE SomeTime BETWEEN %STARTTIME% AND %ENDTIME%)
WHERE SubId IN (%LIST_OF_SUBIDS%);

All done, much faster, and clean.

And that’s about the only time SELECT * can be used legally. But that’s a story for another day.

Advertisements
Categories: Database Tags:
  1. Alex Nedoboi
    2011-01-20 at 1:44 am

    What will SQL Police do to you if you use “select *” illegally? Five days of full table scans?

    • MPelletier
      2011-01-20 at 8:44 am

      That or re-balancing a few indexes by hand, considering I’ve got a mostly clean record.

  1. No trackbacks yet.

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

%d bloggers like this: