Gary,
A bit of a side note on the Project.... where limit = 0. The PROJECT command places a full table lock on the source table where INSERT does not. (This is a good thing. You do not want a table definition being changed while a PROJECT is executing) I had ran into conflicts in a multi user setting when my app was PROJECTING larger temp tables for users that had several hundred rows of data. I would get locking conflicts on occasion due to the amount of time it took for the temp table to project with all the rows. Using LIMIT = 0, the table projects immediately and then the INSERT can run without any concern for a table lock. I believe in Marc's case, the number of rows is small and less time is probably taken. However if the source table is very large and the WHERE clause takes a bit of time to retrieve even a small number of rows, there could still be a locking conflict. So with this in mind, it was my understanding that in a multi user environment, it was "better practice" to PROJECT ... LIMIT = 0 and INSERT versus PROJECT with data. True, it is one less line of code, but possibly avoids a lock conflict in a multi user situation. -Bob ----- Original Message ----- From: " MDRD " <mdrd375@ sbcglobal .net> To: "RBASE-L Mailing List" < rbase -l@ rbase .com> Sent: Wednesday, May 27, 2009 6:58:44 AM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Re: Workstation speed Gary I am using the Pause command to see where the EEP slows down and it seems the Edit Using form is the problem. The Project where count=0 is a suggestion.I am testing Thanks Marc From: Gary Wendike Sent: Wednesday, May 27, 2009 12:03 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Workstation speed Marc, I am joining this a little late in the process...Sorry if these have already been addressed. Two questions... 1. Why are you using a Pause Command? 2. Why use a Project...where count=0 then insert? Wouldn't it be better to use project...using columns desired where custnum = . vaptcust . This eliminates one step out of the process. From: MDRD < mdrd375@ sbcglobal .net > To: RBASE-L Mailing List < rbase -l@ rbase .com> Sent: Tuesday, May 26, 2009 5:35:00 PM Subject: [RBASE-L] - Re: Workstation speed I just heard back from 1 office, ....No luck so far the EEP still gets slower as the day goes on and the more they use it. They said it gets slow when it hits Pause 4 Using '5' just before the Edit Using command This EEP s using a Temp Table using a regular table did not help either DROP TABLE tran_spd PROJECT TEMP tran_spd FROM tran_hist USING * WHERE COUNT = 0 Insert ......... Pause 3 Using '5' EDIT USING spdyov2 DELETE ROWS FROM tran_spd INSERT + INTO tran_spd ( custnum , date_con , tr_date , tr_type , ch_code , ch_price , ptest ,+ dig_ch ,memo, treat_dr , inscomp , inshold ,modf1,modf2,modf3, modf4 ) + SELECT custnum , date_con , tr_date , tr_type , ch_code , ch_price , ptest , dig_ch ,memo,+ treat_dr , inscomp , inshold ,modf1,modf2,modf3, modf4 FROM speedov + WHERE custnum = . vaptcust PAUSE 4 USING '5' EDIT USING spdyov2 I also made sure the Clear form Vars is checked and I run a clear var routine after the Edit Using. Thanks Marc -------------------------------------------------- From: " MikeB " < mbyerley @ byerley .net > Sent: Friday, May 22, 2009 2:15 PM To: "RBASE-L Mailing List" < rbase -l@ rbase .com > Subject: [RBASE-L] - Re: Workstation speed > With external forms, this no longer an issue. No more connection / > disconnection just to display a form.... > > > ----- Original Message ----- > From: "Bernard Lis " < BerLis @comcast.net > > To: "RBASE-L Mailing List" < rbase -l@ rbase .com > > Sent: Friday, May 22, 2009 3:03 PM > Subject: [RBASE-L] - Re: Workstation speed > > > My Main menu is a separate database. > So when someone finishes an app and goes back to the main menu, they are > disconnected from the main database. > When they then select another app, they are disconnected from the menu > database and reconnected to the main database. > Try this and see if it maintains normal speed throughout the day. > Good Luck, > Bernie Lis > ----- Original Message ----- > From: MDRD > To: RBASE-L Mailing List > Sent: Friday, May 22, 2009 11:28 AM > Subject: [RBASE-L] - Re: Workstation speed > > > Dennis > > No, that may be a little hard the way my app is designed using a Form for > the main menu > > Marc > > > > From: Dennis McGrath > Sent: Friday, May 22, 2009 9:44 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Workstation speed > > > Have you tried disconnecting from the DB occasionally to clear all temp > tables? > > > > Dennis McGrath > > > > > ------------------------------------------------------------------------------ > > > From: rbase -l@ rbase .com [ mailto : rbase -l@ rbase .com ] On Behalf Of >MDRD > Sent: Thursday, May 21, 2009 5:29 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Workstation speed > > > > Buddy > > > > My code is fast at the start of the day, the longer they use my app the > slower this gets > > the rest of my app stays fast. Restarting Rbase / my app makes it faster > again > > > > Not to sound dense but I will anyway... I do not under the Where Count = > 0 ? > > > > To me it seems like the Temp table bogs down the longer you use it and is > not cleared from > > memory or some other tech thingy. > > > > Marc > > > > > > From: Walker, Buddy > > Sent: Thursday, May 21, 2009 4:56 PM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - Re: Workstation speed > > > > Marc > > If your form is based on the temp table and you are projecting only the > rows you want, then I wouldn't use the where clause on the EDIT using. I > wouldn't delete rows from the table either I would drop the table and > recreate it. > > > > One thing you might want to try is > > > > DROP TABLE tran_spd > > PROJECT TEMP TABLE tran_spd FROM tran_hist USING * WHERE COUNT = 0 > > INSERT INTO tran_spd (list only the columns you actually need) + > > SELECT the same list of columns in table FROM + > > Tran_hist WHERE custnum = . vcust AND ... > > > > EDIT USI spdyov2 > > No where clause should be necessary since you already filtered the > table with the project and insert where clause. > > > > Buddy > > > > From: rbase -l@ rbase .com [ mailto : rbase -l@ rbase .com ] On Behalf Of >MDRD > Sent: Thursday, May 21, 2009 5:21 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Workstation speed > > > > Bob and Karen > > > > It seems when I first went from 6.5 to 7.5 I used a regular table with > Indexes. > > But I switched to Temp tables thinking it would be safer is 2 > workstations were doing the same EEP . > > It has been a long time and in the server you can not tell any > difference. > > > > There are only 3-5 rows each time. > > > > These offices waited for several updates of mine so I can't be sure which > of my "improvements" caused this <g> > > > > Scratch is Local TMP > > > > I have tried Drop and Project but they say it is still slow > > DROP TABLE tran_spd > PROJECT TEMP tran_spd FROM tran_hist USING * + > WHERE custnum = . vcust AND tr_type = 1 AND tr_date = . vmaxdate > > EDIT USING spdyov2 + > WHERE tr_type = 1 AND custnum = . vcust CAPTION . vcap > > I need to find 2 slow computers to network to make it easier to test this > > > > > > Thanks > > Marc > > > > > > > > From: ttc [email protected] > > Sent: Thursday, May 21, 2009 3:10 PM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - Re: Workstation speed > > > > Marc, > > I would not use Delete Rows from a temp table when wanting to remove > all rows. This is much slower > > if there are any number of rows to delete. > > > > Instead try, > > > > Drop table Tran_spd > > Project temp Tran_Spd from (Permanent empty table name of same column > structure) using all > > or > > If Speedov has the same column names, skip the insert and > > > > Project temp Tran_Spd from Speedov using (column list) where...... > > > > This may prove even faster yet. However, note that a lock will be > placed on Speedov if you > > use the project from it. Otherwise project using Where limit = 0 and > then use the Insert if a lock > > on the table would be problematic. > > > > You do not say how many records are being inserted at a time. You do not > show building an > > index on your temp table. I normally do not use indexes on temp tables > unless there is a lot of > > data and of a type that an index would help with. If you have indexes > and are using the > > Delete Rows command, that will definitely be slower as it has to update > all the indexes as well. > > > > As Karen stated, make sure your temp settings are local. > > -Bob > > ----- Original Message ----- > From: KarenTellef @cs.com > To: "RBASE-L Mailing List" < rbase -l@ rbase .com > > Sent: Thursday, May 21, 2009 2:53:10 PM GMT -06:00 US/Canada Central > Subject: [RBASE-L] - Re: Workstation speed > > Marc: Have you tried using a permanent table in 7.5 to see if it's > faster than using the temp table? And if you're using temp tables, make > sure your scratch setting is to a local drive (like SET SCRATCH C:\TEMP) > so that temp table information is kept local rather than traveling through > the network. > > Karen > > This is the second office that has brought up the issue of Rbase > getting slower as the day wears on. > > This tech is a certified network guy so I assume the network is OK, new > computers 1 gig switches ... > > My old code used a regular table and I would just delete rows .....but > I never heard a complaint on > the speed > > -- tran_spd is a temp table that is created on start of the main menu > DELETE ROWS FROM tran_spd > -- APPEND .... using append instead of insert does not seem to make > any difference > > INSERT + > INTO tran_spd ( custnum , date_con , tr_date , tr_type , ch_code , >ch_price ,+ > ptest , dig_ch ,memo, treat_dr , inscomp , inshold ,modf1,modf2,modf3,+ > modf4 ) SELECT custnum , date_con , tr_date , tr_type , ch_code , >ch_price ,+ > ptest , dig_ch ,memo, treat_dr , inscomp , inshold ,modf1,modf2,modf3,+ > modf4 FROM speedov WHERE custnum = . vaptcust > > EDIT USING spdyov2 + > WHERE tr_type = 1 AND custnum = . vcust CAPTION . vcap > > Then then click a button to Save or append these charges to another > table then > go back to this same EEP again. > > Even if I drop Temp tab and Project Temp tab is not faster > > I think is it my program logic not 7.5, but my old permanent table in > 6.5 was faster than the > temp table in 7.5 > > Why would using Temp table be slower? > > > Marc > >
