Memory / Aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Memory / Aggregate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-01 : 07:49:40

Marty writes "SQL TEAM,

Using Windows 2003 Server Enterprise Edition
Using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: )

I have a stored procedure as follows:

*******************************************
SELECT TOP 10
mls_Account_Transaction.TRN_AMT_Acct_bi,
SUM(mls_Account_Transaction.TRN_RcvdAmt_dc) AS Total,
mls_Account_Master.AMT_FName_vc,
mls_Account_Master.AMT_LName_vc

FROM mls_Account_Transaction

INNER JOIN mls_Account_Master
ON mls_Account_Master.AMT_Acct_ID_bi = mls_Account_Transaction.TRN_AMT_Acct_bi

WHERE YEAR(TRN_BAT_BatchDate_dt) = @Year

GROUP BY mls_Account_Transaction.TRN_AMT_Acct_bi,
mls_Account_Master.AMT_FName_vc,
mls_Account_Master.AMT_LName_vc

ORDER BY Total DESC
*******************************************

When I run this in Q.A. it returns everything correctly. The problems are:

1) It takes about 1 minute to get the report (working with 22 million records)
2) As I watch the memory during the task, it eats up 1.4GB of the 2GB of memory AND DOES NOT RELEASE IT WHEN ITS THROUGH!!!!

If I take the ORDER BY part out, it runs FAST (3 seconds) with little memory allocation.

Can you guys help me out in making it run faster AS WELL AS using little memory WHILE returning the results that I need?

If you need any more information, please feel free to contact me.

Much thanks,
Marty
msmith@geekgalore.com"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-01 : 08:52:33

Of course it's faster w/o an ORDER BY -- it can take the first 10 totals it gets and just return them. With the order by, it must process every row to calculate the results you need. Makes sense?

The biggest problem that you might be able to optmize is this part:

WHERE YEAR(TRN_BAT_BatchDate_dt) = @Year

That expression cannot use an index. (I assume that column is indexed; if not, that is the first thing you should do)

Translate the @YEar into a startdate/enddate, and then use THAT as your filter. One way to do it is like this:

set @StartDate = DateAdd(yy,@Year-1900,0)
set @EndDate = DateAdd(yy,@Year-1899,0)

and then your WHERE becomes

WHERE TRN_BAT_BatchDate_dt >= @StartDate AND TRN_BAT_BatchDate_Dt < @EndDate

That will allow an index to be used and should perform much faster.

Also -- you are grouping on firstname/last name which you should not do. You should group your numeric data first on TRN_AMT_Acct_bi, and THEN join that to you master table of acounts. Something like this:


SELECT mls_Account_Master.AMT_Acct_bi, A.Total,
  mls_Account_Master.AMT_FName_vc,
  mls_Account_Master.AMT_LName_vc
FROM
(
  SELECT TOP 10 TRN_AMT_Acct_bi, SUM(TRN_RcvdAmt_dc) As Total
  FROM mls_Account_Transaction
  WHERE TRN_BAT_BatchDate_dt >= @StartDate AND TRN_BAT_BatchDate_dt  < @EndDate
  GROUP BY TRN_AMT_Acct_bi
  ORDER BY Total DESC
) A
INNER JOIN mls_Account_Master
  ON mls_Account_Master.AMT_Acct_ID_bi = A.TRN_AMT_Acct_bi

The idea is, you get the numeric data first, and return your results grouped as needed on primary keys only. Once the data is there, and it runs as fast as needed, THEN you can join to tables that return names and descriptions and other lookups.

(By the way, no offense, but that's horrible naming on those columns -- no need for _dt and table name prefixes ... might be the worst I've ever seen...

)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources