Slowdown issues with Optimistic Table Buffering? 
Author Message
 Slowdown issues with Optimistic Table Buffering?

I have a database that I am using optimistic table buffering with.  As it
turns out in this case, currently only one record at a time is being
updated, but there could be several, thus table buffering instead of record
buffering.  For some reason, as the table gets larger, the updates appear to
slow down.  For instance, when the table was at about 10,000 items, the
application took about 6 seconds to process and insert 50 records (with a
table update after each record). By the time the table approached 50,000
items, the same process took upwards of 20 seconds for 50 records.
Ultimately, this table will have as much as 1,000,000 records in it.  If the
trend follows, the insertion of records is going to slow to a crawl.

Now, since the code must first check to see if the matching record already
exists (and then either updating or inserting), I have a few Rushmore
Optimized LOCATEs.  I have looked over them several times and do not any
slowdown here, which is unfortunate because that would have been an obvious
explanation (if the LOCATE was ignoring indexes).  I am looking over the
LOCATEs again, but I am now looking into the buffering to see if maybe that
is the problem.

So, does anyone know of any performance issues concerning optimistic table
buffering and either updating or appending one record at a time?  (BTW, this
table does have a primary key, so TABLEUPDATE() should be using that, or at
least I hope it is.)

--
Seairth Jacobs




Mon, 06 Oct 2003 23:27:58 GMT  
 Slowdown issues with Optimistic Table Buffering?
LOCATE ignore indexes and searches sequentially.
--
Andrea Mariottini
Quote:

> Newsgroup: microsoft.public.fox.vfp.dbc
> Data: Thu, 19 Apr 2001 11:27:58 -0400
> Oggetto: Slowdown issues with Optimistic Table Buffering?

> I have a database that I am using optimistic table buffering with.  As it
> turns out in this case, currently only one record at a time is being
> updated, but there could be several, thus table buffering instead of record
> buffering.  For some reason, as the table gets larger, the updates appear to
> slow down.  For instance, when the table was at about 10,000 items, the
> application took about 6 seconds to process and insert 50 records (with a
> table update after each record). By the time the table approached 50,000
> items, the same process took upwards of 20 seconds for 50 records.
> Ultimately, this table will have as much as 1,000,000 records in it.  If the
> trend follows, the insertion of records is going to slow to a crawl.

> Now, since the code must first check to see if the matching record already
> exists (and then either updating or inserting), I have a few Rushmore
> Optimized LOCATEs.  I have looked over them several times and do not any
> slowdown here, which is unfortunate because that would have been an obvious
> explanation (if the LOCATE was ignoring indexes).  I am looking over the
> LOCATEs again, but I am now looking into the buffering to see if maybe that
> is the problem.

> So, does anyone know of any performance issues concerning optimistic table
> buffering and either updating or appending one record at a time?  (BTW, this
> table does have a primary key, so TABLEUPDATE() should be using that, or at
> least I hope it is.)

> --
> Seairth Jacobs





Tue, 07 Oct 2003 15:45:29 GMT  
 Slowdown issues with Optimistic Table Buffering?
On Fri, 20 Apr 2001 09:45:29 +0200, Andrea Mariottini

Quote:

>LOCATE ignore indexes and searches sequentially.
>--
>Andrea Mariottini

Afraid i beg to differ. Locate uses Rushmore so it isn't 'ignoring'
indexes far from it.

JML



Tue, 07 Oct 2003 16:55:16 GMT  
 Slowdown issues with Optimistic Table Buffering?
I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore optimized)
but manual say:
"Sequentially searches the table for the first record that matches
the specified logical expression".
Moreover I done some tests and SEEK is much faster than LOCATE,
so I guessed "Sequentially" means "in current index order" only
and other indexes are ignored. So Rushmore applies only to
current index order.
If this is not the case why SEEK is better?

--
Andrea Mariottini

Quote:

