Using Design View versus SQL Statement View

[Guest]

I need to build a query with expressions with the following logic:

ExpressionA

AND

(ExpressionB OR ExpressionC)

AND

(ExpressionD OR ExpressionE)

I was assuming the SQL Statement View would be the most efficient way to
implement this query because in Design View I would have to use several OR
rows repeating expressions in several rows and it seemed to me that the
resulting SQL Statement would be unnecessarily verbose.

I started building this in SQL Statement View and switched to Designer View
just for curiosity. I noticed in Design View multiple OR rows were created,
repeating ExpressionA in the rows, which I realize is necessary to implement
the logic.

My question is if I use SQL Statement View will the query be more efficient
or is it just as efficient to use Design View?

Thanks,

John

 

[Marshall Barton]

I need to build a query with expressions with the following logic:

ExpressionA

AND

(ExpressionB OR ExpressionC)

AND

(ExpressionD OR ExpressionE)

I was assuming the SQL Statement View would be the most efficient way to
implement this query because in Design View I would have to use several OR
rows repeating expressions in several rows and it seemed to me that the
resulting SQL Statement would be unnecessarily verbose.

I started building this in SQL Statement View and switched to Designer View
just for curiosity. I noticed in Design View multiple OR rows were created,
repeating ExpressionA in the rows, which I realize is necessary to implement
the logic.

My question is if I use SQL Statement View will the query be more efficient
or is it just as efficient to use Design View?

Hard to say. A query goes through an optimazation process
so you never know what's actually evaluated. If you think
you know the best way then you might be able to give the
optimizer a head start by specifying it in SQL view.

 

[Allen Browne]

John, I don't have a definitive answer for you. Perhaps someone else will.

Certainly the SQL statement will be more readable, clearer, and easier to
maintain if you write it in SQL view.

The JET query optimizer does do some amazing things through, and those are
not published. That's why I can't give you a definitive answer. If you want
to know with a particular SQL statement, I think you would need to run some
timing trials. It may well realise that ExpressionA is in each of the 3
ANDs, and evaluate it once only.

Occasionaly that kind of optimization has undesirable side effects. For
example, if ExpressionC involves calling a VBA fuction that is supposed to
preform some operation for every record, and the optimizer realises that
ExpressionB is true so it knows (ExpressionB OR ExpressionC) will be True
and doesn't bother to evaluation ExpressionC, the expected function call may
not be triggered, and so the expected operation may not be performed for
every record. The JET optimizer does indeed have these kinds of issues.

A classic example is if you use Rnd() in a query expression to generate a
random number for each record, the query optimizer sees no argument going to
the function, and so calls it once only (instead of for every record.)

 

[Smartin]

I need to build a query with expressions with the following logic:

ExpressionA

AND

(ExpressionB OR ExpressionC)

AND

(ExpressionD OR ExpressionE)

I was assuming the SQL Statement View would be the most efficient way to
implement this query because in Design View I would have to use several OR
rows repeating expressions in several rows and it seemed to me that the
resulting SQL Statement would be unnecessarily verbose.

I started building this in SQL Statement View and switched to Designer View
just for curiosity. I noticed in Design View multiple OR rows were created,
repeating ExpressionA in the rows, which I realize is necessary to implement
the logic.

My question is if I use SQL Statement View will the query be more efficient
or is it just as efficient to use Design View?

Thanks,

John

If you mean, will the query run faster? It matters not. The JET engine
will process the same code either way.

From a design or readability standpoint your mileage will vary.
Whichever method is easiest for you is the most efficient.

 

[Guest]

Thank you all for your replies. Very good information.

Readability and maintainability are important to me, and when I mentioned
“efficiency†in the back of my mind, I also meant readability and
maintainability, though from the answers above I realize an easily readable
query does not necessarily mean efficiency.

I thought, as was mentioned, the SQL View would produce the most readable
and maintainable query. I was starting from a simpler query that I had
previously created in Design View and switched to SQL View to add additional
expressions, increasing the complexity. I added a number of returns to make
the statement much more readable, sometimes having parenthesis on separate
lines so I could be sure that when I added the new expressions I would not
get an unwanted query resulting from placing expressions within unintended
parentheses.

After saving the query and later reopening the query and returning to SQL
View, the layout I had carefully made was lost and everything ran together
again, making this SQL View, in my opinion, very difficult to maintain, worse
than multiple rows in the Design View. I realize I could save the layout in
a text editor, but then there are more files to keep track of.

If there were some way to preserve the layout in SQL View, then it really
would be most maintainable; otherwise, I think it is more trouble.

