Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 To: Pratik Singh
  Where is Pratik Singh?
 Kota
 India
 Pratik Singh
 Tags
Subject: RE: SLOW WORKING ON LAN
Thread ID: 365586 Message ID: 365607 # Views: 48 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Forms
Date: Friday, December 28, 2012 2:33:05 PM         
   


> thanks for your rply
> sorry for missing well i am using vfp native database in application
> i am using below query for checking max date enter in order table
> VCODE ALSO INDEXED
>
> SELECT top 1 DATE AS TDATE FROM order WHERE order.vcode = M.VCODE ORDER BY DATE desc INTO CURSOR _CHECK
>
>
> >
> >
> > Hi,
> > Many questions on this topic lately. First thing that is unclear to me are you using VFP native database or another database like SQL Server?
> > You mention multi-tables with relationships. Then you also mention SQL Select statement. So I am not sure exactly what your database is.
> >
> > You mention some table filtering? Again, filtering from in a local cursor from a SQL Select statement or a filter on a native VFP table?
> >
> > Where is your VFP application EXE? On the local workstations or loading from a network shared folder?
> >
> > On a SQL Select statement like:
> >
SELECT MAX(BDATE) AS DATE FROM VENDOR INTO CURSOR _CHECK
You do realize that your are processing all the records in the Vendor table and this is not optimized even though you have an index
> > on BDATE. You need a WHERE condition.
> >
> >
> > So many will write the select statement like:
> >
Select max(InvDatetime) from InvoiceData

> > This query takes a very long time when it is executed against a table with 500,000 to 600,000 + Rows.
> > Even though there is an index on the InvoiceData table with InvDateTime as the first sorted column.
> >
> > To optimize this simple query you just have to add a Where clause like this:
> >
Select max(InvDateTime) from InvoiceData Where InvDateTime > DATETIME() - 30

> > In the above SQL statement you get the max in the past 30 days.
> > Or
> > You could try this below:
> >
Select max(InvDateTime) from InvoiceData Where InvDateTime > 0

> >
> >
> > In a VFP datatbase environment you would have:
> > SET MULTILOCKS ON
> > Probably ensure all tables are using buffermode - 5
> > Use transaction processing in updating multiple tables such in a parent-> child relationship.
> >
> > The main key point in multi-user network optimization is to only bring down through the network the exact
> > recordset you need at the time.
> >
> > In a mid-sized to large mutli-user application here are some additional pointers:
> > - Use SQL Select statements in your forms to create cursors and ensure your SQL statements are all optimized.
> > ie: Use a WHERE condition and ensure the WHERE field has an index on it where possible.
> > - Limit the recordsset rows being retrieved in your SQL statments to create cursors in form's LOAD event.
> > That is in many cases you do not need the entire recordset maybe only the past 30 to 60 days is enough.
> > Even in cases like your Vendor table.. follow this kind of logic. Lets say on a Purchase Order form you need
> > to select a Vendor and you have 10,000 vendors. You can load a ComboBox with only the VendorID and Name so initially
> > you are issuing a SQL Select statement to obtain only two fields out of the Vendor table. So now you pick the vendor
> > you wish for your PO and then a secondary SQL select statement obtains all the required fields just for this one
> > vendor to populate the information you need on your PO.
> > - Make sure the VFP EXE and runtime files are on the local workstations running the application.
> >
> > Always test network connections between workstations and server to ensure you do not have somethink like a flaky server
> > network card. Look for any other issues of you do have a slow connection. YOu can test this by copying a large file from
> > workstation to server to see how long say a 100 meg file takes to copy over.
> >
> > You can read a bit more on this topic from my other active thread here in the forum:
> > Thread ID: 364542 Message ID: 365230
> >
> > Pete "the IceMan", from the Great White North of Canada.
> > www.marathongriffincomputers.com
> > Home of the Canadian and US download for Chen's VFP C++ Compiler
> > http://www.marathongriffincomputers.com/VFP-C++-Compiler.html
>
>
>
> Learning never ends


Hi,

Yes you can use the TOP 1 with ORDER BY ASC/DESC to get the highest and lowest values out of a table.
You of course can use the the MAX() AND MIN() functions here.
One of the major differences is that the SELECT..TOP 1 will only ever return one record.
Where as joining against the max has a possibility of returning more than one record.

So tell us how you make out with your speed issues?

Pete "the IceMan", from the Great White North of Canada.
www.marathongriffincomputers.com
Home of the Canadian and US download for Chen's VFP C++ Compiler
http://www.marathongriffincomputers.com/VFP-C++-Compiler.html

ENTIRE THREAD

SLOW WORKING ON LAN Posted by Pratik Singh @ 12/28/2012 10:36:57 AM
RE: SLOW WORKING ON LAN Posted by Pete Sass @ 12/28/2012 11:42:17 AM
RE: SLOW WORKING ON LAN Posted by Pratik Singh @ 12/28/2012 1:18:20 PM
RE: SLOW WORKING ON LAN Posted by Pete Sass @ 12/28/2012 2:33:05 PM
RE: SLOW WORKING ON LAN Posted by Pratik Singh @ 12/28/2012 5:56:09 PM
RE: SLOW WORKING ON LAN Posted by David Mustakim @ 12/28/2012 6:07:19 PM