Performance optimization by excluding JOIN with tables only with one record

Question

Performance optimization by excluding JOIN with tables only with one record

lapus014

Old Hand

Points: 340
More actions
February 1, 2024 at 11:59 am

#4371343

We are adding condition that check of the table variable contains one or less records and at this case switching JOIN to adding conditon to WHERE clause, for example:
IF EXISTS(SELECT 1 FROM @TableTypePlayersTypes)
BEGIN
SELECT @RowCount = COUNT(*)
FROM @TableTypePlayersTypes
IF @RowCount <=1
BEGIN
UPDATE PlayersTypes
SET LastModifiedDateTime = @dt
WHERE PlayersRestrictionID = (SELECT PlayersRestrictionID FROM @TableTypePlayersTypes)
END
ELSE
BEGIN
UPDATE pr
SET pr.LastModifiedDateTime = @dt
INNER JOIN @TableTypePlayersRestrictions AS prt ON prt.PlayersRestrictionID = pr.PlayersRestrictionID
END
Is it possible to do something similar in case of left/right join?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246976 More actions · Answer 1

Are you sure that this will improve performance? It's going to introduce a lot of code 'waffle', which looks inelegant to me.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

lapus014 Old Hand Points: 340 More actions · Answer 2

We saw that it improves performance.

Why not? Making join usually takes more resources and time then simple select.

Phil Parkin SSC Guru Points: 246976 More actions · Answer 3

lapus014 wrote:

We saw that it improves performance.
Why not? Making join usually takes more resources and time then simple select.

First you check whether the table has data. Then you obtain the rowcount. Then you do a SELECT from the table to include in the WHERE clause.

Or you simply do a JOIN.

That's why not. Three reads vs one. Have you got a clustered index on PlayersRestrictionID?

Also, this line of code doesn't make sense:

IF @RowCount <=1

This statement is executed only if the table contains one or more rows ... why check whether it is < 1?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

lapus014 Old Hand Points: 340 More actions · Answer 4

We have an index on PlayersRestrictionID, but not clustered

ScottPletcher SSC Guru Points: 101117 More actions · Answer 5

Your code is not really consistent, but here's my best guess:

UPDATE pt 
SET LastModifiedDateTime = @dt
FROM dbo.PlayersTypes AS pt
INNER JOIN @TableTypePlayersRestrictions AS prt ON prt.PlayersRestrictionID = pt.PlayersRestrictionID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246976 More actions · Answer 6

This semi-join version of the UPDATE might perform better

UPDATE pt
SET LastModifiedDateTime = @dt
FROM PlayersTypes pt
WHERE EXISTS
(
    SELECT 1
    FROM @TableTypePlayersTypes ttpt
    WHERE ttpt.PlayersRestrictionID = pt.PlayersRestrictionID
);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101117 More actions · Answer 7

Perhaps, but I would really expect SQL to generate effectively the same query plan for either code for something this straight forward.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lapus014 Old Hand Points: 340 More actions · Answer 8

lapus014

Old Hand

Points: 340

February 1, 2024 at 5:13 pm

#4371519

Thank you all !

lapus014 Old Hand Points: 340 More actions · Answer 9

But if we have LEFTor RIGHT JOIN as below,is it possible to improve something at this case?

SELECT pr.column1,

pr.column2

FROM PlayersTypesAS pr WITH(NOLOCK)

INNER JOIN dbo.Players AS p WITH(NOLOCK) ON p.PlayerTypeID = pr.PlayerTypeID

LEFT JOIN @PlayersRestrictionsTable AS t ON t.PlayerTypeID = pr.PlayerTypeID

ScottPletcher SSC Guru Points: 101117 More actions · Answer 10

lapus014 wrote:

But if we have LEFTor RIGHT JOIN as below,is it possible to improve something at this case?
SELECT pr.column1, pr.column2 FROM PlayersTypesAS pr WITH(NOLOCK) INNER JOIN dbo.Players AS p WITH(NOLOCK) ON p.PlayerTypeID = pr.PlayerTypeID LEFT JOIN @PlayersRestrictionsTable AS t ON t.PlayerTypeID = pr.PlayerTypeID

SQL will ignore @PlayersRestrictionsTable in that query since it's not being referenced in the query itself.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".