August 19, 2002 at 12:19 am
Hi,
I have a stored proc which tries to obtain the last transaction history record for each customer from a sales history table containing 30,000,000 records.
Currently, this process takes about 1.25 hours, and pushes the tempdb out to 2-2.5 gig to create. I'm trying to see if there is any way to enhance what is, whether through indexes or sql coding or something else.
My sql looks thus:
SELECT * INTO last_record_table
FROM sales_transaction st1
WHERE NOT EXISTS
(SELECT *
FROM sales_transaction st2
WHERE st1.client_id = st2.client_id AND
st1.category_id = st2.category_id AND
st1.trans_type = st2.trans_type AND
st2.trans_date > st1.trans_date)
The table looks thus:
computer_id int
transaction_id int
client_id smallint
trans_date date
category_id smallint
trans_type char(1)
The current indexes on the table are:
clustered, unique - (computer_id, transaction_id, client_id, trans_date, category_id)
trans_date
Reports run off the table created (last_record_table), so introducing another lookup table (like an index) would not be feasible.
Any ideas or thoughts would be appreciated.
August 19, 2002 at 1:04 am
Going for the SQL coding here ...
Don't know about the difference of INSERT INTO and SELECT * INTO, but I'm sticking with your notation.
SELECT * INTO last_record_table
FROM sales_transaction st1
INNER JOIN
(SELECT client_id, max(trans_date) as trans_date FROM sales_transaction group by client_id) st2
ON
st1.client_id = st2.client_id AND st1.trans_date = st2.trans_date
Causes a problem with duplicate records (trans_date equal for distinct records for one client), but then again, your original query would have the same problems.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply