Solved

SQL Select effecient?

Posted on 2016-08-31
6
83 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
  • 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 39

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 39

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 12

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now