> Organizzazione: -= Skynet Usenet Service =-
> Newsgroup: microsoft.public.fox.vfp.dbc
> Data: Fri, 20 Apr 2001 10:55:16 +0200
> Oggetto: Re: Slowdown issues with Optimistic Table Buffering?

> On Fri, 20 Apr 2001 09:45:29 +0200, Andrea Mariottini

>> LOCATE ignore indexes and searches sequentially.
>> --
>> Andrea Mariottini

> Afraid i beg to differ. Locate uses Rushmore so it isn't 'ignoring'
> indexes far from it.

> JML



Tue, 07 Oct 2003 18:19:28 GMT  
 Slowdown issues with Optimistic Table Buffering?
On Fri, 20 Apr 2001 12:19:28 +0200, Andrea Mariottini

Quote:

>I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore optimized)
>but manual say:
>"Sequentially searches the table for the first record that matches
>the specified logical expression".
>Moreover I done some tests and SEEK is much faster than LOCATE,
>so I guessed "Sequentially" means "in current index order" only
>and other indexes are ignored. So Rushmore applies only to
>current index order.
>If this is not the case why SEEK is better?

Andrea,

I didn't say SEEK was better or worse than LOCATE but i did say you're
statement :"LOCATE ignore indexes and searches sequentially." wasn't
right.
Albeit you'll need a rather big table to see the difference between:

SELECT customer
SET ORDER TO && Rushmore works faster when no tags active. I.O.W.
you'd better not help it along
LOCATE FOR customer.firstname = 'Andrea'

and

SET ORDER TO firstname
SEEK 'Andrea'

On the assumption of course there's and appropriate index tag on
firstname.

JML



Tue, 07 Oct 2003 20:31:11 GMT  
 Slowdown issues with Optimistic Table Buffering?
Further, LOCATE has a distinct advantage over SEEK, namely the implicit use
of multiple indexes.  For instance. For instance:

Index1 : cField1
Index2 : cField2

LOCATE FOR (cField1 == lcValue1) AND (cField2 == lcValue2)

whereas, SEEK would have to be:

Index 1: cField1 + cField2

SEEK lcValue1+lcValue2

Yes, outwardly, this appears like a no-brainer.  However, suppose you have a
table that is searched against in several ways (inventory tends to be one
like that for me).  You could either create an index for each combination of
search method used or you could just index on each field used.  Recently, I
rewrote searching on such a table to use LOCATEs instead of SEEKs.  The
number of indexes went from 12 down to 5.

Now, I don't know about anyone else, but I have never been fond of large
index files.  They aren't the most stable thing in the world.  Whenever I
can keep those files to a minimum, I am willing to sacarfice nearly
inperceptable performance penalties for it.  Further, this technique also
allows my coding to be more flexible, since I can search for records on any
combination of those indexed fields and not just on the ones that are in
compound indexes (not to mention their Rushmore-related usefulness in the
SQL commands, etc.)

There is one thing I would like, however:  and "IN cAlias" clause for the
LOCATE.  The one thing I do not like about LOCATE is that the table has to
be selected first.  I would be much better if I could just specify the
target table with the additional clause.

--
Seairth Jacobs



Quote:
> On Fri, 20 Apr 2001 12:19:28 +0200, Andrea Mariottini

> >I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore
optimized)
> >but manual say:
> >"Sequentially searches the table for the first record that matches
> >the specified logical expression".
> >Moreover I done some tests and SEEK is much faster than LOCATE,
> >so I guessed "Sequentially" means "in current index order" only
> >and other indexes are ignored. So Rushmore applies only to
> >current index order.
> >If this is not the case why SEEK is better?

> Andrea,

> I didn't say SEEK was better or worse than LOCATE but i did say you're
> statement :"LOCATE ignore indexes and searches sequentially." wasn't
> right.
> Albeit you'll need a rather big table to see the difference between:

> SELECT customer
> SET ORDER TO && Rushmore works faster when no tags active. I.O.W.
> you'd better not help it along
> LOCATE FOR customer.firstname = 'Andrea'

