Linked Oracle Tables - Query - SLOW!!!

[lbernarde]

I have a linked table with a huge amount of data. I created a query where I
would type in the criteria I wanted to pull. This took approx 30 seconds
which for my purposes I was okay with. However, I am now trying to make this
process an automated process, where I can push a button, go to lunch and I
have created files for all the customers I need. Here is the challenge.
When I create the query and type the criteria in, it runs fine. When I try
to put a value (via code) on a form, reference that value on the form in the
query, the process does not complete, just hangs. So I tried using code
(creating a new qdf) to make a new query with the appropriate value as my
criteria thinking this would run as quickly as the original query, wrong! So
my question is, how can I get this mountain of data in a linked Oracle table
to run and not hang. Not sure if this is a ODBC issue or a query, issue,
seems like a little of both!
Thanks Much!

 

[Rick Brandt]

I have a linked table with a huge amount of data. I created a query
where I would type in the criteria I wanted to pull. This took
approx 30 seconds which for my purposes I was okay with. However, I
am now trying to make this process an automated process, where I can
push a button, go to lunch and I have created files for all the
customers I need. Here is the challenge. When I create the query and
type the criteria in, it runs fine. When I try to put a value (via
code) on a form, reference that value on the form in the query, the
process does not complete, just hangs. So I tried using code
(creating a new qdf) to make a new query with the appropriate value
as my criteria thinking this would run as quickly as the original
query, wrong! So my question is, how can I get this mountain of data
in a linked Oracle table to run and not hang. Not sure if this is a
ODBC issue or a query, issue, seems like a little of both!
Thanks Much!

How exactly did you use the query that "took approximately 30 seconds"? If all
you did was open it in datasheet view then you were only seeing the results of a
few pages worth of data. If your current process needs to process the FULL
query then that is why it is taking longer.

How much data are we talking about? Are there any joins in the query? If there
are you should use a view or stored procedure on the server as joins against
linked ODBC tables can be inefficient.

 

[lbernarde]

It actually was a make table. It returned approximately 30,000 records. I'm
not sure on the total record size of the table. I'm thinking 500,000 plus,
opening right now. Right now I do not have any joins. I'm trying to get the
smaller dataset to work with then add the necessary joins and data
conversion. Is there a way I can create a snapshot of the linked table and
get my smaller dataset from that to work with? Haven't done that before is
it possible?