BookmarkSubscribeRSS Feed
Pato485
Calcite | Level 5

Hello,

 

i want to ask, if i have query like this...

 

pro sql;

 

create table DATABASE.SKUSKA  AS 

SELECT....

 

an this querry runs like 40 mins, and this table database.skuska is base of front end table in application, so when user come to application in the time when querry runs, there is incomplete information. Is there a way to 1. run a querry and then 2. replace a table faster? So there will not be 40 minutes incomplete information in application? How can i do it if it is possible?

 

For example run a querry in work library and when is table complete just overwrite database.skuska table.

 

Thanks.

5 REPLIES 5
Patrick
Opal | Level 21

The generic answer is: Yes, there will be options to improve things. 

You need to provide more detail for a more detailed answer.

 

Questions
1. DATABASE.SKUSKA: is "database" a libref that points to a folder with SAS files or is it pointing to some actual database. If it's a database then which one? - Oracle, SQL Server, ....

2. What OS are you running your code in Unix/Linux, Windows, other?

3. Do you have option XCMD set (to answer this question: Proc Options option=xcmd; run; )

4. We're talking SAS9.4 and not Viya/CAS - right?

 

Do you know why your SQL takes 40 minutes? Would there be an opportunity to tweak the code for better performance?

 

"so when user come to application in the time when querry runs, there is incomplete information"

Unless for some database side trickle feed techniques it's normally "all or nothing" so not sure what your statement means.

Pato485
Calcite | Level 5

It is a sql database, we use sql server and it runs so long because there are many rows in table, a few milions.

Patrick
Opal | Level 21
@Pato485 wrote:

It is a sql database, we use sql server and it runs so long because there are many rows in table, a few milions.


1. So you always create a new table in SQL and then it's a full load into this table?

2. Where are your source tables located?

 

Ideally share the full SQL you're using. Are there any joins between SQL and SAS tables or are all source tables either SQL or SAS - which ones?

Kurt_Bremser
Super User

Push your data to a temporary/intermediate table in the SQL server first, and do the final update step in the SQL server with explicit pass-through.

 

In my personal experience, using a remote database as a backend for BI with SAS is suboptimal; it adds additional configuration/maintenance requirements, and never performs as good as your SAS on its own (unless the database is specifically confgured for BI requirements and you have a VERY FAT network connection to it).

The ideal way to handle your issue would be this:

  • run your BI exclusively in SAS
  • run your SAS on UNIX
  • create the new dataset with a new name in the target library first, then use the FDELETE function to remove the current file (not the library member!), and the RENAME function to rename the new file to the target name

The delete/rename happens so fast that no service interruption happens; users having the file open will see the old contents until they close and reopen.

LinusH
Tourmaline | Level 20

Please share the query, and elaobrate how/when the source data is being updated.

Data never sleeps

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 580 views
  • 0 likes
  • 4 in conversation