Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ugur YILMAZ
  Where is Ugur YILMAZ?
 Istanbul
 Turkey
 Ugur YILMAZ
 To: Jim Carls
  Where is Jim Carls?
 Nashville
 Tennessee - United States
 Jim Carls
 Tags
Subject: RE: Slow SELECT with subquery
Thread ID: 288745 Message ID: 288829 # Views: 24 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 24, 2010 1:05:01 PM         
   


Hello JIM;

In my personal experience; when I am using correlated subqueries by 3 or more Join ( especially network environment ) the query responding time is needs long time.

I am using similar effect on my network environment :

1.) Desing a table for remote queries on your server similar like this : "TableRemoteRequest"

requestID (GUID), ClientTime (DateTime), ServerStarttime(DateTime), ServerStoptime(DateTime), Query(Memo), TmpResultTable(Memo)

2.) Make a ServerVFP project that just handle your "TableRemoteRequest" and add a Timer event for your table : "TableRemoteRequest"

3.) Request the new query for your server from your cliend and insert the SQL Query on "TableRemoteRequest" what you need;

4.) Your ServerVFP exe handle new requests by queue on Query(Memo) field and then your SQL finish, create "TmpResultTable(Memo)" from "Query(Memo)"

5.) Handle by a Timer on "TableRemoteRequest" table ServerStoptime(DateTime) and TmpResultTable(Memo) fields if process finished on ServerVFP

6.) Use From ClientProgram TmpResultTable(Memo)

By that way some complex queries ( over than 1 minute or over ) I obtain 3 - 7 seconds.

Ugur
Amateur Programmer
VFP 9 SP2, Win7 64 Bit

ENTIRE THREAD

Slow SELECT with subquery Posted by Jim Carls @ 12/23/2010 4:06:07 PM
RE: Slow SELECT with subquery Posted by Tamar Granor @ 12/23/2010 10:04:55 PM
RE: Slow SELECT with subquery Posted by Jim Carls @ 12/23/2010 11:59:20 PM
RE: Slow SELECT with subquery Posted by Tamar Granor @ 12/24/2010 10:38:28 PM
RE: Slow SELECT with subquery Posted by Ugur YILMAZ @ 12/24/2010 1:05:01 PM
RE: Slow SELECT with subquery Posted by Cetin Basoz @ 12/24/2010 4:16:30 PM
RE: Slow SELECT with subquery Posted by Jim Carls @ 12/24/2010 8:34:58 PM