Re: Is there any way to make this query faster?

On Tue, 10 Jan 2006 18:36:02 -0800, g5g wrote:

>Is there any way to make this query faster?
>
>select
>sum(p.QTY * p.PricePromotion) as ttl_trans_amt,
>sum(p.QTY * p.PriceSold) as ttl_trans_amt,
>(sum(p.QTY * p.PricePromotion) - sum(p.QTY * p.PriceSold)) / sum(p.QTY *
>p.PricePromotion) * 100 as disc_given
>from Price p

Hi g5g,

Hard to say without knowing how your table looks, what your indexes are,
etc. Take a look at www.aspfaq.com/5006 to see what information you need
to post in order to help us help you.

Since you want a total for the whole table, there's no way to avoid a
table or index scan. The best way to limit the damage of this scan is to
have a nonclustered index on only the columns used in this query.

Another option that you might wish to investigate is to use an indexed
view. See the description in Books Online (or post more detailed info if
you need more help). Be aware that indexed views require extra handling
on data modification; if insert, update, and delete performance are
crucial in your database, then take care to momnitor exactly how the
indexed view will affect the performance of those statements.

--
Hugo Kornelis, SQL Server MVP
.