SQL Select effecient?

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

Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Larry Bristersr. DeveloperAuthor Commented:
Icohan...
And I read this.... how?
Screen Print
0
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks guy... appreciated
0
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.

All Courses

From novice to tech pro — start learning today.