I have a form that pulls info from an ODBC source via a Remote View. When I try to RUN my form, my entire project locks up. Is this because the info pulling from ODBC is too large How can I fix this Any ideas
Should I use a cursor instead of a remote view Or unselect fields in the Update fields selection
View, Query or Cursor?
Hot Topic
Visual FoxPro
- DELETE FOR-PACK ON BUFFERED TABLES
- Time format saved a character field
- creating tables
- Trap F10 in the KeyPress()
- How to center "Report Picture" in a FoxPro 8.0 Report
- Registering a Visual FoxPro driver in SQL
- records disappear forom tables
- New to VFP please recomend any good books
- Old Fox Pro Code Please Help
- Using a Lan Connection on Visual FoxPro 9
View, Query or Cursor?
zerep
There is a connection overhead. Past that the amount data comes into play. You should create parametric queries. ie:
select * from myTable && this one requests all data
* Next should be familiar to you:) - Requests a subset of data
select * from myTable where hire_date between m.ldStart and m.ldEnd
Latter one is parametric and when view is used (or requeried) if m.ldStart and m.ldEnd is not available it prompts for the values.
Despite parametric queries data still might be large or your need might be absolutely nonparametric query. To speed it up you use fetching. Data is fetched in blocks as needed. See FetchSize (you might also find it under Tools\Options\Remote Data). However don't take this wrong and test before you pick. Depending on your need this might be the slowest way. Test with Synch and Asynch connection.
During form's load you might not need any data. Then you set NoDataOnLoad property of cursor object or NoData clause of 'use' command.
PS: These info would help you only as a starter. The topic itself is huge and there is also SQLPassThrough, CursorAdapter (VFP8 and up) and such.
Becky Weiss - MSFT
{ oj ... } syntax is depreceated as far as I know. Use left join, inner join ... instead. You're selecting all records, I didn't see a filter in SQL.
Anyway if it's executing in RV designer right away and hanging in your form then it might be something else in your form. ie: Multiple RV's to same backend and DBSetProp(ThisView,"View","ShareConnection",.F.). Try setting it to .T.
daniel qi
sdsachs
Wonder why that had any bearing on it...
bryant1410
Here is my SQL. Could the problem be in my DBSetProp
SELECT
Master_prm_employee.Employee,;Master_prm_employee.Employee_Name, Master_prm_employee.
Title,;Master_prm_employee.Address_1, Master_prm_employee.Address_2,;
Master_prm_employee.City, Master_prm_employee.State,;
Master_prm_employee.Zip_Code, Master_prm_employee.Phone_Number,;
Master_prm_employee.Social_Security_Number,;
Master_prm_employee.Gender, Master_prm_employee.Birth_Date,;
Master_prm_employee.Ethnic, Master_prm_employee.Pay_ID,;
Master_prm_employee.Temporary, Master_prm_employee.Part_Time,;
Master_prm_employee.Full_Time,;
Master_prm_employee_custom_fields.Spouse,;
Master_prm_employee_custom_fields.Division,;
Master_prm_employee_custom_fields.Company_Vehical,;
Master_prm_employee.Hire_Date, Master_prm_employee.Termination_Date;
FROM ;{oj MASTER_PRM_EMPLOYEE Master_prm_employee ; LEFT OUTER JOIN MASTER_PRM_EMPLOYEE_CUSTOM_FIELDS Master_prm_employee_custom_fields ; ON Master_prm_employee.Employee = Master_prm_employee_custom_fields.Employee};
ORDER BY Master_prm_employee.EmployeeDBSetProp
(ThisView,"View","SendUpdates",.F.)DBSetProp
(ThisView,"View","BatchUpdateCount",1)DBSetProp
(ThisView,"View","CompareMemo",.T.)DBSetProp
(ThisView,"View","FetchAsNeeded",.F.)DBSetProp
(ThisView,"View","FetchMemo",.T.)DBSetProp
(ThisView,"View","FetchSize",1)DBSetProp
(ThisView,"View","MaxRecords",-1)DBSetProp
(ThisView,"View","Prepared",.F.)DBSetProp
(ThisView,"View","ShareConnection",.F.)DBSetProp
(ThisView,"View","AllowSimultaneousFetch",.F.)DBSetProp
(ThisView,"View","UpdateType",1)DBSetProp
(ThisView,"View","UseMemoSize",255)DBSetProp
(ThisView,"View","WhereType",3)DBSetProp
(ThisView+".employee","Field","DataType","C(15)")DBSetProp
(ThisView+".employee","Field","UpdateName","MASTER_PRM_EMPLOYEE.Employee")DBSetProp
(ThisView+".employee","Field","KeyField",.T.)DBSetProp
(ThisView+".employee","Field","Updatable",.F.)DBSetProp
(ThisView+".employee_name","Field","DataType","C(35)")DBSetProp
(ThisView+".employee_name","Field","UpdateName","MASTER_PRM_EMPLOYEE.Employee_Name")DBSetProp
(ThisView+".employee_name","Field","KeyField",.F.)DBSetProp
(ThisView+".employee_name","Field","Updatable",.F.)DBSetProp
(ThisView+".title","Field","DataType","C(30)")DBSetProp
(ThisView+".title","Field","UpdateName","MASTER_PRM_EMPLOYEE.Title")DBSetProp
(ThisView+".title","Field","KeyField",.F.)DBSetProp
(ThisView+".title","Field","Updatable",.F.)DBSetProp
(ThisView+".address_1","Field","DataType","C(30)")DBSetProp
(ThisView+".address_1","Field","UpdateName","MASTER_PRM_EMPLOYEE.Address_1")DBSetProp
(ThisView+".address_1","Field","KeyField",.F.)DBSetProp
(ThisView+".address_1","Field","Updatable",.F.)DBSetProp
(ThisView+".address_2","Field","DataType","C(30)")DBSetProp
(ThisView+".address_2","Field","UpdateName","MASTER_PRM_EMPLOYEE.Address_2")DBSetProp
(ThisView+".address_2","Field","KeyField",.F.)DBSetProp
(ThisView+".address_2","Field","Updatable",.F.)DBSetProp
(ThisView+".city","Field","DataType","C(15)")DBSetProp
(ThisView+".city","Field","UpdateName","MASTER_PRM_EMPLOYEE.City")DBSetProp
(ThisView+".city","Field","KeyField",.F.)DBSetProp
(ThisView+".city","Field","Updatable",.F.)DBSetProp
(ThisView+".state","Field","DataType","C(4)")DBSetProp
(ThisView+".state","Field","UpdateName","MASTER_PRM_EMPLOYEE.State")DBSetProp
(ThisView+".state","Field","KeyField",.F.)DBSetProp
(ThisView+".state","Field","Updatable",.F.)DBSetProp
(ThisView+".zip_code","Field","DataType","C(10)")DBSetProp
(ThisView+".zip_code","Field","UpdateName","MASTER_PRM_EMPLOYEE.Zip_Code")DBSetProp
(ThisView+".zip_code","Field","KeyField",.F.)DBSetProp
(ThisView+".zip_code","Field","Updatable",.F.)DBSetProp
(ThisView+".phone_number","Field","DataType","C(15)")DBSetProp
(ThisView+".phone_number","Field","UpdateName","MASTER_PRM_EMPLOYEE.Phone_Number")DBSetProp
(ThisView+".phone_number","Field","KeyField",.F.)DBSetProp
(ThisView+".phone_number","Field","Updatable",.F.)DBSetProp
(ThisView+".social_security_number","Field","DataType","C(11)")DBSetProp
(ThisView+".social_security_number","Field","UpdateName","MASTER_PRM_EMPLOYEE.Social_Security_Number")DBSetProp
(ThisView+".social_security_number","Field","KeyField",.F.)DBSetProp
(ThisView+".social_security_number","Field","Updatable",.F.)DBSetProp
(ThisView+".gender","Field","DataType","C(20)")DBSetProp
(ThisView+".gender","Field","UpdateName","MASTER_PRM_EMPLOYEE.Gender")DBSetProp
(ThisView+".gender","Field","KeyField",.F.)DBSetProp
(ThisView+".gender","Field","Updatable",.F.)DBSetProp
(ThisView+".birth_date","Field","DataType","D")DBSetProp
(ThisView+".birth_date","Field","UpdateName","MASTER_PRM_EMPLOYEE.Birth_Date")DBSetProp
(ThisView+".birth_date","Field","KeyField",.F.)DBSetProp
(ThisView+".birth_date","Field","Updatable",.F.)DBSetProp
(ThisView+".ethnic","Field","DataType","C(20)")DBSetProp
(ThisView+".ethnic","Field","UpdateName","MASTER_PRM_EMPLOYEE.Ethnic")DBSetProp
(ThisView+".ethnic","Field","KeyField",.F.)DBSetProp
(ThisView+".ethnic","Field","Updatable",.F.)DBSetProp
(ThisView+".pay_id","Field","DataType","C(10)")DBSetProp
(ThisView+".pay_id","Field","UpdateName","MASTER_PRM_EMPLOYEE.Pay_ID")DBSetProp
(ThisView+".pay_id","Field","KeyField",.F.)DBSetProp
(ThisView+".pay_id","Field","Updatable",.F.)DBSetProp
(ThisView+".temporary","Field","DataType","L")DBSetProp
(ThisView+".temporary","Field","UpdateName","MASTER_PRM_EMPLOYEE.Temporary")DBSetProp
(ThisView+".temporary","Field","KeyField",.F.)DBSetProp
(ThisView+".temporary","Field","Updatable",.F.)DBSetProp
(ThisView+".part_time","Field","DataType","L")DBSetProp
(ThisView+".part_time","Field","UpdateName","MASTER_PRM_EMPLOYEE.Part_Time")DBSetProp
(ThisView+".part_time","Field","KeyField",.F.)DBSetProp
(ThisView+".part_time","Field","Updatable",.F.)DBSetProp
(ThisView+".full_time","Field","DataType","L")DBSetProp
(ThisView+".full_time","Field","UpdateName","MASTER_PRM_EMPLOYEE.Full_Time")DBSetProp
(ThisView+".full_time","Field","KeyField",.F.)DBSetProp
(ThisView+".full_time","Field","Updatable",.F.)DBSetProp
(ThisView+".spouse","Field","DataType","C(15)")DBSetProp
(ThisView+".spouse","Field","UpdateName","MASTER_PRM_EMPLOYEE_CUSTOM_FIELDS.Spouse")DBSetProp
(ThisView+".spouse","Field","KeyField",.F.)DBSetProp
(ThisView+".spouse","Field","Updatable",.F.)DBSetProp
(ThisView+".division","Field","DataType","C(20)")DBSetProp
(ThisView+".division","Field","UpdateName","MASTER_PRM_EMPLOYEE_CUSTOM_FIELDS.Division")DBSetProp
(ThisView+".division","Field","KeyField",.F.)DBSetProp
(ThisView+".division","Field","Updatable",.F.)DBSetProp
(ThisView+".company_vehical","Field","DataType","C(11)")DBSetProp
(ThisView+".company_vehical","Field","UpdateName","MASTER_PRM_EMPLOYEE_CUSTOM_FIELDS.Company_Vehical")DBSetProp
(ThisView+".company_vehical","Field","KeyField",.F.)DBSetProp
(ThisView+".company_vehical","Field","Updatable",.F.)DBSetProp
(ThisView+".hire_date","Field","DataType","D")DBSetProp
(ThisView+".hire_date","Field","UpdateName","MASTER_PRM_EMPLOYEE.Hire_Date")DBSetProp
(ThisView+".hire_date","Field","KeyField",.F.)DBSetProp
(ThisView+".hire_date","Field","Updatable",.F.)DBSetProp
(ThisView+".termination_date","Field","DataType","D")DBSetProp
(ThisView+".termination_date","Field","UpdateName","MASTER_PRM_EMPLOYEE.Termination_Date")DBSetProp
(ThisView+".termination_date","Field","KeyField",.F.)DBSetProp
(ThisView+".termination_date","Field","Updatable",.F.)mike_the_
Not sure what the backend database is in your case, but maybe you can check the SQL query code in the backend first to make sure the query is functioning correctly, and there are no serious performance issues.
SQL Server for instance has Query Analyzer to run queries and Profiler which will show you what VFP is sending it when you execute the remote view.
Claudio Biancardi
Hi SweetCoder,
You haven't said anything about the SQL for your view, or the number of records you're retrieving.
When working with remote data you definitely don't want to Select * From BigTable - you need to limit the data in some way and present to the user only as much data as he really needs to see. The query usually has limits in a Where clause such as Where CustomerNumber = ....