April 10, 2014 at 10:43 am
Hi,
WITH cte_OrderProjectType AS
(
select Orderid, min(TypeID) , min(CTType) , MIN(Area)
from tableA A inner join
tableB B ON A.PID = B.PID left join
tableC C ON C.TypeID = B.TypeID LEFT JOIN
tableD D ON D.AreaID = B.ID
group by A.orderid
)
This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..
Is there any way I can improve the query performance ?
Thanks,
PSB
April 10, 2014 at 10:54 am
What's wrong with 1.3 seconds?
Seriously though, be sure you have indices on the join columns. Pre-aggregate the minimum calculations do reduce the join rows.
April 10, 2014 at 10:55 am
Try this method may be.. http://sqlsaga.com/sql-server/how-to-remove-duplicates-from-a-table-in-sql-server/[/url]
There are ranking functions that you can use instead of aggregations too.. but depends on the purpose again..
Read about ranking functions here @ http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/[/url]
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 10, 2014 at 10:56 am
Gerald.Britton 28181 (4/10/2014)
What's wrong with 1.3 seconds?Seriously though, be sure you have indices on the join columns. Pre-aggregate the minimum calculations do reduce the join rows.
I think OP meant 90 secs, may be 😛
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 10, 2014 at 11:09 am
There are multiple queries in the view that uses MIN and total time to execute the view is 8 minutes . 🙁
This view is used by a stored procedure which is being called from a application and we are receiving timeout issue.
April 14, 2014 at 2:12 pm
Please add an alias to columns in the SELECT:
select ?.Orderid, min(?.TypeID) , min(?.CTType) , MIN(?.Area)
Otherwise we can't really rewrite any of the code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 14, 2014 at 4:42 pm
Query with alias :
WITH cte_OrderProjectType AS
(
select A.Orderid, min(B.TypeID) , min(C.CTType) , MIN(D.Area)
from tableA A inner join
tableB B ON A.PID = B.PID left join
tableC C ON C.TypeID = B.TypeID LEFT JOIN
tableD D ON D.AreaID = B.ID
group by A.orderid
)
Thanks,
PSB
April 14, 2014 at 4:51 pm
You could try something like this and see if it helps:
WITH cte_OrderProjectType AS
(
select A.Orderid, min(B.TypeID) , min(C.CTType) , MIN(D.Area)
from tableA A inner join (
select PID, min(TypeID) AS TypeID
from tableB
group by PID ) AS
B ON A.PID = B.PID left join (
select TypeID, min(CTType) AS CTType
from tableC
group by TypeID ) AS
C ON C.TypeID = B.TypeID LEFT JOIN (
select AreaID, MIN(area) AS Area
from tableD
group by AreaID ) AS
D ON D.AreaID = B.ID
group by A.orderid
)
select *
from cte_OrderProjectType
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 14, 2014 at 6:14 pm
Here is another way, using ranking
😎
WITH CTE_OPT_BASE AS
(
select
ROW_NUMBER() OVER
(
PARTITION BY
A.Orderid
,B.TypeID
,C.CTType
,D.Area
ORDER BY
(SELECT NULL)
) AS ORDER_RID
,A.Orderid
,B.TypeID
,C.CTType
,D.Area
from tableA A inner join
tableB B ON A.PID = B.PID left join
tableC C ON C.TypeID = B.TypeID LEFT JOIN
tableD D ON D.AreaID = B.ID
)
,cte_OrderProjectType AS
(
SELECT
OPT.Orderid
,OPT.TypeID
,OPT.CTType
,OPT.Area
FROM CTE_OPT_BASE
WHERE ORDER_RID = 1
)
April 14, 2014 at 6:58 pm
Thanks everyone.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply