Cross joins slow? - 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

Cross joins slow?

Author

Topic

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-12-09 : 06:09:34

The following script runs incredibly slowly..

I started by cross joining supplier_codes with product categories. I've then used a few nested queries in the select statement to return the total number of products those suppliers have in those categories.

The nested queries serve to determine which products belong to which supplier and which product categories those products belong to, both referencing the outer query.

Is there a quicker method to achieve this? I've heard joining tables is quicker than using nested queries but due to the nature of this query requiring the cross join, that doesn't feel like the intuitive strategy?

Any help is much appreciated.

Query:

Select distinct ord.supplier_code,
cat.category,
(select count(pasc.productcode) prodCount from productsBySupplier pasc where pasc.suppliercode = ord.supplier_code and pasc.idProdAttribute in
(select pa.idProdAttribute from productAttribute pa Where pa.idProductAttributeGroup in
(select pag.idProductAttributeGroup from productAttributeGroups pag where pag.attributeGroup = cat.category))) prodCount
From orders ord
Cross Join categories cat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 07:02:17

[code]
select m.*,prodCount
from
(
Select distinct ord.supplier_code,
cat.category
From orders ord
Cross Join categories cat
)m
left join productAttributeGroups pag
on pag.attributeGroup = m.category
left join productAttribute pa
on pa.idProductAttributeGroup = pag.idProductAttributeGroup
left join (select suppliercode,idProdAttribute,count(pasc.productcode) prodCount
from productsBySupplier
group by suppliercode,idProdAttribute
)pasc
on pasc.suppliercode = m.supplier_code
and pasc.idProdAttribute = pa.idProdAttribute
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources