Pass-through query to SQL Server takes a long time to run...

[H4xc1ty]

4.3
As the title states, I have a program in a process flow that pulls in data from SQL Server via a pass-through query.  The problem is that it takes a LOT longer to run in Enterprise Guide than in SQL Server Management Studio.  My SQL is identical.  In Management Studio it runs in 1 minutes 17 seconds.  In Enterprise Guide I kill it after 11 minutes (before I get an angry call or email from our DBA).  Since the actual SQL submitted to SQL Server isn't the issue, I'm posting only the SAS-specific code.  Hopefully someone can help.  I have searched everywhere and am posting because I have exhausted other options.

%let dsnname = ODBC_DATA_SOURCE_NAME;

proc sql;
connect to odbc as dw (dsn="&dsnname" uid=USERNAME pwd=PASSWORD);
create table work.output_dataset as
  select distinct
  *
  from connection to dw
  (

......SQL CODE SENT TO SQL SERVER.......

  )
  order by
   column1
   ,column2
   ,column3
   ,column4;
disconnect from dw;
quit;

[TomKari]

There are a couple of issues here. I assume that when you say "My SQL is identical", you're referring to the contents of your "SQL CODE SENT..." block.

However, you are doing several other things here:

1. You're sorting your result set by four variables;

2. You're transferring the result set from your physical database server to your physical SAS server;

3. You're loading the data into "work.output_dataset" on your physical SAS server.

Probably, your delay lies in one of these factors.

It seems odd that you don't include your "order by" clause in your pass through SQL. If you do, does it make a difference to your Management Studio times?

During the 11 minutes, do you know for a fact that your database server is chugging hard on your request (therefore steaming your DBA), or could the database server be fairly quiescent?

Is your result dataset bigger than 20 GB? Anything under that shouldn't present major problems.

Tom

[H4xc1ty]

There are a couple of issues here. I assume that when you say "My SQL is identical", you're referring to the contents of your "SQL CODE SENT..." block.

However, you are doing several other things here:

1. You're sorting your result set by four variables;

     I do this because event though I include it in my passthrough SQL Enterprise Guide (or proc sql, not sure which) will automatically reorder data by column order if I don't

2. You're transferring the result set from your physical database server to your physical SAS server;

     It is probably worth noting that we don't have an actual SAS server we use Enterprise Guide with the Local Server.

3. You're loading the data into "work.output_dataset" on your physical SAS server.

Probably, your delay lies in one of these factors.

It seems odd that you don't include your "order by" clause in your pass through SQL. (see above) If you do, does it make a difference to your Management Studio times? (no)

During the 11 minutes, do you know for a fact that your database server is chugging hard on your request (therefore steaming your DBA), or could the database server be fairly quiescent?

Is your result dataset bigger than 20 GB? Anything under that shouldn't present major problems.

Tom

[TomKari]

1. I have never seen PROC SQL or Enterprise Guide reorder output data...this seems very, very odd.

2. Based on your reply, my first area of investigation would be the data transfer speed to your local PC. Can you monitor the throughput of the network throughput to your machine, and see it it's maxed out over the 11 minute duration? I've seen cases where server-to-server communication is very fast (GB or 10 GB / second), but the runs to the local machines are 10 MB or 100 MB.

Another way to check this is just to copy a file that's about the same size as your SAS result from a server to your PC, and see how long it takes.

Tom

[SASKiwi]

Run this test. It should run at a similar speed to your SQL Server Management Studio version. If so then you can assume that it is the transferring of the data back to your PC that is the problem.

Also I agree with Tom - reordering your data again in SAS should not be necessary, unless it is some unusual issue with ordering missing or null values.

proc sql;
connect to odbc as dw (dsn="&dsnname" uid=USERNAME pwd=PASSWORD);
  select count(*)
  from connection to dw
  (

......SQL CODE SENT TO SQL SERVER.......

  )
  ;
disconnect from dw;
quit;

[Kurt_Bremser]

You're doing a select distinct * outside of your pass-through. This causes SAS to do a sort by all variables in the local SAS server. Move that distinct into the pass-through, so that it is executed in the DB.

Or do a proper sort after the SQL to eliminate multiple entries.

[LinusH]

This is a typical problem when using explicit SQL pass-thru, you must be aware on what is happening where.

if you tried implicit pass-thru, SAS will automatically send any appropriate part of the code to the source DB, and in most cases, it does what's optimal.

[ToreB]

Hi, Just saw this old discussion. Did you get any solution? I have a similar problem. The answers about sorting, select distinct and transferring data is not the problem. (Just attempts of explantions). 🙂

In my query I get 30000 raws in the result-table. In SQL Server Management Studio it took more or less 10 sec. In EG, it took half an hour!!

I have preassigned my library to get advantages from pass-though. This works well in 99% of our queries.

Explicit pass-through will definately help, even it is not a smooth solution.

Then....

I tried with select top n ... in the sql-code within the parentesis (SQL Server-side). Nice!! I wrote select top 50000... It helped even if my result-dataset was only 30k. With no top-stmt it took much longer time.

In my case it depends on the complexity of the query and the tables in the query. When I removed one table (not the biggest) it used again 10sec to get 30k raws in the result-dataset.