Passthrough Query SLOWS Access Query Editor

#1 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14006-passthrough-query-slows-access-query-editor.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
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

#2 (**permalink**) 28-Jul-2006, 08:42 AM

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

Pieter

"Marc" wrote in message
news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> Query Editor or open up a query with a passthrough query as a source,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> 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
>

#3 (**permalink**) 28-Jul-2006, 08:42 AM

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

Pieter

"Marc" wrote in message
news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> 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

#4 (**permalink**) 28-Jul-2006, 08:42 AM

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 wrote:
> 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
>
> Pieter
>
>
>
> "Marc" wrote in message
> news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> > 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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> > 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

#5 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
shortcode no - to contact me -direct about this
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006

"Marc" wrote in message
news:1153956917.927395.74000@h48g2000cwc.googlegro ups.com...
> 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 wrote:
>> 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
>>
>> Pieter
>>
>>
>>
>> "Marc" wrote in message
>> news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
>> > 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
>

#6 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
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

"Marc" wrote in message
news:1153956917.927395.74000@h48g2000cwc.googlegro ups.com...
> 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 wrote:
>> 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
>>
>> Pieter
>>
>>
>>
>> "Marc" wrote in message
>> news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
>> > 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
>

--
----------------------------------------
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

#7 (**permalink**) 28-Jul-2006, 08:42 AM

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)

"Marc" wrote in message
news:1153956917.927395.74000@h48g2000cwc.googlegro ups.com...
> 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 wrote:
>> 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
>>
>> Pieter
>>
>>
>>
>> "Marc" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
>> news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
>> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
>> > 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
>

#8 (**permalink**) 28-Jul-2006, 08:42 AM

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

Pieter

"david epsom dot com dot au" wrote in message
news:%23wAtSLSsGHA.3832@TK2MSFTNGP06.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
> 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)
>
> "Marc" wrote in message
> news:1153956917.927395.74000@h48g2000cwc.googlegro ups.com...
>> 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 wrote:
>>> 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
>>>
>>> Pieter
>>>
>>>
>>>
>>> "Marc" wrote in message
>>> news:1153949821.293115.270480@s13g2000cwa.googlegr oups.com...
>>> > 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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14006
>>> > 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
>>
>
>

--
----------------------------------------
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