Totally confused by query behavior and plan

Question

Totally confused by query behavior and plan

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Bob Hovious (2/6/2009)
Give this a try at least. It runs faster with your test data.
I use glue tables all the time too... but always in this format:
select blah
from tableA a
join glueAB glue on glue.acolumn = a.column
join tableB b on b.column = glue.bcolumn
Here's the revision.
----------------------------------------------------------------------------------------------
SELECT C.Citizenship_ID
FROM (select person_ID,iso_code
From data_xfer.citizen_xfer AS CX
JOIN data_xfer.ntm_iso_country_xref AS NICX ON CX.ctrycd = NICX.ntm_code
JOIN (SELECT ISNULL(data_xfer.person_xfer.person_no, dbo.Persons.ntmaa_person_no) AS personno, dbo.Persons.Person_ID
FROM data_xfer.person_xfer
RIGHT OUTER JOIN dbo.Persons ON data_xfer.person_xfer.primary_person_no = dbo.Persons.ntmaa_person_no
) AS P ON CX.personno = P.personno
) as newDT
JOIN dbo.Citizenships AS C ON newDT.Person_Id = C.Person_Id AND
newDT.iso_code = C.Country_Code

Tried it this morning and got the same odd query plan and performance.

Thanks for the effort.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I appreciate your giving it a shot and giving me a reply, Jack.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

Garadin (2/8/2009)

Is it good practice then to add all criteria to the derived tables instead of the outer query? Guess that makes more sense overall anyways.

I hate to use the ol' cliche`, but "It depends"...

If the inner queries can do their thing correctly when you add row limiting criteria to them, then I'll do that in the inner queries for two reasons...

1. Makes troubleshooting easier

2. Main goal of any code to use/touch/return only the rows and columns needed.

Sounds obvious to a lot of folks, but many miss doing that.

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Jeff Moden (2/9/2009)

Garadin (2/8/2009)
Is it good practice then to add all criteria to the derived tables instead of the outer query? Guess that makes more sense overall anyways.
I hate to use the ol' cliche`, but "It depends"...
If the inner queries can do their thing correctly when you add row limiting criteria to them, then I'll do that in the inner queries for two reasons...
1. Makes troubleshooting easier
2. Main goal of any code to use/touch/return only the rows and columns needed.
Sounds obvious to a lot of folks, but many miss doing that.

Yep. I think I used to always do this... and somewhere in the past couple months I'd started putting all the criteria into the outer query. I think this was a case where the easier something is to do, the less you think about it, and the less you think about it... BOOM.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

I used to get burned on this in SQL 2000 and ever since I've always used the same WHERE clause in the glue table. Since then I've never had a problem.

I've always thought of the glue table more as a 'stub' table, but I guess 'glue' might be a better description.

Todd Fifield