topic Optimizing query to SQL-Sever using SAS EG 7.1 in SAS Programming

Optimizing query to SQL-Sever using SAS EG 7.1

teelov — Wed, 03 Oct 2018 09:13:46 GMT

Hello,

I have just been given over 2000 lines of code to optimize, the query's i need more help with and being able to spot if the PROC SQL queries are optimum as the connection is ODBC using SQL-server and i want to limit the amount of stress the in DBMS processing / pass thru does on the other end.

we are confident after speaking to the DBA that SAS sends the exactl query across, and i can confirm this with sastrace options.

this comes down to better SQL coding, which is not my most strong point, any help will be appreciated to get this running faster

i need to add there are 300,000,000 rows in the SQL table, of which 14,543,543 are "STRINGX"

/*read in the relevant VAR2 outcome line per exception*/

PROC SQL;
	CREATE table work.A as select
	 	VAR1
		,max(case when VAR2="STRINGX" then id end)as NEW_STRING
	from mysqlx.B
	group by VAR1;
quit;

Re: Optimizing query to SQL-Sever using SAS EG 7.1

RW9 — Wed, 03 Oct 2018 09:11:30 GMT

Sorry, I don't see any simple way of making that any better. How does it relate to this 2000 lines of code - which I assume is SQL? Have you done a root analysis of the code, i.e. documented where each part needs to run, how things link together? You say you don't want processing on the database - which would be where I would do it - in which extract the data to SAS, then process it there using Base SAS? This is a whole process that you and the database team needs to go through, if you have good documentation on the whole process then it makes it a lot easier, as then you can assign various sections to the various tools, and take a deeper look at the code and data enclosed within.

Sorry can't be more specific as not seeing what the "question" here is?

Oh, to add, you have the _method and _tree options in sql to show the SQL compiler actions, which may help with the SQL debugging, although the DB will likely have much better tools for it.

Re: Optimizing query to SQL-Sever using SAS EG 7.1

teelov — Wed, 03 Oct 2018 09:40:57 GMT

Your reply helps a lot as thats exactly what i have done, a lot of the initial processing pulls datasets from the SQL server into SAS work.

i've mapped out the entire process in a high level process flow, to also identify what can be run in parallel.

using SAS EG Analyze program for grid, sas was able to compile a HUGE program with a lot of Rsubmits, but there is an issue where it creates a lot of remote session and uses a slot in the queue but no CPU activity on the PID