Solved

SQL Select effecient?

Posted on 2016-08-31
6
107 Views
Last Modified: 2016-08-31
I have a select that is working but would like to know if it is efficient.
Potentially looking at @ 300k records

There is a base table
There is a table of people that are folliing other people.
I can be both an object and the subject of following or being followed.

SO...
Using id from my profile "ewC"
Get the folks that are following me  "c2"
Get the folks that I am following "c3"

Set to 1 or 0 in my case statement


DECLARE	@CounsumerProfileID INT;
SET @CounsumerProfileID = 35557;
SELECT	ewC.ConsumerProfileID MyID,
		ewC.UserID MyUserID,
		ewC.FirstName ,
		ewC.LastName ,
		c2.FollowerConsumerProfileID FollowingMeID,
		c2.FirstName + ' '  + c2.LastName FollowingMeName ,
		c2.AvatarImage FollowingMeAvatar ,
		--If I am following the person that is following me
		CASE WHEN c3.ConsumerProfileID IS NULL THEN 0
			 ELSE 1
		END I_AM_Following
FROM	ewConsumerProfiles ewC
		--Get people who are following me
		LEFT JOIN (
				   SELECT	cp.ConsumerProfileID AS FollowerConsumerProfileID ,
							cp.FirstName ,
							cp.LastName ,
							cp.AvatarImage ,
							cf.ConsumerProfileID
				   FROM		ewConsumerFollowing cf
							INNER JOIN ewConsumerProfiles cp ON cp.ConsumerProfileID = cf.FollowingConsumerProfileID
				  ) c2 ON ewC.ConsumerProfileID = c2.ConsumerProfileID
		--Get the people I am following
		LEFT JOIN (
				   SELECT	ConsumerProfileID ,
							FollowingConsumerProfileID
				   FROM		ewConsumerFollowing
				   WHERE	FollowingConsumerProfileID = @CounsumerProfileID
				  ) c3 ON c2.FollowerConsumerProfileID = c3.ConsumerProfileID
WHERE	ewC.ConsumerProfileID = @CounsumerProfileID;

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:lrbrister
ID: 41778720
ewConsumerFollowing is a table that has...
A unique ID
A person being followed id
And the person following them id

It is set in a community page with a simple checkbox

So...
A person can follow me...
However it happens... I can follow them as well
There are now two records
the id
And the to personal ids that reference each other
I don't design it myself
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41778722
Best approach in my opinion is for you to check the execution plan under SSMS "Query" menu option - just select "Display Estimated Execution Plan" to see how things are done in SQL and find potential missing index details.
0
 

Author Comment

