Solved

SQL Select effecient?

Posted on 2016-08-31
6
101 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

685 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