> and

> SET ORDER TO firstname
> SEEK 'Andrea'

> On the assumption of course there's and appropriate index tag on
> firstname.

> JML



Tue, 07 Oct 2003 21:55:02 GMT  
 Slowdown issues with Optimistic Table Buffering?
Further, LOCATE has a distinct advantage over SEEK : CONTINUE

Fred

Seairth Jacobs a crit :

Quote:

> Further, LOCATE has a distinct advantage over SEEK, namely the implicit use
> of multiple indexes.  For instance. For instance:

> Index1 : cField1
> Index2 : cField2

> LOCATE FOR (cField1 == lcValue1) AND (cField2 == lcValue2)

> whereas, SEEK would have to be:

> Index 1: cField1 + cField2

> SEEK lcValue1+lcValue2

> Yes, outwardly, this appears like a no-brainer.  However, suppose you have a
> table that is searched against in several ways (inventory tends to be one
> like that for me).  You could either create an index for each combination of
> search method used or you could just index on each field used.  Recently, I
> rewrote searching on such a table to use LOCATEs instead of SEEKs.  The
> number of indexes went from 12 down to 5.

> Now, I don't know about anyone else, but I have never been fond of large
> index files.  They aren't the most stable thing in the world.  Whenever I
> can keep those files to a minimum, I am willing to sacarfice nearly
> inperceptable performance penalties for it.  Further, this technique also
> allows my coding to be more flexible, since I can search for records on any
> combination of those indexed fields and not just on the ones that are in
> compound indexes (not to mention their Rushmore-related usefulness in the
> SQL commands, etc.)

> There is one thing I would like, however:  and "IN cAlias" clause for the
> LOCATE.  The one thing I do not like about LOCATE is that the table has to
> be selected first.  I would be much better if I could just specify the
> target table with the additional clause.

> --
> Seairth Jacobs




> > On Fri, 20 Apr 2001 12:19:28 +0200, Andrea Mariottini

> > >I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore
> optimized)
> > >but manual say:
> > >"Sequentially searches the table for the first record that matches
> > >the specified logical expression".
> > >Moreover I done some tests and SEEK is much faster than LOCATE,
> > >so I guessed "Sequentially" means "in current index order" only
> > >and other indexes are ignored. So Rushmore applies only to
> > >current index order.
> > >If this is not the case why SEEK is better?

> > Andrea,

> > I didn't say SEEK was better or worse than LOCATE but i did say you're
> > statement :"LOCATE ignore indexes and searches sequentially." wasn't
> > right.
> > Albeit you'll need a rather big table to see the difference between:

> > SELECT customer
> > SET ORDER TO && Rushmore works faster when no tags active. I.O.W.
> > you'd better not help it along
> > LOCATE FOR customer.firstname = 'Andrea'

> > and

> > SET ORDER TO firstname
> > SEEK 'Andrea'

> > On the assumption of course there's and appropriate index tag on
> > firstname.

> > JML

--
FE AVP&Cie
Juillenay, Europe

http://fox.wikis.com/wc.dll?Wiki~WillMicrosoftMarketVFP
http://fox.wikis.com/wc.dll?Wiki~_WillMicrosoftMarketVFP_Deutsch
http://fox.wikis.com/wc.dll?Wiki~_WillMicrosoftMarketVFP_Espanol
http://fox.wikis.com/wc.dll?Wiki~_WillMicrosoftMarketVFP_Italiano
http://fox.wikis.com/wc.dll?Wiki~_WillMicrosoftMarketVFP_Portugues
http://fox.wikis.com/wc.dll?Wiki~_WillMicrosoftMarketVFP_Francais



Wed, 08 Oct 2003 00:18:53 GMT  
 Slowdown issues with Optimistic Table Buffering?
