StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Comment on Emtucifor solution (as I cant make regular comments)</p> <p>I like this solution, but have some comments how it could be improved (in this specific case).</p> <p>It can't be done much if you have everything in one table, but having few tables as in John Saunders' solution will make things a bit different.</p> <p>As we are dealing with numbers in [CandyPreferences] table we can use math operation instead of concatenation to get max value.</p> <p>I suggest PreferenceFactor to be decimal instead of real, as I believe we don't need here size of real data type, and even further I would suggest decimal(n,n) where n<10 to have only decimal part stored in 5 bytes. Assume decimal(3,3) is enough (1000 levels of preference factor), we can do simple</p> <p>PackedData = Max(PreferenceFactor + CandyID)</p> <p>Further, if we know we have less than 1,000,000 CandyIDs we can add cast as:</p> <p>PackedData = Max(Cast(PreferenceFactor + CandyID as decimal(9,3)))</p> <p>allowing sql server to use 5 bytes in temporary table</p> <p>Unpacking is easy and fast using floor function.</p> <p>Niikola</p> <p>-- ADDED LATER ---</p> <p>I tested both solutions, John's and Emtucifor's (modified to use John's structure and using my suggestions). I tested also with and without joins.</p> <p>Emtucifor's solution clearly wins, but margins are not huge. It could be different if SQL server had to perform some Physical reads, but they were 0 in all cases.</p> <p>Here are the queries:</p> <pre><code> SELECT [PersonID], CandyID = Floor(PackedData), PreferenceFactor = Cast(PackedData-Floor(PackedData) as decimal(3,3)) FROM ( SELECT [PersonID], PackedData = Max(Cast([PrefernceFactor] + [CandyID] as decimal(9,3))) FROM [z5CandyPreferences] With (NoLock) GROUP BY [PersonID] ) X SELECT X.PersonID, ( SELECT TOP 1 CandyID FROM z5CandyPreferences WHERE PersonID=X.PersonID AND PrefernceFactor=x.HighestPreference ) AS TopCandy, HighestPreference as PreferenceFactor FROM ( SELECT PersonID, MAX(PrefernceFactor) AS HighestPreference FROM z5CandyPreferences GROUP BY PersonID ) AS X Select p.PersonName, c.Candy, y.PreferenceFactor From z5Persons p Inner Join (SELECT [PersonID], CandyID = Floor(PackedData), PreferenceFactor = Cast(PackedData-Floor(PackedData) as decimal(3,3)) FROM ( SELECT [PersonID], PackedData = Max(Cast([PrefernceFactor] + [CandyID] as decimal(9,3))) FROM [z5CandyPreferences] With (NoLock) GROUP BY [PersonID] ) X ) Y on p.PersonId = Y.PersonId Inner Join z5Candies c on c.CandyId=Y.CandyId Select p.PersonName, c.Candy, y.PreferenceFactor From z5Persons p Inner Join (SELECT X.PersonID, ( SELECT TOP 1 cp.CandyId FROM z5CandyPreferences cp WHERE PersonID=X.PersonID AND cp.[PrefernceFactor]=X.HighestPreference ) CandyId, HighestPreference as PreferenceFactor FROM ( SELECT PersonID, MAX(PrefernceFactor) AS HighestPreference FROM z5CandyPreferences GROUP BY PersonID ) AS X ) AS Y on p.PersonId = Y.PersonId Inner Join z5Candies as c on c.CandyID=Y.CandyId </code></pre> <p>And the results:</p> <pre><code> TableName nRows ------------------ ------- z5Persons 200,000 z5Candies 150,000 z5CandyPreferences 497,445 Query Rows Affected CPU time Elapsed time --------------------------- ------------- -------- ------------ Emtucifor (no joins) 183,289 531 ms 3,122 ms John Saunders (no joins) 183,289 1,266 ms 2,918 ms Emtucifor (with joins) 183,289 1,031 ms 3,990 ms John Saunders (with joins) 183,289 2,406 ms 4,343 ms Emtucifor (no joins) -------------------------------------------- Table Scan count logical reads ------------------- ---------- ------------- z5CandyPreferences 1 2,022 John Saunders (no joins) -------------------------------------------- Table Scan count logical reads ------------------- ---------- ------------- z5CandyPreferences 183,290 587,677 Emtucifor (with joins) -------------------------------------------- Table Scan count logical reads ------------------- ---------- ------------- Worktable 0 0 z5Candies 1 526 z5CandyPreferences 1 2,022 z5Persons 1 733 John Saunders (with joins) -------------------------------------------- Table Scan count logical reads ------------------- ---------- ------------- z5CandyPreferences 183292 587,912 z5Persons 3 802 Worktable 0 0 z5Candies 3 559 Worktable 0 0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload