Bill et Al, The database statistics are also updated when you do a PACK.
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Bill Downall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Mon, December 7, 2009 11:19:43 AM Subject: [RBASE-L] - Re: I/O PROBLEMS WITH SIMPLE VIEWS Jim, Well, that's not what I was suggesting, just that it is fine to reference an indexed column in your WHERE clause, and it should be faster than an update without an indexed column in the WHERE clause. It's only a question when you have an index or key constraint on a column that is being updated. But now that you mention it, I have had experience both with a massive command that would work faster if you created the index, ran the command, and dropped the index (indexing a column referenced in the WHERE clause), and also with a situation where it was faster to DROP an index on a column that was being updated, then do the UPDATE, then recreate the index. You would have to test and time each scenario on a recently reloaded database to see if there would be a noticeable difference. I say recently reloaded, because at reload, R:BASE updates its internal statistics about the proximity of rows and duplicate values, and it uses those stats every time it tries to figure out whether to use an index, and which one to use. Bill On Mon, Dec 7, 2009 at 12:09 PM, Jim Belisle <[email protected]> wrote: > > > > > > > > > > >> >> >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. > > >
