Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: William Sanders
  Where is William Sanders?
 Houston
 Texas - United States
 William Sanders
 To: Bronny
  Where is Bronny?
 Budapest
 Hungary
 Bronny
 Tags
Subject: RE: Speeding up
Thread ID: 111354 Message ID: 113133 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Wednesday, November 15, 2006 5:33:46 PM         
   


> > 1. this is a view to what ? VFP data ? ms-sql data ? mySql data ?
> > 2. is this a parameterized view ? a remote view ? a parameterized remote view ?
> >
> > Regards [Bill]
> > ---------------------
> > William Sanders / Electronic Filing Group
> >
> >

>
> This is a view to VFP9 DBF tables.
> This is a not parameterized, local view.


OK - i think i get the picture now.

First - you are doing a select against a view. Allowed, but mostly (ime) a time waster . There's many reasons why its a time waster, and I won't get into them now.

Second, are you certain you have indexes on fields that are used in your where and join clauses, that MATCH ?? if you don't, then vfp will not respect any index.

I would suggest you back up a bit, and create a new view without using any other views. Also index those fields !

Without a good index, RushMore technology will NOT kick in .

There's a section in the help file about optimization - take a good read,
then map out your dbc by hand, on paper. You should see areas in your database for optimization. Then, when yer comfortable, make the changes.

But - you have this complaint because of users in a production system.

I suggest (if you haven't done so already) to make a copy of the production system ELSEWHERE (another server, another folder, another mapped drive, etc etc) and
try to make a 'development' set of the codebase, that you can use to test with.

For an example, let me break down your code block a bit..
SELECT * FROM ChildTableName t ;
WHERE !exists ;
(select * FROM ViewName v ;
 WHERE v.id=t.id) ;
INTO CURSOR CAddedByAnotherUser



1. assume viewname view is not populated at that moment .
The first that that happens is that it VFP will 'pull the view' and
populate it with records that match the original conditions in the original view. At this moment, I don't know whtat those original conditions are. Once this view is pulled, it is now 'static' to the process - you are not dealing with live data any more, so real time changes that occur after your processing to 'pull the view' will not be seen (this is a side effect and mostly people live with it)

2. Now that the view is populated, this query will try to match up records that do not exist , based on the values of two id fields.

3. once the match is finally made, another hit is done to retrieve all of the records from the child table that match the selection criteria.

4. the final comparison will be on 'all fields in a row of child table' not existing in 'all fields in a row of the view' Do you REALLY need to do a field by field comparison at this stage ? VFP will do it, sure, but it adds to the overhead.
OTOH, if you are trying to actually MAKE this comparison, you'll still need to have the comparison (ya, i know, redundant, but there it is). If the viewname view contains more fields than the childtable, then you are sucking in more data that you need. This comparison for !exists occurs based on data being different based on the same FIELD names, ya..

so far, you've done a lot of iterations.

Back up a bit, what are you really wanting to get as a result set ?
a set of records from the ChildTable that do not exist in the ViewName view,
yes? Well, what is that local view a view of ? do you really need a view in the first place ?

If this view is a subset of something, or a join of two or more tables, then back up and deconstruct what you are trying to get to.
Do you need all of that data in the view ? I'm guessing not, as the only thing you are using for comparison is the ID field.

hth, and Good Luck !

Regards [Bill]
---------------------
William Sanders / Electronic Filing Group



ENTIRE THREAD

Speeding up Posted by Bronny @ 10/27/2006 7:34:39 AM
RE: Speeding up Posted by Boudewijn Lutgerink @ 10/27/2006 7:57:48 AM
RE: Speeding up Posted by Bronny @ 10/27/2006 9:32:34 AM
RE: Speeding up Posted by Boudewijn Lutgerink @ 10/27/2006 9:53:53 AM
RE: Speeding up Posted by Bronny @ 10/27/2006 10:17:10 AM
RE: Speeding up Posted by Boudewijn Lutgerink @ 10/27/2006 11:38:05 AM
RE: Speeding up Posted by Bronny @ 11/15/2006 4:37:21 AM
RE: Speeding up Posted by Ken Murphy @ 11/15/2006 5:08:59 AM
RE: Speeding up Posted by Bronny @ 11/15/2006 5:27:31 AM
RE: Speeding up Posted by Ken Murphy @ 11/15/2006 5:29:00 AM
RE: Speeding up Posted by William Sanders @ 10/27/2006 11:17:38 PM
RE: Speeding up Posted by Bronny @ 11/15/2006 4:52:36 AM
RE: Speeding up Posted by William Sanders @ 11/15/2006 5:33:46 PM
RE: Speeding up Posted by William Sanders @ 11/18/2006 1:49:05 AM
RE: Speeding up Posted by Bronny @ 11/23/2006 12:55:11 PM
RE: Speeding up Posted by William Sanders @ 11/27/2006 1:15:13 AM
RE: Speeding up Posted by Bronny @ 11/28/2006 6:22:47 AM
RE: Speeding up Posted by tushar @ 11/27/2006 5:22:54 AM
RE: Speeding up Posted by Bronny @ 11/28/2006 6:31:14 AM