June 27, 2003 at 8:50 am
At my company, we have several tables that are updated on a weekly basis. These tables have about 2 million rows of data, with a lot of the rows containing nearly the same data, but assigned to different dates.
When we query these tables, we use something like:
SELECT acct_number,fieldb,fieldc
FROM tablea
INNER JOIN
(SELECT acct_number,MAX(target_date) AS target_date
FROM tablea
WHERE target_date <= @param1
GROUP BY acct_number
) max_date
ON
tablea.acct_number = max_date.acct_number AND
tablea.target_date = max_date.target_date
This was allright to begin with, but as the tables are getting larger, this slows down considerably. We are getting ready to redesign the database and I was wondering if there were any strategies we might pursue during that time.
Thanks
Marlon
June 27, 2003 at 1:25 pm
You could consider creating a permanent table storing the max date for each account number (essentially making your derived table permanent). This would improve performance since tempdb would not need to be utilized for the grouped select on account number; however, there would be some maintenance processing work to keep the max date updated...
What is the purpose of the account table and the target date? If I understood this, perhaps I can think of something else...
June 27, 2003 at 1:44 pm
The table is used for reporting purposes. The account table holds information about account balances at a certain date (target_date). So, if you want to see how the account looked on 6/1/2003, you would find the max(target_date) that was less than or equal to 6/1/2003.
I don't think a permanent table would work because the reporting needs to be run for different dates for historical reporting.
Thanks,
Marlon
June 27, 2003 at 9:09 pm
Slowing down...how slow? You could look at building a cube, might be overkill. I wouldnt consider 2 mil rows a lot. What size server?
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply