By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Performance on access queries with conditions

P: n/a
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly
written into the query or if it is a 'normal' access parameter value
that is entered during the query.
If I however create a separate parameter table that contains nothing
but the date I want to use in the query and then refer to this table
(the query now reads like 'where date > parametertabe.parameterdate',
the query becomes very slow (minutes).
What is the reason for this and how can i avoid the performance
problem but still not either 'hardcode' or manually enter the date
each time?
Thank you.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
bhbgroup wrote:
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly
written into the query or if it is a 'normal' access parameter value
that is entered during the query.
If I however create a separate parameter table that contains nothing
but the date I want to use in the query and then refer to this table
(the query now reads like 'where date > parametertabe.parameterdate',
the query becomes very slow (minutes).
What is the reason for this and how can i avoid the performance
problem but still not either 'hardcode' or manually enter the date
each time?
Thank you.


Just a guess, since you didn't post your query & table set up: add an
index to the parameter date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Nov 13 '05 #2

P: n/a
MGFoster <me@privacy.com> wrote in message news:<Xi*****************@newsread1.news.pas.earth link.net>...
bhbgroup wrote:
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly
written into the query or if it is a 'normal' access parameter value
that is entered during the query.
If I however create a separate parameter table that contains nothing
but the date I want to use in the query and then refer to this table
(the query now reads like 'where date > parametertabe.parameterdate',
the query becomes very slow (minutes).
What is the reason for this and how can i avoid the performance
problem but still not either 'hardcode' or manually enter the date
each time?
Thank you.


Just a guess, since you didn't post your query & table set up: add an
index to the parameter date.


Thanks, unfortunately no change. The query is still slow. Additional
info I can add:
- The table that I want to query is a linked Oracle-table.
- It contains a datefield (date and time) on which I want to filter.
- The table contains position entries and for each position an entry
per valuation-date.

I want the records of the latest valuation only. I chose to write the
query "where valuationdate > latestvaluationdate" since this will give
me all relevant records (everything with a time after 00:00:00).

The query performs very nicely when formulating the where clause is
formulated like ">#7/28/2004#"

When I add a second table (call it Paramtertable) with just one column
/ row (call it latestvaluationdate) and change the where statement to
read ">'parametertable.latestvaluationdate'", the query becomes slow.

It does not make any difference, whether the "parametertable" is
indexed or not (autoindex, using the date as the index field or
creating a new index bring all the same result).
Nov 13 '05 #3

P: n/a
Are both these tables in the Oracle database? If the second table, for
example, is a local Access table, then every record in the Oracle table must
be retrieved for comparison on the local machine. If the second table
already _is_ an Oracle table, then try using a passthrough Query, that is,
an Oracle Query (passthrough warns Jet, "Hands off this query."), to force
all the work being done on the server.

In working with other server databases (I haven't done Access clients to
Oracle), if there were several joins, sometimes Jet would "decide" that it
would be more efficient to retrieve all the data and do the manipulation
itself (which, of course, it never was). In those cases, we often were able
to create a View on the server that forced work to be done there, and were
able to improve performance a great deal. That, of course, is a different
situation from your criteria / criteria table. On the other hand, changing
the selection to an Outer Join from a Where clause might make a difference.

Larry Linson
Microsoft Access MVP

"bhbgroup" <bh******@gmx.net> wrote in message
news:c9**************************@posting.google.c om...
MGFoster <me@privacy.com> wrote in message

news:<Xi*****************@newsread1.news.pas.earth link.net>...
bhbgroup wrote:
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly
written into the query or if it is a 'normal' access parameter value
that is entered during the query.
If I however create a separate parameter table that contains nothing
but the date I want to use in the query and then refer to this table
(the query now reads like 'where date > parametertabe.parameterdate',
the query becomes very slow (minutes).
What is the reason for this and how can i avoid the performance
problem but still not either 'hardcode' or manually enter the date
each time?
Thank you.


Just a guess, since you didn't post your query & table set up: add an
index to the parameter date.


Thanks, unfortunately no change. The query is still slow. Additional
info I can add:
- The table that I want to query is a linked Oracle-table.
- It contains a datefield (date and time) on which I want to filter.
- The table contains position entries and for each position an entry
per valuation-date.

I want the records of the latest valuation only. I chose to write the
query "where valuationdate > latestvaluationdate" since this will give
me all relevant records (everything with a time after 00:00:00).

The query performs very nicely when formulating the where clause is
formulated like ">#7/28/2004#"

When I add a second table (call it Paramtertable) with just one column
/ row (call it latestvaluationdate) and change the where statement to
read ">'parametertable.latestvaluationdate'", the query becomes slow.

It does not make any difference, whether the "parametertable" is
indexed or not (autoindex, using the date as the index field or
creating a new index bring all the same result).

Nov 13 '05 #4

P: n/a
Thanks for pointing out the problem; the parameter-table is of course
an access-table and the data-table only is on Oracle. Unfortunately I
don't have the possibility to change that. I also cannot really work
with outer joins with the datastructure I find on the oracle db.
Bringing the whole datatable to access is what I want to avoid. So it
seems like either accepting the response time or use simple (hard
coded or entered) access parameters.
Seems too bad that SQL can handle the query with a 'fixed' parameter
but not with a parameter taken from another db.
"Larry Linson" <bo*****@localhost.not> wrote in message news:<ZA**************@nwrddc03.gnilink.net>...
Are both these tables in the Oracle database? If the second table, for
example, is a local Access table, then every record in the Oracle table must
be retrieved for comparison on the local machine. If the second table
already _is_ an Oracle table, then try using a passthrough Query, that is,
an Oracle Query (passthrough warns Jet, "Hands off this query."), to force
all the work being done on the server.

In working with other server databases (I haven't done Access clients to
Oracle), if there were several joins, sometimes Jet would "decide" that it
would be more efficient to retrieve all the data and do the manipulation
itself (which, of course, it never was). In those cases, we often were able
to create a View on the server that forced work to be done there, and were
able to improve performance a great deal. That, of course, is a different
situation from your criteria / criteria table. On the other hand, changing
the selection to an Outer Join from a Where clause might make a difference.

Larry Linson
Microsoft Access MVP

"bhbgroup" <bh******@gmx.net> wrote in message
news:c9**************************@posting.google.c om...
MGFoster <me@privacy.com> wrote in message

news:<Xi*****************@newsread1.news.pas.earth link.net>...
bhbgroup wrote:

> I have a query on one large table. I only add one condition, i.e. a
> date (the SQL reads like 'where date > parameterdate'.
> This query is rather quick if 'parameterdate' is either explicitly
> written into the query or if it is a 'normal' access parameter value
> that is entered during the query.
> If I however create a separate parameter table that contains nothing
> but the date I want to use in the query and then refer to this table
> (the query now reads like 'where date > parametertabe.parameterdate',
> the query becomes very slow (minutes).
> What is the reason for this and how can i avoid the performance
> problem but still not either 'hardcode' or manually enter the date
> each time?
> Thank you.

Just a guess, since you didn't post your query & table set up: add an
index to the parameter date.


Thanks, unfortunately no change. The query is still slow. Additional
info I can add:
- The table that I want to query is a linked Oracle-table.
- It contains a datefield (date and time) on which I want to filter.
- The table contains position entries and for each position an entry
per valuation-date.

I want the records of the latest valuation only. I chose to write the
query "where valuationdate > latestvaluationdate" since this will give
me all relevant records (everything with a time after 00:00:00).

The query performs very nicely when formulating the where clause is
formulated like ">#7/28/2004#"

When I add a second table (call it Paramtertable) with just one column
/ row (call it latestvaluationdate) and change the where statement to
read ">'parametertable.latestvaluationdate'", the query becomes slow.

It does not make any difference, whether the "parametertable" is
indexed or not (autoindex, using the date as the index field or
creating a new index bring all the same result).

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.