代码之家 › 专栏 › 技术社区 › Brian Gillespie

高效SQL 2000查询优选糖果

sql-server-2000 database sql

Brian Gillespie · 技术社区 · 17 年前

SQL Server 2000

Name   Candy       PreferenceFactor
Jim    Chocolate   1.0
Brad   Lemon Drop   .9
Brad   Chocolate    .1
Chris  Chocolate    .5
Chris  Candy Cane   .5
499,995 more rows...

期望的查询结果:

Jim    Chocolate   1.0
Brad   Lemon Drop   .9
Chris  Chocolate    .5
~250,000 more rows...

11 回复 | 直到 17 年前

momo 17 年前

彼得在这方面走对了路,但有一些不必要的复杂性。

CREATE TABLE #CandyPreference (
   [Name] varchar(20),
   Candy varchar(30),
   PreferenceFactor decimal(11, 10)
)
INSERT #CandyPreference VALUES ('Jim', 'Chocolate', 1.0)
INSERT #CandyPreference VALUES ('Brad', 'Lemon Drop', .9)
INSERT #CandyPreference VALUES ('Brad', 'Chocolate', .1)
INSERT #CandyPreference VALUES ('Chris', 'Chocolate', .5)
INSERT #CandyPreference VALUES ('Chris', 'Candy Cane', .5)

SELECT
   [Name],
   Candy = Substring(PackedData, 13, 30),
   PreferenceFactor = Convert(decimal(11,10), Left(PackedData, 12))
FROM (
   SELECT
      [Name],
      PackedData = Max(Convert(char(12), PreferenceFactor) + Candy)
   FROM CandyPreference
   GROUP BY [Name]
) X

DROP TABLE #CandyPreference

一般来说,当试图提高查询性能时,如果可以节省I/O,你可以做很多额外的数学运算。保存整个表的查找或扫描可以大大加快查询速度,即使有所有的转换和子字符串等。

D'Arcy Rittich 17 年前

select c.Name, max(c.Candy) as Candy, max(c.PreferenceFactor) as PreferenceFactor
from Candy c
inner join (
    select Name, max(PreferenceFactor) as MaxPreferenceFactor
    from Candy
    group by Name
) cm on c.Name = cm.Name and c.PreferenceFactor = cm.MaxPreferenceFactor
group by c.Name
order by PreferenceFactor desc, Name

John Saunders 17 年前

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

不过,我确实在PersonName和Candy上创建了一个主键。使用SQL Server 2008并且没有其他索引时,显示它使用了两次聚集索引扫描,因此情况可能会更糟。

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

查询变为:

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

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

Niikola 17 年前

我建议PreferenceFactor是decimal而不是real,因为我认为这里不需要real数据类型的大小,更进一步地,我建议decimal(n,n),其中n<10仅将小数部分存储在5个字节中。假设十进制(3,3)就足够了(1000级偏好因子),我们可以做简单的

打包数据=最大值(首选因素+糖果ID)

打包数据=最大值(强制转换(首选因子+CandyID为十进制(9,3)))

使用地板功能可以轻松快速地打开包装。

我测试了John和Emtucifor的两种解决方案(根据John的结构和我的建议进行了修改)。我也测试了是否有连接。

    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

 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

nWorx 17 年前

select Name,Candy,PreferenceFactor
from candyTable ct 
where PreferenceFactor = 
    (select max(PreferenceFactor) 
     from candyTable where ct.Name = Name)

select top 1 Name,Candy,PreferenceFactor
from candyTable ct
where name = @name
and PreferenceFactor= 
    (select max([PreferenceFactor]) 
     from candyTable where name = @name )

改变!to@

erikkallen 17 年前

SELECT Name, Candy, PreferenceFactor
  FROM table AS a
 WHERE NOT EXISTS(SELECT * FROM table AS b
                   WHERE b.Name = a.Name
                     AND (b.PreferenceFactor > a.PreferenceFactor OR (b.PreferenceFactor = a.PreferenceFactor AND b.Candy > a.Candy))

Santosh Chandavaram 17 年前

select name, candy, max(preference)
from tablename
where candy=@candy
order by name, candy

David Walker 17 年前

我将您的列名称更改为PersonName,以避免任何常见的保留字冲突。

SELECT     PersonName, MAX(Candy) AS PreferredCandy, MAX(PreferenceFactor) AS Factor
FROM         CandyPreference
GROUP BY PersonName
ORDER BY Factor DESC

shahkalpesh 17 年前

SELECT d.Name, a.Candy, d.MaxPref
FROM myTable a, (SELECT Name, MAX(PreferenceFactor) AS MaxPref FROM myTable) as D
WHERE a.Name = d.Name AND a.PreferenceFactor = d.MaxPref

Peter Radocchia 17 年前

select name
, candy  = substring(preference,7,len(preference))
  -- convert back to float/numeric
, factor = convert(float,substring(preference,1,5))/10
from (
  select name, 
    preference = (
      select top 1 
           -- convert from float/numeric to zero-padded fixed-width string
           right('00000'+convert(varchar,convert(decimal(5,0),preferencefactor*10)),5)
         + ';' + candy
       from candyTable b
       where a.name = b.name
       order by 
         preferencefactor desc
       , candy
       )
  from (select distinct name from candyTable) a
  ) a

convert(varchar,preferencefactor) + ';' + candy

factor = convert(float,substring(preference,1,charindex(';',preference)-1))
candy = substring(preference,charindex(';',preference)+1,len(preference))

Niikola 17 年前

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

                           |     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