You have the same speed issue whether you use the tables or the views. And the views just add overhead.
If indexes would be extremely inefficient (small number of values) I would not recommend using them. Is ShipDate Indexed in either table? That looks like a logical column to index. Does ordhead have a primary key? If so this will make things fly, if shipdate is indexed in BOLHead. UPDATE OrdHead SET FreightPro = T2.FreightPro FROM + OrdHead T1, BOLHead T2 WHERE T1.PrimaryKey in + (SELECT PrimaryKey from OrdHead WHERE Control# IN ( &vControlList )) + AND T1.ShipDate = T2.ShipDate AND + T1.Control# = T2.Control# Dennis McGrath -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Monday, December 07, 2009 10:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Dennis, I could do that but with the OrderHeader tables potentially having thousands of rows, the UPDATE may take a while to Find and update the few fields. I use indexes in other tables, so would you suggest indexes to speed up the process? Jim -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Monday, December 07, 2009 10:21 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Why bother with the views???? UPDATE OrdHead SET FreightPro = T2.FreightPro FROM + OrdHead T1, BOLHead T2 + WHERE T1.Control# IN ( &vControlList ) + AND T1.ShipDate = T2.ShipDate AND + T1.Control# = T2.Control# Dennis McGrath ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Monday, December 07, 2009 9:41 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Here is the code for UPDATING one view form the other view: The views are created and I can manually edit data with no problem. CREATE TEMP VIEW View_OrdHead (Control#, ShipDate, FreightPro) + AS SELECT Control#, Shipdate, FreightPro + FROM OrderHeader WHERE Control# IN (&vControlList) CREATE TEMP VIEW View_BOLHead (Control#, ShipDate, FreightPro) + AS SELECT Control#, Shipdate, FreightPro + FROM BOLHeader WHERE Control# IN (&vControlList) -- Update OrderHeader, OrderShip & BOLHeader w/ Shipdates, FreightPros then delete nulls. UPDATE View_OrdHead SET FreightPro = T2.FreightPro FROM + View_OrdHead T1, View_BOLHead T2 WHERE T1.ShipDate = T2.ShipDate AND + T1.Control# = T2.Control# This is where I get the I/O problem. Jim ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Saturday, December 05, 2009 9:28 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS If a single-table view has an "order by", that also makes it not editable. I agree with others -- something in your view makes it not-editable. Maybe post your view syntax. Karen Jim, Just because a view is a single table view does not automatically make it updateable. If for example it contains a GROUP BY statement then it is NOT updateable. There are other syntax elements that might make a single table view un-updateable.
