Effective SQL query to find the first (by date) related record in a 1-N ratio

Question

Here's the scenario: there is a software table (PK = SoftwareID) and its associated Release table (PK = [SoftwareID, Version]).

The release can be major or minor, the type of release is identified by Release.ReleaseType ('MAJ', 'MIN').

The release is also characterized by the date: Release.ReleaseDate.

The software is divided into categories identified by Software.CategoryID.

Task: to require an effective T-SQL query to list all pieces of software in a certain category and have the first significant release date falling within this interval, divided by @DateFrom, @DateTo. The only columns needed in the final result set are SoftwareID and ReleaseDate.

This is not a real scenario, but I have formulated it in such a way that it is easier to understand. In the real case, the Release table will have about 10 million records, and the Software table will have about 1 million. I came up with a solution, but it is rather slow, and I feel that the experts here can find something better.

Here is my slow solution:

select  s.SoftwareID, min(r.ReleaseDate)
from
    Software s inner join Release r on (s.SoftwareID = r.SoftwareID)
where s.CategoryID = @Category
      and r.ReleaseType = 'MAJ'
group by
    s.SoftwareID
having
    min(r.ReleaseDate) >= @DateFrom
    and min(r.ReleaseDate) < @DateTo

Thanks.

Answer 1

.

, :

• "MSSQL Management Studio": "" → " ", " " ()
• "MSSQL Management Studio": "" → " ". , .

Answer 2

. ReleaseDate , ReleaseDate ( ReleaseDate)?

Answer 3

- :

select  
  s.SoftwareID, 
  min(r.ReleaseDate)
from
  Software s 
  inner join Release r on s.SoftwareID = r.SoftwareID
where 
  s.CategoryID = @Category
  and r.ReleaseType = 'MAJ'
  and s.ReleaseDate >= @DateFrom
  and s.ReleaseDate < @DateTo
group by
  s.SoftwareID

: , .

s.SoftwareID. , , HAVING MIN(s.ReleaseDate) >= ... , WHERE s.ReleaseDate >= ....

Answer 4

. , , , , .

2 , :

CREATE NONCLUSTERED INDEX [IX_Release_1234] ON [dbo].[Release] 
(
    [ReleaseType] ASC,
    [SoftwareID] ASC
)
INCLUDE ( [ReleaseDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

50%. (25-30% ) , , Release, :

select s.SoftwareID, r.ReleaseDate
from Software s inner join (
    select   SoftwareID, min(ReleaseDate) as ReleaseDate
    from     Release
    where    ReleaseType = 'MAJ'
    group by SoftwareID
    having
        min(ReleaseDate) >= @DateFrom
        and min(ReleaseDate) < @DateTo
) r on (
    s.SoftwareID = r.SoftwareID
)
where
    s.CategoryID = @Category

, :)...

Below, use the engine tuning advisor whenever possible, and thank you again.