Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Simon Cropper
  Where is Simon Cropper?
 Melbourne
 Australia
 Simon Cropper
 To: Luca Moioli
  Where is Luca Moioli?
 Venezia
 Italy
 Luca Moioli
 Tags
Subject: RE: Rushmore for SCAN
Thread ID: 345230 Message ID: 345377 # Views: 47 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, May 23, 2012 3:16:40 AM         
   


> Dear friends,
> I have a typical SCAN used many times in application:
>
>
> SELECT Table1
> SCAN ALL FOR Field1=m.Val1 AND ALLTRIM(Field2)==m.Val2
> 

>
> I have SET EXACT OFF and I would like optimize at best performaces with Rushmore.
> Table1 is indexes on DELETED(), Field1 and Field2.
>
> Is this the best condition for optimization?
> Should Field2 be indexed on ALLTRIM(Field2)?
> I have always avoided functions in indexes like
>
>
> INDEX ON ALLTRIM(Field2)
> 

>
> because I fear they slow performaces.
> Thank you very much

Luca,

There are many ways of solving this problem.

What you should do is benchmark it yourself.

Find your biggest dataset you have, access it using the SCAN command in the various forms suggested and record how long it takes for the scan to finish.

Use VFP's native time functions to record the start and finish times.

You will be surprised at the results. Using different combinations of indexes, functions, etc can have a dramatic impact on performance.

Experiment!

Half a hour testing now can save hours of computational time later.

As an example, years ago I was working with a developer that was benchmarking some SQL. He was creating an inner join from memory but that aside the process took hours to complete. Alternatively he ran 3-4 separate SQL commands to acheive the same thing, ensuring indexes were available for each primary and foreign key and the entire process was done in several minutes. Now that was a performance boost!


Simon Cropper

ENTIRE THREAD

Rushmore for SCAN Posted by Luca Moioli @ 5/21/2012 7:46:07 AM
RE: Rushmore for SCAN Posted by mk sharma @ 5/21/2012 7:55:07 AM
RE: Rushmore for SCAN Posted by tushar @ 5/21/2012 8:56:21 AM
RE: Rushmore for SCAN Posted by Chuanbing Chen @ 5/22/2012 12:47:40 AM
RE: Rushmore for SCAN Posted by Simon Cropper @ 5/23/2012 3:16:40 AM
RE: Rushmore for SCAN Posted by Luca Moioli @ 5/23/2012 7:06:58 AM
RE: Rushmore for SCAN Posted by mk sharma @ 5/23/2012 7:27:19 AM
RE: Rushmore for SCAN Posted by Luca Moioli @ 5/23/2012 9:30:59 AM
RE: Rushmore for SCAN Posted by mk sharma @ 5/23/2012 9:35:40 AM