Yo Ben! I love the select statement, but ......it would have to do one more thing. There are multiple rows in the transact table for each patient, each with a value for the time of day it was entered. Only the last one for each patient should be used in the select statement (each transaction changes the patient's balance, but only the last transaction has the current balance). So.....can that be done with a select statement????
Mike Ben Petersen wrote: > Mike, > > I doubt these address the two user speed problem, but who > knows... <g> > > TransAct appears to be a reserved word > > >WRI 'TOTAL CHARGES AND PAIDS IN TRANSACT DON'T > ACCOUNT FOR NEW BALANCE' > The word "Don't" needs another single quote > --- Don''t (that's two single quotes) > > You can reduce a lot of processing overhead by using multi-table > selects and views rather than cursor routines > > (I think this would replace your first cursor) > SEL t1.TOTLNAME, t2.NEWBALAN, T1.PREVBAL from PATINFO > t1, TransAct t2 WHE t1.Patnumber = t2.Patnumber and > (t2.NewBalan - t1.PrevBal) <> 0 > > Ben Petersen > > On 26 Feb 2002, at 8:40, MJS wrote: > > > Ok...here is the scoop... > > Rbase for DOS 6.5++ > > Win98 SE or DOS 6.22 stations > > The program compares totals from different tables to check for data integrity, > > using a declare cursor (I'll paste the program below) > > SET STATIC made no difference. > > I have not tried the others yet. > > > > > > *(THIS PROGRAM WILL TRY TO FIND SPECIFIC ERRORS NOTED BY THE CLOSE DAY PROGRAM > > CALLED OFFICE.009 ) > > CONNECT dbname > > SET V VSTART = .#TIME > > WRITE .VSTART > > OUTPUT LPT1 > > SET ESCAPE ON > > SET ERROR VARIABLE DERR > > SET MESSAGES OFF > > SET ERROR MESSAGES OFF > > UPDATE PATINFO SET BALANCE = 0 WHE BALANCE FAILS > > DROP CURSOR C#3 > > SET MESSAGES ON > > SET ERROR MESSAGES ON > > SET RULES OFF > > SET BELL OFF > > SET RULES ON > > SET V VNAME TEXT > > SET V VPATBAL CURRENCY > > SET V VPATPREV CURRENCY > > SET V TOTCHARG CURRENCY > > SET V TOTPAID CURRENCY > > SET TIME FOR HH:MM:SS.SSS > > SET V VPNUM REAL > > WRITE 'THINKING' AT 12 40 > > DECLARE C#3 CURSOR FOR SEL PATNUMBR OLDBALAN NEWBALAN FROM TRANSACT ORDER BY + > > PATNUMBR TRANTIME = D > > OPEN C#3 > > FETCH C#3 INTO VPNUM I1,VOLDBAL I2,VNEWBAL I3 > > SET VARIABLE VERR = .DERR > > SET V VNEXT REAL > > SET V VNEXT TO 0 > > WHILE VERR EQ 0 THEN > > IF VNEXT EQ .VPNUM THEN > > GOTO NEXT > > ENDIF > > SET V VNAME TO TOTLNAME IN PATINFO WHE PATNUMBR EQ .VPNUM > > SET V VPATBAL TO BALANCE IN PATINFO WHE PATNUMBR EQ .VPNUM > > SET V VPATPREV TO PREVBAL IN PATINFO WHE PATNUMBR EQ .VPNUM > > IF ( .VPATBAL - .VNEWBAL ) NE 0 THEN > > WRI 'CURRENT BALANCE ERROR WITH PATIENT#: ' > > WRI .VPNUM > > WRI .VNAME > > WRI ' ' > > ENDIF > > COMPUTE TOTCHARG AS SUM CHARGE FRO TRANSACT WHE PATNUMBR EQ .VPNUM > > COMPUTE TOTPAID AS SUM TPAID FRO TRANSACT WHE PATNUMBR EQ .VPNUM > > IF ( .VPATPREV + .TOTCHARG - .TOTPAID ) NE .VPATBAL THEN > > WRI 'TOTAL CHARGES AND PAIDS IN TRANSACT DON'T ACCOUNT FOR NEW BALANCE' > > WRI .VPNUM > > WRI .VNAME > > WRI ' ' > > ENDIF > > LABEL NEXT > > SET V VNEXT TO .VPNUM > > FETCH C#3 INTO VPNUM I1,VOLDBAL I2,VNEWBAL I3 > > SET VARIABLE VERR = .DERR > > ENDWHILE > > LABEL SKIP > > SET ERROR VARIABLE DERR > > SET V VCURBAL CURRENCY > > SET V VCURBAL TO $100000 > > SET V VBALANCE CURRENCY > > SET V VPREVBAL CURRENCY > > SET V VPNUM REAL > > DROP CURSOR C#3 > > DECLARE C#3 CURSOR FOR SEL PATNUMBR BALANCE PREVBAL FRO PATINFO > > OPEN C#3 > > FETCH C#3 INTO VPNUM I1,VBALANCE I2,VPREVBAL I3 > > SET VARIABLE VERR = .DERR > > WHILE VERR EQ 0 THEN > > IF (.VBALANCE - .VPREVBAL) EQ 0 THEN > > GOTO NEXTPAT > > ENDIF > > DROP CURSOR C#2 > > DECLARE C#2 CURSOR FOR SEL NEWBALAN FRO TRANSACT ORDER BY TRANTIME = D WHE + > > PATNUMBR EQ .VPNUM > > OPEN C#2 > > FETCH C#2 INTO VCURBAL I1 > > IF VBALANCE NE .VCURBAL THEN > > WRI 'CHECK BALANCE AND PREVBAL FOR PATIENT #' > > WRI .VPNUM > > SET V VNAME TO TOTLNAME IN PATINFO WHE PATNUMBR EQ .VPNUM > > WRI .VNAME > > WRI ' ' > > ENDIF > > LABEL NEXTPAT > > FETCH C#3 INTO VPNUM I1,VBALANCE I2,VPREVBAL I3 > > SET V VERR = .DERR > > IF VPNUM = 0 OR VPNUM FAILS THEN > > RUN DEBUG.CMD > > ENDIF > > ENDWHILE > > SET ESCAPE OFF > > CLEAR VARIABLE VNAME TOTPAID TOTCHARG VPATPREV VPATBAL VPNUM VOLDBAL VNEWBAL + > > VERR DERR > > SET V VFINISH = .#TIME > > WRITE .VSTART > > WRITE .VFINISH > > SET V VTOTAL = (.VFINISH - .VSTART) > > SHO VAR VTOTAL > > CLEAR VARIABLE VNAME VNEXT VCURBAL VBALANCE VPREVBAL VPRN I1 I2 I3 VFINISH > > CLEAR VARIABLE VSTART VTOTAL > > SET TIME FOR HH:MM:SS > > > > > > The transact table generally has about 60 rows of data, the patinfo table has > > about 20,000 rows of data. > > The program takes 10 seconds to run off the server with 1 station attached, 40 > > seconds with 2 stations attached. > > > > Any ideas? > > > > Mike > > > > > > Troy Sosamon wrote: > > > > > Mike, > > > > > > I need more info to really help with this problem. I have run R:base on every > > > version of Novell except the latest version 6. > > > > > > What version of R:base? > > > What is the OS of the workstations? > > > What is your program doing? > > > One thing to keep in mind that once you add that second user to the database, > > > now updates have to check for concurency control. Depending on the size of > > > your tables and your indexes, updates, inserts, and deletes can get slow. If > > > you are doing bulk updates on big tables, there is a lot of overhead checking > > > for concurency. This is where the various settings can make big differences. > > > Here are some settings I recomend: > > > > > > SET VAR MICRORIM_F2MAXBLK INT = 64 > > > SET VAR MICRORIM_F3MAXBLK INT = 256 > > > SET VAR MICRORIM_BTMAXBLK INT = 64 > > > SET VAR MICRORIM_SRTMAXBLK INT = 64 > > > SET STATIC ON > > > SET ROWLOCKS ON > > > SET QUALCOLS 10 -- page locking - large updates require fewer locks > > > SET TRANSACT OFF > > > SET MULTI ON > > > SET WHILEOPT OFF -- very few apps gain from this, and it can cause problems > > > > > > Some of this stuff works a little differently depending on the version of > > > R:base you are using. The static setting is good, that gives you some > > > performance help because updates don't have to check for structure changes. > > > Every version of R:base that comes out continues to be tweeked at the engine > > > level for performance, and every release gets faster. > > > > > > You might want to check the Novell properties on the directory and on the > > > database files. I don't have NW 6, so I don't know what to tell you to look > > > for. > > > > > > Troy Sosamon > > > > > > >===== Original Message From [EMAIL PROTECTED] ===== > > > >We have also not had the problem on a Novell 4 server at several different > > > >clients. > > > > > > > >======================================= > > > >At 09:35 AM 2/25/2002 -0500, you wrote: > > > >>My comment was not clearly written. The problem we have identified is > > > >specific to W2K servers. We do not run Novell, but did not have the problem > > > >on a Novell 4 test server. > > > >> > > > >> > > > >>MJS <[EMAIL PROTECTED]> wrote: > > > >> > > > >>>The Server is NOVELL 6.0! > > > >>> > > > >>>SO......what's it gonna take to fix this? > > > >>> > > > >>>Mike > > > >>> > > > >>>Albert Berry wrote: > > > >>> > > > >>> We delved into this scenario quite a bit. M$ blames RBTI, RBTI could > > > >not provide a solution either. 10:1 the server is W2K > > > >>> > > > >>> MJS <[EMAIL PROTECTED]> wrote: > > > >>> > > > >>> >Hi all, > > > >>> >A strange thing (at least I think it's strange) showed up today in > > > >Rbase. The > > > >>> >database is on a server. I connect with just one station, and run a > > > >program > > > >>> >which uses a declare cursor to check data in a bunch of rows. The > > > program > > > >>> >completes in 10 seconds, plus or minus 1 second. I sometimes use this > > > >program > > > >>> >as a test for performance. Now....just for grins, I run Rbase on an > > > >additional > > > >>> >station, but I don't connect to the database... I just let the computer > > > >sit at > > > >>> >an R>. Keep in mind, these are the only 2 stations that are attached > > > >to Rbase, > > > >>> >1 connected to the database, 1 not connected to the database. Nobody > > > >else is > > > >>> >around, no other programs are being run on the server. I run the same > > > >program > > > >>> >again, same results, 9-11 seconds to finish. NOW.......I go to the > > > second > > > >>> >machine, and just do a > > > >>> >CONNECT dbname (multi user is on, staticdb is on). No other programs are > > > >>> >running. Now I run the same program again.....40 seconds!! 400% > > > >longer!!!! > > > >>> >Does this make sense? Network traffic should be about the same, so I > > > don't > > > >>> >think it;s a network issue. Any ideas or comments? > > > >>> > > > > >>> >TIA! > > > >>> > > > > >>> >Mike Sinclair > > > >>> > > > > >>> >================================================ > > > >>> >TO SEE MESSAGE POSTING GUIDELINES: > > > >>> >Send a plain text email to [EMAIL PROTECTED] > > > >>> >In the message body, put just two words: INTRO rbase-l > > > >>> >================================================ > > > >>> >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >>> >In the message body, put just two words: UNSUBSCRIBE rbase-l > > > >>> >================================================ > > > >>> >TO SEARCH ARCHIVES: > > > >>> >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > >>> > > > > >>> -- > > > >>> > > > >>> __________________________________________________________________ > > > >>> Your favorite stores, helpful shopping tools and great gift ideas. > > > >Experience the convenience of buying online with Shop@Netscape! > > > >http://shopnow.netscape.com/ > > > >>> > > > >>> Get your own FREE, personal Netscape Mail account today at > > > >http://webmail.netscape.com/ > > > >>> > > > >>> ================================================ > > > >>> TO SEE MESSAGE POSTING GUIDELINES: > > > >>> Send a plain text email to [EMAIL PROTECTED] > > > >>> In the message body, put just two words: INTRO rbase-l > > > >>> ================================================ > > > >>> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >>> In the message body, put just two words: UNSUBSCRIBE rbase-l > > > >>> ================================================ > > > >>> TO SEARCH ARCHIVES: > > > >>> http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > >>> > > > >>>================================================ > > > >>>TO SEE MESSAGE POSTING GUIDELINES: > > > >>>Send a plain text email to [EMAIL PROTECTED] > > > >>>In the message body, put just two words: INTRO rbase-l > > > >>>================================================ > > > >>>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >>>In the message body, put just two words: UNSUBSCRIBE rbase-l > > > >>>================================================ > > > >>>TO SEARCH ARCHIVES: > > > >>>http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > >>> > > > >>-- > > > >> > > > >> > > > >> > > > >> > > > >>__________________________________________________________________ > > > >>Your favorite stores, helpful shopping tools and great gift ideas. > > > >Experience the convenience of buying online with Shop@Netscape! > > > >http://shopnow.netscape.com/ > > > >> > > > >>Get your own FREE, personal Netscape Mail account today at > > > >http://webmail.netscape.com/ > > > >> > > > >>================================================ > > > >>TO SEE MESSAGE POSTING GUIDELINES: > > > >>Send a plain text email to [EMAIL PROTECTED] > > > >>In the message body, put just two words: INTRO rbase-l > > > >>================================================ > > > >>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >>In the message body, put just two words: UNSUBSCRIBE rbase-l > > > >>================================================ > > > >>TO SEARCH ARCHIVES: > > > >>http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > >> > > > > > > > >================================================ > > > >TO SEE MESSAGE POSTING GUIDELINES: > > > >Send a plain text email to [EMAIL PROTECTED] > > > >In the message body, put just two words: INTRO rbase-l > > > >================================================ > > > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >In the message body, put just two words: UNSUBSCRIBE rbase-l > > > >================================================ > > > >TO SEARCH ARCHIVES: > > > >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > > Troy Sosamon > > > Denver Co > > > [EMAIL PROTECTED] > > > > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: INTRO rbase-l > > > ================================================ > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > > ================================================ > > > TO SEARCH ARCHIVES: > > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
