Slow VBA-Generated SQL Involving Totals Query

[markmarko]

Hello!

I have a form frmOrderHandler that contains 2 subforms. One for Sales Orders
and the other for Installation Orders (Often, an install order is associated
to a Sales Order via a field [Record-Orders-Sales].AssociatedSalesOrder. If
the Install Order was not generated from a sale, then this field is null. The
question ignores that possibility and only concerns with the case of those
Install Orders which are pointing to a Sales Order).

The user may filter by DateOfSale or DateScheduled (for installation), using
a text control called Filter_Date in conjunction with an option button with
"Date Of Sale" and "Date Scheduled" options. Filtering by data of sale, which
is a field in the Sales Orders table, is easy and the sql runs in about 2
seconds. However, filtering on DateScheduled takes 3-5 minutes. We don't have
a lot of records yet… Approx 6000 Install Orders and 5000 Sale Orders.
Nevertheless, I've indexed all relevant fields, which seems to only have
trimmed about 10 seconds.

Here's the lowdown on how it's setup... Since a job may need to be
rescheduled, we've put that data in a separate table tblSalesScheduling,
which points to [Record-Orders-Sales].ID (which is the PK for the Sales
Orders table). This way, we can have a history of scheduled dates for a given
job.

Then, we use a totals query [FindRecent-SalesScheduling] to find the
MaxOfID, which will be the ID with the current schedule date. Then a second
query [Recent-SalesScheduling], which uses [FindRecent-SalesScheduling] and
adds other relevant fields, particularly ScheduledDate .
(example: Job was originally scheduled for 09-01-08, then rescheduled for
09-15-08, [FindRecent-SalesScheduling] finds the highest nubmered ID for the
given SalesOrder, then [Recent-SalesScheduling] adds useful data, so we then
have 09-15-08 as the most recently entered ScheduledDate.)

Now to go a step further, to filter the Install Orders subform, I need it to
collect those Install Orders which are pointing to a Sales Order who's most
recent Schedule date is equal to Filter_Date.

Originally, being somewhat rushed, the sql generated by the VBA code looked
like this (the code has already placed #9-06-08# for Filter-Date).
Incidentally, the generated sql references the saved queries mentioned above.

EXISTS
(
SELECT [Record-Orders-Sales].[SalesOrderID]
FROM [Record-Orders-Sales]
WHERE [Record-Orders-Sales].[SalesOrderID] =
[Record-Orders-Install].[AssociatedSalesOrder]
AND EXISTS
(SELECT [Recent-SalesScheduling].ID
FROM [Recent-SalesScheduling]
WHERE [Recent-SalesScheduling].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID]
AND [Recent-SalesScheduling].[ScheduledDate] = #9/6/2008#
)
)

This took ~3-4 minutes to run. I thought it was because I had nested EXISTS
statements, and I wondered if the slowdown was due to having to 'rerun' the
nested EXISTS for every iteration of the higer level EXISTS (not even
positive this is how the engine performs these tasks), so I rewrote it to
this:

EXISTS
(
SELECT [FindRecent-SalesScheduling].AssociatedSalesOrder
FROM [FindRecent-SalesScheduling] INNER JOIN [Record-SalesScheduling]
ON [FindRecent-SalesScheduling].MaxOfID = [Record-SalesScheduling].ID
WHERE [Record-SalesScheduling].ScheduledDate = #9/6/2008#
AND [FindRecent_SalesScheduling].AssociatedSalesOrder =
[Record-Orders-Install].[AssociatedSalesOrder]
)

I was surprised that this was even slower, at about 5-6 minutes.

If anyone understands this scenerio, and has any useful advice, please share
it! I'm pretty stuck myself.

 

[markmarko]

Anyone? Bueller?... Bueller?

 

[Douglas J. Steele]

As you suspect, using Exists can be expensive.

Try using LEFT (or RIGHT) JOINS and filtering for NULL or NOT NULL values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello!

I have a form frmOrderHandler that contains 2 subforms. One for Sales
Orders
and the other for Installation Orders (Often, an install order is
associated
to a Sales Order via a field [Record-Orders-Sales].AssociatedSalesOrder.
If
the Install Order was not generated from a sale, then this field is null.
The
question ignores that possibility and only concerns with the case of those
Install Orders which are pointing to a Sales Order).

The user may filter by DateOfSale or DateScheduled (for installation),
using
a text control called Filter_Date in conjunction with an option button
with
"Date Of Sale" and "Date Scheduled" options. Filtering by data of sale,
which
is a field in the Sales Orders table, is easy and the sql runs in about 2
seconds. However, filtering on DateScheduled takes 3-5 minutes. We don't
have
a lot of records yet. Approx 6000 Install Orders and 5000 Sale Orders.
Nevertheless, I've indexed all relevant fields, which seems to only have
trimmed about 10 seconds.

Here's the lowdown on how it's setup... Since a job may need to be
rescheduled, we've put that data in a separate table tblSalesScheduling,
which points to [Record-Orders-Sales].ID (which is the PK for the Sales
Orders table). This way, we can have a history of scheduled dates for a
given
job.

Then, we use a totals query [FindRecent-SalesScheduling] to find the
MaxOfID, which will be the ID with the current schedule date. Then a
second
query [Recent-SalesScheduling], which uses [FindRecent-SalesScheduling]
and
adds other relevant fields, particularly ScheduledDate .
(example: Job was originally scheduled for 09-01-08, then rescheduled for
09-15-08, [FindRecent-SalesScheduling] finds the highest nubmered ID for
the
given SalesOrder, then [Recent-SalesScheduling] adds useful data, so we
then
have 09-15-08 as the most recently entered ScheduledDate.)

