BookmarkSubscribeRSS Feed
Michael_SAS
SAS Employee

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?

4 REPLIES 4
JBailey
Barite | Level 11

My experiences closely match those described in the paper;)

Patrick
Opal | Level 21

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
Barite | Level 11

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1771 views
  • 5 likes
  • 4 in conversation