access97 / sql server, how to speed up this query

[lesperancer]

SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));

tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an
indexed long integer
the primary key is t_orno, t_pono

tblFilterDate is an access table used to store reporting criteria, and
it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=(select fromYearMonth from tblFilterDate where
dateId = 1) And (b.t_yearMonth)<=(select toYearMonth from tblFilterDate
where dateId = 1)));

this one is runs in a second
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=98 And (b.t_yearMonth)<=99));

if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes,
and change the query to refer to the form fields, again the query runs
in a second

showplan.out for the long running query refers to a temporary table
01) Restrict rows of table tblMonthlyBooking
by scanning
testing expression "b.t_yearMonth>= And b.t_yearMonth<="
store result in temporary table

is there another way to build this query, to make use of the two
tables, and have results in a second ?

note, the tblFilterDate table cannot be moved to sqlServer, that would
be a massive reengineering exercise

[Anthony England]

<le*********@natpro.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));

tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an
indexed long integer
the primary key is t_orno, t_pono

tblFilterDate is an access table used to store reporting criteria, and
it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=(select fromYearMonth from tblFilterDate where
dateId = 1) And (b.t_yearMonth)<=(select toYearMonth from tblFilterDate
where dateId = 1)));

this one is runs in a second
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=98 And (b.t_yearMonth)<=99));

if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes,
and change the query to refer to the form fields, again the query runs
in a second

showplan.out for the long running query refers to a temporary table
01) Restrict rows of table tblMonthlyBooking
by scanning
testing expression "b.t_yearMonth>= And b.t_yearMonth<="
store result in temporary table

is there another way to build this query, to make use of the two
tables, and have results in a second ?

note, the tblFilterDate table cannot be moved to sqlServer, that would
be a massive reengineering exercise

I would prefer the following which should be faster and is certainly easier
to read:

SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE b.t_yearMonth BETWEEN 98 AND 99

But what about using both tables?
Come on, you're not serious, right?
The local Jet table is restricted to one row and its purpose is store a
MinValue and a MaxValue for your reporting purposes. This is fine, and may
be useful in letting you close the database and remember these settings -
but they are only really settings and trying to join these tables for the
single purpose of passing two parameters to your SQL Server database isn't
really sensible.

The best solution will depend on a number of things:
Are you using linked tables
Are you able to create server objects such as a stored procedure, a view,
etc
Do you have a preference for DAO or ADO code
Is the recordset you return to be read-only or not.

One option would be to create a stored procedure on the server, with two
parameters for the min and max value. You could then generate a recordset
by using ADO to execute the recordset, passing in the values which it looks
up from your local table.
Another option might be to look up the values, then dynamically re-write a
pass-through query to get the records.

[Bob Quintal]

le*********@natpro.com wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));

tblMonthlyBooking is a sql server table, 200K rows, yearMonth
is an indexed long integer
the primary key is t_orno, t_pono

tblFilterDate is an access table used to store reporting
criteria, and it will always only have one row, yearMonth is
an indexed long integer
Why are you using two instances of tblfilterdate???? Because of
the cartesian joins, this will produce double the number of
rows, which must be reduced again by the select distinct.

Reccomendation:
SELECT distinct
b.t_orno,
b.t_pono
FROM tblMonthlyBooking AS b,
tblFilterDate,
WHERE b.t_yearMonth Between [tblfilterdate].[fromDate] And
[tblFilterDate].[toDate];

you may not even need the distinct statement any more.

this query takes approx 3 minutes to run, so does this one
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=(select fromYearMonth from
tblFilterDate where dateId = 1) And (b.t_yearMonth)<=(select
toYearMonth from tblFilterDate where dateId = 1)));

this one is runs in a second
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=98 And (b.t_yearMonth)<=99));

if I load 'fromYearMonth' and 'toYearMonth' into a form's
textboxes, and change the query to refer to the form fields,
again the query runs in a second

showplan.out for the long running query refers to a temporary
table
01) Restrict rows of table tblMonthlyBooking
by scanning
testing expression "b.t_yearMonth>= And
b.t_yearMonth<=" store result in temporary table

is there another way to build this query, to make use of the
two tables, and have results in a second ?

note, the tblFilterDate table cannot be moved to sqlServer,
that would be a massive reengineering exercise

--
Bob Quintal

PA is y I've altered my email address.

[Tom van Stiphout]

On Tue, 21 Mar 2006 22:49:24 GMT, Bob Quintal <rq******@sympatico.ca>
wrote:

Not double, but the square.
-Tom.

<clip>

Why are you using two instances of tblfilterdate???? Because of
the cartesian joins, this will produce double the number of
rows, which must be reduced again by the select distinct.

<clip>

[lesperancer]

> Are you using linked tables
yes

Are you able to create server objects such as a stored procedure, a view, etc yes
Do you have a preference for DAO or ADO code access97, so DAO
Is the recordset you return to be read-only or not. yes

If I store the parameters as text boxes on a hidden form and refer to
the text boxes as parameters in my query, query timing is substantially
improved

I thought access would do the same optimization when using a small
table, but obvious not

I will look at using a stored procedure ... thanks

Anthony England wrote: <le*********@natpro.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));

tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an
indexed long integer
the primary key is t_orno, t_pono

tblFilterDate is an access table used to store reporting criteria, and
it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=(select fromYearMonth from tblFilterDate where
dateId = 1) And (b.t_yearMonth)<=(select toYearMonth from tblFilterDate
where dateId = 1)));

this one is runs in a second
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE (((b.t_yearMonth)>=98 And (b.t_yearMonth)<=99));

