SQL table joins: Join vs. Where -- MIDRANGE-L

Here's another one for ya Joe...

Back in the days when I took my sabbatical from the 400 and was cranking out VB Code and working with DB's like MS SQL or MS ACCESS (Ok, OK.. It's arguable whether these are really Databases. But for the sake of our discussion) I discovered an interesting twist when coding SQL Statements.

If I coded:

Select * From FileA, FileB Where FileA.Fld1 = 'BOB' and FileB.Fld1=FileA.Fld1

It ran significantly slower than coding the equivalent (I think) statement:

Select * From FileA, FileB Where FileA.Fld1 = 'BOB' and FileB.Fld1= 'BOB'

FYI, in neither case would "BOB" be hard-coded. The statements would be dynamically built and "BOB" would be the value of some variable, like so:

strName = "BOB" StrSQL = "Select * From FileA, FileB Where FileA.Fld1 = '" & strName & "' and FileB.Fld1= '" & strName & "'"

At any rate, the difference was so obvious, that I began coding any similar statements using the structure from the second example. Many of my fellow developers accused me of being superstitious but I think a benchmark would have proved me correct.

So maybe you could try this one as well. I'd be interested in knowing if DB2 reacts the same way.

Mike

_________________________________________________________________ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.