· 8 years ago · Sep 01, 2018, 09:28 PM
1Help optimizing a SQL query
2GO
3/****** Object: StoredProcedure [dbo].[Nop_ProductLoadAllPaged] Script Date: 04/25/2011 13:26:39 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10ALTER PROCEDURE [dbo].[Nop_ProductLoadAllPaged]
11(
12 @CategoryID int = 0,
13 @ManufacturerID int = 0,
14 @ProductTagID int = 0,
15 @FeaturedProducts bit = null, --0 featured only , 1 not featured only, null - load all products
16 @PriceMin money = null,
17 @PriceMax money = null,
18 @RelatedToProductID int = 0,
19 @Keywords nvarchar(MAX),
20 @SearchDescriptions bit = 0,
21 @ShowHidden bit = 0,
22 @PageIndex int = 0,
23 @PageSize int = 2147483644,
24 @FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). e.g. 14,15,16
25 @LanguageID int = 0,
26 @OrderBy int = 0, --0 position, 5 - Name, 10 - Price, 15 - creation date
27 @WarehouseCombinationID int,
28 @TotalRecords int = null OUTPUT
29)
30AS
31BEGIN
32
33 --init
34 DECLARE @SearchKeywords bit
35 SET @SearchKeywords = 1
36 IF (@Keywords IS NULL OR @Keywords = N'')
37 SET @SearchKeywords = 0
38
39 SET @Keywords = isnull(@Keywords, '')
40 SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
41
42 --filter by attributes
43 SET @FilteredSpecs = isnull(@FilteredSpecs, '')
44 CREATE TABLE #FilteredSpecs
45 (
46 SpecificationAttributeOptionID int not null
47 )
48 INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
49 SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');
50
51 DECLARE @SpecAttributesCount int
52 SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs
53
54 --paging
55 DECLARE @PageLowerBound int
56 DECLARE @PageUpperBound int
57 DECLARE @RowsToReturn int
58
59 SET @RowsToReturn = @PageSize * (@PageIndex + 1)
60 SET @PageLowerBound = @PageSize * @PageIndex
61 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
62
63 CREATE TABLE #DisplayOrderTmp
64 (
65 [ID] int IDENTITY (1, 1) NOT NULL,
66 [ProductID] int NOT NULL
67 )
68
69 INSERT INTO #DisplayOrderTmp ([ProductID])
70 SELECT p.ProductID
71 FROM Nop_Product p with (NOLOCK)
72 LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
73 LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
74 LEFT OUTER JOIN Nop_ProductTag_Product_Mapping ptpm with (NOLOCK) ON p.ProductID=ptpm.ProductID
75 LEFT OUTER JOIN Nop_RelatedProduct rp with (NOLOCK) ON p.ProductID=rp.ProductID2
76 LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
77 LEFT OUTER JOIN Nop_ProductVariant_Warehouse_Mapping wpv with (NOLOCK) ON pv.ProductVariantID = wpv.ProductVariantID
78 LEFT OUTER JOIN Nop_ProductVariantLocalized pvl with (NOLOCK) ON pv.ProductVariantID = pvl.ProductVariantID AND pvl.LanguageID = @LanguageID
79 LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = @LanguageID
80 WHERE
81 (
82 (
83 @CategoryID IS NULL OR @CategoryID=0
84 OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
85 )
86 AND (
87 @ManufacturerID IS NULL OR @ManufacturerID=0
88 OR (pmm.ManufacturerID=@ManufacturerID AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
89 )
90 AND (
91 @ProductTagID IS NULL OR @ProductTagID=0
92 OR ptpm.ProductTagID=@ProductTagID
93 )
94 AND (
95 @RelatedToProductID IS NULL OR @RelatedToProductID=0
96 OR rp.ProductID1=@RelatedToProductID
97 )
98 AND (
99 @ShowHidden = 1 OR p.Published = 1
100 )
101 AND
102 (
103 p.Deleted=0 AND wpv.Deleted=0
104 )
105 AND
106 (
107 @ShowHidden = 1 OR pv.Published = 1
108 )
109 AND (
110 @ShowHidden = 1 OR wpv.Published = 1
111 )
112 AND
113 (
114 @ShowHidden = 1 OR pv.Deleted = 0
115 )
116 AND (
117 @PriceMin IS NULL OR @PriceMin=0
118 OR wpv.Price > @PriceMin
119 )
120 AND (
121 @PriceMax IS NULL OR @PriceMax=2147483644 -- max value
122 OR wpv.Price < @PriceMax
123 )
124 AND (
125 wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)
126 )
127 AND (
128 @SearchKeywords = 0 or
129 (
130 -- search standard content
131 patindex(@Keywords, p.name) > 0
132 or patindex(@Keywords, pv.name) > 0
133 or patindex(@Keywords, pv.sku) > 0
134 or (@SearchDescriptions = 1 and patindex(@Keywords, p.ShortDescription) > 0)
135 or (@SearchDescriptions = 1 and patindex(@Keywords, p.FullDescription) > 0)
136 or (@SearchDescriptions = 1 and patindex(@Keywords, pv.Description) > 0)
137 -- search language content
138 or patindex(@Keywords, pl.name) > 0
139 or patindex(@Keywords, pvl.name) > 0
140 or (@SearchDescriptions = 1 and patindex(@Keywords, pl.ShortDescription) > 0)
141 or (@SearchDescriptions = 1 and patindex(@Keywords, pl.FullDescription) > 0)
142 or (@SearchDescriptions = 1 and patindex(@Keywords, pvl.Description) > 0)
143 )
144 )
145 AND
146 (
147 @ShowHidden = 1
148 OR
149 (getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
150 )
151 AND
152 (
153 --filter by specs
154 @SpecAttributesCount = 0
155 OR
156 (
157 NOT EXISTS(
158 SELECT 1
159 FROM #FilteredSpecs [fs]
160 WHERE [fs].SpecificationAttributeOptionID NOT IN (
161 SELECT psam.SpecificationAttributeOptionID
162 FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam
163 WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID
164 )
165 )
166
167 )
168 )
169 )
170 ORDER BY
171 CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
172 THEN pcm.DisplayOrder END ASC,
173 CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
174 THEN pmm.DisplayOrder END ASC,
175 CASE WHEN @OrderBy = 0 AND @RelatedToProductID IS NOT NULL AND @RelatedToProductID > 0
176 THEN rp.DisplayOrder END ASC,
177 CASE WHEN @OrderBy = 0
178 THEN p.[Name] END ASC,
179 CASE WHEN @OrderBy = 5
180 THEN dbo.NOP_getnotnullnotempty(pl.[Name],p.[Name]) END ASC,
181 CASE WHEN @OrderBy = 10
182 THEN wpv.Price END ASC,
183 CASE WHEN @OrderBy = 15
184 THEN wpv.Price END DESC,
185 CASE WHEN @OrderBy = 20
186 THEN wpv.Price END DESC,
187 CASE WHEN @OrderBy = 25
188 THEN wpv.UnitPrice END ASC
189
190 DROP TABLE #FilteredSpecs
191
192 CREATE TABLE #PageIndex
193 (
194 [IndexID] int IDENTITY (1, 1) NOT NULL,
195 [ProductID] int NOT NULL
196 )
197 INSERT INTO #PageIndex ([ProductID])
198 SELECT ProductID
199 FROM #DisplayOrderTmp with (NOLOCK)
200 GROUP BY ProductID
201 ORDER BY min([ID])
202
203 --total records
204 SET @TotalRecords = @@rowcount
205 SET ROWCOUNT @RowsToReturn
206
207 DROP TABLE #DisplayOrderTmp
208
209 --return
210 SELECT
211 p.ProductId,
212 p.Name,
213 p.ShortDescription,
214 p.FullDescription,
215 p.AdminComment,
216 p.TemplateId,
217 p.ShowOnHomePage,
218 p.MetaKeywords,
219 p.MetaDescription,
220 p.MetaTitle,
221 p.SEName,
222 p.AllowCustomerReviews,
223 p.AllowCustomerRatings,
224 p.RatingSum,
225 p.TotalRatingVotes,
226 p.Published,
227 p.Deleted,
228 p.CreatedOn,
229 p.UpdatedOn
230 FROM
231 #PageIndex [pi]
232 INNER JOIN Nop_Product p with (NOLOCK) on p.ProductID = [pi].ProductID
233 WHERE
234 [pi].IndexID > @PageLowerBound AND
235 [pi].IndexID < @PageUpperBound
236 ORDER BY
237 IndexID
238
239 SET ROWCOUNT 0
240
241 DROP TABLE #PageIndex
242END