join again and again - 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

SQL Server 2005 Forums

Transact-SQL (2005)

join again and again

Author

Topic

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 11:35:41

i am joining the same two tables again and again and again, here's the view that i have

SELECT AutoDownloadID, DatePerformed, PriorDownload, DownloadedOrders,
(SELECT COUNT(i.InvoiceID) AS Expr1
FROM dbo.Invoice AS i INNER JOIN dbo.InvoiceClubs AS ic ON i.InvoiceID = ic.InvoiceID
WHERE (i.OrderDate < ad.PriorDownload) AND (ad.AutoDownloadID = ic.AutoDownloadID)) AS ForcedSuppressions,
(SELECT COUNT(i.InvoiceID) AS Expr1
FROM dbo.Invoice AS i INNER JOIN
dbo.InvoiceClubs AS ic ON i.InvoiceID = ic.InvoiceID INNER JOIN
dbo.WineClubOffers AS wc ON wc.WineClubOfferID = ic.WineClubOfferID
WHERE (i.OrderDate >= ad.PriorDownload) AND (i.OrderDate <= ad.DatePerformed) AND (ic.NumShipments > 0) OR (i.OrderDate >= ad.PriorDownload) AND (i.OrderDate <= ad.DatePerformed) AND (wc.PromoCode LIKE 'INET%')) AS ManualOrders, (SELECT COUNT(i.InvoiceID) AS Expr1
FROM dbo.Invoice AS i INNER JOIN dbo.InvoiceClubs AS ic ON i.InvoiceID = ic.InvoiceID WHERE (i.OrderDate >= ad.PriorDownload) AND (i.OrderDate <= ad.DatePerformed) AND (i.ZeroShip = 1)) AS ZeroShips,
(SELECT COUNT(i.InvoiceID) AS Expr1
FROM dbo.Invoice AS i INNER JOIN dbo.InvoiceClubs AS ic ON i.InvoiceID = ic.InvoiceID
WHERE (i.OrderDate >= ad.PriorDownload) AND (i.OrderDate <= ad.DatePerformed) AND (ic.Suppression = 1)) AS Suppressions,
(SELECT COUNT(i.InvoiceID) AS Expr1
FROM dbo.Invoice AS i INNER JOIN
dbo.InvoiceClubs AS ic ON i.InvoiceID = ic.InvoiceID
WHERE (i.OrderDate >= ad.PriorDownload) AND (i.OrderDate <= ad.DatePerformed)) AS TotalOrders
FROM dbo.AutoDownload AS ad

Its working extremely slow. And i don't see how can i group those same joins, rather than joining the same tables again and again, i wanna make this faster. anyone know how can i group them or do something that will make it fast.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 11:42:41

Can you post your requirement with tables structures?else it wil be really difficult for somebody trying to help u out

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 11:45:40

Thanks for your quick reply man. All i want to know is this:

i have invoice table and invoiceclubs table which i am joining again and again and with different where conditions i am invoiceids. How can i group them, so it will only join those tables once with i.Invoiceid = ic.invoiceid and then use the same joined table, and execute wheres and countinvoiceids. ? does it make sense, or will the structure of the table help?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-03 : 11:49:36

You could so SUM(CASE...) statements instead of subquery COUNT() statements. Something like the following may work:

SELECT  AutoDownloadID,
        DatePerformed,
        PriorDownload,
        DownloadedOrders,
        SUM(CASE WHEN (i.OrderDate < ad.PriorDownload) THEN 1
                 ELSE 0
            END) AS ForcedSuppressions,
        SUM(CASE WHEN (i.OrderDate >= ad.PriorDownload)
                      AND (i.OrderDate <= ad.DatePerformed)
                      AND (ic.NumShipments > 0)
                      OR (i.OrderDate >= ad.PriorDownload)
                      AND (i.OrderDate <= ad.DatePerformed)
                      AND (wc.PromoCode LIKE 'INET%') THEN 1
                 ELSE 0
            END) AS ManualOrders,
        SUM(CASE WHEN (i.OrderDate >= ad.PriorDownload)
                      AND (i.OrderDate <= ad.DatePerformed)
                      AND (i.ZeroShip = 1) THEN 1
                 ELSE 0
            END) AS ZeroShips,
        SUM(CASE WHEN (i.OrderDate >= ad.PriorDownload)
                      AND (i.OrderDate <= ad.DatePerformed)
                      AND (ic.Suppression = 1) THEN 1
                 ELSE 0
            END) AS Suppressions,
        SUM(CASE WHEN (i.OrderDate >= ad.PriorDownload)
                      AND (i.OrderDate <= ad.DatePerformed) THEN 1
                 ELSE 0
            END) AS TotalOrders
FROM    dbo.AutoDownload AS ad
        INNER JOIN dbo.InvoiceClubs AS ic
        ON ad.AutoDownloadID = ic.AutoDownloadID
        INNER JOIN dbo.Invoice AS i
        ON i.InvoiceID = ic.InvoiceID
        LEFT OUTER JOIN dbo.WineClubOffers AS wc
        ON wc.WineClubOfferID = ic.WineClubOfferID

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 11:57:25

I don't think i can use that, because i can not join autodownload table with the rest two, since it doesn't have anything common.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-03 : 12:10:33

I had my join order swapped. In the query I am joining AutoDownload -> InvoiceClubs -> Invoice and WineClubOffers. Will this not work with your db schema?

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 12:34:03

works like a charm and lot faster than it used to. thanks a lot. appreciate it.

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 13:46:59

Need a little more help, to make it even faster i took out inner join for autodownload and made it like this:
FROM dbo.AutoDownload AS ad,
dbo.InvoiceClubs AS ic INNER JOIN
dbo.Invoice AS i ON i.InvoiceID = ic.InvoiceID LEFT OUTER JOIN
dbo.WineClubOffers AS wc ON wc.WineClubOfferID = ic.WineClubOfferID
ON wc.WineClubOfferID = ic.WineClubOfferID

It works fine and fast, but when i store it in as view and reopened it, it inserted CROSS JOIN for autodownload, that's why its working slow again, how can i prevent management studio from inserting that cross join in there. ?? thanks for the help in advance.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-03 : 13:59:04

By removing the keywords INNER JOIN you are telling SQL Server to do a cross join. Its the same as if you typed:
FROM dbo.AutoDownload AS ad, CROSS JOIN
dbo.InvoiceClubs AS ic INNER JOIN
dbo.Invoice AS i ON i.InvoiceID = ic.InvoiceID LEFT OUTER JOIN
dbo.WineClubOffers AS wc ON wc.WineClubOfferID = ic.WineClubOfferID
ON wc.WineClubOfferID = ic.WineClubOfferID

By turning your INNER JOIN into a CROSS JOIN you have removed all criteria from the join and are now creating a Cartesian Product of the two tables.

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 14:50:42

that's what i thought, but i tried executing both, and the one without cross join explicitly stated runs alot faster than the other one. totally lost on why is this. i confirmed it few times, and as soon as i put it as view, it inserts cross join automatically.

samir80
Starting Member

10 Posts

Posted - 2008-03-03 : 15:09:08

Anyways, believe it or not i changed the order and it works as fast as it did without cross join, strange but works. Thanks alot jdaman for help. just incase you interested in the order, it is

FROM dbo.InvoiceClubs AS ic INNER JOIN
dbo.Invoice AS i ON i.InvoiceID = ic.InvoiceID LEFT OUTER JOIN
dbo.WineClubOffers AS wc ON wc.WineClubOfferID = ic.WineClubOfferID CROSS JOIN
dbo.AutoDownload AS ad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources