Jim, What he means is that if OrdHead.FreightPro is indexed, this could slow down the update because that index has to be updated along with the actual data. Indexes typically take longer to update than actual data.
Dennis ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Monday, December 07, 2009 11:09 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Bill, In your suggestion, would I be creating Indexes "On the fly" in the WHERE clause? Is that what you mean? Jim ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Monday, December 07, 2009 11:04 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Jim, Also, in general, an index or key on a column being updated will slow down the update (because the Index has to be updated for each row, too). An index or key on a column referenced only in the WHERE clause is likely to speed it up. Bill On Mon, Dec 7, 2009 at 11:57 AM, Dennis McGrath <[email protected]> wrote: Jim, Let us know how it works out. Dennis -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Monday, December 07, 2009 10:49 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS 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.
