Dennis, Thanks for the suggestions. I am changing my database and adding features so the primary key may be the route to go.
Jim -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Monday, December 07, 2009 10:40 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS 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.
