Query rewrite for optimization

Last Post 08 Sep 2011 07:10 AM by Vikrant More. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
rerichards
New Member
New Member

--
30 Jul 2011 06:46 PM
I have the following query that I am wondering if it can be rewritten to return the desired results more optimally. If not, and it is what it is, then I can live with it. I am just looking for a second set of eyes to see if anything can be done.

SELECT T.LiteKey, T.ModTypeFID
FROM (SELECT WidgetFID, Min(WidgetMod_UID) AS WidgetMod_UID, LiteKey
           FROM dbo.tb_Widgets_ModTypes
           WHERE LiteKey = @LiteKey
           GROUP BY LiteKey, WidgetFID) WMT
JOIN dbo.tb_Widgets_ModTypes T
  ON T.WidgetMod_UID = WMT.WidgetMod_UID
  AND T.LiteKey = WMT.LiteKey
WHERE T.LiteKey = @LiteKey

As you can see, tb_Widgets_ModTypes is joining on itself to get the desired results. In the derived table I am returning the Min(WidgetMod_UID) for each LiteKey, WidgetFID combination. Most of the records (perhaps 98% of the records) in the table are returned. Those results are then self joining on to tb_Widgets_ModTypes to get the ModTypeFID for each of the matching records in the derived table. So, retrieving 98% of the table and then essentially doing it all over again is expensive.

The statement uses a Clustered Index Seek in each instance. This is the table schema and the indexes employed.

CREATE TABLE dbo.tb_Widgets_ModTypes(
  LiteKey int NOT NULL,
  WidgetMod_UID int IDENTITY(1,1) NOT NULL,
  WidgetFID int NOT NULL,
  ModTypeFID int NOT NULL,
  CONSTRAINT PK_tb_Widgets_ModTypes PRIMARY KEY CLUSTERED (WidgetMod_UID, LiteKey))
GO

CREATE NONCLUSTERED INDEX IX_tb_Widgets_ModTypes_LiteKey_Include
ON dbo. tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID)
INCLUDE(WidgetMod_UID)
GO

The following is some sample data:
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(113980, 7897, 22)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(113980, 8675, 21)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(113980, 8675, 22)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(114696, 22700, 78)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(114696, 22700, 78)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(114696, 22805, 81)
INSERT dbo.tb_Widgets_ModTypes (LiteKey, WidgetFID, ModTypeFID) VALUES(114696, 22805, 82)
Vikrant More
New Member
New Member

--
08 Sep 2011 07:10 AM
I just wrote the simple query
SELECT DISTINCT T.LiteKey, T.ModTypeFID FROM dbo.tb_Widgets_ModTypes T
WHERE T.LiteKey = @LiteKey
which gives me the same out put as you wrote a query above.
i tried it for this condition WHERE T.LiteKey = 113980
it gives me 2 rows in the resultset
Out Put from both Query
LiteKey ModTypeFID
113980 21
113980 22
 


Acceptable Use Policy
---