SQL Select Query optimization for Inline select statements

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

I'm defining my situation here with creating some example:

SQL

DECLARE @TB1 TABLE (PK_ID1 INT, NAME NVARCHAR(50))
INSERT INTO @TB1
    VALUES (1, 'ABC')
            ,(2, 'DEF')
            ,(3, 'GHI')
            ,(4, 'JKL')
            ,(5, 'MNO')


SELECT * FROM @TB1

DECLARE @TB2 TABLE (PK_ID2 INT, PROJECT NVARCHAR(10), MANAGER_ID INT, LEADER_ID INT, USER1_ID INT, USER2_ID INT)
INSERT INTO @TB2
    VALUES (1, 'Proj-1', 1, 4, NULL, NULL)
            ,(2, 'Proj-2', 3, NULL, NULL, NULL)
            ,(3, 'Proj-3', 3, NULL, 4, 1)
            ,(4, 'Proj-4', 2, 1, 3, 5)

SELECT * FROM @TB2

SELECT PK_ID2
        , PROJECT
        , MANAGER_ID
        , (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = MANAGER_ID) MANAGER_NAME
        , LEADER_ID
        , (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = LEADER_ID) LEADER_NAME
        , USER1_ID
        , (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = USER1_ID) USER1_NAME
        , USER2_ID
        , (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = USER2_ID) USER2_NAME
    FROM @TB2

Is there any way not to use "select top 1" for every record since this will create a lot of load on DB as my database will have thousands of records in each table.

Joining is an option but my where clause already have 12 different left joins.

Unfortunately the table structure can't be changed.

Kindly suggest.

VB

Thanks & Regards,
Abhishek Kumar

Posted 8-Oct-14 3:10am

Abhishek Kumar (here to help!!)

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Answer 1 · 2014-10-08T03:33:00

Solution 1

You don't need Top 1 if each sub-select can only return 1 row. Since you have named the field PK_ID1, I assume that it serves as a primary key?

I assume that you will be querying real tables rather than the temporary ones in the example. If this is not correct (i.e., you are querying temporary tables for this exercise) and the table is large adding a primary key definition to your temporary table will improve select performance like so:

DECLARE @TB1 TABLE (PK_ID1 INT PRIMARY KEY, NAME NVARCHAR(50))

Posted 8-Oct-14 3:33am

PhilLenoir

Updated 8-Oct-14 3:35am

v2

Comments

Richard Deeming 8-Oct-14 9:40am

For large tables, a "real" temporary table (#TB1) will probably give better performance than a table variable (@TB1). The query optimizer tends to assume that table variables only contain a single row, whereas temp tables get real statistics.

PhilLenoir 8-Oct-14 9:45am

Good point Richard!

Maciej Los 8-Oct-14 11:38am

Phil, at this moment PK_ID1 is used to join several fields (manager, leader, user1 and user2). It's bad idea! Even if your proposal of TB1 structure is good, the structure of TB2 is bad. Please, see my answer. I explained there how to change it.

PhilLenoir 8-Oct-14 11:52am

Maciej, the poster says that he cannot change the table structure, so I did not attempt a critique. In my view a correct model would look more like an user table, a role lookup table and a link table that models relationships between users (user ID, related ID and role ID). Such a structure would allow for a pivot query and possibly "path" type queries using CTEs. Given the constraint that the model can't be changed and the lack of detail on the current model, I believe the solution works! :)

Maciej Los 8-Oct-14 12:06pm

I missed that part: "(...) the table structure can't be changed.". I understand your point of view, but i'm affraid there is no hope for optimization. The structure of database (table 2) must be changed. I haven't tested CTE, but i'll try.

PhilLenoir 8-Oct-14 12:17pm

Removing "Top 1" will avoid SS from creating a temporary index, so it should run faster (although I'm not sure how much!) If you can see a way to make this slicker with a CTE, all power to you (I'm not seeing it! :))

My point about the CTE was simply that with a good model and a recursive join, you could find a full reporting line (for instance). Good modelling supports functionality not even imagined at design time, which is why I distrust function driven design for a database (except for scoping).

In my shop, I want a REALLY good reason for not conforming to 3rd Normal, and those are rare!

Maciej Los 8-Oct-14 12:29pm

