08-21-2013 03:04 PM
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?
02-21-2018 03:49 AM
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".
02-21-2018 08:23 AM - edited 02-21-2018 08:25 AM
This is my favorite SAS-side fix...
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.
02-24-2018 05:03 PM
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.