Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I tested also ROW_NUMBER() version + added additional index</p> <p>Create index IX_z5CandyPreferences On z5CandyPreferences(PersonId,PrefernceFactor,CandyID) </p> <p>Response times between Emtucifor's and ROW_NUMBER() version (with index in place) are marginal (if any - test should be repeated number of times and take averages, but I expect there would not be any significant difference)</p> <p>Here is query:</p> <pre><code>Select p.PersonName, c.Candy, y.PrefernceFactor From z5Persons p Inner Join (Select * from (Select cp.PersonId, cp.CandyId, cp.PrefernceFactor, ROW_NUMBER() over (Partition by cp.PersonId Order by cp.PrefernceFactor, cp.CandyId ) as hp From z5CandyPreferences cp) X Where hp=1) Y on p.PersonId = Y.PersonId Inner Join z5Candies c on c.CandyId=Y.CandyId </code></pre> <p>and results with and without new index:</p> <pre><code> | Without index | With Index ---------------------------------------------- Query (Aff.Rows 183,290) |CPU time Elapsed time | CPU time Elapsed time -------------------------- |-------- ------------ | -------- ------------ Emtucifor (with joins) |1,031 ms 3,990 ms | 890 ms 3,758 ms John Saunders (with joins) |2,406 ms 4,343 ms | 1,735 ms 3,414 ms ROW_NUMBER() (with joins) |2,094 ms 4,888 ms | 953 ms 3,900 ms. Emtucifor (with joins) Without index | With Index ----------------------------------------------------------------------- Table |Scan count logical reads | Scan count logical reads -------------------|---------- ------------- | ---------- ------------- Worktable | 0 0 | 0 0 z5Candies | 1 526 | 1 526 z5CandyPreferences | 1 2,022 | 1 990 z5Persons | 1 733 | 1 733 John Saunders (with joins) Without index | With Index ----------------------------------------------------------------------- Table |Scan count logical reads | Scan count logical reads -------------------|---------- ------------- | ---------- ------------- z5CandyPreferences | 183292 587,912 | 183,290 585,570 z5Persons | 3 802 | 1 733 Worktable | 0 0 | 0 0 z5Candies | 3 559 | 1 526 Worktable | 0 0 | - - ROW_NUMBER() (with joins) Without index | With Index ----------------------------------------------------------------------- Table |Scan count logical reads | Scan count logical reads -------------------|---------- ------------- | ---------- ------------- z5CandyPreferences | 3 2233 | 1 990 z5Persons | 3 802 | 1 733 z5Candies | 3 559 | 1 526 Worktable | 0 0 | 0 0 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POEfficient SQL 2000 Query for Selecting Preferred Candy
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNiikola
    1. USNiikola
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COHow many rows are in your tables?
      singulars
      1. PO
      1. USErikE
    2. COyou have it in my previous post
      singulars
      1. PO
      1. USNiikola
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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