million transaction - improve performance of website

I have a page that calls a Stored procedure to read 3 to 4 million data, make some calculations and return a small data table. The SP is slow approx. 20 to 30 sec. therefore, overall page load is slow.

Will i re-factor the SP? but problem is that whatever i do my end result wi开发者_开发技巧ll be the small Datatable.

Is there any suggestion to improve the performance?

If the table is not updated so often make a "aggregated" table that have better performance.

If you're churning through 3 or 4 million rows of data and actually doing real work, 20 or 30 seconds is pretty decent performance, IMHO. Check the execution plan of your stored procedure. In the ideal world every table would be getting hit with index seeks rather than table scans. Consult with your DBA if you're not sure how to interpret the showplan results. I assume you're using SQL Server.

Check to make sure your tables have appropriate indices and that the statistics are up to date. Update them if not. Recompile the stored procedure. Parameters passed to the stored procedure can bollux up the cached execution plan, if the are oddball values. You can prevent this by coding your stored procedure like so:

create proc myProc

  @p1 varchar(32) 

as

  declare
    @p1Local varchar(32)

  set @p1Local = @p1

  ...

If your data does not get updated too often, you can create an indexed view.

http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx:

indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways: Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution. Tables can be prejoined and the resulting data set stored. Combinations of joins or aggregations can be stored.