Now to go a step further, to filter the Install Orders subform, I need it
to
collect those Install Orders which are pointing to a Sales Order who's
most
recent Schedule date is equal to Filter_Date.

Originally, being somewhat rushed, the sql generated by the VBA code
looked
like this (the code has already placed #9-06-08# for Filter-Date).
Incidentally, the generated sql references the saved queries mentioned
above.

EXISTS
(
SELECT [Record-Orders-Sales].[SalesOrderID]
FROM [Record-Orders-Sales]
WHERE [Record-Orders-Sales].[SalesOrderID] =
[Record-Orders-Install].[AssociatedSalesOrder]
AND EXISTS
(SELECT [Recent-SalesScheduling].ID
FROM [Recent-SalesScheduling]
WHERE [Recent-SalesScheduling].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID]
AND [Recent-SalesScheduling].[ScheduledDate] = #9/6/2008#
)
)

This took ~3-4 minutes to run. I thought it was because I had nested
EXISTS
statements, and I wondered if the slowdown was due to having to 'rerun'
the
nested EXISTS for every iteration of the higer level EXISTS (not even
positive this is how the engine performs these tasks), so I rewrote it to
this:

EXISTS
(
SELECT [FindRecent-SalesScheduling].AssociatedSalesOrder
FROM [FindRecent-SalesScheduling] INNER JOIN [Record-SalesScheduling]
ON [FindRecent-SalesScheduling].MaxOfID = [Record-SalesScheduling].ID
WHERE [Record-SalesScheduling].ScheduledDate = #9/6/2008#
AND [FindRecent_SalesScheduling].AssociatedSalesOrder =
[Record-Orders-Install].[AssociatedSalesOrder]
)

I was surprised that this was even slower, at about 5-6 minutes.

If anyone understands this scenerio, and has any useful advice, please
share
it! I'm pretty stuck myself.

 

[markmarko]

Well, if I create outer joins, the issue is that part of that is a totals
query, which renders the resultant records as uneditable, but they need to be
editable.

The filter options also include 'Completed' which uses a similar EXISTS
function (only 1 level of it, as opposed to nested EXISTS or EXISTS with
Outer Joins) and that filter runs in about 10 seconds. It's SQL looks a bit
like this...

AND exists
(SELECT [Record-Orders-Sales].[SalesOrderID]
FROM [Record-Orders-Sales]
WHERE [Record-Orders-Sales].[SalesOrderID] =
[Record-Orders-Install].[AssociatedSalesOrder]
AND [Record-Orders-Sales].[DateOfSale] = #9/15/2008#)

Any further recommendations?

 

[markmarko]

Do you suppose it's a problem that the SWL references a saved Query? Would
that cause a slowdown?

 

[Douglas J. Steele]

That shouldn't be an issue.

 

[david]

query, which renders the resultant records as uneditable, but they need to

be
editable.

How are they editable if you are using a Totals Query to find Max ID?

Anyway, sometimes you have to choose the lesser of several evils:

3 Options I see:

1) Leave it slow

2) Use a temp table as a batch process, (probably to find the
maxID) that then enables you to use a fast query for updates,
then update back to the main table from the temp table if you
need to update multiple tables.

3) Use a separate query and form after you have identified the
records you wish to update.

Is this a JET/MDB database? Are you filtering by applying a
filter? (Does it work better if you update the RecordSource
instead of applying a filter?) Is the query slow, or just the form
/subform?

(david)

Well, if I create outer joins, the issue is that part of that is a totals
query, which renders the resultant records as uneditable, but they need to
be
editable.

The filter options also include 'Completed' which uses a similar EXISTS
function (only 1 level of it, as opposed to nested EXISTS or EXISTS with
Outer Joins) and that filter runs in about 10 seconds. It's SQL looks a
bit
like this...

AND exists
(SELECT [Record-Orders-Sales].[SalesOrderID]
FROM [Record-Orders-Sales]
WHERE [Record-Orders-Sales].[SalesOrderID] =
[Record-Orders-Install].[AssociatedSalesOrder]
AND [Record-Orders-Sales].[DateOfSale] = #9/15/2008#)

Any further recommendations?

As you suspect, using Exists can be expensive.

Try using LEFT (or RIGHT) JOINS and filtering for NULL or NOT NULL
values.

 

[markmarko]

Responses inline....

How are they editable if you are using a Totals Query to find Max ID?

The current setup uses the Totals query in an EXISTS clause, so it doesn't
restrict the actual records, but seemingly results in slow filtering.

2) Use a temp table as a batch process, (probably to find the
maxID) that then enables you to use a fast query for updates,
then update back to the main table from the temp table if you
need to update multiple tables.

This is a good idea... I'm going to give it a try....

Is this a JET/MDB database? Are you filtering by applying a
filter? (Does it work better if you update the RecordSource
instead of applying a filter?) Is the query slow, or just the form
/subform?

I'm using Access 2007, so yes it's Jet, ACCDB. No, it's not quicker to
update recordsource... That's actually how I had it set up originally (prior
to asking this question).

 

[david]

The current setup uses the Totals query in an EXISTS clause, so it doesn't

restrict the actual records, but seemingly results in slow filtering.

Oh. Yes.

(david)

 

[markmarko]

Well that worked... I used a temp table to store the MaxOfID, and then used
the temp table directly in the subsequent query. It now runs in about 3
seconds.