The SQL tuning checklist

[Michael_SAS]

The SQL query had been running for 36 hours before it was killed. The project team is panicking. The customer is upset. What can you, a SAS® programmer, do to get out of this
dilemma? Something has to be done, but what? 

Chances are you have been there. You have felt the pain. You have lived through the helplessness and fear of imminent failure.

Fortunately, it is possible to get out of this mess. A SAS colleagues, Tatyana Petrova, and Jeff Bailey wrote a SAS Global Forum 2013 paper that shows show you the steps (and the mindset) required to solve this all-too-common problem. The paper includes examples from DB2, Greenplum, Oracle, and Teradata.

I want to know about your experiences with such situations. What have you done to resolve them?

[JBailey]

My experiences closely match those described in the paper;)

[Patrick]

90%+ of such performance issues I've encountered where about the basics like:

- heterogeneous joins

- usage of SAS functions which didn't allow for in-database processing

- "bad" designs creating a lot of data movement between the DB and SAS

The rest was often about tweaking the SQL, creating indices, using hints. And the remaining less than one percent is then what causes the real pain and there the reasons for bad performance can be "anything".

Oh, and last but not least: It's amazing how many sites don't change default settings for connection options like insertbuff and dbcommit - and changing these defaults allows often for significant "quick wins".

[JBailey]

Hi @Patrick

This is my favorite SAS-side fix...

DBIDIRECTEXEC: GOFAST=YES for Database Processing

I have seen this take a SAS job from hours to seconds.

Many of the database problems involve gathering statistics on the database tables, well designed partitions, and proper indexing. 

Best wishes,

Jeff

[SASKiwi]

I had a recent experience where simply changing the ODBC driver being used to query an SQL Server database using SQL passthru reduced the query time from over an hour to less than 3 seconds!

The clue here was the identical query in SQL Server Studio it was taking just a few seconds also.

I guess the moral of this story is the value of comparing the query in another tool then trying to figure out what is different.