Friday, March 30, 2012
Optimizing Query - Product Tips
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated)
---------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
---------------
end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Thanks for any time spent!DDL and sample data would help alot...
But I'll give it a go...
DDL:
CREATE TABLE...
Sample Data
INSERT INTO myTable(Cols...
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
Should help us get an answer quicker...|||Or maybe not...I think I hurt myself...
Let me point out a coupld of things...
SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT TOP 1
products.prodID
FROM orders
INNER JOIN orderProducts ON order.orderID = orderProducts.orderID
INNER JOIN products ON orderProducts.prodID = products.prodID
WHERE orders.custID=394
GROUP BY products.prodType
ORDER BY SUM(orderProducts.PCS) DESC), 2))
Does this even run?
Does ProdType = ProdId?
a GROUP BY qith no SCALAR in the SELECT?
OREDER BY SUM...what for?
Why the COALESCE? IF it's NULL (what ever it is) is won't be evaluated
I guess what I'm saying is..post the ddl sample data AND expected results, and tell us what the business req is...
it'll be a lot faster that way...|||I don't have access to the sql server right now, so I cant give you the result and the table structure 100% - maybe i mistyped something on the query itself, but i dont think so..
I've missed the ORDER by cntSold DESC at the end of the query
it worked as far as i've tried :-)
sorry - let me get some things:
table bestSold7Days (generated hourly, articles best sold in the last 7 days)
prodID - product ID
cntSold - sold pieces in 7 days
prodType - product type - e.g. 0 hardware, 1 software, 2 special product
Title - product Title
table orders
orderID - order ID identity
custID - customer ID
table orderProdcuts (products contained in order)
orderID - order ID
prodID - product ID
pcs - Pieces ordered
the whole query puts out following:
prodID, Title, Price
349, H53-39, 393.33
39392, P3838-3, 5959.21
the sense of the hole thing is to get the top 2 sold products in the last 7 days, wich are the same of interest (prodType) wich the customer prefers and which he didn't already order...|||In your query statement, there are three JOIN words. That makes execution of the query very slow. My suggestion is that you may use a stored procedure in which you can separate your query into several steps. That will improve the performance.|||Hello gyuan,
I've already tried that.
Did a sp wich got me the favorite prodType, but overall it didn't really improve the performance very much.
Currently I'm using it through the stored procedure (4 different tables, 4 different prodTypes) and then querying the top 2 products which he didn't already order.
The SP gives out: 3,4,3,50 - this i split in vb and use it in the queries following...
The whole thing takes from 8-30 seconds (depending on how much the customer ordered)|||What about this?
select top 2 b.prodid
from
BestSold7Days b
left join
(
select distinct(p.prodID)
from Orders o
inner join OrderProducts p
on p.orderid = o.orderid
where o.custID = 394
) x
on x.prodID = b.prodID
where x.prodID is null
order by b.cntSold desc|||A good stored procedure can definitely improve the execution speed of the query statements, but it depends on the content of the stored procedure. If you can post the details of your tables and requirements, that will give us a good help to solve it.|||table sets:
products
products2
products3
products4
(all same structure)
prodID - identity
prodType - product type - int
orders
orderID - order ID (identity)
custID - customer ID - int
orderProducts
orderID - order ID - int
prodID - int
pcs - int - pieces ordered
Here's the sp i currently use to do the query:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[getTopprodType] (@.custID int )
RETURNS varchar(50) AS
BEGIN
DECLARE @.Ret varchar(50)
SET @.Ret=ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID = @.custID) GROUP BY products.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products2].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products2 ON orderProducts.prodID = products2.prodID WHERE (orders.custID = @.custID) GROUP BY products2.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products3].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products3 ON orderProducts.prodID = products3.prodID WHERE (orders.custID = @.custID) GROUP BY products3.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products4].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products4 ON orderProducts.prodID = products4.prodID WHERE (orders.custID = @.custID) GROUP BY products4.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
RETURN @.Ret
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
There are 4 different product tables (unique id's) wich get together here.
The SP puts out the following
2,3,4,9
I put this into an array in vb to use it for the following query
SELECT TOP 2 bestSold7Days.Anz, products.prodID, products.Title, products.Price FROM bestSold7Days INNER JOIN products ON bestSold7Days.prodID = products.prodID WHERE ((products.Price IS NOT NULL) AND ((SELECT TOP 1 orderProducts.prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID = " & Me.custID & ") GROUP BY orderProducts.prodID HAVING (orderProducts.prodID = bestSold7Days.prodID)) IS NULL)" & tempGen & " ORDER BY bestSold7Days.Anz DESC;
tempGen is replaced by the favourite prodType (e.g. AND prodType=4 if has any)|||found a faster solution for the second query!
if i do the lookup of the products wich the customer already ordered in a derived table (like a join, but the table is a query) it's much faster!
gut the whole process down to ~2-6 seconds.
Now i need to optimize the getTopProdType procedure, still taking some seconds...|||Could you post how many records in each of the tables? And what are indexes on those tables?|||Indexes on all searched fields
prodID
prodType
(in all tables)
products1 ~ 20000 records
products2 ~ 200000 records
products3 ~ 5000 records
products4 ~ 2000000 records
orders ~ 50000 records
orderProducts ~ 2000000 records
prodType is not clustered.
does anybody have a rule when to make an index clustered?|||1. Based on your query, an index on order.custID would be helpful.
2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.|||sorry forgot this field - custID is also indexed.
Only field queried wich is not indexed is the pcs field in the orderProducts table.|||I think item 2 would be very helpful to speed up your query.
If a little slower update on orders related tables is acceptable, you may update the information in table xxx using triggers or whenever an order is updated.
SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT prodType
FROM xxxx
WHERE custID=394), 2))|||what do you mean by item 2?
i've just seen that you can only do one clustered index... all main id's are clustered indexes...|||Originally posted by shianmiin
1. Based on your query, an index on order.custID would be helpful.
2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.
I mean the item no. 2 above.|||that query would take 69 hours to finish (~50000 customers, ~5 seconds per prodType get).|||i've just got it working with a runtime of 60 seconds for all customers.
I don't know why it's that fast - but it works :)
INSERT INTO tProdTypeFavourites(custID, prodType1...)
(SELECT ... ) (SELECT ...)
the sub-queries are pretty big but the whole thing runs very fast!|||good job. :)|||thanks - same for you! thnx for every reply!
Wednesday, March 21, 2012
Optimization of query
My Query is like this..
set @.Grouptitle = @.GroupPFR
set @.GroupOrder = 5
set @.Unittype = 2
set @.MetricName = 'Product to Net Revenue %'
set @.MetricOrder = 6
insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)
select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,
max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,
@.GroupOrder,@.MetricOrder --from @.temptable
from @.FinalData f1 inner join @.FinalData f2 on f1.weekdate = f2.weekdate
where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')
and ( f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue')
group by f1.weekdate
There are many calculations like this in my procedure.
and It takes like 3 min to run whole procedure
now as I am doing group by..
So In Execution plan it show me that 60% of the query time is take n by SORT operation..
can any one give me any other option to do this.
Thanks
Its difficult to make any absolutes without seeing the whole query and DDL etc. However, a possible suggestion would be to use temp tables rather than table variables. You are then able to create indexes which may improve the performance of the query and SQL Server keeps distribution statistics on temp tables which may also help it select a better plan.
HTH!
|||If I use temp table my procedure will recompile..
wont that increse the executin time?
|||Try changing your code to:
set @.Grouptitle = @.GroupPFR
set @.GroupOrder = 5
set @.Unittype = 2
set @.MetricName = 'Product to Net Revenue %'
set @.MetricOrder = 6
insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)
select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,
max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,
@.GroupOrder,@.MetricOrder --from @.temptable
from @.FinalData f1
inner join @.FinalData f2 on f1.weekdate = f2.weekdate and f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue'
where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')
group by f1.weekdate
|||It may well increase execution time though this may less than the gains of using the temp table. As i mentioned, its difficult to say for definite with only that code to go by.
The only way you'll be able to tell is to load test the suggestions and see which one performs best.
Good luck.
|||How can I create index on temp table|||
Just like any other table:
Code Snippet
create table #rich
(int1 int)
create index idx_rich_1
on #rich(int1)
drop table #rich
|||If i use KEEP PLAN in my query than it wont every time recompile for Temp table. Is that right?|||I believe thats correct. Check out the following links which may be of assistance:
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Optimization of query
My Query is like this..
set @.Grouptitle = @.GroupPFR
set @.GroupOrder = 5
set @.Unittype = 2
set @.MetricName = 'Product to Net Revenue %'
set @.MetricOrder = 6
insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)
select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,
max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,
@.GroupOrder,@.MetricOrder --from @.temptable
from @.FinalData f1 inner join @.FinalData f2 on f1.weekdate = f2.weekdate
where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')
and ( f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue')
group by f1.weekdate
There are many calculations like this in my procedure.
and It takes like 3 min to run whole procedure
now as I am doing group by..
So In Execution plan it show me that 60% of the query time is take n by SORT operation..
can any one give me any other option to do this.
Thanks
Its difficult to make any absolutes without seeing the whole query and DDL etc. However, a possible suggestion would be to use temp tables rather than table variables. You are then able to create indexes which may improve the performance of the query and SQL Server keeps distribution statistics on temp tables which may also help it select a better plan.
HTH!
|||If I use temp table my procedure will recompile..
wont that increse the executin time?
|||Try changing your code to:
set @.Grouptitle = @.GroupPFR
set @.GroupOrder = 5
set @.Unittype = 2
set @.MetricName = 'Product to Net Revenue %'
set @.MetricOrder = 6
insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)
select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,
max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,
@.GroupOrder,@.MetricOrder --from @.temptable
from @.FinalData f1
inner join @.FinalData f2 on f1.weekdate = f2.weekdate and f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue'
where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')
group by f1.weekdate
|||It may well increase execution time though this may less than the gains of using the temp table. As i mentioned, its difficult to say for definite with only that code to go by.
The only way you'll be able to tell is to load test the suggestions and see which one performs best.
Good luck.
|||How can I create index on temp table|||
Just like any other table:
Code Snippet
create table #rich
(int1 int)
create index idx_rich_1
on #rich(int1)
drop table #rich
|||If i use KEEP PLAN in my query than it wont every time recompile for Temp table. Is that right?|||I believe thats correct. Check out the following links which may be of assistance:
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
sqlOptimization in retrieval using indexes
Supposing i have a table TABLE1(ID,ID2,field1,field2,...,fieldn)
The primary key is ID.
There is a secondary index on ID2. ID2 is a candidate key.
These are the only indices.
1)Do modern database systems like Oracle,DB2,SQL Server provide a way to execute the following query
SELECT * FROM TABLE1 ORDER BY ID
in approximately the same time taken for the foll. query
SELECT * FROM TABLE1
Particularly if the primary index (or file organisation) can be organised in a way (maybe i choose the file/index orgn. offered by the DB) so that the above is achieved.
Any help is appreciated.
Thanks in advance.For Oracle (any version > V7), your PK must have an index and
your other key, you say, is indexed so if you order by either of
these columns that are indexed, the query will not take very much
longer that your #2 statement.
You mention "approximately" so... this'll really have to do with
how many records are in the database. If all you have is a four (4)
column table with, lets say, < 5,000 rows, querying that table
and accessing index columns (in either the "where" or "Order by"
clause, will be very fast.
For exact results and complete details as to what the query will
do, you'd need to "anaylze" the query.|||One more doubt please.
With ref. to the first post,
1)SELECT * FROM TABLE1 WHERE ID>M AND ID<N
AND
2)SELECT * FROM TABLE1 WHERE ID2>M AND ID2<N
Won't 1) execute very much faster coz of using a primary index that matches the file organisation leading to virtually sequential retrieval?|||It also largly depends on your optimizer mode.
If you are using COST Based Optimisation, then the oracle database internally calculates the load and decide on wether to use the index or not.sql