Re: Question: Stored Procedures or Views?

In article <20000410145030.23934.00001677_at_ng-md1.aol.com>, Hokeyboy25 <hokeyboy25_at_aol.com> writes
>Hello.
>
>I am new to SQL Server and I have a question.
>
>Currently, we are using stored procedures to retrieve data for reports. These
>procedures are often run hundreds or thousands of times in succession, and are
>sucking up a lot of procesing time.
>
>These procedures involve multiple MIN and SUMs, with about three JOINs per
>procedure.
>
>It was brought up that the creation of a new table might speed up our report
>generation. This table would contain the calculated values produced by the SUM
>commands. Thus, instead of SUMming a group of values, we would do a a SELECT
>based on a unique ID.

That's a fairly common approach to performance problems. But before you rush to do it, think about the possible problems.

>
>My first question is: would a simple SELECT be faster or more efficient than a
>SUM within a stored procedure?

In theory, yes. And this is a database theory newsgroup. There's always a chance that some quirk of your particular system might not work that way, but it's a pretty safe bet that using a summary table will be a lot faster.

The disadvantage is that you will have the same data stored in two places, and the two copies might get out of step. If the main database is completely static then there's no risk. If the main database is continually being updated the summary data will always be out of date.

Even if the main database does get updated a lot you might be able to accept a snapshot copy as an approximation to the state of the live database.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk