Alternative to View for complex Data?_问答_开发者

I have a table that contains partial data that is of no use to the user viewing my application. The viewer wants to see a similar table that has all the values calculated out, and wants to be able to quickly query THAT data. Normally, this would be the perfect place to use a View. Unfortunately the complex nature of the calculations limit my use of Views, so I need an alternate solution. I was thinking of doing something like the following...

Table A contains back-end data. Any time this table is updated, a trigger fired that updates Table B, which shows the result of these calculations. Table B can be queried quickly at this point.

My only concern is that the calculation procedure is somewhat lengthy, and Table A has the potential to be updated many times in bursts. Are there any solutions that are almost like a "Before Select" trigger? So basically Table A could be updated many times in a row, but the calculation would only occur when Table B was queried?

Here's an example timeline:

Table A is Updated
Table A is Updated Again [Don't run SP since nobody needs Table B data yet]
Data is requested from Table B [Run SP to update Table B before getting data]
Table A is Updated
Data is requested from Table B [Run SP to update Table B before getting data]
Data is requested from Table B [Don't run stored procedure because Table A wasn't updated between requests for Table B data]

So my questions are:

Is there anything existing like what I described above?
If not, is there a way to make triggers hold off until the开发者_Go百科 completion of a batch of updates/inserts? Table A won't be modified very often, so I can live with a slow trigger when it is updated. The only problem is that when Table A does get updated, it is normally hundreds of rows at a time and I don't want a slow trigger to run every time.

Thanks for any solutions/insight!

Edit - A little more detail on why (I think at least) the trigger implementation would be slow:

The application that will be sending the Update/Insert/Delete statements is using LINQ-to-SQL, which isn't particularly good with batch operations. So if I wanted to delete a bunch of records, it would send a bunch of delete statements instead of a batch delete statement. Is there any way to group delete statements and have the trigger run after that? (Maybe I'm getting too far off topic here).
The "calculations" I'm speaking of involve some recursive functions and some decision-making processes. The data I'm actually dealing with is scheduling data. So Table A contains tasks which may or may not have a scheduled start. If there is no scheduled start defined, it has to derive it from its predecessors scheduled start + its predecessors duration. In some cases the predecessor might not have that information either, hence the recursive query that keeps digging until it finds the result. It's not painfully slow, but if it had to run on every insert/update/delete, it would get there. The "Table B" I refer to above is basically the same table, but it already has the calculated scheduled start data within (which needs to change on update of Table A).

I would think a materialized view would be able to solve this, and run fairly fast. However the OP doesn't say what the "calculations" are and only that they run slow. As a result here is you best option:

Add a trigger to your main Table A, in that trigger insert the ID of the affected rows into a new "work needs to be done" table. This would have very low overhead, as the calculations are not done yet.

create a job that runs every x minutes to do the work calculations and clear the "work needs to be done" table. This job would would populate and keep in sync your Table B, which has the final answers.

give access to the "query" only via a stored procedure call, in this procedure, run the processing used by the job (will make sure that the data in Table B is up to date) then it will run the query using Table B.

Triggers operate on the entire batch of records that are inserted or updated, not one row at a time.

If the calculations are taking too long with a only a few hundred new records, then the calculation itself probably needs to be performance tuned.

If you do the calculations in a trigger, then they are part of each transaction and they will finish before another transaction is allowed, so it could conceivably slow down inserts. If you write the trigger properly to deal with sets of data and not row-by-agonizing-row, it probably will not cause an issue. Hundreds of rows are a trivial amount to do most calulations on. If you were inserting millions of rows in a batch, I would be more concerned about the trigger performance interfering with other processes.

You could create a proc to do the calculations and schedule it to run every ten minutes or so and then run again when the report is called for. This way it might do most of the precalcualting in advance and just catch the last couple of new records at the report time.

Give us a sample of the calculation and the potential trigger and we can better help you.

There is no way to group a bunch if individual transactions into one group trigger run. Perhaps you should stop using LINQ for this and and senda abatch stament if you need batches to process together. Set-based operations are usually preferable if you are working with more than one record at a time.

If possible (which of course it might not be in your specific situation), you could return your data from table B via a stored procedure call, rather than SELECTing from a view/table. That way your procedure does the heavy lifting only when it is specifically called (i.e. after Table A has been updated).

This is a simpler variation on what's already been discussed...

If you had DeltaTs/EditTs columns on both of your tables, you could:

Return the query data via a stored procedure, which would check the max(Deltats) vs max(deltats) on both tables and update table b as necessary. The first time you select after updating table A, you'd take a bit of a hit, but it would be nice and fast afterwards.