make query faster ideas - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

make query faster ideas

Author

Topic

logpop
Starting Member

16 Posts

Posted - 2013-06-04 : 22:49:38

Does anyone have any idea if its possible to make this query faster? Its job is to get 10 "also bought" items from online store.
Algorithm is like this:
1. find all orders which contain ProductID
2. take all products from all orders found, sort them by most frequent and return top 10.


CREATE PROCEDURE [dbo].[_ATCustomerAlsoBought]
    @ProductID int = 2142,
    @ShowOnlyIfStock bit = 0,
    @PortalID int = 0,
    @NumberOfItems int = 10
As

declare @cIdent char(16) = (select acIdent from tHE_SetItem where anQId=@ProductId)
if @cIdent is null
	return

if not exists(SELECT top 1 acKey FROM tHE_OrderItem WHERE acIdent = @cIdent) 
   return
   

SET ROWCOUNT @NumberOfItems 
select T2.ProductID,cast (SUM(T3.anQty) as int) as TotalNum,ProductName,ProductNumber,T2.Description,ProductImage,
 T2.UnitCost,UnitCost2,UnitCost3,UnitCost4,UnitCost5,UnitCost6,
 BulkPriceLimit1,BulkPriceLimit2,BulkPriceLimit3,BulkPriceLimit4,BulkPriceLimit5,SalePrice,SaleStart,SaleEnd,
 URL,EAN,ISBN,Free1,Free2,Free3,KeyWords,Stock,OrderQuant,Status,T2.ProductCost,PayPalSubscription,
 T2.CategoryID,Category2ID,Category3,PublicationStart,PublicationEnd,StartingPrice,ReservePrice,
 'HasOptions' = 'false', -- case when (exists (select 1 from CAT_ProductsOptions T5 where T5.ProductID = T1.ProductID) or exists (select 1 from CAT_ProductImages T6 where T6.ProductID = T1.ProductID and T6.ImageType = 1)) then 'true' else 'false' end,
 'HasRequiredOptions' = 'false' -- case when (exists (select 1 from CAT_ProductsOptions T7 inner join CAT_Options T8 on T7.OptionID = T8.OptionID where T7.ProductID = T1.ProductID and T8.Status = '0')) then 'true' else 'false' end
from tHE_OrderItem T1
inner join tHE_OrderItem T3 on T1.acKey = T3.acKey
inner join _ATTmpProducts T2 ON T3.acIdent = T2.acIdent
where T1.acIdent = @cIdent and T2.acIdent <> @cIdent
GROUP BY T2.ProductID,ProductName,ProductNumber,T2.Description,ProductImage,T2.UnitCost,UnitCost2,UnitCost3,
	UnitCost4,UnitCost5,UnitCost6,BulkPriceLimit1,BulkPriceLimit2,BulkPriceLimit3,BulkPriceLimit4,BulkPriceLimit5,
	SalePrice,SaleStart,SaleEnd,URL,EAN,ISBN,Free1,Free2,Free3,KeyWords,Stock,OrderQuant,Status,T2.ProductCost,
	PayPalSubscription,T2.CategoryID,Category2ID,Category3,PublicationStart,PublicationEnd,StartingPrice,
	ReservePrice
ORDER BY TotalNum DESC

SET ROWCOUNT 0
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:30:22

how are you checking most frequent here? i cant see you taking count anywhere to order and take top 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ijmar86
Starting Member

8 Posts

Posted - 2013-06-05 : 00:36:35

Check for any missing indexes on ur table,

possibly do not include too many cases in select statement, it makes execution delay, instead use conditions like if with 2 select statement.

First take the records frm ur main table into ur Temp table and thn make a join of the temp table with other tables. It'll be faster.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-06-05 : 05:22:27

Post your query execution plan then easy to check whether you have proper indexes on join columns.

Visakh:may be with Rowcount they are getting top 10 records.

Thanks....

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 05:24:33

quote:
Originally posted by mmkrishna1919

Post your query execution plan then easy to check whether you have proper indexes on join columns.

Visakh:may be with Rowcount they are getting top 10 records.

Thanks....

M.MURALI kRISHNA

How will it give them most frequent ones

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 05:30:37

quote:
Originally posted by visakh16

quote:
Originally posted by mmkrishna1919

Post your query execution plan then easy to check whether you have proper indexes on join columns.

Visakh:may be with Rowcount they are getting top 10 records.

Thanks....

M.MURALI kRISHNA

How will it give them most frequent ones

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh, they are sorting by TotalNum in descending and set rowcount to 10.. so he/she can get top 10 records based on TotalQuantity
SET @NumberOfItems = 10
.
.
SET ROWCOUNT @NumberOfItems
select T2.ProductID,cast (SUM(T3.anQty) as int) as TotalNum
.
.
.
ORDER BY TotalNum DESC
SET ROWCOUNT 0

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 05:35:38

