Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Passthrough Query SLOWS Access Query Editor

Discussion in 'Information Technology' started by Marc, Jul 28, 2006.

  1. Marc

    Marc
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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



    "Marc" <vermutmb@gmail.com> wrote in message
    news:1153949821.293115.270480@s13g2000cwa.googlegroups.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
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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



    "Marc" <vermutmb@gmail.com> wrote in message
    news:1153949821.293115.270480@s13g2000cwa.googlegroups.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
     
  5. Marc

    Marc
    Expand Collapse
    Guest

    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 <g>
    >
    > Pieter
    >
    >
    >
    > "Marc" <vermutmb@gmail.com> wrote in message
    > news:1153949821.293115.270480@s13g2000cwa.googlegroups.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. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Marc" <vermutmb@gmail.com> wrote in message
    news:1153956917.927395.74000@h48g2000cwc.googlegroups.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 <g>
    >>
    >> Pieter
    >>
    >>
    >>
    >> "Marc" <vermutmb@gmail.com> wrote in message
    >> news:1153949821.293115.270480@s13g2000cwa.googlegroups.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

    >
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Marc" <vermutmb@gmail.com> wrote in message
    news:1153956917.927395.74000@h48g2000cwc.googlegroups.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 <g>
    >>
    >> Pieter
    >>
    >>
    >>
    >> "Marc" <vermutmb@gmail.com> wrote in message
    >> news:1153949821.293115.270480@s13g2000cwa.googlegroups.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
     
  8. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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" <vermutmb@gmail.com> wrote in message
    news:1153956917.927395.74000@h48g2000cwc.googlegroups.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 <g>
    >>
    >> Pieter
    >>
    >>
    >>
    >> "Marc" <vermutmb@gmail.com> wrote in message
    >> news:1153949821.293115.270480@s13g2000cwa.googlegroups.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

    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "david epsom dot com dot au" <david@epsomdotcomdotau> 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
    > 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" <vermutmb@gmail.com> wrote in message
    > news:1153956917.927395.74000@h48g2000cwc.googlegroups.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 <g>
    >>>
    >>> Pieter
    >>>
    >>>
    >>>
    >>> "Marc" <vermutmb@gmail.com> wrote in message
    >>> news:1153949821.293115.270480@s13g2000cwa.googlegroups.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
     

Share This Page