0

I have database with not too much of data, bud there can be extremely many combinations how this data is being presentenced. Today this is done with "huge views" based on configuration in different tables, so you basically will end up with a huge result (compared to the source data) covering every possible output and from this view there will be filtered ( something like select * from myHugeView where customerId = @p1 and orderDate = @p2)

The performance of this is not good enough, even with the right indexes. I guess this is because it has to create all the possible combinations every time the view is being selected from.

So the question is, will sql server perform better if all this possibilities where stored in tables with triggers on every insert/update/delete statement which can affect the result from myHugeView? or would it be better performance to keep it as it today?

In general, is sql server better for memory intensive operations or cpu intensive operations?

This is on ms sql server 2014, but by the end of the year it will be upgraded to 2017.

1 Answer 1

0

This sound as the wrong way to obtain your data.

After all, a view is nothing more that a temp table created issuing some underlying queries. This means you should be able to directly issue your queries to the original tables, without incurring the performance problem of your "huge" view.

Another approach would be to create multiple, specific but smaller view, each with the right data for your queries.

Anyway, to answer your question, you need to collect some performance metrics and analyze them.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .