Re: paging

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Anders Altberg (x_pragma_at_telia.com)
Date: 08/24/04

  • Next message: phil: "RE: Copying FoxPro to SQL Server" 
    Date: Tue, 24 Aug 2004 18:46:33 +0200

    Hi Olaf
    I misread you to say that VFP always has to drag the whole table of the net
    before it can apply a search condition in a request for data.
    I don't think I can agree with this opion though:
    > This is complicated, but the way to go
    > with a file based database. I think it's the
    > reason, why something like LIMIT isn't
    > implemented in Foxpro, and why you
    > don't see something like paging in foxpro
    > desktop applications.

    I would think that FoxPro has always implemented the purpose of the MySQL's
    LIMIT clause in its Xbase language, SKIP 20, NEXT 20, and so on. But it's
    not implemented in VFP SQL and not in Standard SQL either; the very idea is
    anti-SQL as being a language that works on the principle of the set-based
    relational tables. There are no row numbers in a set. So it would have
    nothing to do with being file based, on the contrary, it's MySQL that views
    tables a row-numbered files.
    Cheers,
    -Anders

    "Olaf Doschke" <T2xhZi5Eb3NjaGtlQFNldG1pY3MuZGU@strconv.14> wrote in message
    news:OWZz1DeiEHA.3320@TK2MSFTNGP11.phx.gbl...
    > Concerning my observation, that ordering is done
    > when the resultset is fully loaded and not beforehand:
    > > Rushmore? <g>.
    >
    > Test this:
    >
    > use in Select("foxcode")
    > use in Select("foxcodeindexed")
    > select 0
    > Use (Home()+"foxcode")
    > Cd (GetEnv("TEMP"))
    > Set Safety off
    > Erase (".\foxcodeindexed.*")
    > Set Safety on
    >
    > Copy To foxcodeindexed
    > use in Select("foxcode")
    > select 0
    > use foxcodeindexed In 0
    > Index On abbrev tag abbrev
    >
    > LOCAL loFunctions
    > loFunctions = CREATEOBJECT("Functions")
    >
    > Set order To abbrev
    >
    > Sys(3054,11)
    > *for comparison: all
    > select *;
    > from foxcodeindexed;
    > order by abbrev;
    > into cursor crsOrdered nofilter
    >
    > * and just one page:
    > select loFunctions.genkey() as nRecNum,;
    > Recno() as nRecno, *;
    > from foxcodeindexed;
    > order by abbrev ;
    > having between(nRecNum,51,60);
    > into cursor crsPage nofilter
    >
    > DEFINE CLASS Functions AS Custom
    > * due to the observation of one
    > * "extra" call per Select, begin
    > * count at -1:
    > nCount=-1
    > PROCEDURE genkey()
    > this.ncount = this.ncount+1
    > RETURN this.ncount
    > ENDPROC
    > ENDDEFINE
    >
    > Sys(3054) says, there is no optimization done.
    > No wonder, as one can assume from the help
    > on sys(3054) rushmore optimizes only filter
    > conditions and join conditions, no more no
    > less it uses the indexes for that, but not for
    > optimizing order by. That may be used by the
    > foxpro SQL engine, but is not within the scope
    > of rushmore.
    >
    > No question: Indexes are for ordering and help
    > a lot. But If I select the way I do with the
    > GenKey() Function, and that doesn't number
    > the records in the order they are through the
    > order clause, this suggests to me, that the ordering
    > is done after numbering and retrieving the resultset.
    > compare nRecNo with nRecnNum, you see?
    > Numbering is first done, then ordering, which
    > doesn't enable paging this way, we've
    > won nothing with GenKey() and proven, that
    > ordering is done later...
    >
    > Bye, Olaf.
    >
    >

  • Next message: phil: "RE: Copying FoxPro to SQL Server"