querying a SQL database - why faster when loading tables to work first

[Filipvdr]

Hello,

I'm making a left join between two tables on one field.

When using the table stored in the library MW Measure Business Staging it takes hours to finish.

When creating a work table of the two tables, and performing the join on the work tables it takes 1 second to finish.

Why? 🙂 

[JBailey]

Hi @Filipvdr 

Let's see if I have this straight. You have two Microsoft SQL Server tables. Running the JOIN against the database tables takes hours. If you copy the two Microsoft SQL Server tables into SASWORK and perform the join in SAS files, it takes seconds (just for the join, not copying all the data).

I am going to assume what I say above is true.

It appears that the join is not being performed by SQL Server.

It may be a good idea to run this OPTIONS statement to see what SAS is asking SQL Server to do. 

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

The SAS log will clearly show if SQL Server is performing the join.

The problem could be that SAS is performing the join. This is a second way to tell (really guess)- If the time it takes to copy the tables from SQL Server to SAS (plus the time required to process the JOIN) is similar to processing the data in SQL Server, you know that SQL Server is not processing the JOIN. Another way to say this is that SAS is reading the contents of the two tables into SAS and then SAS is joining the tables.

It is always a great idea to make sure that database tables are indexed properly and have current catalog statistics. This can greatly impact the performance of database joins. 

UPDATE: This paper explains the recommended process for troubleshooting database query performance problems.

The SQL Tuning Checklist: Making Slow Database Queries a Thing of the Past

The following EXPLAIN statement (covered in the above SGF paper) output shows the impact of not having proper indexing and catalog statistics. It can be huge. Fixing the issues enabled the query to run in minutes. Granted, the tables were HUGE!

[Filipvdr]

Hi, even copying the data from SQL to SAS work tables goes within seconds...

[JBailey]

Hi @Filipvdr 

Run the SASTRACE statement then rerun poorly performing code. Then post the log to this thread. If you can get the same performance characteristics using PROC SQL you can set the NOEXEC option. This will generate the SQL that SAS is passing to SQL Server without running the actual query.. 

It will help to know the relative sizes of the tables. 

If I had to guess, I would say that there is an issue with indexing and statistics on the database tables. If you are using SAS functions (that cannot pass to the database) in your WHERE predicates this could cause issues, too. But, that wouldn't explain the significant performance issue you are seeing. 

I have added a link to my initial response (I want to keep the suggestions in a single post). Have a look at it. You may want to submit a tech support track. They may have seen this problem in the past.