I haven't said that i see a way to make this with CTE. I've said: i'll try. It makes the difference. ;)

The case like this i call: "hopeless". Even superhuman effort can't help when the database design is wrong, bad, etc.

PhilLenoir 8-Oct-14 12:32pm

I know (on both counts), I didn't want to dissuade you if you could see something I couldn't!

Maciej Los 8-Oct-14 12:48pm

I don't give up so fast ;)

PhilLenoir 8-Oct-14 12:49pm

LOL

Maciej Los · Answer 2 · 2014-10-08T05:33:00

Let's start from remarks:
1) bad table design for @TB2!
2) bad SELECT statement!

Have a look at example:

SQL

DECLARE @TB1 TABLE (PK_ID1 INT IDENTITY(1,1), NAME NVARCHAR(50))
INSERT INTO @TB1 (NAME)
VALUES ('ABC'),('DEF'), ('GHI'),('JKL'), ('MNO')

DECLARE @TB2 TABLE (PK_ID2 INT IDENTITY(1,1),  PROJECT NVARCHAR(10), RoleName VARCHAR(30), USRID INT)
INSERT INTO @TB2 (PROJECT , RoleName , USRID )
VALUES('proj-1', 'Manager', 1),
('proj-1', 'Leader', 4),
('proj-2', 'Manager', 3),
('proj-3', 'Manager', 3),
('proj-3', 'User', 4),
('proj-3', 'User', 1),
('proj-4', 'Manager', 2),
('proj-4', 'Leader', 1),
('proj-4', 'User', 1),
('proj-4', 'User', 1)

SELECT t2.PK_ID2, t2.PROJECT, t2.USRID, t1.NAME, t2.RoleName
FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1

Result:

1	proj-1	1	ABC	Manager
2	proj-1	4	JKL	Leader
3	proj-2	3	GHI	Manager
4	proj-3	3	GHI	Manager
5	proj-3	4	JKL	User
6	proj-3	1	ABC	User
7	proj-4	2	DEF	Manager
8	proj-4	1	ABC	Leader
9	proj-4	1	ABC	User
10	proj-4	1	ABC	User

If you want to display all roles, you need to define pivot table:

SQL

SELECT PROJECT, [Manager], [Leader], [User]
FROM (
    SELECT t2.PROJECT, t1.NAME, t2.RoleName
    FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1
) AS DT
PIVOT(MAX(Name) FOR RoleName IN([Manager], [Leader], [User])) AS PT

Result:

proj-1	ABC	JKL		NULL
proj-2	GHI	NULL	NULL
proj-3	GHI	NULL	JKL
proj-4	DEF	ABC		ABC

Do you see the difference?

Finally, i strongly recommend to read about relational database[^], joins[^], etc., like: Visual Representation of SQL Joins[^]

Remeber: query optimization is not possible without database optimization(structure, table definition, etc.).

[EDIT]

Please, read the comments to the Solution1.

I tried to create query using Common Table Expressions[^], but... (read note under below code):

SQL

;WITH Managers AS
(
	SELECT t2.PK_ID2, t2.PROJECT, t2.MANAGER_ID, T1.NAME AS MANAGER_NAME, t2.LEADER_ID, t2.USER1_ID, t2.USER2_ID
	FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.MANAGER_ID  = t1.PK_ID1 
),
	Leaders AS
	(
		SELECT t2.*, t1.NAME AS LEADER_NAME
		FROM Managers AS t2 LEFT JOIN @TB1 AS t1 ON t2.LEADER_ID = t1.PK_ID1 
	),
		UsersOne AS
		(
			SELECT t2.*, t1.NAME AS USER1_NAME
			FROM Leaders AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER1_ID  = t1.PK_ID1 
		),
			UsersTwo AS
			(
				SELECT t2.*, t1.NAME AS USER2_NAME
				FROM UsersOne AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER2_ID  = t1.PK_ID1
			)
SELECT PK_ID2, PROJECT, MANAGER_ID, MANAGER_NAME, LEADER_ID, LEADER_NAME, USER1_ID, USER1_NAME, USER2_ID, USER2_NAME 
FROM UsersTwo

Note: I'm affraid about performance. I can't guarantee that this solution is faster then multi SELECT statement.

For further information about CTE, please see:
Using Common Table Expressions[^]
Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]

[/EDIT]