SQL Statement

Question

SQL Statement

Malky

SSC Rookie

Points: 30
More actions
December 15, 2004 at 4:34 pm

#61499

Is there better way write following statement?
Thanks

SELECT TOP 50 tblAccount.AccountID,tblAccountInfo.AccountNumber, tblAccountType.AccountTypeDesc, tblBorrower.FullName,
tblBorrowerPhone.PH1Phone,tblBorrowerPhone.PH2Phone,tblBorrowerAddress.ADD1Address1,tblBorrowerAddress.ADD1Address2,
tblBorrowerAddress.ADD1City,tblBorrowerAddress.ADD1State,tblBorrowerAddress.ADD1Postal, tblBorrower.SSN, tblAccountInfo.LoanNumber, tblBorrower.FirstName, tblBorrower.LastName
FROM tblAccountType INNER JOIN tblAccount ON tblAccountType.AccountTypeID = tblAccount.AccountTypeID
INNER JOIN tblBorrowerAddress ON tblAccount.AccountID = tblBorrowerAddress.AccountID
INNER JOIN tblBorrower ON tblAccount.AccountID = tblBorrower.AccountID
INNER JOIN tblBorrowerPhone ON tblAccount.AccountID = tblBorrowerPhone.AccountID
INNER JOIN tblAccountInfo ON tblAccount.AccountID = tblAccountInfo.AccountID
WHERE tblAccountInfo.AccountNumber = '5617703210'
ORDER BY tblAccountInfo.AccountNumber

Viewing 15 posts - 1 through 15 (of 22 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Why do you think there is anything wrong with the statement?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 75908 More actions · Answer 2

Basically all your tables relate on the accountId field.

So tblBorrowerAddress.AccountID = tblAccount.AccountID and tblAccount.AccountID = tblBorrower.AccountID

A human being will infer that tblBorrowerAddress can be linked to tblBorrower but a computer cannot determine that.

You may have hit lucky and specified the optimum join path for your query, but if you are explicit in your joining then the query optimiser can make those decisions for itself.

I believe that there is an ultimate limit on how many combinations of joins the query optimiser will evaluate but I'm not sure what it is.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

IIRC, this number is 4 or 5. Beyond this the optimiser will choose tablescans, because of the sheer amount of possible permutations.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Seems to fit into this thread so, can anyone tell me why the INNER JOIN is preferred to creating the relationships in the WHERE clause e.g.

SELECT TOP 50 ACC.AccountID,AIN.AccountNumber, ATP.AccountTypeDesc, BOR.FullName,

BPH.PH1Phone,BPH.PH2Phone,BAD.ADD1Address1,BAD.ADD1Address2,

BAD.ADD1City,BAD.ADD1State,BAD.ADD1Postal, BOR.SSN, AIN.LoanNumber, BOR.FirstName, BOR.LastName

FROM tblAccount ACC, tblAccountType ATP, tblBorrower BOR,

tblBorrowerAddress BAD, tblBorrowerPhone BPH, tblAccountInfo AIN

WHERE ATP.AccountTypeID = ACC.AccountTypeID

AND ACC.AccountID = BAD.AccountID

AND ACC.AccountID = BOR.AccountID

AND ACC.AccountID = BPH.AccountID

AND ACC.AccountID = AIN.AccountID

AND AIN.AccountNumber = '5617703210'

ORDER BY AIN.AccountNumber

Steve

We need men who can dream of things that never were.

David.Poole SSC Guru Points: 75908 More actions · Answer 5

I have never seen a performance difference but from my point of view the separation of join conditions and selection conditions aids clarity to the code.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

The result is the same. Actually you should find, when you compare execution plans, that such statement are execute identical. The optimizer should restate it to using JOINs. However using JOINs is the preferred ANSI way of doing things, though I know that doing this in the WHERE clause is still around in SQL 2003 standard. Therefore, I guess way to go till it will deprecate.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 7

Sorry to drop this in on you gents......

I only asked because I was asked to re-write a search query that had performance issues and when I changed it from the JOIN syntax to utilising the WHERE clause - there was a massive performance gain...

I know JOIN is the recommended way forward but nobody has ever really given me any proper reasons for this. Just wondered if anybody knew of any..........

Best regards

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Hm, maybe Joe Celko knows...

And if he answers, I would like to know why ANSI invented NULL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 9

It could be something sutle in the way the query engine restated the query in JOIN terms.

Can you post the size of each table for me as I have altered the order of the joins before and seen drastic changes in performance when large tables are involved.

For example (as long as I didn't screw up my operation order here as I had no real way to test) this could be potentially faster than your original join statement ran.

SELECT TOP 50

tblAccount.AccountID,

tblAccountInfo.AccountNumber,

tblAccountType.AccountTypeDesc,

tblBorrower.FullName,

tblBorrowerPhone.PH1Phone,

tblBorrowerPhone.PH2Phone,

tblBorrowerAddress.ADD1Address1,

tblBorrowerAddress.ADD1Address2,

tblBorrowerAddress.ADD1City,

tblBorrowerAddress.ADD1State,

tblBorrowerAddress.ADD1Postal,

tblBorrower.SSN,

tblAccountInfo.LoanNumber,

tblBorrower.FirstName,

tblBorrower.LastName

FROM

dbo.tblAccountInfo tblAccountInfo

INNER JOIN

dbo. tblAccount tblAccount

INNER JOIN

dbo.tblBorrowerAddress tblBorrowerAddress

ON

tblAccount.AccountID = tblBorrowerAddress.AccountID

INNER JOIN

dbo.tblBorrower tblBorrower

ON

tblAccount.AccountID = tblBorrower.AccountID

INNER JOIN

dbo.tblBorrowerPhone tblBorrowerPhone

ON

tblAccount.AccountID = tblBorrowerPhone.AccountID

ON

tblAccountType.AccountTypeID = tblAccount.AccountTypeID AND

tblAccountInfo.AccountNumber = '5617703210'

INNER JOIN

dbo.tblAccountType tblAccountType

ON

tblAccount.AccountID = tblAccountInfo.AccountID

ORDER BY

tblAccountInfo.AccountNumber

The problem is how do you state it for optimal performance and even then sometimes there is a SET option that can alter even that further.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 10

Hi All,

I would like to see a comparison with the original query, the optimised query and the query without the joins - if it isn't too much trouble.

Is there some relation between Joe Celko and JC All seeing, all knowing? (No offence intended before we start )

Would also like to see an answer from Yoda on this, those replies always make me chuckle

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

I don't understand your reference to JC?

But I agree Yoda's answer should also be interesting.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 12

Sorry Frank,

The one out of the Bible........

Best regards

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

...oh, I see. Well, no wonder, I didn't understand. This acronym isn't usual here in Germany

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 75908 More actions · Answer 14

Wasn't there a Monty Python sketch on this very subject refering to John Cleese as the comic mesiah?

For the younger programmers, Monty Python was a comedy team before the invention of political correctness and predates the time when swearing itself was considered funny.

LinkedIn Profile