triangular joins

I read this:

http://www.sqlservercentral.com/articles/T-SQL/61539/

it seems that triangular joins is even worse that RBAR code, right?

any way to get ride of it?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, by using the windowed aggregate functions
SUM(field ) OVER (partition by ... order by )
COUNT( * ) OVER (partition by ... order by )

that will do the job in a much better way
0
marrowyungSenior Technical architecture (Data)Author Commented:
so you agree that it is really bad? you double check it from execution plan?

any link suggest how to convert it to a much better query ?

but the problem is it counts value in a nested loop sub query ? so it just loop out like RBAR ?

in that example from the link:

SELECT x.OrderID, 
        x.Freight, 
        (SELECT SUM(y.Freight)   
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningTotal,
        (SELECT COUNT(y.Freight)
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningCount
  FROM dbo.Orders X
 ORDER BY x.OrderID

Open in new window


how about I make sure that the column in the where clause indexed (y.Freight,y.OrderID, x.OrderID) ? then even if it really has to run through subquery, it will run faster to loop all value out ?

so in this case only the subquery :

   (SELECT SUM(y.Freight)   
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningTotal,
        (SELECT COUNT(y.Freight)
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningCount

Open in new window


gives trouble but not the count, right?

I want to known the logic about the problems here.
0
PortletPaulfreelancerCommented:
The examples above are to produce "running sum" and "running count"

In older versions of SQL Server, e.g. SQL Server 2000, there was little or no alternative to those "triangular join" approaches.

Now, in more recent SQL Server versions you do have very good alternatives that Guy has already identified.
       SUM(field ) OVER (partition by ... order by ... ) --<< for running sum
       COUNT( * ) OVER (partition by ... order by ... ) --<< for running count
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

marrowyungSenior Technical architecture (Data)Author Commented:
Paul Maxwell,

"The examples above are to produce "running sum" and "running count""

this means the count/sum keep running while this is running:

(SELECT SUM(y.Freight)   
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningTotal,
        (SELECT COUNT(y.Freight)
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID) AS RunningCount

Open in new window


?

so  RunningTotal = (SELECT SUM(y.Freight)  
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID)

and  RunningCount  =  (SELECT COUNT(y.Freight)
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID)

is not good ?

"Now, in more recent SQL Server versions you do have very good alternatives that Guy has already identified."

so this one: (partition by ... order by ... ) --<< for running sum, is :

(SELECT SUM(y.Freight)   
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID

Open in new window


and this one:  (partition by ... order by ... ) --<< for running count is:

(SELECT COUNT(y.Freight)
           FROM dbo.Orders y
          WHERE y.OrderID <= x.OrderID)

Open in new window


?

so what partition by do in here and that's what I don't understand !

so we don't do SELECT SUM(y.Freight)  but SUM(y.Freight) OVER ( ... ) ?

and   COUNT( y.Freight) OVER (..). to replace SELECT COUNT(y.Freight) ...  ?
0
PortletPaulfreelancerCommented:
If you want definitional information on a T-SQL feature, use Books Online (BOL)

OVER() is explained there
PARTITION BY
Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
I suggest reading the reference above fully

so we don't do SELECT SUM(y.Freight)  but SUM(y.Freight) OVER ( ... ) ?
YES
and   COUNT( y.Freight) OVER (..). to replace SELECT COUNT(y.Freight) ...  ?
YES
WHEN you need a RUNNING SUM or RUNNING COUNT
0
marrowyungSenior Technical architecture (Data)Author Commented:
I am so sorry that I think I have to make the logic clear.

but the problem is it counts value in a nested loop sub query ? so it just loop out like RBAR ?
0
PortletPaulfreelancerCommented:
bad (like RBAR) is "triangular joins"

good (NOT like RBAR) is:
       SUM(field ) OVER (partition by ... order by ... ) --<< for running sum
       COUNT( * ) OVER (partition by ... order by ... ) --<< for running count

very simple, no further comment needs to be made on this
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.