CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
WinFXGuy
WinFXGuy

Reputation: 1599

How to rewrite this as a nested query?

I was told to rewrite thie following query with a nested query without joins for better performance. Is it true? If so, how to rewrite it?

SELECT distinct A.Company_Name,C.Outlet_Name,
       C.Outlet_FCE_ID,D.Usergroup_Name
FROM   company A, PURCHASE_INVOICE B, 
       Outlet C, User_Group D,CT_USER E
WHERE A.Company_ID = B.Company_ID AND B.Outlet_ID  = C.Outlet_ID 
  AND B.Company_ID = C.Company_ID AND B.Username   = E.Username 
  AND E.Usergroup_ID=D.Usergroup_ID

Here is the table structure.

enter image description here

Upvotes: 1

Views: 198

Answers (2)

WinFXGuy
WinFXGuy

Reputation: 1599

Let me answer my own question. I don't like it this way, but here we go....

    SELECT  distinct
        ( SELECT    Company_Name
          FROM      dbo.COMPANY
          WHERE     Company_ID = p.Company_ID
        ) AS 'CompanyName' ,
        ( SELECT    Outlet_Name
          FROM      dbo.OUTLET
          WHERE     Company_ID = p.Company_ID
                    AND Outlet_ID = p.Outlet_ID
        ) AS 'OutletName' ,
        ( SELECT    Outlet_FCE_ID
          FROM      dbo.OUTLET
          WHERE     Company_ID = p.Company_ID
                    AND Outlet_ID = p.Outlet_ID
        ) 'OutletFCEID' ,
        ( SELECT    Usergroup_Name
          FROM      dbo.USER_GROUP
          WHERE     Usergroup_ID IN ( SELECT    Usergroup_ID
                                      FROM      CT_USER
                                      WHERE     Username = p.UserName )
        ) 'UsergroupName'
FROM    dbo.PURCHASE_INVOICE p

Upvotes: 0

gjvdkamp
gjvdkamp

Reputation: 10536

The optimizer should get this right, but you may want to compare the the performance of the original query against this one: 

select distinct 
    c.company_name, 
    o.outlet_name, 
    o.outlet_fce_id, 
    ug.usergroup_name
from company c 
    inner join (select distinct company_id, outlet_id, username from purchase_invoice) i on c.company_id=i.company_id
    inner join outlet o 
        on  i.outlet_id  = o.outlet_id
        and i.company_id = o.company_id
    inner join ot_user u     on b.username     = e.username
    inner join user_group ug on u.usergroup_id = d.usergroup_id

The distinct on the purschases table could eliminate some work, but I doublt if there will be that many dupes.

What would help more is an index on purchase_invoice (username, outlet_id, company_id). This could speed it up because it would be a covering index on the table. The join would only have to look at the index and could skip reading the actual table. That helps if the table is wide.

Also note the order of columns in the index.I'm guessing that ct_user also has a lot of rows, and that it has a clustered index on Username. This way both the index and ct_user will be sorted on username, allowing a merge join for joining the two big tables.

Also please use join syntax and meaningful aliases for your tables like c fr company and ug for user_group. It shouldn't matter to the database but it defenitely helps humans trying to read your code. Also, allcaps LOOKS LIKE YOUR SCREAMING ALL THE TIME, but maybe that's just me :-)

GJ

Upvotes: 1

Related Questions