Eliminate CROSS JOIN

Question

Eliminate CROSS JOIN

TriggerMe

SSChampion

Points: 11752
More actions
January 1, 2007 at 11:58 pm

#153362

Is there a way to not use the CROSS JOIN below? The CROSS JOIN is proving to be an expensive JOIN.
DECLARE @Comp VARCHAR(8000)
DECLARE @cComment NVARCHAR(4000)
SET @Comp = '1,2,3,4,5,6,7,8,9,10'
SET @Comp1 = CONVERT(nvarchar(4000), @Comp)
SELECT DISTINCT
FROM PRS_Txt p CROSS JOIN PRS_TSWs t
WHERE t.PerId IN (SELECT Value
FROM dbo.fnSplitString(@Comp1,','))
AND PRSStatusId = 9
AND p.Rqrd = t.TypeId
AND p.Rqrd <> 0
AND dbo.fnGetPerDate(t.PerId) BETWEEN p.StartDate AND p.EndDate
AND dbo.fnGetPerDate(t.PerId) < GETDATE()
Kindest Regards,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I don't know what either table has in it nor what either is used for and you left out the SELECT list so it's really hard to tell, but first blush says just change the CROSS JOIN to an INNER JOIN and see what you get. I'd also be tempted to remove the DISTINCT.

I'd also take a look at that split string usage... WHERE IN works but is usually not as good as a good ol' fashioned join. There's absolutely no reason I can see where the function couldn't be used directly in the FROM as a "table" in the main query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Hi Jeff,

There is no correlation between the 2 tables hence the CROSS JOIN. In other words, there is no column to join on.

Kindest Regards,

Sergiy SSC Guru Points: 110208 More actions · Answer 3

What?

Then what is that:

AND p.Rqrd = t.TypeId ?

It's clear join.

And this kind of WHERE clause eliminates any chance of NULL values coming from either table.

So, you can replace CROSS JOIN with INNER JOIN ON p.Rqrd = t.TypeId.

_____________
Code for TallyGenerator

TriggerMe SSChampion Points: 11752 More actions · Answer 4

Let me go back to the Users and clearly find out what on earth is really required here!

This is something I have inherited not written myself. Stay tuned!

Kindest Regards,