SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

The SQL tuning checklist

Reply
SAS Employee
Posts: 13

The SQL tuning checklist

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?

SAS Employee
Posts: 284

Re: The SQL tuning checklist

Posted in reply to Michael_SAS

My experiences closely match those described in the paperSmiley Wink

Respected Advisor
Posts: 4,736

Re: The SQL tuning checklist

Posted in reply to Michael_SAS

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".

 

SAS Employee
Posts: 284

Re: The SQL tuning checklist

[ Edited ]

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

Highlighted
Super User
Posts: 3,924

Re: The SQL tuning checklist

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.

Ask a Question
Discussion stats
  • 4 replies
  • 526 views
  • 5 likes
  • 4 in conversation