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.