CODEWITHSUNDEEP
sqlsql-server
Simran
Simran

Reputation: 569

check for data updation in SQL server

Tables:

  1. dbo.Patients--> Data table

  2. dbo.ResultsSTored --> stores already calculated results to avoid redundant calculations

     if not exists(select Top 1 PercentMatch from dbo.ResultsStored where HashedKey = HASHBYTES('MD5', @StringConcat)
 AND ModifiedAt  > (Select MAX(ModifiedAt) FROM dbo.Patients as Pat WHERE Pat.RowID = ResultsStored.RowID ))
         begin
        /*some code that calls functions that check for percentage match in dbo.Patients*/
            end
            else
            begin
            select SearchSerial,StringSearched, RowId, PercentMatch  from dbo.ResultsStored where HashedKey = HASHBYTES('MD5', @StringConcat) order by SearchSerial desc, PercentMatch desc

             end

Now, there may arise a situation when the searched results are present in dbo.ResultsStored from two days back but yesterday someone added a new row in dbo.Patients table which may have higher match percentage than what is already there in dbo.ResultsStored table.

In that case what dbo.ResultsStored is giving me is not the row with highest percentage.

dbo.ResultsStored table is basically meant to avoid unnecessary look ups in dbo.patients table when what we are looking for is there in dbo.ResultsStored table already.

But how do I handle new data added to dbo.Patients table to get updated and correct results.

Upvotes: 0

Views: 33

Answers (1)

G B
G B

Reputation: 1462

It sounds like ResultsStored is refreshed on some kind of schedule. How about having a view which only gets the records in Patients that aren't in ResultsStored and calculates the PercentMatch only for those few records. You could then Union that with your ResultsStored table so you get the benefit of not having to re-calculate historical data and you get to include the most recent data with minimal overhead.

Upvotes: 1

Related Questions