How expensive are correlated queries in a summary view??

[cmmrfrds]

Is is very expensive in processing time/resources to add some correlated subqueries to a summary view. What is needed is some transaction types to be summarized into 5 columns which would be the correlated subqueries. Is this the wrong way to accomplish these summaries? Would it be better to make each subquery a view and then link to these views in the summary view? What percent would each subquery add to the overall summary view creation, given that each transaction type would have a couple of records per group.

 

[carp]

It's very difficult to make a valid answer to your question. As is often the case, the answer is "it depends". How large are your tables? What do the subqueries look like compared to your alternative? Do you have indexes? What is the percentage of rows within your table(s) that are going to be returned?

Your best bet is to code it both ways and compare your results (via timing and examining the execution plan).

 

[cmmrfrds]

Thank you for your fast reply. It is one table with 25 million plus rows. All the table is summarized into the view. There are about 20 records per group by. Each subquery would return a few of the 20 records that make up the group. All the fields in the subquery are indexed. Does this help to venture a general opinion? I am wondering if the subqueries will double/triple the time to build the view. An educated opinion is what I am looking for.

 

[Cagliostro]

an educated programmer sould use indexes. If it does not help, use snapshots instead of views. If snapshots does not help, use indexes on snapshots.

Ion Filipski