PDA

View Full Version : Best Practice Advice



Shred Dude
03-15-2011, 02:11 PM
When writing SQL statements in VBA procedures that will use them to open recordsets via ADO, any advice on syntax.

For example, If I create a query in Access and copy the Resulting SQL to my VBA procedure it may look something like this:

sSQL = "SELECT A.[xxx], B.[yyy], A.[zzz] " & _
"FROM tblAlpha A INNER JOIN tblBeta B ON A.[ID] = B.[ID];"

Any advantage over using the Join statement as compared to something like this:

sSQL = "SELECT A.[xxx], B.[yyy], A.[zzz] " & _
"FROM tblAlpha A, tblBeta B " & _
"WHERE A.[ID] = B.[ID];"


I'm looking at building a routine to construct the needed SQL based on some User selections/actions and was hoping to nail down a syntax that would be easy to logically construct based on the state of the user interface when requested.

Bob Phillips
03-15-2011, 04:24 PM
Why not just run the two queries and look at the query plan?

Shred Dude
03-16-2011, 10:43 PM
Not sure what you mean by "Query Plan".

I can build a query in Access with the Query Designer and it will build the INNER JOIN sql. I can then go change the SQL to resemble the latter example above with the WHERE clause. Both return identical results.

I guess my question was one of efficiency. On larger databases is there an impact on performance with one versus the other? I haven't created a large DB to test it on. Maybe I'll go do that.

Bob Phillips
03-20-2011, 06:14 AM
Sorry, I was assuming you were using SQL server not Access.

I knocked up a similar query in SQL Server, and checked the execution plans, and they were identical. I admit, I expected the JOIN to be more efficient, but in my data, no difference.