June 3, 2015 at 11:57 am
I have a view that is not performing as well as I'd like. That is, it's performing about as well as I think it can, but it just has too much work to do. It contains grouping, Stuff and For XML Path clauses, so I can't put an index on the view. The source tables contain tens of thousand of records, which is enough to slow the query down with these kind of operations.
An idea occurred to me, that seems a little offbeat - at least, I don't recall ever reading about anyone using it. What if I put triggers on the source tables, which copied all the data generated by the view into a table any time one of the source tables was modified in a way that affects the result of the view, and redirected the view to this table? The source tables will not be modified that often, so the drag caused by this rewriting of the table should not be a huge annoyance, but this view is used fairly often, so I'd like its performance to be brisk. The table will take up some room, naturally, but disk space is absolutely NOT an issue on this machine.
Can this be done with a trigger at all? If so, can the trigger do the task asynchronously, so that modifications don't have to wait for a table rebuild to finish? Is this likely to cause fragmentation - should such a table be in its own file or filegroup? Is it a completely stupid idea overall?
To be clear - I'm not asking for help in tuning this query, but for an assesment of my "Copy of query output as Table" notion.
June 3, 2015 at 12:14 pm
Unless real time updates to the view are needed could you just run a scheduled job every however often to populate your temp table instead of using triggers?
June 3, 2015 at 12:28 pm
ZZartin (6/3/2015)
Unless real time updates to the view are needed could you just run a scheduled job every however often to populate your temp table instead of using triggers?
Yes, I could do that, but I don't know when updates may happen - the activity is quite sporadic. There could be several during the course of a day, then days or weeks with no change.
June 3, 2015 at 2:43 pm
You certainly could do that with a trigger. And you'd have the extra benefit of being able to cluster the resulting table exactly as you needed for querying it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply