Passthrough Query SLOWS Access Query Editor

[Marc]

I've searched this group for an answer and think I have a sense of the
problem, but I would like to canvas the more knowledgeable among us for
ideas.

Basically, I have created a few Passthrough Queries to pull data from
Visual FoxPro 6.0 free datatables (no stored procedures available) into
an Access database. However, when I seek to add these queries into the
Query Editor or open up a query with a passthrough query as a source,
it takes forever. My understanding is that Access needs to actually
execute the full passthrough query in order to obtain the field
information for the Editor.

Now, these passthrough queries are to very very large tables (upwards
of a gig) with a ton of records. Is there anyway to speed up Access in
design mode to open up and/or add queries more quickly?

Thanks,
marc

 

[Pieter Wijnen]

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter

 

[Pieter Wijnen]

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter

I've searched this group for an answer and think I have a sense of the
problem, but I would like to canvas the more knowledgeable among us for
ideas.

Basically, I have created a few Passthrough Queries to pull data from
Visual FoxPro 6.0 free datatables (no stored procedures available) into
an Access database. However, when I seek to add these queries into the
Query Editor or open up a query with a passthrough query as a source,
it takes forever. My understanding is that Access needs to actually
execute the full passthrough query in order to obtain the field
information for the Editor.

Now, these passthrough queries are to very very large tables (upwards
of a gig) with a ton of records. Is there anyway to speed up Access in
design mode to open up and/or add queries more quickly?

Thanks,
marc

--

 

[Marc]

Pieter,

Thanks for the quick response. I've searched for information on the
maxrows property and can only seem to figure that it limits the results
to the first [n] records returned by the query. If that's the case,
would that mean I need to have an ORDER BY statement and guesstimate
the rows that might come back (i.e. how many sales transactions would
occur over how long a relevant sales period would be)?

With respect to #4, this would be my greatest desire, but it is a 3rd
party solution that is what it is.

To the extent that I can implement #2, I do.

For #3, how do you determine what fields are indexed in a VFP free
datatable?

Thanks,
marc

 

[Pieter Wijnen]

I thought your main concern was the development time
meaning to set the maxrows returned was the core issue
you can easily enough loop through the qdef collection & reset the maxrows
property prior to deployment
for the rest I've had no working experience with FoxPro (ie knowing it's
indicies, et all), except that is was developed (partly) on the DBASE
foundation (also file based) & you might want to stress to the management
that MSSQL (msde edition) is free
the reason I'm hung up on this (I *do* recognize your frustration) is that
I've experienced the same working on a 64K WAN
I do foresee a lot of time (read money) spent to optimize performance.
Anyways read up on SQL-89 & SQL-92 to optimize your Where clauses

not much further help :-(

Pieter

PS you can use: crap wijnen more crap at (the sign) online we go agian dot
shortcode no - to contact me -direct about this

Pieter,

Thanks for the quick response. I've searched for information on the
maxrows property and can only seem to figure that it limits the results
to the first [n] records returned by the query. If that's the case,
would that mean I need to have an ORDER BY statement and guesstimate
the rows that might come back (i.e. how many sales transactions would
occur over how long a relevant sales period would be)?

With respect to #4, this would be my greatest desire, but it is a 3rd
party solution that is what it is.

To the extent that I can implement #2, I do.

For #3, how do you determine what fields are indexed in a VFP free
datatable?

Thanks,
marc

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter







--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Pieter Wijnen]

I thought your main concern was the development time
meaning to set the maxrows returned was the core issue
you can easily enough loop through the qdef collection & reset the maxrows
property prior to deployment
for the rest I've had no working experience with FoxPro (ie knowing it's
indicies, et all), except that is was developed (partly) on the DBASE
foundation (also file based) & you might want to stress to the management
that MSSQL (msde edition) is free
the reason I'm hung up on this (I *do* recognize your frustration) is that
I've experienced the same working on a 64K WAN
I do foresee a lot of time (read money) spent to optimize performance.
Anyways read up on SQL-89 & SQL-92 to optimize your Where clauses

