How to optimize this query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to optimize this query

How to optimize this query

Rate Topic

Display Mode

Topic Options

Author

Message

mabud_ncc

Posted Friday, December 04, 2009 2:32 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 5, Visits: 51

I have a query for a report which is basically aggregating some data from different tables as following:
/*******************************************************/

WITH CAT AS

(SELECT DISTINCT
c.cat_id,
c.name

FROM c INNER JOIN pv ON c.cat_id=pv.cat_id
WHERE
pv.step=1 and
c.status=0
)

SELECT
c.name,
(select count(1)
from p inner join p2p on p.prod_id=p2p.prod_id
inner join pv on p2p.path_id = pv.path_id
where
pv.cat_id=c.cat_id and
p.status=0 and
p2p.status=0
) AS 'Total products',

(select count(1)
from p inner join o on p.product_id=o.product_id
inner join p2p on p.prod_id=p2p.prod_id
inner join pv on p2p.path_id = pv.path_id
where
pv.cat_id=c.cat_id and
p.status=0 and
o.status=0 and
p2p.status=0
) AS 'Total Reviews',

(select count(1)
from p inner join p2p on p.prod_id=p2p.prod_id
inner join pv on p2p.path_id = pv.path_id
where
pv.cat_id=c.cat_id and
p.status=0 and
p2p.status=0 and
(select count(1) from O where prod_id = p.prod_id and status=0)>1) AS 'Prod. With 1 Review'

From
CAT c
order by c.name

/*************************************/
as you can see in the above query I am using some common tables again and again for different aggragation purpose like
p, p2p,pv are common tables used in all subqueries. As the no of record in each of these tables is very high so the query is running very slow.

Can anyone please suggest me how can i optimize this query?

Thanks

Post #828698

Dave Ballantyne

Posted Friday, December 04, 2009 2:40 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

Cross post, please keep to one thread

http://www.sqlservercentral.com/Forums/Topic828700-360-1.aspx

Clear Sky SQL
My Blog
Kent user group

Post #828703

« Prev Topic | Next Topic »

Permissions