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. > > >