quote:
Originally posted by logpop

2. take all products from all orders found, sort them by most frequent and return top 10.

Hi,
Check once this link
http://msdn.microsoft.com/en-us/library/ms188774(v=sql.90).aspx

Better to use TOP n Option instead of SET ROWCOUNT

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 05:37:11

quote:
Originally posted by bandi

quote:
Originally posted by visakh16

quote:
Originally posted by mmkrishna1919

Post your query execution plan then easy to check whether you have proper indexes on join columns.

Visakh:may be with Rowcount they are getting top 10 records.

Thanks....

M.MURALI kRISHNA

How will it give them most frequent ones

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh, they are sorting by TotalNum in descending and set rowcount to 10.. so he/she can get top 10 records based on TotalQuantity
SET @NumberOfItems = 10
.
.
SET ROWCOUNT @NumberOfItems
select T2.ProductID,cast (SUM(T3.anQty) as int) as TotalNum
.
.
.
ORDER BY TotalNum DESC
SET ROWCOUNT 0

--
Chandu

oh ok didnt see that SUM(T3.anQty)
ok..makes sense now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 05:38:31

quote:
Originally posted by visakh16
oh ok didnt see that SUM(T3.anQty)
ok..makes sense now

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 05:42:48

try this and see if this is any better


CREATE PROCEDURE [dbo].[_ATCustomerAlsoBought]
    @ProductID int = 2142,
    @ShowOnlyIfStock bit = 0,
    @PortalID int = 0,
    @NumberOfItems int = 10
As

declare @cIdent char(16) = (select acIdent from tHE_SetItem where anQId=@ProductId)
if @cIdent is null
	return

if not exists(SELECT top 1 acKey FROM tHE_OrderItem WHERE acIdent = @cIdent) 
   return
SELECT *
FROM
(   
SELECT *,DENSE_RANK() OVER (ORDER BY TotalNum DESC) AS Rnk
FROM 
(
select T2.ProductID,
SUM(T3.anQty) OVER (PARTITION BY T2.ProductID) as TotalNum,
ProductName,ProductNumber,T2.Description,ProductImage,
 T2.UnitCost,UnitCost2,UnitCost3,UnitCost4,UnitCost5,UnitCost6,
 BulkPriceLimit1,BulkPriceLimit2,BulkPriceLimit3,BulkPriceLimit4,BulkPriceLimit5,SalePrice,SaleStart,SaleEnd,
 URL,EAN,ISBN,Free1,Free2,Free3,KeyWords,Stock,OrderQuant,Status,T2.ProductCost,PayPalSubscription,
 T2.CategoryID,Category2ID,Category3,PublicationStart,PublicationEnd,StartingPrice,ReservePrice,
 'HasOptions' = 'false', -- case when (exists (select 1 from CAT_ProductsOptions T5 where T5.ProductID = T1.ProductID) or exists (select 1 from CAT_ProductImages T6 where T6.ProductID = T1.ProductID and T6.ImageType = 1)) then 'true' else 'false' end,
 'HasRequiredOptions' = 'false' -- case when (exists (select 1 from CAT_ProductsOptions T7 inner join CAT_Options T8 on T7.OptionID = T8.OptionID where T7.ProductID = T1.ProductID and T8.Status = '0')) then 'true' else 'false' end
from tHE_OrderItem T1
inner join tHE_OrderItem T3 on T1.acKey = T3.acKey
inner join _ATTmpProducts T2 ON T3.acIdent = T2.acIdent
where T1.acIdent = @cIdent and T2.acIdent <> @cIdent
)t
)r
WHERE Rnk <=10
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

logpop
Starting Member

16 Posts

Posted - 2013-06-05 : 15:42:02

Hi,
visakh16 your procedure takes just as long but doesnt work correct, ~1400 results are returned and not sorted correct.
Using top instead of row count seems to work faster.
I figured out solution by myself, code wasnt even returning correct results because group by was missing one field from joined table: t3.acName

this is statistics:
(10 row(s) affected)
Table 'tHE_OrderItem'. Scan count 1524, logical reads 21894, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '_ATTmpProducts'. Scan count 1, logical reads 343, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 138 ms.

logpop
Starting Member

16 Posts

Posted - 2013-06-07 : 10:25:47

i found out it's working fast only once it's in cache.
but on first run it still takes 1 minute.
i simplified query to this:

select T2.ProductID
from OrderItems T1
join OrderItems T2 on T1.OrderID = T2.OrderID
where T1.ProductID = @ProductID

and saw it was just as slow (after sql server restart)

then i looked at execution plan and saw there was rid lookup taking 97 %.
i saw on google cause for this was using non clustered index.
so i changed to clustered index
and now it works ok! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources