0

I have a SQL-Server table which needs to be updated via a SQLCLR function. An update to a single row will need to trigger table-wide update. I was wondering how to properly perform the update and normalize the table. The main problem I see is that it's linked to a website, so there could be multiple updates coming in at any time. The website will only see a few hundred visitors and after a short period of time will be closed (collecting data for research).

To get an idea of the SQLClr call:

DECLARE @ID INT = 501;
SELECT dbo.fn_ComputeJaccard(
        RectangleTwo.MinX, RectangleTwo.MinY, RectangleTwo.MaxX, RectangleTwo.MaxY,
        RectangleOne.MinX, RectangleOne.MinY, RectangleOne.MaxX, RectangleOne.MaxY) as RunningTotal
FROM PreProcessed RectangleOne
INNER JOIN PreProcessed RectangleTwo
ON RectangleTwo.ID <> RectangleOne.ID

WHERE dbo.fn_ComputeJaccard(
        RectangleTwo.MinX, RectangleTwo.MinY, RectangleTwo.MaxX, RectangleTwo.MaxY,
        RectangleOne.MinX, RectangleOne.MinY, RectangleOne.MaxX, RectangleOne.MaxY) > .97
AND RectangleTwo.ID = @ID

I would need to select this data into a temp table, normalize that table and then perform an update to the original table with the values (newValue*.5 + oldValue*.9) then renormalize the whole table. I imagine this would take a while to process, so I'm looking for the most efficient way of doing that, plus a solution to the multiple updates flying in issue.

Any advice you could give me would be great!

Thanks

Anon
  • 10,660
  • 1
  • 29
  • 31
Faraday
  • 2,904
  • 3
  • 23
  • 46
  • Your question is very confusing. Can you describe what you mean by "normalize that table"? Also, what kind of running time are you expecting for each update? You say that: "I imagine this would take a while to process," but we have no idea what you're doing in order to gauge the time (thus cannot gauge how many concurrent database transactions will be running). – tommy_o Jan 14 '14 at 19:27
  • Normalize the table: Make the sum of the scores equal to one. When I said "I imagine this would take a while", I meant each update run, which would (in my estimation) take around a second. Concurrent DB transactions, I'd like to be able to handle as many as possible (SQL box has 32GB of RAM), but you tell me what is possible... I don't even know if the approach I suggested is the best approach... – Faraday Jan 14 '14 at 22:52
  • 1
    I think there is vocabulary confusion between "normalize (statistics)" and "normalize (database theory)". – Anon Jan 14 '14 at 23:21
  • Updated title... lol, my bad! Didn't even occur to me... – Faraday Jan 14 '14 at 23:25

0 Answers0