Select Max - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Max

Author

Topic

robbie_muir@gfsg.co.nz
Starting Member

1 Post

Posted - 2005-12-14 : 22:56:09

Have a problem, I have a securities price table where 2000 prices get entered in each day for different securities. This has been happening for 7 years now so there are approx 5,000,000 records. Each security does not get loaded every day though.

I need to create a query that will return the current price at any specific day, ie the record closest to the specific date that is <= to it. I have tried creating some select queries but are incredible slow.

Below are a couple of examples of what Ive tried and neither have acceptable response times.

SELECT @EndDate As ProcessDate, cd.ClientId, Case When sh.AccountId = 6 Then 1 Else sh.AccountId End, Case When sh.AccountId = 6 Then 26 ELSE sp.SectorId END,
sh.SecurityId, Sum(sh.Units) As Units, p.SecurityPrice, Sum(sh.Units*p.SecurityPrice), Sum(sh.BaseCost) As BaseCost
FROM @ClientDetails cd
INNER JOIN SecurityHolding sh On sh.ClientId = cd.ClientId
INNER JOIN Price1 p On p.SecurityId = sh.SecurityId
Inner Join (Select Max(ValueDate) as valuedate, SECURITYID from price p2 where p2.valuedate <= @EndDate group by securityid ) as p2 on p2.valuedate = p.valuedate
LEFT JOIN vClientSecurityProfile sp On sp.clientId = sh.clientId
And sp.AccountId = sh.AccountId
And sp.SecurityId = sh.SecurityId

WHERE sh.PurchaseDate <= @EndDate
and p.securityid = p2.securityId

also I tried:a

SELECT @EndDate As ProcessDate, cd.ClientId, Case When sh.AccountId = 6 Then 1 Else sh.AccountId End, Case When sh.AccountId = 6 Then 26 ELSE sp.SectorId END,
sh.SecurityId, Sum(sh.Units) As Units, p.SecurityPrice, Sum(sh.Units*p.SecurityPrice), Sum(sh.BaseCost) As BaseCost
FROM @ClientDetails cd
INNER JOIN SecurityHolding sh On sh.ClientId = cd.ClientId
INNER JOIN Price1 p On p.SecurityId = sh.SecurityId
LEFT JOIN vClientSecurityProfile sp On sp.clientId = sh.clientId
And sp.AccountId = sh.AccountId
And sp.SecurityId = sh.SecurityId

WHERE sh.PurchaseDate <= @EndDate
and p.securityid = p2.securityId
and (select max(valuedate) from price p2 where p1.securityid = p2.securityid and p2.valuedate <=@EndDate) = p1.valuedate

Does any one know of a way of doing this. Thanks in advance if you can help.

Robbie.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-14 : 23:57:43

Hi Robbie

What is an unacceptable response time? 20 seconds? 5 seconds?

I can't see that you're doing anything inherently wasteful, and I would have thought both methods above would be compiled to the same execution plan.

You might be able to do some tuning for slight improvements, update your hardware etc, but basically I would have thought that the other option is to keep a register of the most recent prices, and update this whenever prices are modified. That way you won't have to scan the entire price-history table for every hit.

Others may have more suggestions?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-15 : 02:12:47

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

cfederl
Starting Member

26 Posts

Posted - 2005-12-15 : 07:23:13

A few comments:

1. The SQL posted is incomplete as the "GROUP BY" is missing.

2. Is vClientSecurityProfile as view ? If so, please post the view source.

3. What is the difference between tables Price1 and Price ?

4. The select portion has
p.SecurityPrice, Sum(sh.Units*p.SecurityPrice),

There are some possible performance gains by replacing this with
p.SecurityPrice, Sum(sh.Units ) * p.SecurityPrice

Carl Federl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources