Sub select speed improvement

#1

I found a speed improvement in a sub select. Using "SELECT TOP 1" instead of
"SELECT", a union query completed in seconds, instead of minutes.

tblzDate is a temp table that holds 1 record of dates input from the user.
The union query compiles cost information from multiple tables for a report.
Each query in the union uses the dates to limit the records selected. Before
the change the report would take more then 30 minutes to open, now it takes
less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

#2

ron paii wrote:

Quote:

I found a speed improvement in a sub select. Using "SELECT TOP 1"
instead of "SELECT", a union query completed in seconds, instead of
minutes.

tblzDate is a temp table that holds 1 record of dates input from the
user. The union query compiles cost information from multiple tables
for a report. Each query in the union uses the dates to limit the
records selected. Before the change the report would take more then
30 minutes to open, now it takes less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

What is the purpose of that DISTINCTROW keyword in this query? This query,
because it's only returning a single column that aggregates all the data,
is going to return a single result - get rid of the useless DISTINCTROW.

Instead of the subselects, simply join to tblzDate:

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i, tblzDate As d
WHERE i.DateIssued BETWEEN Date1 AND Date2

Or

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i JOIN tblzDate As d
ON i.DateIssued BETWEEN Date1 AND Date2

#3

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

Quote:

ron paii wrote:
I found a speed improvement in a sub select. Using "SELECT TOP 1"
instead of "SELECT", a union query completed in seconds, instead of
minutes.

tblzDate is a temp table that holds 1 record of dates input from the
user. The union query compiles cost information from multiple tables
for a report. Each query in the union uses the dates to limit the
records selected. Before the change the report would take more then
30 minutes to open, now it takes less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

What is the purpose of that DISTINCTROW keyword in this query? This query,
because it's only returning a single column that aggregates all the data,
is going to return a single result - get rid of the useless DISTINCTROW.

The DISTINCTROW is an artifact of the original query that returned more then
1 row. I strip out the other columns to make it easer to show in the post.
But even so it may not need the DISTINCTROW.

Quote:

Instead of the subselects, simply join to tblzDate:

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i, tblzDate As d
WHERE i.DateIssued BETWEEN Date1 AND Date2

Or

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i JOIN tblzDate As d
ON i.DateIssued BETWEEN Date1 AND Date2

I will test that, don't know why I used a sub query instead.

#4

"ron paii" <none (AT) nospam (DOT) com> wrote

Quote:

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:j5fpqt$9c3$1 (AT) dont-email (DOT) me...
ron paii wrote:
I found a speed improvement in a sub select. Using "SELECT TOP 1"
instead of "SELECT", a union query completed in seconds, instead of
minutes.

tblzDate is a temp table that holds 1 record of dates input from the
user. The union query compiles cost information from multiple tables
for a report. Each query in the union uses the dates to limit the
records selected. Before the change the report would take more then
30 minutes to open, now it takes less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

What is the purpose of that DISTINCTROW keyword in this query? This
query,
because it's only returning a single column that aggregates all the data,
is going to return a single result - get rid of the useless DISTINCTROW.

The DISTINCTROW is an artifact of the original query that returned more
then 1 row. I strip out the other columns to make it easer to show in the
post. But even so it may not need the DISTINCTROW.

Instead of the subselects, simply join to tblzDate:

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i, tblzDate As d
WHERE i.DateIssued BETWEEN Date1 AND Date2

Or

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i JOIN tblzDate As d
ON i.DateIssued BETWEEN Date1 AND Date2

I will test that, don't know why I used a sub query instead.

After some testing there wasn't much a difference in performance between the
sub query and the join. Getting rid of the DISTINCTROW was more noticeable.

#5

On Sep 22, 10:23*am, "ron paii" <n... (AT) nospam (DOT) com> wrote:

Quote:

I found a speed improvement in a sub select. Using "SELECT TOP 1" insteadof
"SELECT", a union query completed in seconds, instead of minutes.

tblzDate is a temp table that holds 1 record of dates input from the user..
The union query compiles cost information from multiple tables for a report.
Each query in the union uses the dates to limit the records selected. Before
the change the report would take more then 30 minutes to open, now it takes
less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

I've not been a fan of subselects in the past due to the extreme
degradataion of speed in queries. it appears not much has changed.

What would happen of you had two queries; TopDate1 and TopDate2?
TopDate1's SQL was
SELECT TOP 1 tblzDate.Date1 FROM tblzDate

And then join table/query Cost to TopDate1 and TopDate2?

I'll assume Date1 and Date2 are indexed.

Glad you got it down from 30 minutes. That'd be excuciating.

#6

ron paii wrote:

Quote:

After some testing there wasn't much a difference in performance
between the sub query and the join. Getting rid of the DISTINCTROW
was more noticeable.

I'm a little puzzled as to why these dates are being stored in a table. I
assume the user uses a form to enter the dates ... You could unbind the
textboxes from the table and directly reference the values in the textboxes
from your query. It would save a bit of i/o ...

#7

On 23/09/2011 12:55:26, "ron paii" wrote:

Quote:

"ron paii" <none (AT) nospam (DOT) com> wrote in message
news:j5g174$100$1 (AT) dont-email (DOT) me...

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:j5fpqt$9c3$1 (AT) dont-email (DOT) me...
ron paii wrote:
I found a speed improvement in a sub select. Using "SELECT TOP 1"
instead of "SELECT", a union query completed in seconds, instead of
minutes.

tblzDate is a temp table that holds 1 record of dates input from the
user. The union query compiles cost information from multiple tables
for a report. Each query in the union uses the dates to limit the
records selected. Before the change the report would take more then
30 minutes to open, now it takes less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

What is the purpose of that DISTINCTROW keyword in this query? This
query,
because it's only returning a single column that aggregates all the data,
is going to return a single result - get rid of the useless DISTINCTROW.

The DISTINCTROW is an artifact of the original query that returned more
then 1 row. I strip out the other columns to make it easer to show in the
post. But even so it may not need the DISTINCTROW.

Instead of the subselects, simply join to tblzDate:

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i, tblzDate As d
WHERE i.DateIssued BETWEEN Date1 AND Date2

Or

SELECT Sum([Issued]*[Price]) AS Cost
FROM tblIssued As i JOIN tblzDate As d
ON i.DateIssued BETWEEN Date1 AND Date2

I will test that, don't know why I used a sub query instead.

After some testing there wasn't much a difference in performance between
the sub query and the join. Getting rid of the DISTINCTROW was more
noticeable.

If it's any help, Ron, I use a date input form which creates a Global
Variable DateField which looks like Between #01/Sep/2011 00:00:00# And
#19/Sep/2011 08:41:51# Also a Global Variable (Integer) ButtonPressed

You need a bit of coding on the OnOpen Event of the report - something like

DoCmd.OpenForm "DateEntry"

CheckDateEntryClosed:
If IsLoaded("DateEntry") Then ' Date Entry form
Call apWait(5, False) ' Wait 5 seconds
GoTo CheckDateEntryClosed
End If

If ButtonPressed = vbCancel Then
Cancel = True
End If

StgFilter = "DateIssued " & DateField
Me.Filter = StgFilter
Me.FilterOn = True

Quite a bit of other coding to create the DateField which I can supply if
required.

Turns out quite useful as having created the DateField, you can use something
like GetDate1() as the criteria for queries which would return the
#01/Sep/2011 00:00:00# part of the date

Phil

#8

"ron paii" <none (AT) nospam (DOT) com> wrote in
news:j5g174$100$1 (AT) dont-email (DOT) me:

Quote:

The DISTINCTROW is an artifact of the original query that returned
more then 1 row.

But you should never use DISTINCTROW except if you are trying to
make an otherwise non-updatable query updatable -- instead, you
should use DISTINCT. DISTINCTROW is a Jet/ACE-specific keyword and
not compatible. It occasionally can work some magic, making an
non-editable query with a join in the FROM clause editable, but a
query whose SELECT includes nothing but an aggregate function is not
going to be editable in the first place.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#9

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

Quote:

ron paii wrote:

After some testing there wasn't much a difference in performance
between the sub query and the join. Getting rid of the DISTINCTROW
was more noticeable.

I'm a little puzzled as to why these dates are being stored in a table. I
assume the user uses a form to enter the dates ... You could unbind the
textboxes from the table and directly reference the values in the
textboxes from your query. It would save a bit of i/o ...

They are user input of range of dates. I stored them in a table for

reference by a union query that is a source for a report. It's using a
calendar form for input, i could try adding hidden text boxes to store the
selected dates.

#10

"Patrick Finucane" <patrickfinucanetx (AT) gmail (DOT) com> wrote

Quote:

On Sep 22, 10:23 am, "ron paii" <n... (AT) nospam (DOT) com> wrote:
I found a speed improvement in a sub select. Using "SELECT TOP 1" instead
of
"SELECT", a union query completed in seconds, instead of minutes.

tblzDate is a temp table that holds 1 record of dates input from the
user.
The union query compiles cost information from multiple tables for a
report.
Each query in the union uses the dates to limit the records selected.
Before
the change the report would take more then 30 minutes to open, now it
takes
less then a minute.

Following is an example of one on the queries.

SELECT DISTINCTROW Sum([Issued]*[Price]) AS Cost FROM tblIssued WHERE
(((tblIssued.DateIssued) Between (SELECT TOP 1 tblzDate.Date1 FROM
tblzDate

And (SELECT TOP 1 tblzDate.Date2 FROM tblzDate

));

I've not been a fan of subselects in the past due to the extreme
degradataion of speed in queries. it appears not much has changed.

What would happen of you had two queries; TopDate1 and TopDate2?
TopDate1's SQL was
SELECT TOP 1 tblzDate.Date1 FROM tblzDate

And then join table/query Cost to TopDate1 and TopDate2?

I'll assume Date1 and Date2 are indexed.

The table only has at most 1 record, so it is not indexed.

Quote:

Glad you got it down from 30 minutes. That'd be excuciating.

Strange thing is this query ran much faster before the BE was converted from

A97 to A2010.