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


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



Hi Mike,
i'm not sure that you are right. Your index is small, because have FOR clause. When it will be full (without FOR), it must be much bigger (500.000 records...). I try it with 250.000 records, it have 1.29 MB.

Index with FOR clause cannot by useful in Rushmore. Maybe i'm wrong, but it's my experience. Also vfp help says: "If you use a FOR clause in the INDEX command, Rushmore cannot use the index for optimization".

So, that mean that we have two choice's:
1) index on deleted() ... with cca 3MB index file transmitted over network,
2) do not index ... with some small penalty (13 of 500.000) records.

Martin

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