Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I tried:</p> <pre><code>SELECT X.PersonName, ( SELECT TOP 1 Candy FROM CandyPreferences WHERE PersonName=X.PersonName AND PreferenceFactor=x.HighestPreference ) AS TopCandy FROM ( SELECT PersonName, MAX(PreferenceFactor) AS HighestPreference FROM CandyPreferences GROUP BY PersonName ) AS X </code></pre> <p>This seems to work, though I can't speak to efficiency without real data and a realistic load.</p> <p>I did create a primary key over PersonName and Candy, though. Using SQL Server 2008 and no additional indexes shows it using two clustered index scans though, so it could be worse.</p> <hr> <p>I played with this a bit more because I needed an excuse to play with the Data Generation Plan capability of "datadude". First, I refactored the one table to have separate tables for candy names and person names. I did this mostly because it allowed me to use the test data generation without having to read the documentation. The schema became:</p> <pre><code>CREATE TABLE [Candies]( [CandyID] [int] IDENTITY(1,1) NOT NULL, [Candy] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Candies] PRIMARY KEY CLUSTERED ( [CandyID] ASC ), CONSTRAINT [UC_Candies] UNIQUE NONCLUSTERED ( [Candy] ASC ) ) GO CREATE TABLE [Persons]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [PersonName] [nvarchar](100) NOT NULL, CONSTRAINT [PK_Preferences.Persons] PRIMARY KEY CLUSTERED ( [PersonID] ASC ) ) GO CREATE TABLE [CandyPreferences]( [PersonID] [int] NOT NULL, [CandyID] [int] NOT NULL, [PrefernceFactor] [real] NOT NULL, CONSTRAINT [PK_CandyPreferences] PRIMARY KEY CLUSTERED ( [PersonID] ASC, [CandyID] ASC ) ) GO ALTER TABLE [CandyPreferences] WITH CHECK ADD CONSTRAINT [FK_CandyPreferences_Candies] FOREIGN KEY([CandyID]) REFERENCES [Candies] ([CandyID]) GO ALTER TABLE [CandyPreferences] CHECK CONSTRAINT [FK_CandyPreferences_Candies] GO ALTER TABLE [CandyPreferences] WITH CHECK ADD CONSTRAINT [FK_CandyPreferences_Persons] FOREIGN KEY([PersonID]) REFERENCES [Persons] ([PersonID]) GO ALTER TABLE [CandyPreferences] CHECK CONSTRAINT [FK_CandyPreferences_Persons] GO </code></pre> <p>The query became:</p> <pre><code>SELECT P.PersonName, C.Candy FROM ( SELECT X.PersonID, ( SELECT TOP 1 CandyID FROM CandyPreferences WHERE PersonID=X.PersonID AND PrefernceFactor=x.HighestPreference ) AS TopCandy FROM ( SELECT PersonID, MAX(PrefernceFactor) AS HighestPreference FROM CandyPreferences GROUP BY PersonID ) AS X ) AS Y INNER JOIN Persons P ON Y.PersonID = P.PersonID INNER JOIN Candies C ON Y.TopCandy = C.CandyID </code></pre> <p>With 150,000 candies, 200,000 persons, and 500,000 CandyPreferences, the query took about 12 seconds and produced 200,000 rows.</p> <hr> <p>The following result surprised me. I changed the query to remove the final "pretty" joins:</p> <pre><code>SELECT X.PersonID, ( SELECT TOP 1 CandyID FROM CandyPreferences WHERE PersonID=X.PersonID AND PrefernceFactor=x.HighestPreference ) AS TopCandy FROM ( SELECT PersonID, MAX(PrefernceFactor) AS HighestPreference FROM CandyPreferences GROUP BY PersonID ) AS X </code></pre> <p>This now takes two or three seconds for 200,000 rows.</p> <p>Now, to be clear, nothing I've done here has been meant to improve the performance of this query: I considered 12 seconds to be a success. It now says it spends 90% of its time in a clustered index seek.</p>
    singulars
    1. This table or related slice is empty.
    1. POEfficient SQL 2000 Query for Selecting Preferred Candy
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJohn Saunders
    1. USJohn Saunders
    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. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONested select statements which check against a column of the outer select are inefficient and will slow down the query with a large number of items. Since you only do it once per name, this has less impact than in the suggestions others have made.
      singulars
      1. PO
      1. USLucero
    2. COCan you characterize "inefficient"? The clustered index scan didn't seem like such a bad thing. Adding an index over PersonName, Preference changed it to use a nonclustered index seek and nonclustered index scan over the new index instead of using the primary key. Can't tell perf difference w/o test data.
      singulars
      1. PO
      1. USJohn Saunders
    3. CO@Lucero: You are wrong. SQL server can without problem turn this into join equivalents. Check the execution plans, I promise you it does it if it finds it worth it.
      singulars
      1. PO
      1. USerikkallen
 

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