4

I have two tables with a structure like below: enter image description here and am selecting the data with below query:

SELECT
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = PN) PN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Winder) Winder,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = CouplingType) CouplingType,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Type) Type,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = ILayer) ILayer,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = OLayer) OLayer
FROM Product_Coupling_Serial

So,what is the best practice for the best performance of selects like that (fastest way)?

9
  • Give each row unique Id (Identity) in on table and refer it to another. Commented Jan 30, 2017 at 10:27
  • @RajeshRanjan That is exactly the same as you said,right now. Commented Jan 31, 2017 at 4:41
  • Use LEFT OUTER JOIN as TH said and create index on the column using in ON condition Commented Jan 31, 2017 at 8:18
  • @RajeshRanjan Would you please make an example?I don't understand Commented Jan 31, 2017 at 9:30
  • @Nima: Rejesh's solution should be faster, use SET STATISTICS TIME ON to make sure you are measuring your query time correctly.
    – pacreely
    Commented Feb 6, 2017 at 13:06

5 Answers 5

9

Create a covering index on Id column and Include Name on the table Management_EnumerableItem.

CREATE INDEX IDX_Name on Management_EnumerableItem (Id) Include (Name) with (fillfactor=90);

and write it like this.

SELECT A.NAME,
B.NAME,
C.NAME,
D.NAME,
E.NAME,
F.NAME,
G.NAME,

FROM Product_Coupling_Serial P
LEFT OUTER JOIN Management_EnumerableItem A
ON A.ID = P.DN
LEFT OUTER JOIN Management_EnumerableItem B
ON B.ID = P.PN
LEFT OUTER JOIN Management_EnumerableItem C
ON C.ID = P.Winder
LEFT OUTER JOIN Management_EnumerableItem D
ON D.ID = P.CouplingType
LEFT OUTER JOIN Management_EnumerableItem E
ON E.ID = P.[Type]
LEFT OUTER JOIN Management_EnumerableItem F
ON F.ID = P.[ILayer]
LEFT OUTER JOIN Management_EnumerableItem G
ON G.ID = P.[OLayer]

Thanks

7
  • Dear Rajesh,I'm using Sql Server 2088 R2 and there is 400,000 rows in my tables.I tried my solution and your solution on same engine,and in the same situation over 20 times,but result was exactly 16 seconds every time with both solutions ! So I prefer my solution because that is easy understood as T.H. said too. Commented Feb 1, 2017 at 5:49
  • Create the index I added to my answer at top then execute the query. Commented Feb 1, 2017 at 7:14
  • 2
    This is similar to the classic structure of a star schema used in OLAP, with the difference that the dimensions are always part of the same table. What's easier to understand... depends on the perspective of the watcher, I guess. It took me longer to understand the original ... As for performance: I don't think there's much to improve, SQL Server is able to find out that both queries are equivalent, and choose what it considers the optimum execution plan.
    – joanolo
    Commented Feb 5, 2017 at 18:11
  • 2
    @MatthewSontum sorry but I do see a possible advantage (small or big) of using this index over (the possibly much wider) clustered index. Commented Feb 16, 2017 at 19:19
  • 1
    Also, if he's querying the whole table, the optimizer is just going to do a full scan of Product_Coupling_Serial. So how can indexes on the Product_Coupling_Serial table work? Also, if this is a table with a copious amount of data, the selectivity of each value that is an FK to Management_EnumerableItem.ID may be very low, limiting the utility of any index on them.
    – ErikE
    Commented Feb 16, 2017 at 21:42
4

Please consider trying out this query, and let me know how it performs.

WITH EnumerableItemNames AS (
   SELECT
      PcsID = pcs.ID,
      v.ColName,
      ItemName = me.Name
   FROM
      dbo.Product_Coupling_Serial pcs
      CROSS APPLY (VALUES
         ('DN', pcs.DN),
         ('PN', pcs.PN),
         ('Winder', pcs.Winder),
         ('CouplingType', pcs.CouplingType),
         ('Type', pcs.[Type]),
         ('ILayer', pcs.ILayer),
         ('OLayer', pcs.OLayer)
      ) v (ColName, EnumerableItemID)
      LEFT JOIN dbo.Management_EnumerableItem me
         ON v.EnumerableItemID = me.ID
)
SELECT
   p.DN, p.PN, p.Winder, p.CouplingType, p.[Type], p.ILayer, p.OLayer
FROM
   EnumerableItemNames ein
   PIVOT (
      Max(ein.ItemName)
      FOR ein.ColName IN (DN, PN, Winder, CouplingType, [Type], ILayer, OLayer)
   ) p
;

While it could theoretically be a total failure in performance, I also think it could end up performing better. If it isn't better, you still might be able to get it there by inserting the results of the CTE (without the LEFT JOIN to Management_EnumerableItem) into a temp table with some carefully-chosen indexes, then doing a second query using the temp table. It does depend a bit on how exactly you're querying the table, how many rows it has, how wide they are, how many result rows there are, and so on.

