topic Re: How to make SQL query faster? in SAS Enterprise Guide

How to make SQL query faster?

nbknoid — Fri, 31 May 2019 15:47:56 GMT

Currently takes about 1.5 hours to run.

PROC SQL;
CREATE TABLE WORK.QUERY_MedicalTable AS

SELECT DISTINCT
(CASE WHEN MISSING(SUBSTR(t1.SVCCD,1,3)) THEN t2.SOURCE2 ELSE (SUBSTR(t1.SVCCD,1,3)) END) AS SOURCE
,t1.LOC_ID_CD AS PRIOR_LOCATION
,(SUM(t1.SVC_UNT_QT)/3) AS PRIOR_QUANTITY_AVG
,t2.LOC_ID_CD AS CURRENT_MONTH_LOCATIONS
,t2.CURRENT_MONTH_QUANTITY
,((t2.CURRENT_MONTH_QUANTITY - (SUM(t1.SVC_UNT_QT)/3))/ (SUM(t1.SVC_UNT_QT))) AS VARIANCE
FROM MedicalTable t1
FULL JOIN (SELECT DISTINCT
SUBSTR(t2a.SVCCD,1,3) AS SOURCE2
,t2a.LOC_ID_CD
,SUM(t2a.SVC_UNT_QT) AS CURRENT_MONTH_QUANTITY
FROM MedicalTable t2a
WHERE t2a.YR_NB = 2019 AND t2a.MM_NB = 4
GROUP BY SUBSTR(t2a.SVCCD,1,3), t2a.LOC_ID_CD) t2
ON (t1.LOC_ID_CD = t2.LOC_ID_CD AND SUBSTR(t1.SVCCD,1,3) = t2.SOURCE2)
WHERE t1.YR_NB = 2019 AND t1.MM_NB BETWEEN 1 AND 3
GROUP BY SUBSTR(t1.SVCCD,1,3), t1.LOC_ID_CD, t2.LOC_ID_CD, t2.SOURCE2
HAVING (((t2.CURRENT_MONTH_QUANTITY - (SUM(t1.SVC_UNT_QT)/3))/ (SUM(t1.SVC_UNT_QT))) NOT BETWEEN .0000001 AND .049999)
OR (((t2.CURRENT_MONTH_QUANTITY - (SUM(t1.SVC_UNT_QT)/3))/ (SUM(t1.SVC_UNT_QT))) NOT BETWEEN -.0000001 AND -.049999)
ORDER BY SUBSTR(t1.SVCCD,1,3) ASC , t1.LOC_ID_CD ASC;

QUIT;

Re: How to make SQL query faster?

SASKiwi — Sat, 01 Jun 2019 00:29:32 GMT

Welcome to the SAS Community.

There's a lot going on in your query and you seem to have only one source SAS table. How many rows does it have?

I suggest you start by removing the sub-query join and time how long the remaining query takes.

Due you really need to use DISTINCT? If your GROUP BY's are working correctly these shouldn't be necessary.

Re: How to make SQL query faster?

LinusH — Mon, 03 Jun 2019 09:23:25 GMT

Also, provide the log with FULLSTIMER option enabled, where you can see whether your performance is I/O or CPU bound.

If you have any indexes use MSGLEVEL=I to see if any is being used.

Executing SQL with _method gives hints on how PROC SQL is evaluating and plan the query.