Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Mike Yearwood
  Where is Mike Yearwood?
 Toronto
 Canada
 Mike Yearwood
 To: Martin Krivka
  Where is Martin Krivka?
 
 Czech Republic
 Martin Krivka
 Tags
Subject: RE: speed difference
Thread ID: 268122 Message ID: 268633 # Views: 24 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, July 13, 2010 4:47:14 PM         
   


> >
> > Are you making the common mistake of comparing performance on your local machine versus their LAN? If you have SET DELETED ON, and if you are getting full optimization, that means you have an INDEX ON DELETED() with the binary clause things might be better, but without it, such an index performs well on a local machine, but bad over a LAN, especially with larger tables.
> >
> > Mike Yearwood
> > Microsoft MVP Visual FoxPro 2008, 2009
> > We have enough youth. We need a fountain of smart!
> > There may be many ways to skin a cat, but there are very few right ways to do it.
>
>
> ...especially with larger tables with many deleted records.
>
> No, I do not make this mistake, I think.
>
> About deleted records. In old 2.x times I had set deleted off. If I remember good, tried indexing on 'deleted()' and 'for !deleted()' with very small impact on speed. Must say, that in these times, 10Mb speed LAN was 'hot news'. Later, on vfp7, I change my meaning and do 'deleted on'. I still think that the use of 'index on deleted()' depends on the characteristics of the table. If it contain small count of deleted records, can be more effective do not create index. The index file is transmitted over the network and must be updated on each change. But, as I said, it's only my meaning.
> Use sys(3054) to diagnose problem. Not to have all queries 'fully optimized' :-)
>
> Martin

I prefer to have a single approach to things if I can get it. In VFP 9 we gained a lot regarding deleted indexes. It used to be impossible to use a filtered index for optimization, but we can now do...

INDEX ON DELETED() FOR DELETED() TAG IXDELETED

which in my largest table only includes 13 records out of almost 500,000. I did a special index command to determine the size of the resulting cdx.

INDEX ON DELETED() FOR DELETED() TAG IXDELETED OF MYTEST.CDX

and the file size was only 3072 bytes! That index command was just for scientific curiosity. Do not use it! However back to the first one.

INDEX ON DELETED() FOR DELETED() TAG IXDELETED

is useful for Rushmore optimization of DELETED() and NOT DELETED() ...

SET DELETED ON
SELECT * FROM TABLE WHERE SOMEPK = SOMEVALUE

and

SET FILTER TO SOMEPK=SOMEVALUE AND NOT DELETED()

That means the network traffic will be almost nothing and we can SET DELETED and check optimization without using SET DELETED OFF.

Mike Yearwood
Microsoft MVP Visual FoxPro 2008, 2009
We have enough youth. We need a fountain of smart!
There may be many ways to skin a cat, but there are very few right ways to do it.

ENTIRE THREAD

speed difference Posted by John Peart @ 7/8/2010 5:23:56 PM
RE: speed difference Posted by Mike Yearwood @ 7/8/2010 7:25:22 PM
RE: speed difference Posted by John Peart @ 7/9/2010 11:23:28 AM
RE: speed difference Posted by Tamar Granor @ 7/8/2010 10:24:49 PM
RE: speed difference Posted by John Peart @ 7/9/2010 11:33:15 AM
RE: speed difference Posted by Martin Krivka @ 7/9/2010 12:19:30 PM
RE: speed difference Posted by John Peart @ 7/9/2010 12:28:02 PM
RE: speed difference Posted by Martin Krivka @ 7/9/2010 12:56:19 PM
RE: speed difference Posted by John Peart @ 7/9/2010 2:08:40 PM
RE: speed difference Posted by Leonid Lepin @ 7/9/2010 2:18:25 PM
RE: speed difference Posted by John Peart @ 7/9/2010 4:36:41 PM
RE: speed difference Posted by Mike Yearwood @ 7/9/2010 3:47:54 PM
RE: speed difference Posted by tushar @ 7/9/2010 4:25:03 PM
RE: speed difference Posted by John Peart @ 7/9/2010 4:55:34 PM
RE: speed difference Posted by Mike Yearwood @ 7/9/2010 3:35:51 PM
RE: speed difference Posted by Martin Krivka @ 7/9/2010 9:38:02 PM
RE: speed difference Posted by Mike Yearwood @ 7/13/2010 4:47:14 PM
RE: speed difference Posted by Martin Krivka @ 7/13/2010 5:11:23 PM
RE: speed difference Posted by Mike Yearwood @ 7/13/2010 5:28:13 PM
RE: speed difference Posted by Martin Krivka @ 7/13/2010 6:07:53 PM
RE: speed difference Posted by tom knauf @ 7/9/2010 12:45:31 PM
RE: speed difference Posted by John Peart @ 7/9/2010 12:50:28 PM
RE: speed difference Posted by Mike Yearwood @ 7/9/2010 3:39:32 PM
RE: speed difference Posted by John Peart @ 7/9/2010 5:01:17 PM