03/09/2016 – Efficient SQL

What is efficiency?

That’s the question that plagued me when I stared blankly into a log telling me it spent an abysmal amount of time running a few simple queries.

Usually, I run a few SAS queries before I leave work so that I can look at results as soon I arrive in the morning. I was running a total of 5 months worth of financial product performance information, split into 5 different tables. There wasn’t a lot of information so the expected run time was about 10 minutes per table. Hence I was flabbergasted when the log told me it took 1 hour 28 minutes to run each month of data.

Long story short, after playing it around with it some more I decided to condense 5 queries into a single SQL query and ran it again. Guess how long it took this time?

5 minutes 28 seconds.

1236% increase in efficiency.

SQL_runtime

I think I’ll be able to go home early today.

Advertisements