not much further help :-(

Pieter

PS you can use: crap wijnen more crap at (the sign) online we go agian dot
shortcode no - to contact me -direct about this

Pieter,

Thanks for the quick response. I've searched for information on the
maxrows property and can only seem to figure that it limits the results
to the first [n] records returned by the query. If that's the case,
would that mean I need to have an ORDER BY statement and guesstimate
the rows that might come back (i.e. how many sales transactions would
occur over how long a relevant sales period would be)?

With respect to #4, this would be my greatest desire, but it is a 3rd
party solution that is what it is.

To the extent that I can implement #2, I do.

For #3, how do you determine what fields are indexed in a VFP free
datatable?

Thanks,
marc

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter







--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

--

 

[david epsom dot com dot au]

No, Access supports a separate MaxRows property as well
as the kind of thing you are talking about. The MaxRows
property is not an SQL or Order kind of thing: it just
addresses the mechanics of how data is transferred from
one place to another. 5 rows = 5 rows, content is not
relevant. Dunno what effect this will have on design view.

Another way is to just create an empty database, and link
to that. For a VFP database, it is a simple matter to rename
the folders or use a mapped drive while you are developing.

(david)

Pieter,

Thanks for the quick response. I've searched for information on the
maxrows property and can only seem to figure that it limits the results
to the first [n] records returned by the query. If that's the case,
would that mean I need to have an ORDER BY statement and guesstimate
the rows that might come back (i.e. how many sales transactions would
occur over how long a relevant sales period would be)?

With respect to #4, this would be my greatest desire, but it is a 3rd
party solution that is what it is.

To the extent that I can implement #2, I do.

For #3, how do you determine what fields are indexed in a VFP free
datatable?

Thanks,
marc

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter







--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Pieter Wijnen]

I stand corrected. Haven't checked it out, but thought it might help

Pieter

No, Access supports a separate MaxRows property as well
as the kind of thing you are talking about. The MaxRows
property is not an SQL or Order kind of thing: it just
addresses the mechanics of how data is transferred from
one place to another. 5 rows = 5 rows, content is not
relevant. Dunno what effect this will have on design view.

Another way is to just create an empty database, and link
to that. For a VFP database, it is a simple matter to rename
the folders or use a mapped drive while you are developing.

(david)

Pieter,

Thanks for the quick response. I've searched for information on the
maxrows property and can only seem to figure that it limits the results
to the first [n] records returned by the query. If that's the case,
would that mean I need to have an ORDER BY statement and guesstimate
the rows that might come back (i.e. how many sales transactions would
occur over how long a relevant sales period would be)?

With respect to #4, this would be my greatest desire, but it is a 3rd
party solution that is what it is.

To the extent that I can implement #2, I do.

For #3, how do you determine what fields are indexed in a VFP free
datatable?

Thanks,
marc

1) set the maxrows returned property
2) make sure to restrict to the data you need via WHERE
3) make sure the WHERE clause uses indexes - if possible
4) Move the big nasty to MSSQL or Oracle

using 1) is probably what you want to do <g>

Pieter



I've searched this group for an answer and think I have a sense of the
problem, but I would like to canvas the more knowledgeable among us
for
ideas.

Basically, I have created a few Passthrough Queries to pull data from
Visual FoxPro 6.0 free datatables (no stored procedures available)
into
an Access database. However, when I seek to add these queries into
the
Query Editor or open up a query with a passthrough query as a source,
it takes forever. My understanding is that Access needs to actually
execute the full passthrough query in order to obtain the field
information for the Editor.

Now, these passthrough queries are to very very large tables (upwards
of a gig) with a ton of records. Is there anyway to speed up Access
in
design mode to open up and/or add queries more quickly?

Thanks,
marc




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

--

 

[Marc]

Thanks to you both. I'll see what I can do.

marc

 

[aaron.kempf]

MDB against ODBC is a stupid idea that has had it's day.

Use Access Data Projects; it is a lot easier to manage than ODBC
bullshit.

-Aaron

 

[Marc]

Aaron,

While your point may be accurate, it was more of a comment than
directional. And I'm not sure if it's applicable or not in this
situation. I am not an incredibly advanced Access user and in
reviewing an article on Access Data Projects
(http://articles.techrepublic.com.com/5100-6329_11-5065669.html), I
don't believe that it would be useful to me.

Primarily, that's because the backend is NOT SQL Server, but rather VFP
6.0 free datatables (I had looked into creating stored procedures).
And trying to "upsize" would lead to additional design work.

marc

 

[John Vinson]

While your point may be accurate, it was more of a comment than
directional.

You should know that Aaron mainly seems to post on this newsgroup when
he's bashing JET or MDB's. He occasionally posts helpful comments...
but IMO this wasn't one of them.

John W. Vinson[MVP]

 

[aaron.kempf]

I'm just here to tell you kids that there is a better way.

re-fresh and re-link and update

an occassional 'i cant update an odbc source' message and I left ODBC
bullcrap 6 years ago.

ADP is a great frontend. A great backend. And a great reporting
platform.

Spit on anyone that uses MDB.

-Aaron