I have a rather long running query that uses CTEs to do various status aggregations on the data. As part of this CTE, the base query is a data heavy query that I would like to limit to user selected time frames. The reason for this is that pulling this data for all time can take up significant amounts of time. Often 20 minutes or more. I instead want to display a a limited range of months by default by limiting the initial query to a month previous to the current date and 3 months after the current date. However, the users should be able to also select dates outside of this range as well through the app we are designing.
Because this is a CTE, I want to limit the time range on the base query for performance reasons. If we don't filter by date until the end, it can take that full 20+ minute time frame to aggregate all of the data before it ultimately filters out what it doesn't need by date. I instead want to filter out the data by date before the aggregation. I can run these queries by hand and it makes a drastic difference in performance. It can take it from 20 minutes to about 15 seconds when you want to display just a single week whereas filtering at the end of the CTE still takes the full time.
The issue is that I can't seem to make vvIn_virtual work for this. The relevant portion of the query is this:
I've attempted quite a few variants of this and none of them will work without errors in NAB. If I hardcode the date to something like '2021-07-19', it ultimately processes fine but I don't want the default date to be hardcoded. I want it to change with the current date. I've tried removing the quotes around the string as well as changing the 3 parameter to things like 'date' and none of them seem to work. I can't find any documentation no how vvIn_virtual is defined either. I can only find examples that use the 'char' type.
Does anybody have any suggestions for this?
Because this is a CTE, I want to limit the time range on the base query for performance reasons. If we don't filter by date until the end, it can take that full 20+ minute time frame to aggregate all of the data before it ultimately filters out what it doesn't need by date. I instead want to filter out the data by date before the aggregation. I can run these queries by hand and it makes a drastic difference in performance. It can take it from 20 minutes to about 15 seconds when you want to display just a single week whereas filtering at the end of the CTE still takes the full time.
The issue is that I can't seem to make vvIn_virtual work for this. The relevant portion of the query is this:
Code:
myDate >= vvin_virtual('fromDate', 'current_date - 1 month', 'char', 50) and myDate <= vvin_virtual('toDate', 'current_date + 3 months', 'char', 50)
Does anybody have any suggestions for this?
Comment