Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: Bronny
  Where is Bronny?
 Budapest
 Hungary
 Bronny
 Tags
Subject: RE: Speeding up
Thread ID: 111354 Message ID: 114222 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, November 27, 2006 5:22:54 AM         
   


> There is some view in *.dbc,
> which contains the information
> from the several parent and one child tables.
> The view has so many rows, that if we use the
> REQUERY("ViewName"), in order to see changes made
> by other users, it takes too much time.
> I tried to use (but it does not work)
> SELECT * FROM ChildTableName t ;
> WHERE !exists ;
> 	(select * FROM ViewName v ;
> 	 WHERE v.id=t.id) ;
> INTO CURSOR CAddedByAnotherUser
> SELECT CAddedByAnotherUser
> IF RECCOUNT('CAddedByAnotherUser')>=1
> 	SCAN
> 		SCATTER NAME oAddedByAnotherUser
> 		SELECT ViewName
> 		GATHER NAME oAddedByAnotherUser
> 		SELECT CAddedByAnotherUser
> 	ENDSCAN
> ENDIF
> 
> SELECT * FROM ViewName v ;
> WHERE !exists ;
> 	(select * FROM ChildTableName t ;
> 	 WHERE v.id=t.id) ;
> INTO CURSOR CDeletedByAnotherUser
> SELECT CDeletedByAnotherUser
> IF RECCOUNT('CDeletedByAnotherUser')>=1
> 	SCAN
> 		SCATTER NAME oDeletedByAnotherUser
> 		SELECT ViewName
> 		DELETE FOR id=oDeletedByAnotherUser.id
> 		SELECT CDeletedByAnotherUser
> 	ENDSCAN
> ENDIF
> 
> llChanged=TABLEUPDATE(.t.,.t.,'ViewName')
> 
> && ViewName property SendUpdates=.F.

>
>
> Could you advise what should I do to
> speed up the execution of requering ?
> Is there another method except REQUERY("ViewName") ?

You are comparing all the fields of ChildTable with all the fields of ViewName. This will not be optimised until all the fields have indexes. Try the following. I have not tested it.

SELECT * from ChildTableName t WHERE t.id NOT in (select id FROM ViewName) v INTO CURSOR CAddedByAnotherUser
SELECT * FROM ViewName v WHERE v.id NOT in (select id FROM ChildTableName) t INTO CURSOR CDeletedByAnotherUser

Also to find out the records added by other user, the value of ID should be more that the highest ID found in your view, so you can instead give

select ViewName
calc max(id) to maxid

SELECT * from ChildTableName t WHERE id>maxid INTO CURSOR CAddedByAnotherUser


HTH

Regards
Tushar




Regards
Tushar

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