If you need more columns from the Product_Coupling_Serial table, then you could try including those in the CTE, but that may affect the performance badly by bloating the memory required for the pivot operation, so you could also just join back to it at the end:

// CTE here...
SELECT
   pcs.Year,
   pcs.ProductCode,
   pcs.QRText,
   pcs.UniqueSerial,
   ...
   p.DN, p.PN, p.Winder, p.CouplingType, p.[Type], p.ILayer, p.OLayer
FROM
   EnumerableIDs ei
   PIVOT (
      Max(ei.ItemName)
      FOR ei.ColName IN (DN, PN, Winder, CouplingType, [Type], ILayer, OLayer)
   ) p
   INNER JOIN dbo.Product_Coupling_Serial pcs
      ON ei.PcsID = pcs.ID
;
1

Although you can rewrite the query with a string of left joins, the performance will be the same as SQL Server will optimise the subqueries in your code into joins. (This may depend on the SQL Server version, and also on what other complexity you're adding into the final query. You can confirm using the execution plan.)

So the way you've done it is probably the best as it's the most easily understood.

1
  • 2
    I have to disagree here. Putting Selects in each line of your select statement is about as easily understood as using Comma-based joins. If one of my developers were to submit that code, I'd kindly ask them to use proper joins before I deploy it.
    – SQLDevDBA
    Commented Feb 9, 2017 at 15:17
0
+50

You've already defined the primary key on Management_EnumerableItem as ID, which will by default created the clustered index on that column. Since the query you've written can use the clustered index on that table, it has no cause to use a non-clustered index.

There could, however, be performance benefits to creating clustered indexes on each of the joined columns in the Product_Coupling_Serial table. So:

CREATE INDEX IX_DN ON Product_Coupling_Serial (DN);
CREATE INDEX IX_PN ON Product_Coupling_Serial (PN);
CREATE INDEX IX_Winder ON Product_Coupling_Serial (Winder);
CREATE INDEX IX_CouplingType ON Product_Coupling_Serial (CouplingType);
CREATE INDEX IX_Type ON Product_Coupling_Serial ([Type]);
CREATE INDEX IX_ILayer ON Product_Coupling_Serial (ILayer);
CREATE INDEX IX_OLayer ON Product_Coupling_Serial (OLayer);

However, it would be best just to run your original query through SSMS to view the actual execution plan, which will return any missing indexes, to verify that those indexes will be used. But if you're feeling lucky you can just run the SQL I included above.

Next you'll want to rewrite your query in minimal form. SQL Server is a set based interpreted language, and the query optimizer is written in such a way that the minimal solution is either best, or gets automatically interpreted to the best solution in most cases. In your case the minimal form is:

SELECT
E1.NAME as DN,
E2.NAME as PN,
E3.NAME as Winder,
E4.NAME as CouplingType,
E5.NAME as [Type],
E6.NAME as ILayer,
E7.NAME as OLayer
FROM Product_Coupling_Serial P
LEFT JOIN Management_EnumerableItem E1 ON E1.ID = P.DN
LEFT JOIN Management_EnumerableItem E2 ON E2.ID = P.PN
LEFT JOIN Management_EnumerableItem E3 ON E3.ID = P.Winder
LEFT JOIN Management_EnumerableItem E4 ON E4.ID = P.CouplingType
LEFT JOIN Management_EnumerableItem E5 ON E5.ID = P.[Type]
LEFT JOIN Management_EnumerableItem E6 ON E6.ID = P.ILayer
LEFT JOIN Management_EnumerableItem E7 ON E7.ID = P.OLayer;

This should evaluate to the best possible execution plan with minimal code. If it does not you'll want to explore CTEs (Common Table Expressions) next, but it is unlikely you'll find anything faster than what I wrote. If you can upgrade your server to 2012 another possibility is the use of a Columnstore index, but since you listed 2088 R2 (which I assume is a typo for 2008 R2) you'll not be able to use that feature until you upgrade to 2012 or above.

3
  • Yes,2088 was a typo,I'm using 2008 R2. Commented Feb 12, 2017 at 11:44
  • 4
    How will non-clustered indexes on the Product_Coupling_Serial table help given that it is almost certainly a huge table, much larger than the Management_EnumerableItem, and that the resulting execution plan is almost guaranteed to use the Product_Coupling_Serial table as the left input to any join and thus using a scan (or seek if any conditions are specified), obviating any need for indexes? Or, are you envisioning a scenario where conditions are placed on the Management_EnumerableItem table? That's not the asker's scenario, here...
    – ErikE
    Commented Feb 16, 2017 at 18:49
  • That is interesting you thought Product_Coupling_Serial would be larger than Management_EnumerableItem. When Product_Coupling_Serial has 10 foreign keys that reference Management_EnumerableItem... Commented Feb 16, 2017 at 19:26
-1

You should create separate indexes on Product_Coupling_Serial for each column you use on your joins (DN, PN, Winder).Then both the subquery and the left joins will be faster.

You should consider the index size of the table and insert speed.

I don't think SQL server will use a covering index in this scenario because each column is queried separately.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.