Avoiding nested subquery in SQL
I have a SQL table that contains form data:
Id int EventTime dateTime CurrentValue int
There can be multiple rows in the table for a given identifier that represent changes with a value over time (EventTime, which defines the time the value changed).
Given a specific point in time, I would like to calculate the number of different IDs for each given value.
I am currently using a nested subquery and a temporary table, but it looks like it could be much more efficient.
SELECT [Id],
(
SELECT
TOP 1 [CurrentValue]
FROM [ValueHistory]
WHERE [Ids].[Id]=[ValueHistory].[Id] AND
[EventTime] < @StartTime
ORDER BY [EventTime] DESC
) as [LastValue]
INTO #temp
FROM [Ids]
SELECT [LastValue], COUNT([LastValue])
FROM #temp
GROUP BY [LastValue]
DROP TABLE #temp
Here's my first option:
select ids.Id, count( distinct currentvalue)
from ids
join valuehistory vh on ids.id = vh.id
where vh.eventtime < @StartTime
group by ids.id
However, I am not sure if I understand your table model very clearly or the specific question you are trying to resolve.
It will be: Separate "current values" from valuehistory up to a specific date, which for each Id.
Is this what you are looking for?
a source to share
I think I understand your question.
Do you want to get the most recent value for each ID, group by that value, and then see how many IDs have the same value? Is it correct?
If so, here's my first shot:
declare @StartTime datetime
set @StartTime = '20090513'
select ValueHistory.CurrentValue, count(ValueHistory.id)
from
(
select id, max(EventTime) as LatestUpdateTime
from ValueHistory
where EventTime < @StartTime
group by id
) CurrentValues
inner join ValueHistory on CurrentValues.id = ValueHistory.id
and CurrentValues.LatestUpdateTime = ValueHistory.EventTime
group by ValueHistory.CurrentValue
There is no guarantee that this is actually faster, because you will need an index on the EventTime to run at any decent speed.
a source to share
Let's keep in mind that because SQL describes what you want and not how to get it, there are many ways to express a query that will eventually be turned into the same query plan with a good query optimizer. Of course, the level of "good" depends on the database you are using.
In general, subqueries are just a syntactically different way of describing joins. The query optimizer will recognize this and determine the best way to execute the query as best as possible. Temporary tables can be created as needed. So in many cases, reworking the query will do nothing for your actual runtime - it might end up in the same query plan in the end.
If you are trying to optimize, you need to examine the query plan by following the description for that query. Make sure it does not perform full-screen scans on large tables and selects the appropriate indexes when possible. If and only if it makes a suboptimal choice here, try to manually optimize the query.
Now, having said all that, the query you inserted is not entirely consistent with your stated goal of "count the number of distinct ids for each given value". So forgive me if I am not fully answering your needs, but there is something here to move the test to your current request. (The syntax is approximate, sorry - from my desk).
SELECT [IDs].[Id], vh1.[CurrentValue], COUNT(vh2.[CurrentValue]) FROM
[IDs].[Id] as ids JOIN [ValueHistory] AS vh1 ON ids.[Id]=vh1.[Id]
JOIN [ValueHistory] AS vh2 ON vh1.[CurrentValue]=vh2.[CurrentValue]
GROUP BY [Id], [LastValue];
Note that you are likely to see performance gains by adding indexes to make these joins optimal, rather than reworking the query, assuming you are prepared to take a performance hit for update operations.
a source to share