Thanks again, all.

John

 

[Marshall Barton]

Thank you all for your replies. Very good information.

Readability and maintainability are important to me, and when I mentioned
“efficiency” in the back of my mind, I also meant readability and
maintainability, though from the answers above I realize an easily readable
query does not necessarily mean efficiency.

I thought, as was mentioned, the SQL View would produce the most readable
and maintainable query. I was starting from a simpler query that I had
previously created in Design View and switched to SQL View to add additional
expressions, increasing the complexity. I added a number of returns to make
the statement much more readable, sometimes having parenthesis on separate
lines so I could be sure that when I added the new expressions I would not
get an unwanted query resulting from placing expressions within unintended
parentheses.

After saving the query and later reopening the query and returning to SQL
View, the layout I had carefully made was lost and everything ran together
again, making this SQL View, in my opinion, very difficult to maintain, worse
than multiple rows in the Design View. I realize I could save the layout in
a text editor, but then there are more files to keep track of.

If there were some way to preserve the layout in SQL View, then it really
would be most maintainable; otherwise, I think it is more trouble.

A very common complaint that has been screaming for
improvements for over a decade.

If you feel ambitious, you could try to cobble something
together (maybe using a table instead of .TXT files), or you
could try Googling for third party add-ins.

 

[Guest]

Do most Access professional developers maintain SQL source in external files?

 

[David W. Fenton]

Do most Access professional developers maintain SQL source in
external files?

WHy would anyone do that?

Mostly, I don't give a rat's ass how the SQL is formatted, because I
let the QBE write the SQL.

The only exception to that is on-the-fly SQL I construct in VBA
routines. In that case, the query designer is not involved, so the
format I specify remains intact.

 

[Marshall Barton]

Do most Access professional developers maintain SQL source in external files?

Not that I know of, even though most people feel the pain
when working in SQL view. Note that SQL view is not needed
in most situations.

 

[Guest]

Not that I know of, even though most people feel the pain
when working in SQL view. Note that SQL view is not needed
in most situations.

Understand, and in my example in the original message, the Design View would
not be too cumbersome with 4 OR rows. It just seems to me that certain
relationships are best viewed in a single logical expression that the SQL
View represents. If I were modifying the query and used Design View
exclusively, I would have to recreate the logical expression, determine the
modifications, and then translate back to the Design View to implement.

David - I don't use QBE and will take a look at that, but from my background
aside from Access, I am more comfortable with SQL so that view, in a decent
format, is best for me at present.

 

[Allen Browne]

John, I understand (and share) some of your frustation with what Access does
with the SQL View - particularly the lost of line endings and the numerous
extra brackets. But, like other Access developers, I just live with those
frustrations without expending much effort on them.

There's a couple of workarounds:
a) If you save the query in SQL View, it should at least keep the text
correct (if not the line endings.)

b) Sometimes I use a table with a memo field to store SQL statements. I find
that better than text files, as it's always with the database and is easy to
cut'n'paste into SQL View.

c) Very often I find myself generating SQL statements dynamically as VBA
strings. That means I can offer the user lots of filter options, but reduce
the criteria to the boxes they actually used, which enhances both the
performance and readability of the SQL statements.

To get the SQL statement into VBA code, I use a form with:
- a big text box named txtSQL where I paste the SQL statment;
- a big text box named txtVBA where it puts the VBA string.
These text boxes are on pages of a tab control.
Then there's a button that does this:

Private Sub cmdSql2Vba_Click()
Dim strSql As String
Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"
If IsNull(Me.txtSQL) Then
Beep
Else
strSql = Me.txtSQL
strSql = Replace(strSql, """", """""") 'Double up any quotes.
strSql = Replace(strSql, vbCrLf, strcLineEnd)
strSql = "strSql = """ & strSql & """"
Me.txtVBA = strSql
Me.txtVBA.SetFocus
RunCommand acCmdCopy
End If
End Sub

From there, you paste into your code, and tweak.

HTH.

 

[Guest]

Allen,

That is a neat technique. Thanks for sharing.

Not really frustrated. No development environment is perfect. Just like to
hear the workarounds from the experts so I can decide on the best course for
me.

Side comment - this forum is really excellent thanks to the effort of you
and the other MVPs and other very experienced users. I have a number of
Access books, but this forum is the best resource. I realize the time you
and others give is not something to take for granted. If there is some way
to provide feedback to Microsoft and ensure they are aware how you experts
enhance the product, I would like to know.

All the best,

John