Is VFP manual that say "sequentially".
I said SEEK is faster than LOCATE even if the search
if performed over the same indexed field, so because
in the case of SEEK the manual explicitly talk about
indexes I guessed LOCATE ignores.
Why should be SEEK faster if even LOCATE use indexes?

--
Andrea Mariottini

Quote:

> Organizzazione: -= Skynet Usenet Service =-
> Newsgroup: microsoft.public.fox.vfp.dbc
> Data: Fri, 20 Apr 2001 14:31:11 +0200
> Oggetto: Re: Slowdown issues with Optimistic Table Buffering?

> On Fri, 20 Apr 2001 12:19:28 +0200, Andrea Mariottini

>> I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore optimized)
>> but manual say:
>> "Sequentially searches the table for the first record that matches
>> the specified logical expression".
>> Moreover I done some tests and SEEK is much faster than LOCATE,
>> so I guessed "Sequentially" means "in current index order" only
>> and other indexes are ignored. So Rushmore applies only to
>> current index order.
>> If this is not the case why SEEK is better?

> Andrea,

> I didn't say SEEK was better or worse than LOCATE but i did say you're
> statement :"LOCATE ignore indexes and searches sequentially." wasn't
> right.
> Albeit you'll need a rather big table to see the difference between:

> SELECT customer
> SET ORDER TO && Rushmore works faster when no tags active. I.O.W.
> you'd better not help it along
> LOCATE FOR customer.firstname = 'Andrea'

> and

> SET ORDER TO firstname
> SEEK 'Andrea'

> On the assumption of course there's and appropriate index tag on
> firstname.

> JML



Wed, 08 Oct 2003 01:04:30 GMT  
 Slowdown issues with Optimistic Table Buffering?
Check out the help for VFP7 (if you have it),  you might be pleasantly
surprised!

--
Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.


Quote:
> There is one thing I would like, however:  and "IN cAlias" clause for the
> LOCATE.  The one thing I do not like about LOCATE is that the table has to
> be selected first.  I would be much better if I could just specify the
> target table with the additional clause.



Wed, 08 Oct 2003 01:11:27 GMT  
 Slowdown issues with Optimistic Table Buffering?
On Fri, 20 Apr 2001 19:04:30 +0200, Andrea Mariottini

Quote:

>Is VFP manual that say "sequentially".
>I said SEEK is faster than LOCATE even if the search
>if performed over the same indexed field, so because
>in the case of SEEK the manual explicitly talk about
>indexes I guessed LOCATE ignores.
>Why should be SEEK faster if even LOCATE use indexes?

Andrea,

In theory SEEK is faster, trouble is you'll need one hell of a table
to prove it (or a substandard pc)
So in the end it's rather academic......
JML



Wed, 08 Oct 2003 01:45:35 GMT  
 Slowdown issues with Optimistic Table Buffering?
The only reason that I can think of that LOCATE would be slower than SEEK
is:

SEEK uses one index only and it must be the active index (ignoring the ORDER
clause).  It can make assumptions that avoid any setup costs.

LOCATE can use one or more indexes.  Further, for Rushmore to work it's
magic, no index should be active.  This would mean that the LOCATE would
have to do some setup before performing the search.  Also, as the search
expression gets more complex (and more indexes are used), the LOCATE would
have to internally check across indexes until the correct index entry for
each index was found.  To me, this would mean that if you were to have an
expression which causes Rushmore to use three indexes, then the worst case
scenario would be three times as slow as performing a single SEEK (ignoring
overhead for compound indexes).  With various optimizations, it's probably a
bit faster than that and, as Jean-Marie commented, you wouldn't notice the
difference unless the table was sufficiently large enough and the LOCATE
expression was complex enough.

--
Seairth Jacobs



Quote:
> Is VFP manual that say "sequentially".
> I said SEEK is faster than LOCATE even if the search
> if performed over the same indexed field, so because
> in the case of SEEK the manual explicitly talk about
> indexes I guessed LOCATE ignores.
> Why should be SEEK faster if even LOCATE use indexes?

