My understanding of your problem is that the system gets slower as time passes and
the users use the app more. Then exiting Rbase and restarting clears the problem for a while but then speed starts to slow down again. While there might be some reason for a speed difference in APPEND versus INSERT, neither should cause a gradual slow down to my knowledge. Not using a WHERE clause with the insert should speed it up some, but it should not impact a "slow down" situation. INSERT commands (and probably APPEND as well) are such a common command that they must be used in thousands of apps (I know I use INSERT extensively) and any inherent issue would have been seen by many. Therefore I believe logic would imply that the problem does not reside within either command but either an external issue or possibly how your code is arranged. Not to repeat other's input, but to logically reduce the scenario down, let's look at the most definitive known issues. A)Starts fast, slows down after use. B)A restart solves the problem for a period of time. This gives us considerable info actually. All we need to do is look at what a restart does. It disconnects the database, cleans up temp files, drops temp tables and views, drops any cursors, clears variables, clears table locks and frees memory in some respects. Look at each of these and what might your app be doing to effect them. For instance, could variables be defined that use a progressive unique name and not get cleared? I.E. the variable list could continue to grow and grow if not cleared, thus cause issues. (This could happen if you have a SET VAR . vVarName = ....., where you create a variable from another variable name.) Look at your designated temp folder over a period of time. Are the $$$ temp files growing out of control in either number or size? If so, then a look at any command that utilizes temp files is needed and how it is implemented. For example, if an app created a temp table, using a customer or user name as part of the table name, one could inadvertently create hundreds of temp tables, which would certainly slow things down over time. If you delete rows from a temp table, the table size will continue to grow and slow down until either a drop or disconnect is done. Delete rows does not actually remove the data space in a table. When you disconnect, the table is dropped and completely cleared. So I would look at any code or logic that is clearly effected by a shut down. An INSERT or APPEND command should not be, thus I do not think that the issue. I know this is still a bit vague, but might be a fresh way of looking at the problem. You know the issue, you know how to temporarily resolve the issue, then look at only the aspects that are related to the known temporary resolution. In this case a restart. I do not believe a restart would effect an INSERT or APPEND command. Good luck -Bob ----- Original Message ----- From: " MDRD " <mdrd375@ sbcglobal .net> To: "RBASE-L Mailing List" < rbase -l@ rbase .com> Sent: Wednesday, May 27, 2009 8:36:56 AM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Re: Workstation speed Hi all I just got a email form one of the offices and they pinned down the slow part a little more. It seems that loading the table for Edit using is OK but when the click the Save Button which runs this EEP is where it gets slow. .... I use to use APPEND. We load table Tran_spd with 3-5 rows, Edit Using Form. Save button EEP copies the rows to Tan_daily . Maybe Append is faster?? I switched to Insert thinking it was a better way to go I think I can get rid of the Where clause since there is only that 1 customer in the table anyway? But still, why would such a simple thing cause a problem? I am really trying to understand what I am doing and which is the best method of doing things but I do not know enough of the inner details of Append or Insert. INSERT + INTO tran_daily ( custnum , date_con , tr_date , tr_type , ch_code ,+ ch_price , ptest , dig_ch , inscomp , cknum , date_frm , date_to , memo,+ treat_dr , inshold , blddate , tranmod ,modf1,modf2,modf3,modf4) + SELECT custnum , date_con , tr_date , tr_type , ch_code , ch_price , ptest ,+ dig_ch , inscomp , cknum , date_frm , date_to , memo, treat_dr , inshold ,+ blddate , tranmod ,modf1,modf2,modf3,modf4 FROM transpd3 + WHERE custnum = . vaptcust Thanks Marc From: ttc [email protected] Sent: Wednesday, May 27, 2009 7:53 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Workstation speed 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 > >
