Some SQL is *Much* Faster Than Other

[Yisroel Markov]

Greetings,

I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow (20-25 minutes to process
less than a thousand records), and even crashes Access sometimes. Yet
another very similar report runs almost instantly.

Both reports take a couple of parameters from a user form and have SQL
statements for data source. The slow one is:

SELECT tblProperty.Man agerID, tblProperty.Pro pertyID, tblProperty.MSA ,
tblProperty.CUS IP, tblRollForward. DateEntered,
tblRollForward. EndingCost, tblRollForward. EndingMkt FROM tblProperty
INNER JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID;

The fast one is:

SELECT DISTINCTROW tblPropFund.Man agerCode, * FROM (tblPropFund INNER
JOIN tblProperty ON tblPropFund.Fun dNumber = tblProperty.MSA ) INNER
JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID, tblRollForward. DateEntered;

Moving the back end to the same PC has no effect. Is there such a
major difference in these SQL statements that the former one is highly
inefficient? Or is there something else going on?

Many thanks,

Yisroel

[Larry Linson]

"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

Greetings,

I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow (20-25 minutes to process
less than a thousand records), and even . . ..
One beneficial change you can make to both is to eliminate the ORDER BY --
the order of the RecordSource is completely ignored. Report sorting is ONLY
via the Sorting and Grouping properties of the Report.
Both reports take a couple of parameters
from a user form and have SQL
statements for data source.
I see no WHERE clause in either of these. If you are using the
WHERECONDITION of a DoCmd.OpenRepor t, be aware that works like a Filter and
may retrieve all the records before choosing the desired ones. I'd suggest
you create a replacement SQL statement including the WHERE clause, and use
it, in the OPEN event of the Report, to replace the RecordSource. Also, make
certain that the Fields used as Joins and Criteria are indexed Fields...
that way the whole index will be brought for selection, but only the
selected records. If any of these is not indexed, all the records will be
brought across the LAN.

This is important, because Access and the Jet database engine are executing
on the user's workstation and the shared folder is used just as it would be
if the data were on a local hard drive. The extraction and manipulation are
all done on the user's machine... Jet is a file-server database, not a
server-database that executes on the server.
SELECT tblProperty.Man agerID, tblProperty.Pro pertyID, tblProperty.MSA ,
tblProperty.CUS IP, tblRollForward. DateEntered,
tblRollForward. EndingCost, tblRollForward. EndingMkt FROM tblProperty
INNER JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID;

The fast one is:

SELECT DISTINCTROW tblPropFund.Man agerCode, * FROM (tblPropFund INNER
JOIN tblProperty ON tblPropFund.Fun dNumber = tblProperty.MSA ) INNER
JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID, tblRollForward. DateEntered;

Moving the back end to the same PC has no effect. Is there such a
major difference in these SQL statements that the former one is highly
inefficient? Or is there something else going on?

Many thanks,

Yisroel

[Yisroel Markov]

"Larry Linson" <bo*****@localh ost.not> wrote in message news:<5i******* ******@nwrddc03 .gnilink.net>.. .

First, thanks for taking the time to answer.

"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

Greetings,

I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow (20-25 minutes to process
less than a thousand records), and even . . ..
One beneficial change you can make to both is to eliminate the ORDER BY --
the order of the RecordSource is completely ignored. Report sorting is ONLY
via the Sorting and Grouping properties of the Report.

If there's a speed gain from this, it's negligible.

> Both reports take a couple of parameters
> from a user form and have SQL
> statements for data source.

I see no WHERE clause in either of these. If you are using the
WHERECONDITION of a DoCmd.OpenRepor t, be aware that works like a Filter and
may retrieve all the records before choosing the desired ones.

Yes, that's how this thing is built. The user selects the month and
year of the report, and the code behind the form builds a string used
as a filter condition in DoCmd.OpenRepor t.
I'd suggest
you create a replacement SQL statement including the WHERE clause, and use
it, in the OPEN event of the Report, to replace the RecordSource.
I suspect the reason it's not already this way is the filter. Here's
the string the code builds:

