Pass-Through performance issue

[Nano]

Hi,

In an .mdb access database I have stored some queries and reports. The
queries are pass-through queries which connect to an sql server via
ODBC. The reports are mostly forms like invoices, orderconfirmations and
so on.

Thing is, the queries do not store any "WHERE" information to narrow the
results down, but the "WHERE" information is passed from within VB .NET
which opens the Access database and calls a report to print via interop.

I think Access executes the queries, collects ALL results from SQL
server and then narrows down with the passed "WHERE" statement. As the
data in the database grows, some single prints can now take up to 20
second just to load!

Is this standard behaviour of MS Access, when executing queries via
interop? Or is there something which can be done about this?

Thank you very much in advance for any help!

Nano

 

[Baz]

Access will certainly be behaving exactly as you describe. By creating a
pass-through query you are effectively telling Access that the query is in
the SQL syntax of the target server, not in Access SQL syntax, and hence
Access can only submit the query to the server exactly as it stands. Access
cannot modify the query e.g. by adding a WHERE clause to it, because Access
cannot "understand" the syntax.

It would generally be smarter to create Access queries rather than
pass-through queries if you want to use them as record sources for forms and
reports. Access would then mostly be able to generate a query to the
server which includes a WHERE clause when required. You might occasionally
find that an Access query has features that cannot be "translated" to be
executed by the server, once again resulting in poor performance as data are
pulled back to the client for the query to be executed locally, but it's
usually not difficult to tweak such queries to avoid this, or to create SQL
Server views to do the hard bits and query those from Access.

 

[Dale Fye]

Nano,

As Baz indicates, the pass-through will return all the records, which are
then filtered within Access. This can be a very slow process, especially
with large recordsets where you only want to return a few records.

My recommendation would be to create a second pass-through query (copy the
first one) and give it a name that indicates it has a where clause. Then, in
your Access code, you can do something like:

Private Sub cmd_RunQuery_Click

Dim qdf1 as DAO.Querydef, qdf2 as DAO.Querydef

Set qdf1 = currentdb.querydefs("SomeQuery")
Set qdf2 = currentdb.querydefs("SomeQuery_Filtered")

qdf2.SQL = qdf1.SQL & " WHERE [SomeField] = 3"
qdf2.close
Set qdf2 = nothing
qdf1.close
Set qdf1 = nothing

End Sub

Then, use the SomeQuery_Filtered as the data source for your report. This
should run much quicker, since the SQL Server will be doing all of the
processing (selecting records) and will only pass back those records that
meet your criteria.

HTH
Dale

 

[Nano]

Thanks for your reaction guys! It was the confirmation I needed to
proceed with my workaround instead of solving the issue in Access
itself.

My workaround: via interop I first overwrite the SQL of the query
(including the WHERE statement I need, like:

oAccess.CurrentDb.QueryDefs(sQueryName).SQL = sStatement

), then call the report (still with the same WHERE condition, like:

oAccess.DoCmd.OpenReport(ReportName:=sReportName, View:
=Access.AcView.acViewPreview, FilterName:=sFilterName, WhereCondition:
=sWhereCondition)

). I need to store the sql statements locally (in VB) now, but with the
gain on performance (max. 3 instead of 20 seconds), it's extra
maintenance I am willing to invest.

Thanks again!

Nano

 

[Baz]

Yes, that's certainly a reasonable approach to take.