CODEWITHSUNDEEP
sqlsql-server
Will P.
Will P.

Reputation: 8787

Possible to end a query from within a script if time exceeds x?

So we have a schema that holds the view names, and columns for various report queries used in the system. Some of these views are very old and not very well written, but still get the job done (if slowly).

Anyway, I am writing a script that uses dynamic sql to determine the number of distinct values for each column in the reports. The critical parts of this script which loops through each column for each view is the following lines:

SET @sql = N'SELECT @val=COUNT(DISTINCT [' + @curColumnName + N']) FROM [' + @curViewName + N']'
SET @params = N'@val int OUTPUT'

EXEC sp_executesql @sql, @params, @val=@outputValue OUTPUT

This is working well. However, as I mentioned before, a few of these views are rather poorly written, and some of the old schema could probably use better indexing, and as a result retrieving the distinct values from the columns takes a very long time, sometimes several hours or even days.

For this reason it's become far less effective to perform this operation, because it will take months to complete for every report. The thing is, we don't really care what the actual distinct value count is, but really just knowing if there's more than say, 100 distinct values.

So my question is this, is there some way to stop the query once the number of distinct values is over 100 and just return 100, and/or to just cancel the sp_executesql call and then continue the rest of the script if it's taking longer than say, 5 minutes?

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

There is really no way to do what you want before processing the data. Focusing only on the query, you could do:

SELECT @val = COUNT(*)
FROM (SELECT TOP 100 @curColumnNameFROM
      FROM @curViewName 
      GROUP BY @curColumnName
     ) t

It is unlikely that this would have a significant impact on performance.

Sometimes in these cases, you could do:

SELECT @val = COUNT(DISTINCT @curColumnName)
FROM (SELECT TOP 10000 @curColumnNameFROM
      FROM @curViewName 
      GROUP BY @curColumnName
     ) t

This can reduce the size of the data and that might be sufficient for some purposes.

Upvotes: 1

Related Questions