Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rewrite query using LEFT JOIN Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 1:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:58 AM
Points: 718, Visits: 543
Can this query be written using LEFT JOIN to "person" as part of the FROM clause, rather than doing subquery?

Query, IO statics and execution plan is attached. Unfortunately test data is not available.

Regards.


  Post Attachments 
Query_Plan.txt (9 views, 51.66 KB)
Post #1443634
Posted Thursday, April 18, 2013 2:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
This should do the work for you

SELECT	CASE WHEN C.active_flag = 1 THEN 'Active' ELSE 'Inactive' END AS active_flag,
C.name, C.company_code,
SUM( CASE WHEN P.active_flag = 1 AND P.unregister_flag = 0 THEN 1 ELSE 0 END ) AS active_users,
SUM( CASE WHEN P.active_flag = 1 AND P.unregister_flag = 1 THEN 1 ELSE 0 END ) AS new_users,
SUM( CASE WHEN P.active_flag = 0 THEN 1 ELSE 0 END ) AS inactive_users,
C.test_company_flag, C.parent_company_flag
FROM dbo.company AS C
LEFT OUTER JOIN dbo.person AS P ON C.company_code = P.company_code
GROUP BY CASE WHEN C.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,
C.name, C.company_code, C.test_company_flag, C.parent_company_flag




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1443660
Posted Thursday, April 18, 2013 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 7,134, Visits: 13,521
Or this:
SELECT
active_flag = CASE WHEN c.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,
c.name,
c.company_code,
d.active_users,
d.new_users,
d.inactive_users,
test_company_flag,
parent_company_flag
FROM dbo.company c
LEFT JOIN (
SELECT
company_code,
active_users = SUM(CASE WHEN active_flag = 1 AND unregister_flag = 0 THEN 1 END),
new_users = SUM(CASE WHEN active_flag = 1 AND unregister_flag = 1 THEN 1 END),
inactive_users = SUM(CASE WHEN active_flag = 0 THEN 1 END)
FROM dbo.person
GROUP BY company_code
) d ON d.company_code = c.company_code

What do you mean by "Unfortunately test data is not available"? Why can't you make some? You could obfuscate the company name and Bob's your uncle.



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1443817
Posted Thursday, April 18, 2013 8:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:58 AM
Points: 718, Visits: 543

Dhasian, Chris

Thanks for your kind support.


Dhasian,

The query is working fine and much better then before. Good Job. But adding a where clause results in increased scan counts on "person".

Chris,

The query works equally good even with where clause.

OLD
Table 'person'. Scan count 3, logical reads 9593

NEW
Table 'person'. Scan count 1, logical reads 4498

Post #1443895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse