0

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that table, to compute a column called for Active. This is referenced throughout the application to return records from the database in the where clauses.

I noticed this is slowing down each select of the system. I'm assuming GETUTCDATE, just like an article I saw from you saying don't use a Scalar function in a computer column, is just as bad. Do you have a recommendation on what to do here. I've always maintained an actual column of active, and built services to activate as necessary, which would obviously still be hitting sql, but might not effect the front end user interface.

Improve this question
  • 1
    Is the computed column persisted? – Jonathan Fite Oct 22 '19 at 16:02
  • @JonathanFite Thanks for the response, the computed column is not persisted. – Steve Oct 22 '19 at 18:27
1

You wouldn't be able to persist this computed column, as its value needs to change over time. But having such a non-persisted computed column is a recipe for poor performance.

For this scenario you should decide how often you need to recalculate whether a row is "active" and run a scheduled batch process and/or a trigger to maintain the values.

Improve this answer
  • Thanks for the response! That is what I figured but wanted a little more confirmation. – Steve Oct 23 '19 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.