-1

Was wondering if there is a way to estimate the total run time for a query without actually processing the query? I have found when running particular queries it might take hours and I guess it would come in handy to know the approximate completion time as there have been times where I was stuck at work due to waiting for a query to finish.

Sorry not sure if this is a silly question I'm a bit new with SAS.

Thanks guys.

cc_hey
  • 63
  • 1
  • 11
  • 1
    When you say query do you mean PROC SQL query? What is the source data you are querying (local SAS dataset? Remote SAS dataset? Remote database?). Local PC SAS or SAS on a server? I don’t think you can get SAS to estimate it for you, but sometimes you can estimate it by querying a small number of records, the see how query time increases as you scale up. But it’s a very rough approach, especially as in a multiuser server environment network traffic, SAS server usage and database usage can fluctuate. – Quentin Jul 31 '18 at 10:00

1 Answers1

1

This comes down to how well you know the data you're working with. There is no simple method of estimating this that is guaranteed to work in all situations, as there are so many factors that contribute to query performance. That said, there are a few heuristics you can use:

  • If you're reading every row from a large table, try reading a small proportion of it first before scaling up to get an idea of how much that read will contribute to the total query execution time.
  • Try running your query with proc sql inobs = 100 _method; to find out what sorts of joins the query planner is selecting. If there are any cartesian joins (sqxjsl in the log output), your query is going to take at least O(m*n) to run, where m and n are the numbers of rows in the tables being joined.
  • Check whether there are any indexes on the tables that could potentially speed up your query.
user667489
  • 9,501
  • 2
  • 24
  • 35