Enhancing a stored proc's SQL statement

• Brendon

  Ten Centuries

  Points: 1252

  More actions

  August 19, 2002 at 12:19 am

  #158666

  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.

• NPeeters

  SSChampion

  Points: 12590

  More actions

  August 19, 2002 at 1:04 am

  #434769

  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.