(DateEntered = #5/31/2004#) AND tblProperty.Pro pertyID NOT IN (SELECT
tblRollForward. PropertyID FROM tblRollForward WHERE
tblRollForward. DateEntered = #6/30/2004#)

I copied the original SQL into a query and put the query in the
report's Record Source. When I added WHERE and tried putting the
filter string after WHERE, I got a syntax error. I tried experimenting
with brackets, to no avail (which of course doesn't mean anything).
Can a query, or SQL, take this kind of nested WHERE? And if it does,
will it speed things up? The whole database is about 12,000 records.
Also, make
certain that the Fields used as Joins and Criteria are indexed Fields...
that way the whole index will be brought for selection, but only the
selected records. If any of these is not indexed, all the records will be
brought across the LAN.
Check - they are indexed.
This is important, because Access and the Jet database engine are executing
on the user's workstation and the shared folder is used just as it would be
if the data were on a local hard drive. The extraction and manipulation are
all done on the user's machine... Jet is a file-server database, not a
server-database that executes on the server.

Thanks again!

SELECT tblProperty.Man agerID, tblProperty.Pro pertyID, tblProperty.MSA ,
tblProperty.CUS IP, tblRollForward. DateEntered,
tblRollForward. EndingCost, tblRollForward. EndingMkt FROM tblProperty
INNER JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID;

The fast one is:

SELECT DISTINCTROW tblPropFund.Man agerCode, * FROM (tblPropFund INNER
JOIN tblProperty ON tblPropFund.Fun dNumber = tblProperty.MSA ) INNER
JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID, tblRollForward. DateEntered;

Moving the back end to the same PC has no effect. Is there such a
major difference in these SQL statements that the former one is highly
inefficient? Or is there something else going on?

Many thanks,

Yisroel

[Yisroel Markov]

Update - I examined the report further, and it turns out that of the 8
controls it has for displaying data, each has as Control Source a
custom function, which pulls data with yet another SQL statement from
yet a third table.

I ran the report, interrupting code execution every 10 seconds to look
where in the code the execution was, and it looks like it's precisely
these functions that cause the slowdown. Nor is it surprising - it
looks like for each of the 180 properties in the database, 8 SQL
queries have to run. Each of these does have the proper WHERE,
selecting Sum() of transactions from another 10,000 record table based
on a date range and a matching property ID, but that doesn't seem to
help speed.

So I guess the question has changed: is there a better way to build
this report? What if I first SELECT all the sums from this transaction
table and then feed them to the report? What's the best way to do
that?

Thanks,

Yisroel

[Larry Linson]

Instead of using the subquery, try using an outer join on tblRollForward
with a criteria of Null on the id of the tblRollForward record (that should
give the same result as the NOT IN subquery, but without running a separate
query of all the records for a given day of tblRollForward for each of your
main report records).

That should also be sufficiently simple that you can use the Query Builder
rather than writing your own SQL from scratch. It is also likely to be
sufficiently simpler that it will be easier to build the SQL statement with
criteria. I see no reason why you cannot do what you were trying...
sometimes concatenating strings and text can be frustrating -- it is easy to
overlook something and end up with a reference that doesn't work rather than
the value you intended to extract from a control, for example... but if you
keep at it, and display the results before running the query (Debug.Print or
MsgBox), you should be able to get it right. In my case, I might have to
tack on "eventually " to the preceding. :-)

Yes, you can have subqueries in Queries and SQL, and the Queries and SQL
with subqueries are legal for RecordSource.

It may be that you simply have too much data retrieval going on for it to be
very fast. Perhaps you can rethink what you need to accomplish, and come up
with an approach that doesn't require several queries run for each record.
If you can clarify in a precise and concise statement of what you have and
what you are trying to accomplish (rather than how you are going about
trying to do it), perhaps someone here can make useful suggestions.

Larry Linson
Microsoft Access MVP

"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

"Larry Linson" <bo*****@localh ost.not> wrote in message news:<5i******* ******@nwrddc03 .gnilink.net>.. .
First, thanks for taking the time to answer.

"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

Greetings,

I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow (20-25 minutes to process
less than a thousand records), and even . . ..

One beneficial change you can make to both is to eliminate the ORDER BY --

the order of the RecordSource is completely ignored. Report sorting is ONLY via the Sorting and Grouping properties of the Report.

If there's a speed gain from this, it's negligible.
> Both reports take a couple of parameters
> from a user form and have SQL
> statements for data source.

I see no WHERE clause in either of these. If you are using the
WHERECONDITION of a DoCmd.OpenRepor t, be aware that works like a Filter and

may retrieve all the records before choosing the desired ones.

Yes, that's how this thing is built. The user selects the month and
year of the report, and the code behind the form builds a string used
as a filter condition in DoCmd.OpenRepor t.

I'd suggest
you create a replacement SQL statement including the WHERE clause, and use it, in the OPEN event of the Report, to replace the RecordSource.

I suspect the reason it's not already this way is the filter. Here's
the string the code builds:

(DateEntered = #5/31/2004#) AND tblProperty.Pro pertyID NOT IN (SELECT
tblRollForward. PropertyID FROM tblRollForward WHERE
tblRollForward. DateEntered = #6/30/2004#)

I copied the original SQL into a query and put the query in the
report's Record Source. When I added WHERE and tried putting the
filter string after WHERE, I got a syntax error. I tried experimenting
with brackets, to no avail (which of course doesn't mean anything).
Can a query, or SQL, take this kind of nested WHERE? And if it does,
will it speed things up? The whole database is about 12,000 records.

Also, make
certain that the Fields used as Joins and Criteria are indexed Fields...
that way the whole index will be brought for selection, but only the
selected records. If any of these is not indexed, all the records will be brought across the LAN.

Check - they are indexed.

This is important, because Access and the Jet database engine are executing on the user's workstation and the shared folder is used just as it would be if the data were on a local hard drive. The extraction and manipulation are all done on the user's machine... Jet is a file-server database, not a
server-database that executes on the server.

Thanks again!
SELECT tblProperty.Man agerID, tblProperty.Pro pertyID, tblProperty.MSA ,
tblProperty.CUS IP, tblRollForward. DateEntered,
tblRollForward. EndingCost, tblRollForward. EndingMkt FROM tblProperty
INNER JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID;

The fast one is:

SELECT DISTINCTROW tblPropFund.Man agerCode, * FROM (tblPropFund INNER
JOIN tblProperty ON tblPropFund.Fun dNumber = tblProperty.MSA ) INNER
JOIN tblRollForward ON tblProperty.Pro pertyID =
tblRollForward. PropertyID ORDER BY tblProperty.Man agerID,
tblProperty.Pro pertyID, tblRollForward. DateEntered;

Moving the back end to the same PC has no effect. Is there such a
major difference in these SQL statements that the former one is highly
inefficient? Or is there something else going on?

Many thanks,

Yisroel