Solved

SQL Select effecient?

Posted on 2016-08-31
6
116 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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