SQL Server 2008 Query Problem

Question

Post reply

SQL Server 2008 Query Problem

Tempest

SSC Veteran

Points: 241
More actions
June 25, 2011 at 1:28 am

#252568

I have a problem… I cant resolve
I have a table called Transactions that contains around 100,000 records the important fields are:
TransactionID - varchar(20)(this is the unique key)
TransactionDate – datetime
CostPrice – Numeric(18,2)
Reference – varchar(12)
Description – Varchar(50)
Value - Numeric(18,2)
Now what I need to do is update the Description and Value fields in the above Transactions table to be the ShortDescription and Value from the following table called Details (this table has around 15million records and no indexs)
LastUpdatedDate – DateTime
ShortDescription - varchar(50)
CostPrice – Numeric(18,2)
Value - Numeric(18,2)
Reference – varchar(12)
The join between the 2 tables is:
Inner Join on CostPrice = CostPrice and Reference = Reference…
And… here is the complicated bit I cant resolve…
I need the most recent ShortDescription and Value for a transaction where the LastUpdatedDate is <= the TransactionDate
Whats the best / most efficient method for achieving this bearing in mind that my Details Table contains in the region of 15million Records..
Hope someone can help me..!
🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would test how many rows would the following join return:

Inner Join on CostPrice = CostPrice and Reference = Reference AND LastUpdatedDate <= TransactionDate.

Depending on the number of rows returned I would store it in a temp table indexed on Reference and CostPrice.

If it needs to be done one time only, I would use the ROW_NUMBER approach

with (PARTITION BY Reference, CostPrice ORDER BY LastUpdatedDate DESC) as row

and then query for row=1 in the update statement.

If it needs to be done regulary I might also check if the RunningTotal approach (AKA "quirky update") would perform better. The test would also include the "old-fashioned"

MAX(LastUpdatedDate) WHERE LastUpdatedDate <= TransactionDate

subquery approach to find the best performing solution for the given scenario.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tempest SSC Veteran Points: 241 More actions · Answer 2

i think i will try your idea of a temporary table containing just the unique fields I need in my join then create one record for each of these for every trade date I am potentially interested, ie add a column called TransactionDate to this table.

in that way I will have a very tall table but I will then have a very simple join based on the

i have tried the function route that takes all the fields for the join including the TransactionDate and then return the most recent information up to that date, but the function has to be called row by row and takes forever to run, perhaps 12-14 seconds per row. fine if i only had to run it across a 100 records but I have 100,000 records to run it on.