if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes,
and change the query to refer to the form fields, again the query runs
in a second

showplan.out for the long running query refers to a temporary table
01) Restrict rows of table tblMonthlyBooking
by scanning
testing expression "b.t_yearMonth>= And b.t_yearMonth<="
store result in temporary table

is there another way to build this query, to make use of the two
tables, and have results in a second ?

note, the tblFilterDate table cannot be moved to sqlServer, that would
be a massive reengineering exercise

I would prefer the following which should be faster and is certainly easier
to read:

SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE b.t_yearMonth BETWEEN 98 AND 99

But what about using both tables?
Come on, you're not serious, right?
The local Jet table is restricted to one row and its purpose is store a
MinValue and a MaxValue for your reporting purposes. This is fine, and may
be useful in letting you close the database and remember these settings -
but they are only really settings and trying to join these tables for the
single purpose of passing two parameters to your SQL Server database isn't
really sensible.

The best solution will depend on a number of things:
Are you using linked tables
Are you able to create server objects such as a stored procedure, a view,
etc
Do you have a preference for DAO or ADO code
Is the recordset you return to be read-only or not.

One option would be to create a stored procedure on the server, with two
parameters for the min and max value. You could then generate a recordset
by using ADO to execute the recordset, passing in the values which it looks
up from your local table.
Another option might be to look up the values, then dynamically re-write a
pass-through query to get the records.

[Anthony England]

Thanks for the feedback.
Another alternative would be to create a SQL Server view.
You can create an Access table linked to this view as if it were a table.

<le*********@natpro.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

Are you using linked tables

yes

Are you able to create server objects such as a stored procedure, a view,
etc

yes

Do you have a preference for DAO or ADO code

access97, so DAO

Is the recordset you return to be read-only or not.

yes

If I store the parameters as text boxes on a hidden form and refer to
the text boxes as parameters in my query, query timing is substantially
improved

I thought access would do the same optimization when using a small
table, but obvious not

I will look at using a stored procedure ... thanks

Anthony England wrote:

<le*********@natpro.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

> SELECT distinct b.t_orno, b.t_pono
> FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
> tblFilterDate_1
> WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
> [tblFilterDate_1].[toDate]));
>
> tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an
> indexed long integer
> the primary key is t_orno, t_pono
>
> tblFilterDate is an access table used to store reporting criteria, and
> it will always only have one row, yearMonth is an indexed long integer
>
>
> this query takes approx 3 minutes to run, so does this one
> SELECT DISTINCT b.t_orno, b.t_pono
> FROM tblMonthlyBooking AS b
> WHERE (((b.t_yearMonth)>=(select fromYearMonth from tblFilterDate where
> dateId = 1) And (b.t_yearMonth)<=(select toYearMonth from tblFilterDate
> where dateId = 1)));
>
> this one is runs in a second
> SELECT DISTINCT b.t_orno, b.t_pono
> FROM tblMonthlyBooking AS b
> WHERE (((b.t_yearMonth)>=98 And (b.t_yearMonth)<=99));
>
> if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes,
> and change the query to refer to the form fields, again the query runs
> in a second
>
> showplan.out for the long running query refers to a temporary table
> 01) Restrict rows of table tblMonthlyBooking
> by scanning
> testing expression "b.t_yearMonth>= And b.t_yearMonth<="
> store result in temporary table
>
> is there another way to build this query, to make use of the two
> tables, and have results in a second ?
>
> note, the tblFilterDate table cannot be moved to sqlServer, that would
> be a massive reengineering exercise

I would prefer the following which should be faster and is certainly
easier
to read:

SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b
WHERE b.t_yearMonth BETWEEN 98 AND 99

But what about using both tables?
Come on, you're not serious, right?
The local Jet table is restricted to one row and its purpose is store a
MinValue and a MaxValue for your reporting purposes. This is fine, and
may
be useful in letting you close the database and remember these settings -
but they are only really settings and trying to join these tables for the
single purpose of passing two parameters to your SQL Server database
isn't
really sensible.

The best solution will depend on a number of things:
Are you using linked tables
Are you able to create server objects such as a stored procedure, a view,
etc
Do you have a preference for DAO or ADO code
Is the recordset you return to be read-only or not.

One option would be to create a stored procedure on the server, with two
parameters for the min and max value. You could then generate a
recordset
by using ADO to execute the recordset, passing in the values which it
looks
up from your local table.
Another option might be to look up the values, then dynamically re-write
a
pass-through query to get the records.

[Lyle Fairfield]

le*********@natpro.com wrote in news:1142937414.254928.223190
@u72g2000cwu.googlegroups.com:

SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMonth) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));

Joins are often faster and more efficient than Wheres.
Without seeing your tables I can't be completely accurate but I think
something like

SELECT tmb.*
FROM tblMonthBooking AS tmb
INNER JOIN tblFilterDate AS tfd
ON (tmb.t_YearMonth BETWEEN tfd.fromDate AND tfd.toDate)

modified for your exact requirements should be quite fast.

It's inlikley the query-grid-wizard nonsense window will be able to
represent the JOIN given so one would have to use the SQL view to create
the query or VBA to create or run it.

If yearMonth is Integer and fromDate and toDate are Dates I'm not sure how
the Between works, but that's another issue.

When one is doing Dates, Between often fails to do what one wants and more
accurate results may be achieved with something like

tmb.t_YearMonth >= tfd.fromDate (includes fromDate)
AND
tmb.t_YearMonth < tfd.fromDate (excludes toDate).

--
Lyle Fairfield