by:lrbrister
ID: 41778726
Icohan...
And I read this.... how?
Screen Print
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 150 total points
ID: 41778766
As far as I can see is close to perfection as Clustered index seek is fastest then Clustered index scan and you have no missing indexes (they'll show on the diagram if they were), no massive intermediate record sets processed - the lines you see are all narrow and if you hover over them you'll see the actual number of rows. Also if you Hoover over the "icons" you'll see the stats from each step but as it is with data and stats where you ran this it can't be done much faster.

Here are a few links to help you interpret SQL Query plans:
https://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/
https://www.simple-talk.com/sql/performance/execution-plan-basics/
http://www.sqlshack.com/sql-server-query-execution-plans-understanding-reading-plans/
0
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 350 total points
ID: 41778767
I believe this is one of those cases where using a sub-query in the FROM clause will cause performance issues because it is not a correlated sub-query (https://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx).

Hence, you can use one of two options shown below - either use a correlated sub-query in the SELECT (Option 01) or use a self-join (Option 02):

USE tempdb;
GO
DECLARE @ewConsumerProfiles TABLE (ConsumerProfileID INT        NOT NULL,
                                   FirstName         VARCHAR(50)    NULL,
                                   LastName          VARCHAR(50)    NULL
                                  );
             
DECLARE @ewConsumerFollowing TABLE (ConsumerProfileID          INT NOT NULL,
                                    FollowingConsumerProfileID INT NOT NULL
                                   )

INSERT INTO @ewConsumerProfiles (ConsumerProfileID, FirstName, LastName)
VALUES (35555, 'FirstName1', 'LastName1'),
       (35556, 'FirstName2', 'LastName2'),
       (35557, 'FirstName3', 'LastName3'),
       (35558, 'FirstName4', 'LastName4'),
       (35559, 'FirstName5', 'LastName5'),
       (35560, 'FirstName6', 'LastName6');

INSERT INTO @ewConsumerFollowing (ConsumerProfileID, 
                                  FollowingConsumerProfileID
                                 )
VALUES (35555, 35556),
       (35555, 35557),
       (35555, 35558),
       (35556, 35558),
       (35556, 35559),
       (35557, 35555),
       (35557, 35556),
       (35557, 35560),
       (35558, 35555),
       (35558, 35559),
       (35559, 35555),
       (35559, 35557),
       (35560, 35557);

DECLARE	@CounsumerProfileID INT;
SET @CounsumerProfileID = 35557;

--Option 01
SELECT ewC.ConsumerProfileID AS MyID,
       ewC.FirstName,
       ewC.LastName,
       c2.FollowingConsumerProfileID AS FollowingMeID,
       (SELECT fmn.FirstName + ' ' + fmn.LastName
        FROM @ewConsumerProfiles AS fmn
        WHERE fmn.ConsumerProfileID = c2.FollowingConsumerProfileID
       ) AS FollowingMeName,
       CASE WHEN c3.ConsumerProfileID IS NULL THEN 0
             ELSE 1
        END I_AM_Following
FROM @ewConsumerProfiles AS ewC
--Who follows me?
LEFT OUTER JOIN @ewConsumerFollowing AS c2 ON ewC.ConsumerProfileID = c2.ConsumerProfileID
--If I am following them?
LEFT OUTER JOIN @ewConsumerFollowing AS c3 ON ewC.ConsumerProfileID = c3.FollowingConsumerProfileID
                                          AND c3.ConsumerProfileID = c2.FollowingConsumerProfileID
WHERE ewC.ConsumerProfileID = @CounsumerProfileID;

--Option 02
SELECT ewC.ConsumerProfileID AS MyID,
       ewC.FirstName,
       ewC.LastName,
       c2.FollowingConsumerProfileID AS FollowingMeID,
       followingMe.FirstName + ' ' + followingMe.LastName AS FollowingMeName,
       CASE WHEN c3.ConsumerProfileID IS NULL THEN 0
             ELSE 1
        END I_AM_Following
FROM @ewConsumerProfiles AS ewC
--Who follows me?
LEFT OUTER JOIN @ewConsumerFollowing AS c2 ON ewC.ConsumerProfileID = c2.ConsumerProfileID
LEFT OUTER JOIN @ewConsumerProfiles AS followingMe ON c2.FollowingConsumerProfileID = followingMe.ConsumerProfileID
--If I am following them?
LEFT OUTER JOIN @ewConsumerFollowing AS c3 ON ewC.ConsumerProfileID = c3.FollowingConsumerProfileID
                                          AND c3.ConsumerProfileID = c2.FollowingConsumerProfileID
WHERE ewC.ConsumerProfileID = @CounsumerProfileID;

----Original Query
--SELECT	ewC.ConsumerProfileID MyID,
--		--ewC.UserID MyUserID,
--		ewC.FirstName ,
--		ewC.LastName ,
--		c2.FollowerConsumerProfileID FollowingMeID,
--		c2.FirstName + ' '  + c2.LastName FollowingMeName ,
--		--c2.AvatarImage FollowingMeAvatar ,
--		--If I am following the person that is following me
--		CASE WHEN c3.ConsumerProfileID IS NULL THEN 0
--			 ELSE 1
--		END I_AM_Following
--FROM	@ewConsumerProfiles ewC
--		--Get people who are following me
--		LEFT JOIN (
--				   SELECT	cp.ConsumerProfileID AS FollowerConsumerProfileID ,
--							cp.FirstName ,
--							cp.LastName ,
--							--cp.AvatarImage ,
--							cf.ConsumerProfileID
--				   FROM		@ewConsumerFollowing cf
--							INNER JOIN @ewConsumerProfiles cp ON cp.ConsumerProfileID = cf.FollowingConsumerProfileID
--				  ) c2 ON ewC.ConsumerProfileID = c2.ConsumerProfileID
--		--Get the people I am following
--		LEFT JOIN (
--				   SELECT	ConsumerProfileID ,
--							FollowingConsumerProfileID
--				   FROM		@ewConsumerFollowing
--				   WHERE	FollowingConsumerProfileID = @CounsumerProfileID
--				  ) c3 ON c2.FollowerConsumerProfileID = c3.ConsumerProfileID
--WHERE	ewC.ConsumerProfileID = @CounsumerProfileID;

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 41778775
Thanks guy... appreciated
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

736 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question