> --
> Andrea Mariottini


> > Organizzazione: -= Skynet Usenet Service =-
> > Newsgroup: microsoft.public.fox.vfp.dbc
> > Data: Fri, 20 Apr 2001 14:31:11 +0200
> > Oggetto: Re: Slowdown issues with Optimistic Table Buffering?

> > On Fri, 20 Apr 2001 12:19:28 +0200, Andrea Mariottini

> >> I know LOCATE has a NOOPTIMIZE clause (so by default is Rushmore
optimized)
> >> but manual say:
> >> "Sequentially searches the table for the first record that matches
> >> the specified logical expression".
> >> Moreover I done some tests and SEEK is much faster than LOCATE,
> >> so I guessed "Sequentially" means "in current index order" only
> >> and other indexes are ignored. So Rushmore applies only to
> >> current index order.
> >> If this is not the case why SEEK is better?

> > Andrea,

> > I didn't say SEEK was better or worse than LOCATE but i did say you're
> > statement :"LOCATE ignore indexes and searches sequentially." wasn't
> > right.
> > Albeit you'll need a rather big table to see the difference between:

> > SELECT customer
> > SET ORDER TO && Rushmore works faster when no tags active. I.O.W.
> > you'd better not help it along
> > LOCATE FOR customer.firstname = 'Andrea'

> > and

> > SET ORDER TO firstname
> > SEEK 'Andrea'

> > On the assumption of course there's and appropriate index tag on
> > firstname.

> > JML



Wed, 08 Oct 2003 02:43:44 GMT  
 Slowdown issues with Optimistic Table Buffering?
I've got it somewhere around here (beta 1).  But, I am guessing that this
means they added it.  In response, two things:

1) Woo Hoo!!!
2) Did they do the same for SCAN?

--
Seairth Jacobs



Quote:
> Check out the help for VFP7 (if you have it),  you might be pleasantly
> surprised!

> --
> Fred
> Microsoft Visual FoxPro MVP
> Please respond only to the newsgroups so that all may benefit.



> > There is one thing I would like, however:  and "IN cAlias" clause for
the
> > LOCATE.  The one thing I do not like about LOCATE is that the table has
to
> > be selected first.  I would be much better if I could just specify the
> > target table with the additional clause.



Wed, 08 Oct 2003 02:48:04 GMT  
 Slowdown issues with Optimistic Table Buffering?
Sorry, no joy to be found there.

--
Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.


Quote:
> I've got it somewhere around here (beta 1).  But, I am guessing that this
> means they added it.  In response, two things:

> 1) Woo Hoo!!!
> 2) Did they do the same for SCAN?

> --
> Seairth Jacobs




> > Check out the help for VFP7 (if you have it),  you might be pleasantly
> > surprised!

> > --
> > Fred
> > Microsoft Visual FoxPro MVP
> > Please respond only to the newsgroups so that all may benefit.



> > > There is one thing I would like, however:  and "IN cAlias" clause for
> the
> > > LOCATE.  The one thing I do not like about LOCATE is that the table
has
> to
> > > be selected first.  I would be much better if I could just specify the
> > > target table with the additional clause.



Wed, 08 Oct 2003 03:45:33 GMT  
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Update SQL On Buffer Mode Override = Optimistic Table Buffering

2. Help-optimistic table buffering TABLEUPDATE() failure

3. - How to SELECT from table buffered view without issuing TABLEUPDATE

4. Local table buffering and inbreeded procedure issue

5. optimistic row buffering

6. Optimistic buffering and tableupdate/revert

7. Optimistic and Pessimistic Buffering

8. Optimistic buffering limit?

9. Optimistic buffering in Fox Pro

10. VFP 5.0 : Views and optimistic buffering problem

11. HELP - Optimistic Row Buffering and Updating Expressions VFP 5.0a

12. optimistic buffering on field-level

 

 
Powered by phpBB® Forum Software