Re: [RBASE-L] - Thoughts on speeding up a cursor?

[karentellef via RBASE-L]

Thanks, Javier, all good thoughts.

Quick question:  If I am dialing into the system using Remote Desktop (no VPN, 
not dialed into the database server itself), am I seeing the same performance 
as a person on a network workstation?  If not, then that screws up my ability 
to test speed advances.


Multi-line commands:  I wasn't sure if the whole code was read into memory or 
not, didn't know if that was only in while loops.  If a multi-line command is 
read internally as one line that would be awesome.  Although it would be a 
quick "fix" if consolidating those lines would speed it up.

We changed from a WHILE loop to a GOTO in Nov 2014, so a little over 2 years 
ago.  The client was on 9.5 at that time. The routine was taking only 45 
minutes to run in 9.5 with the goto/label and was acceptable, but we upgraded 
to Version 10 and changed to 64-bit and with no changes to the program or table 
structure now it takes 3 hrs.  RBTI gave me some suggestions for checking code 
and table structure, I made one change to indexes (removed duplicate indexes, 
even though I'm not updating the table and it's only used for the primary 
cursor), did another unload of the database, but it didn't help.  Who knows, 
maybe the while loop will not only work, but also would be faster than 
goto/label in version 10?  I'm wondering if, on my own time, I should try to 
put the program "back" to a while loop and test it in version 10 to see if it 
completes and if it's any faster... Hmmm.

Temp tables:  yeah I guess I should think of creating temp tables for all of 
the lookups.  I counted -- there's 10 different tables of lookups (so 10 temp 
tables??), but there's many different criterias.  All the criteria is at least 
3 items, some search for up to 8 items to match.  Some criteria is 
non-indexable such as "not null" or ">".  If I search one time by 8 items, once 
by 3 items, once by 5 items, how would I go about setting up indexes for all 
that?  And do I only include columns where I search using "=" in multi-column 
indexes?

For one example, it first looks at a table for the most restrictive match:  can 
I match the policy, the company, the agent, the year the policy started (which 
is a >), the coverage plan, whether the policy offers advances.  If it doesn't 
match that, then it'll drop 1 or 2 of the criteria and do another search, and 
so on.  The only unique is policy / company.   I don't know how I could set up 
indexes to match the many searches just to this one table, and I literally have 
9 other tables with similar lookups.
 My usual routine is to set individual indexes for the most unique columns 
(which are policy# & company code, both text), but not do indexes for those 
columns that have alot of repetitive data.  For example, for "agent", of the 
40K rows, there may be only 20 different agents.  I don't think that would be a 
good index, do you?

If anyone wants a clue as to why health insurance is so confusing, you should 
see this routine!

Thanks to anyone who had the fortitude to read my verbose email


Karen


 

 

 

-Original Message-
From: Javier Valencia 
To: rbase-l 
Sent: Mon, Apr 24, 2017 3:05 pm
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?



I think you have gotten several good answers already but this is what I know 
based on my experience.
 
Indexes – I had an application that generated random inspection location for a 
large number of records and ran relatively quick. Then it started to run very 
slowly and I could not figure out why at first until I notice one of the techs 
working for me with had removed the indexes and it made a huge difference. Now, 
when looking to speed up code, the first thing I do is make sure I have the 
indices properly configured.
 
WHILE versus GOTO – I remember that at one time there was an issue with WHILE 
loops but as far as I can tell, that issue was resolved a while back. I have an 
optimization utility that has several levels of WHILE loops and I have not had 
an issue for them in a long time. Properly optimized the code runs very fast.
 
Multi-line Commands – I will guest that the code is read first into memory to 
optimize it and each command line, regardless of how many lines it uses, is 
interpreted as one line. For readability purposes and to get around the column 
limitation of the old Codelock, I routinely use multiple lines, many times 
dozens, for one command, particularly when selecting or updating records and I 
also use the full command name rather than the abbreviation. Yes, it takes a 
lot more space but memory is not the issue it used to be in the old DOS days. I 
remember this topics being discussed at one time and I seem to recall that 
multi command lines were not an issue…at least I hope it is not. J
 
Temporary Tables – Temporary Tables are stored in local memory and as such will 
be accessed considerably faster than hard disks, particularly if 

RE: [RBASE-L] - Thoughts on speeding up a cursor?

[Javier Valencia]

I think you have gotten several good answers already but this is what I know 
based on my experience.
 
Indexes – I had an application that generated random inspection location for a 
large number of records and ran relatively quick. Then it started to run very 
slowly and I could not figure out why at first until I notice one of the techs 
working for me with had removed the indexes and it made a huge difference. Now, 
when looking to speed up code, the first thing I do is make sure I have the 
indices properly configured.
 
WHILE versus GOTO – I remember that at one time there was an issue with WHILE 
loops but as far as I can tell, that issue was resolved a while back. I have an 
optimization utility that has several levels of WHILE loops and I have not had 
an issue for them in a long time. Properly optimized the code runs very fast.
 
Multi-line Commands – I will guest that the code is read first into memory to 
optimize it and each command line, regardless of how many lines it uses, is 
interpreted as one line. For readability purposes and to get around the column 
limitation of the old Codelock, I routinely use multiple lines, many times 
dozens, for one command, particularly when selecting or updating records and I 
also use the full command name rather than the abbreviation. Yes, it takes a 
lot more space but memory is not the issue it used to be in the old DOS days. I 
remember this topics being discussed at one time and I seem to recall that 
multi command lines were not an issue…at least I hope it is not. J
 
Temporary Tables – Temporary Tables are stored in local memory and as such will 
be accessed considerably faster than hard disks, particularly if you are 
working over a network.
 
One technique I have used in the past is to set different variables at various 
places in the code equal to #TIME
First, set the format to:
SET TIME FORMAT HH:MM:SS.
Then at various places set various variables
SETVAR vTime1 = .#TIME 
SETVAR vTime2 = .#TIME 
Or you can make your variable names more descriptive:
SETVAR vStartOfLoop = .#TIME 
And so on.
By looking at the times at various places you can determine the time 
differential it takes to execute the code and concentrate in the portions that 
are taking longer within each iteration. If you want to get fancy you can make 
the variable name contain the number of the iteration and write to an external 
file at the end of the iteration and clear the older variables and then load 
the file with all the times to a spreadsheet for easy analysis.
 
Javier,
 
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
 
From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com] 
Sent: Monday, April 24, 2017 1:05 PM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
 
Yeah, I'll try to trace the program while everyone is connected and see if I 
can pinpoint any particular commands that seem like they take longer than they 
"should".  The only issue is that depending on the particular data, it will be 
processing just a small percentage of the entire 600 lines of code because of 
all the jumping around so it's kinda hit-or-miss whether I'll happen to get a 
nice row of data that gets to the section that's hanging everything up

So I'm gonna guess that no one can answer my original question of whether RBase 
can locate a "label" faster if there's less physical lines of code.   If by 
making all the multi-line command files just 1 or 2 lines instead, I could 
probably reduce that 600 lines of code to about 400 I'll bet, but I don't want 
to bother if it won't make a difference.   Mercy, there even so many lines of 
comments trying to explain what's going on, I could probably even get it down 
to 300 lines!


Karen
 
 
 
-Original Message-
From: Dan Goldberg 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:56 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
Depending on the size of the tables and the complexity of the where clause 
sometimes temp tables will speed it up tremendously. I use them throughout my 
programming to speed up different things.
 
Tracing usually shows me which one is slowing it down and that is where I look 
at.
 
Dan Goldberg
 
 
 
From: rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com 
 ] On Behalf Of Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
 
Hard to believe?  No, just proves that sending guys to the moon is easier than 
figuring out insurance stuff.  :)
D
On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I am updating a record.  Never, not once.  It does a 
whole bunch of selects, from a whole bunch of different tables, and there's a 
whole bunch of variable calculations.  Depending 

RE: [RBASE-L] - Thoughts on speeding up a cursor?

[Claudine Robbins]

Karen,

I definitely have NEVER dealt with the table size you’re looking at but 
creating temp tables with filters in decreasing impact value usually brings the 
best/fastest results.

In your case, I would start with a date field or the one field that is most 
often not null choosing whichever one will yield the smallest resulting temp 
table, ultimately looking for agent and policy number last.  It seems 
counterintuitive but it works.

SELECT agtcomm INTO vtestagtcomm +
  FROM agtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode AND polyr = 1 AND agtcomm < 0 +
  AND paidtoagton IS NOT NULL
Claudine

From: rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com] On Behalf Of 
Dan Goldberg
Sent: Monday, April 24, 2017 1:09 PM
To: rbase-l@googlegroups.com
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?

I do not use label-goto commands that much but have not noticed a speed impact 
on the ones I have used them in.

Sometimes for me is just looking at it and finding a better way to accomplish 
it works.

Dan

From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 11:05 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Yeah, I'll try to trace the program while everyone is connected and see if I 
can pinpoint any particular commands that seem like they take longer than they 
"should".  The only issue is that depending on the particular data, it will be 
processing just a small percentage of the entire 600 lines of code because of 
all the jumping around so it's kinda hit-or-miss whether I'll happen to get a 
nice row of data that gets to the section that's hanging everything up

So I'm gonna guess that no one can answer my original question of whether RBase 
can locate a "label" faster if there's less physical lines of code.   If by 
making all the multi-line command files just 1 or 2 lines instead, I could 
probably reduce that 600 lines of code to about 400 I'll bet, but I don't want 
to bother if it won't make a difference.   Mercy, there even so many lines of 
comments trying to explain what's going on, I could probably even get it down 
to 300 lines!


Karen



-Original Message-
From: Dan Goldberg >
To: rbase-l >
Sent: Mon, Apr 24, 2017 11:56 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
Depending on the size of the tables and the complexity of the where clause 
sometimes temp tables will speed it up tremendously. I use them throughout my 
programming to speed up different things.

Tracing usually shows me which one is slowing it down and that is where I look 
at.

Dan Goldberg



From: rbase-l@googlegroups.com 
[mailto:rbase-l@googlegroups.com] On Behalf 
Of Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Hard to believe?  No, just proves that sending guys to the moon is easier than 
figuring out insurance stuff.  :)
D
On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I am updating a record.  Never, not once.  It does a 
whole bunch of selects, from a whole bunch of different tables, and there's a 
whole bunch of variable calculations.  Depending on conditions, it skips around 
all over the place to retrieve those variables from tables, whether or not to 
make certain calcs, etc.

The only table operation it does is at the very end, when it's done with its 
calculations, it finally inserts one row into a temporary table

I know, hard to believe, isn't it?   I don't think NASA has calculations as 
complicated as this routine just to get a single answer.

Karen



-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - I had a similar speed question when using the UPDATE command on 2/3/15, 
although my questions was more about optimal use of the WHERE clause than GOTO 
and labels.  You, Dennis and others offered many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the response 
from Dennis that might help you as far as order of the columns, using parens, 
etc.:
Doug,

First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.

If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in 

RE: [RBASE-L] - Thoughts on speeding up a cursor?

[Dan Goldberg]

I do not use label-goto commands that much but have not noticed a speed impact 
on the ones I have used them in.

Sometimes for me is just looking at it and finding a better way to accomplish 
it works.

Dan

From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 11:05 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Yeah, I'll try to trace the program while everyone is connected and see if I 
can pinpoint any particular commands that seem like they take longer than they 
"should".  The only issue is that depending on the particular data, it will be 
processing just a small percentage of the entire 600 lines of code because of 
all the jumping around so it's kinda hit-or-miss whether I'll happen to get a 
nice row of data that gets to the section that's hanging everything up

So I'm gonna guess that no one can answer my original question of whether RBase 
can locate a "label" faster if there's less physical lines of code.   If by 
making all the multi-line command files just 1 or 2 lines instead, I could 
probably reduce that 600 lines of code to about 400 I'll bet, but I don't want 
to bother if it won't make a difference.   Mercy, there even so many lines of 
comments trying to explain what's going on, I could probably even get it down 
to 300 lines!


Karen



-Original Message-
From: Dan Goldberg >
To: rbase-l >
Sent: Mon, Apr 24, 2017 11:56 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
Depending on the size of the tables and the complexity of the where clause 
sometimes temp tables will speed it up tremendously. I use them throughout my 
programming to speed up different things.

Tracing usually shows me which one is slowing it down and that is where I look 
at.

Dan Goldberg



From: rbase-l@googlegroups.com 
[mailto:rbase-l@googlegroups.com] On Behalf 
Of Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Hard to believe?  No, just proves that sending guys to the moon is easier than 
figuring out insurance stuff.  :)
D
On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I am updating a record.  Never, not once.  It does a 
whole bunch of selects, from a whole bunch of different tables, and there's a 
whole bunch of variable calculations.  Depending on conditions, it skips around 
all over the place to retrieve those variables from tables, whether or not to 
make certain calcs, etc.

The only table operation it does is at the very end, when it's done with its 
calculations, it finally inserts one row into a temporary table

I know, hard to believe, isn't it?   I don't think NASA has calculations as 
complicated as this routine just to get a single answer.

Karen



-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - I had a similar speed question when using the UPDATE command on 2/3/15, 
although my questions was more about optimal use of the WHERE clause than GOTO 
and labels.  You, Dennis and others offered many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the response 
from Dennis that might help you as far as order of the columns, using parens, 
etc.:
Doug,

First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.

If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):

UPDATE TxnHist +
SET ChryInvNbr = INV.ChryInvNbr +
FROM TxnHist TXN, ChryInvDtlTmp INV  +
  WHERE  +
TXN.VPlNmbr = INV.VPlNmbr  AND +
TXN.CusPnbr = INV.CusPnbr AND +
TXN.TxDate = INV.InvoiceDate


This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.

Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that 
question.

BTW, I don't think labels and GOTOs are the problem.  Suppose you rewrote the 
code and saved a few milliseconds per loop by "optimizing" the GOTO/labels.  At 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[karentellef via RBASE-L]

Yeah, I'll try to trace the program while everyone is connected and see if I 
can pinpoint any particular commands that seem like they take longer than they 
"should".  The only issue is that depending on the particular data, it will be 
processing just a small percentage of the entire 600 lines of code because of 
all the jumping around so it's kinda hit-or-miss whether I'll happen to get a 
nice row of data that gets to the section that's hanging everything up

So I'm gonna guess that no one can answer my original question of whether RBase 
can locate a "label" faster if there's less physical lines of code.   If by 
making all the multi-line command files just 1 or 2 lines instead, I could 
probably reduce that 600 lines of code to about 400 I'll bet, but I don't want 
to bother if it won't make a difference.   Mercy, there even so many lines of 
comments trying to explain what's going on, I could probably even get it down 
to 300 lines!


Karen

 

 

 

-Original Message-
From: Dan Goldberg 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:56 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?



Depending on the size of the tables and the complexity of the where clause 
sometimes temp tables will speed it up tremendously. I use them throughout my 
programming to speed up different things.
 
Tracing usually shows me which one is slowing it down and that is where I look 
at.
 
Dan Goldberg
 
 
 

From: rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com]On Behalf Of 
Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

 
Hard to believe?  No, just proves that sending guys to the moon is easier than 
figuring out insurance stuff.  :)
D

On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:

Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I amupdating a record.  Never, not once.  It does a whole 
bunch of selects, from a whole bunch of different tables, and there's a whole 
bunch of variable calculations.  Depending on conditions, it skips around all 
over the place to retrieve those variables from tables, whether or not to make 
certain calcs, etc.

The only table operation it does is at the very end, when it's done with its 
calculations, it finally inserts one row into a temporary table

I know, hard to believe, isn't it?   I don't think NASA has calculations as 
complicated as this routine just to get a single answer.

Karen

 

 

 

-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - I had a similar speed question when using the UPDATE command on 2/3/15, 
although my questions was more about optimal use of the WHERE clause than GOTO 
and labels.  You, Dennis and others offered many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the response 
from Dennis that might help you as far as order of the columns, using parens, 
etc.:
Doug,
 
First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.
 
If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):
 
UPDATE TxnHist +
SET ChryInvNbr = INV.ChryInvNbr +
FROM TxnHist TXN, ChryInvDtlTmp INV  +
  WHERE  +
TXN.VPlNmbr = INV.VPlNmbr  AND +
TXN.CusPnbr = INV.CusPnbr AND +
TXN.TxDate = INV.InvoiceDate
 
 
This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.
 
Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that 
question.
 
BTW, I don't think labels and GOTOs are the problem.  Suppose you rewrote the 
code and saved a few milliseconds per loop by "optimizing" the GOTO/labels.  At 
40,000 records that's only a difference of, say, 40 to 120 seconds total (a few 
minutes), hardly a dent in the several hours the program now runs.  I think 
Dennis's first point might be a clue: Updating an indexed column.

Doug

On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:

That select statement isnot my cursor, that's just one of the many 600 lines of 
code that the cursor is evaluating.  The cursor itself would not be index-able 
as it contains >=, not null, etc

I mean, yes, I could look at the many, many select statements within the loop 
(my wild guess is that 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[Alastair Burr]

Karen,

This is the sort of time when it could be useful to be able to see TRACE 
scrolling through on screen when running to next error or, in your case just 
running to the end. You might then be able to see where it slows down or takes 
time over a particular line.

Regards,
Alastair.



On 24/04/2017 17:37, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I am updating a record.  Never, not once.  It does a 
whole bunch of selects, from a whole bunch of different tables, and there's a 
whole bunch of variable calculations.  Depending on conditions, it skips around 
all over the place to retrieve those variables from tables, whether or not to 
make certain calcs, etc.

The only table operation it does is at the very end, when it's done with its 
calculations, it finally inserts one row into a temporary table

I know, hard to believe, isn't it?   I don't think NASA has calculations as 
complicated as this routine just to get a single answer.

Karen



-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - I had a similar speed question when using the UPDATE command on 2/3/15, 
although my questions was more about optimal use of the WHERE clause than GOTO 
and labels.  You, Dennis and others offered many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the response 
from Dennis that might help you as far as order of the columns, using parens, 
etc.:


Doug,

First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.

If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):

UPDATE TxnHist +
SET ChryInvNbr = INV.ChryInvNbr +
FROM TxnHist TXN, ChryInvDtlTmp INV  +
  WHERE  +
TXN.VPlNmbr = INV.VPlNmbr  AND +
TXN.CusPnbr = INV.CusPnbr AND +
TXN.TxDate = INV.InvoiceDate


This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.

Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that 
question.



BTW, I don't think labels and GOTOs are the problem.  Suppose you rewrote the 
code and saved a few milliseconds per loop by "optimizing" the GOTO/labels.  At 
40,000 records that's only a difference of, say, 40 to 120 seconds total (a few 
minutes), hardly a dent in the several hours the program now runs.  I think 
Dennis's first point might be a clue: Updating an indexed column.

Doug

On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:
That select statement is not my cursor, that's just one of the many 600 lines 
of code that the cursor is evaluating.  The cursor itself would not be 
index-able as it contains >=, not null, etc

I mean, yes, I could look at the many, many select statements within the loop 
(my wild guess is that there's around 50 of them) and maybe there would be 10 
or 15 different potential compound indexes.  I'm not sure if there's a 
practical limit to the number of compound indexes you could create on a single 
table (there would be probably 10 different "lookup" tables).

So yeah, good idea, I'll look at all the lookups and check indexing.  But I'm 
assuming that compounds would only work in instances where all of the 
components are using "=", right?


Karen



-Original Message-
From: Albert Berry 
To: rbase-l 
Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - wild thought. Would a compound index work here?
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)

This would enable an index only retrieval.

Albert
On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L 
> wrote:

Dan:

I had actually posted here on the list a few years ago when, as the business 
grew, our cursor (which used to process about 25K rows) started randomly 
crashing in the 30K or 40K range.  Several people here recommended to replace 
the while loop with goto/label, so that's what I did.  The goto works fine, so 
I'm not interested in revisiting a while loop.

I'm not understanding what you're suggesting on a temp table.  I would have to 
create a temp 

RE: [RBASE-L] - Thoughts on speeding up a cursor?

[Dan Goldberg]

Depending on the size of the tables and the complexity of the where clause 
sometimes temp tables will speed it up tremendously. I use them throughout my 
programming to speed up different things.

Tracing usually shows me which one is slowing it down and that is where I look 
at.

Dan Goldberg



From: rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com] On Behalf Of 
Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Hard to believe?  No, just proves that sending guys to the moon is easier than 
figuring out insurance stuff.  :)
D
On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in that 600 
lines of cursor where I am updating a record.  Never, not once.  It does a 
whole bunch of selects, from a whole bunch of different tables, and there's a 
whole bunch of variable calculations.  Depending on conditions, it skips around 
all over the place to retrieve those variables from tables, whether or not to 
make certain calcs, etc.

The only table operation it does is at the very end, when it's done with its 
calculations, it finally inserts one row into a temporary table

I know, hard to believe, isn't it?   I don't think NASA has calculations as 
complicated as this routine just to get a single answer.

Karen



-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - I had a similar speed question when using the UPDATE command on 2/3/15, 
although my questions was more about optimal use of the WHERE clause than GOTO 
and labels.  You, Dennis and others offered many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the response 
from Dennis that might help you as far as order of the columns, using parens, 
etc.:

Doug,



First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.



If that is not the case I would do this:

1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.

2. Make the temp table the second table, not the first.

3. Set manopt on to make sure R:BASE follows your optimization.

4. Use this syntax (no parenthesis around the where clause):



UPDATE TxnHist +

SET ChryInvNbr = INV.ChryInvNbr +

FROM TxnHist TXN, ChryInvDtlTmp INV  +

  WHERE  +

TXN.VPlNmbr = INV.VPlNmbr  AND +

TXN.CusPnbr = INV.CusPnbr AND +

TXN.TxDate = INV.InvoiceDate





This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.



Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.

I suspect InvoiceDate would be the most unique, but only you can answer that 
question.


BTW, I don't think labels and GOTOs are the problem.  Suppose you rewrote the 
code and saved a few milliseconds per loop by "optimizing" the GOTO/labels.  At 
40,000 records that's only a difference of, say, 40 to 120 seconds total (a few 
minutes), hardly a dent in the several hours the program now runs.  I think 
Dennis's first point might be a clue: Updating an indexed column.

Doug
On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:
That select statement is not my cursor, that's just one of the many 600 lines 
of code that the cursor is evaluating.  The cursor itself would not be 
index-able as it contains >=, not null, etc

I mean, yes, I could look at the many, many select statements within the loop 
(my wild guess is that there's around 50 of them) and maybe there would be 10 
or 15 different potential compound indexes.  I'm not sure if there's a 
practical limit to the number of compound indexes you could create on a single 
table (there would be probably 10 different "lookup" tables).

So yeah, good idea, I'll look at all the lookups and check indexing.  But I'm 
assuming that compounds would only work in instances where all of the 
components are using "=", right?


Karen



-Original Message-
From: Albert Berry 
To: rbase-l 
Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - wild thought. Would a compound index work here?
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)

This would enable an index only retrieval.

Albert
On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L 
> wrote:

Dan:

I had actually posted here on the list a few years ago when, as the business 
grew, our cursor 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[Doug Hamilton]

Hard to believe?  No, just proves that sending guys to the moon is 
easier than figuring out insurance stuff.  :)

D

On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in 
that 600 lines of cursor where I am _updating_ a record.  Never, not 
once.  It does a whole bunch of selects, from a whole bunch of 
different tables, and there's a whole bunch of variable calculations.  
Depending on conditions, it skips around all over the place to 
retrieve those variables from tables, whether or not to make certain 
calcs, etc.


The _only_ table operation it does is at the very end, when it's done 
with its calculations, it finally inserts one row into a temporary 
table


I know, hard to believe, isn't it?   I don't think NASA has 
calculations as complicated as this routine just to get a single answer.


Karen



-Original Message-
From: Doug Hamilton 
To: rbase-l 
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - I had a similar speed question when using the UPDATE command 
on 2/3/15, although my questions was more about optimal use of the 
WHERE clause than GOTO and labels.  You, Dennis and others offered 
many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the 
response from Dennis that might help you as far as order of the 
columns, using parens, etc.:


Doug,

First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.

If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):

UPDATE TxnHist +
 SET ChryInvNbr = INV.ChryInvNbr +
 FROM TxnHist TXN, ChryInvDtlTmp INV  +
   WHERE  +
 TXN.VPlNmbr = INV.VPlNmbr  AND +
 TXN.CusPnbr = INV.CusPnbr AND +
 TXN.TxDate = INV.InvoiceDate


This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.

Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that 
question.

BTW, I don't think labels and GOTOs are the problem. Suppose you 
rewrote the code and saved a few milliseconds per loop by "optimizing" 
the GOTO/labels.  At 40,000 records that's only a difference of, say, 
40 to 120 seconds total (a few minutes), hardly a dent in the several 
hours the program now runs.  I think Dennis's first point might be a 
clue: Updating an indexed column.


Doug

On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:

That select statement is _not_ my cursor, that's just one of the
many 600 lines of code that the cursor is evaluating.  The cursor
itself would not be index-able as it contains >=, not null, etc

I mean, yes, I could look at the many, many select statements
within the loop (my wild guess is that there's around 50 of them)
and maybe there would be 10 or 15 different potential compound
indexes.  I'm not sure if there's a practical limit to the number
of compound indexes you could create on a single table (there
would be probably 10 different "lookup" tables).

So yeah, good idea, I'll look at all the lookups and check
indexing.  But I'm assuming that compounds would only work in
instances where all of the components are using "=", right?


Karen



-Original Message-
From: Albert Berry 

To: rbase-l 

Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - wild thought. Would a compound index work here?
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)

This would enable an index only retrieval.

Albert

On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L
>
wrote:

Dan:

I had actually posted here on the list a few years ago when,
as the business grew, our cursor (which used to process about
25K rows) started randomly crashing in the 30K or 40K range. 
Several people here recommended to replace the while loop with

goto/label, so that's what I did.  The goto works fine, so I'm
not interested in revisiting a while loop.

I'm not understanding what you're suggesting on a temp table. 
I would have to create a temp table that would hold probably

   

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[Albert Berry]

I think using the compounds would allow you to pick up the values from the 
index (index only retrieval) and then the code wouldn’t have to actually 
retrieve the data from file 2 for some of the selects. 

Albert

> On Apr 24, 2017, at 8:50 AM, karentellef via RBASE-L 
>  wrote:
> 
> That select statement is not my cursor, that's just one of the many 600 lines 
> of code that the cursor is evaluating.  The cursor itself would not be 
> index-able as it contains >=, not null, etc
> 
> I mean, yes, I could look at the many, many select statements within the loop 
> (my wild guess is that there's around 50 of them) and maybe there would be 10 
> or 15 different potential compound indexes.  I'm not sure if there's a 
> practical limit to the number of compound indexes you could create on a 
> single table (there would be probably 10 different "lookup" tables).  
> 
> So yeah, good idea, I'll look at all the lookups and check indexing.  But I'm 
> assuming that compounds would only work in instances where all of the 
> components are using "=", right?
> 
> 
> Karen
> 
> 
> 
> -Original Message-
> From: Albert Berry 
> To: rbase-l 
> Sent: Mon, Apr 24, 2017 9:40 am
> Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
> 
> Karen - wild thought. Would a compound index work here? 
> CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)
> 
> This would enable an index only retrieval. 
> 
> Albert
> On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L 
> > wrote:
> 
> Dan:
> 
> I had actually posted here on the list a few years ago when, as the business 
> grew, our cursor (which used to process about 25K rows) started randomly 
> crashing in the 30K or 40K range.  Several people here recommended to replace 
> the while loop with goto/label, so that's what I did.  The goto works fine, 
> so I'm not interested in revisiting a while loop.
> 
> I'm not understanding what you're suggesting on a temp table.  I would have 
> to create a temp table that would hold probably 30K rows, and my "select 
> into" would simply operate against a temp table rather than the permanent 
> table.  Are you saying selecting against a temp table would be faster than a 
> permanent table?
> 
> One thing that I've asked permission to try -- that is to avoid a "declare 
> cursor" altogether, which puts an hours-long "cursor lock" against a very 
> heavily used table.
> I'm thinking I could create a 40K row temp table with the policyID I'm to 
> process (the PK), with an autonumber column, such as:
>  1
>1222  2
>3535  3
> 
> Then using my goto/label block, I could (just quick code here, not 100% right)
>set var vcount int = 1
>label top
>select policyid into vid from temptable where autonumbercol = .vcount
>if vid is null then ; quit ; return
>select    into .  from policytable where policyid = .vid(this 
> replaces the "fetch")
>-- do all the "cursor" loop stuff
>set var vcount = (.vcount + 1)
>goto top
>  
> I don't know if this will speed up the code, but it prevents the routine from 
> putting ANY locks on the main table.
> 
> Karen
> 
> 
> 
> -Original Message-
> From: Dan Goldberg >
> To: rbase-l >
> Sent: Mon, Apr 24, 2017 8:34 am
> Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
> 
> I would find out why the while loop never completes. I have 9 level while 
> loops for my BOM to break down the assemblies into a parts list that runs 
> every night and it always runs.
>  
> There are many tricks on speeding up processing. Sometimes using temp tables 
> to reduce the amount of items in the where clause usually speeds things up. 
> This is only one of them I use.
>  
> Example, maybe use a temp table for the select statement below. I am assuming 
> the select statement runs many times.
>  
> --create temp table to hold values filtering out the standard items
> Create temp table tmpagtcomm (agentno integer, policy_no text, covcode 
> integer)
> Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where 
> polyr = 1 and agtcomm < 0 and paidtoagton is not null
>  
>  
> --While loop
> SELECT agtcomm INTO vtestagtcomm +
>   FROM tmpagtcomm +
>   WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
>   AND covcode = .vcovcode
>  
> This way it is not looking at all the where parameters which might slow it 
> down.
>  
> Not sure if this helps. I usually trace it as well to see what is slowing it 
> down.
>  
>  
> Dan Goldberg
>  
>  
>  
> From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com 
> ] 
> Sent: Monday, April 24, 2017 6:14 AM
> To: rbase-l@googlegroups.com 
> 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[Doug Hamilton]

Karen - I had a similar speed question when using the UPDATE command on 
2/3/15, although my questions was more about optimal use of the WHERE 
clause than GOTO and labels.  You, Dennis and others offered many 
helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the 
response from Dennis that might help you as far as order of the columns, 
using parens, etc.:


Doug,

First of all, is the column you are updating indexed?  That would slow updating 
it tremendously on a table this long.

If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order 
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):

UPDATE TxnHist +
SET ChryInvNbr = INV.ChryInvNbr +
FROM TxnHist TXN, ChryInvDtlTmp INV  +
  WHERE  +
TXN.VPlNmbr = INV.VPlNmbr  AND +
TXN.CusPnbr = INV.CusPnbr AND +
TXN.TxDate = INV.InvoiceDate


This will avoid trying to use any of the single indexes in TxnHist, and use a 
very efficient multi-column index to get the update value from the temp table.

Further optimization can be done by changing the where clause (and temp index) 
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that 
question.

BTW, I don't think labels and GOTOs are the problem.  Suppose you 
rewrote the code and saved a few milliseconds per loop by "optimizing" 
the GOTO/labels.  At 40,000 records that's only a difference of, say, 40 
to 120 seconds total (a few minutes), hardly a dent in the several hours 
the program now runs.  I think Dennis's first point might be a clue: 
Updating an indexed column.


Doug

On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:
That select statement is _not_ my cursor, that's just one of the many 
600 lines of code that the cursor is evaluating.  The cursor itself 
would not be index-able as it contains >=, not null, etc


I mean, yes, I could look at the many, many select statements within 
the loop (my wild guess is that there's around 50 of them) and maybe 
there would be 10 or 15 different potential compound indexes.  I'm not 
sure if there's a practical limit to the number of compound indexes 
you could create on a single table (there would be probably 10 
different "lookup" tables).


So yeah, good idea, I'll look at all the lookups and check indexing.  
But I'm assuming that compounds would only work in instances where all 
of the components are using "=", right?



Karen



-Original Message-
From: Albert Berry 
To: rbase-l 
Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?

Karen - wild thought. Would a compound index work here?
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)

This would enable an index only retrieval.

Albert

On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L
> wrote:

Dan:

I had actually posted here on the list a few years ago when, as
the business grew, our cursor (which used to process about 25K
rows) started randomly crashing in the 30K or 40K range.  Several
people here recommended to replace the while loop with goto/label,
so that's what I did.  The goto works fine, so I'm not interested
in revisiting a while loop.

I'm not understanding what you're suggesting on a temp table.  I
would have to create a temp table that would hold probably 30K
rows, and my "select into" would simply operate against a temp
table rather than the permanent table.  Are you saying selecting
against a temp table would be faster than a permanent table?

One thing that I've asked permission to try -- that is to avoid a
"declare cursor" altogether, which puts an hours-long "cursor
lock" against a very heavily used table.
I'm thinking I could create a 40K row temp table with the policyID
I'm to process (the PK), with an autonumber column, such as:
  1
   1222  2
   3535  3

Then using my goto/label block, I could (just quick code here, not
100% right)
   set var vcount int = 1
   label top
   select policyid into vid from temptable where autonumbercol =
.vcount
   if vid is null then ; quit ; return
   select    into .  from policytable where policyid =
.vid(this replaces the "fetch")
   -- do all the "cursor" loop stuff
   set var vcount = (.vcount + 1)
   goto top

I don't know if this will speed up the code, but it prevents the
routine from putting ANY locks on the main table.

Karen



-Original Message-
From: Dan Goldberg >
To: rbase-l 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[karentellef via RBASE-L]

That select statement is not my cursor, that's just one of the many 600 lines 
of code that the cursor is evaluating.  The cursor itself would not be 
index-able as it contains >=, not null, etc

I mean, yes, I could look at the many, many select statements within the loop 
(my wild guess is that there's around 50 of them) and maybe there would be 10 
or 15 different potential compound indexes.  I'm not sure if there's a 
practical limit to the number of compound indexes you could create on a single 
table (there would be probably 10 different "lookup" tables).  

So yeah, good idea, I'll look at all the lookups and check indexing.  But I'm 
assuming that compounds would only work in instances where all of the 
components are using "=", right?


Karen

 

 

 

-Original Message-
From: Albert Berry 
To: rbase-l 
Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?


Karen - wild thought. Would a compound index work here? 
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)


This would enable an index only retrieval. 


Albert


On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L  
wrote:


Dan:

I had actually posted here on the list a few years ago when, as the business 
grew, our cursor (which used to process about 25K rows) started randomly 
crashing in the 30K or 40K range.  Several people here recommended to replace 
the while loop with goto/label, so that's what I did.  The goto works fine, so 
I'm not interested in revisiting a while loop.

I'm not understanding what you're suggesting on a temp table.  I would have to 
create a temp table that would hold probably 30K rows, and my "select into" 
would simply operate against a temp table rather than the permanent table.  Are 
you saying selecting against a temp table would be faster than a permanent 
table?

One thing that I've asked permission to try -- that is to avoid a "declare 
cursor" altogether, which puts an hours-long "cursor lock" against a very 
heavily used table.
I'm thinking I could create a 40K row temp table with the policyID I'm to 
process (the PK), with an autonumber column, such as:
     1
   1222  2
   3535  3

Then using my goto/label block, I could (just quick code here, not 100% right)
   set var vcount int = 1
   label top
   select policyid into vid from temptable where autonumbercol = .vcount
   if vid is null then ; quit ; return
   select    into .  from policytable where policyid = .vid(this 
replaces the "fetch")
   -- do all the "cursor" loop stuff
   set var vcount = (.vcount + 1)
   goto top
 
I don't know if this will speed up the code, but it prevents the routine from 
putting ANY locks on the main table.

Karen







-Original Message-
From: Dan Goldberg 
To: rbase-l 
Sent: Mon, Apr 24, 2017 8:34 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?



I would find out why the while loop never completes. I have 9 level while loops 
for my BOM to break down the assemblies into a parts list that runs every night 
and it always runs.
 
There are many tricks on speeding up processing. Sometimes using temp tables to 
reduce the amount of items in the where clause usually speeds things up. This 
is only one of them I use.
 
Example, maybe use a temp table for the select statement below. I am assuming 
the select statement runs many times.
 
--create temp table to hold values filtering out the standard items
Create temp table tmpagtcomm (agentno integer, policy_no text, covcode integer)
Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where 
polyr = 1 and agtcomm < 0 and paidtoagton is not null
 
 
--While loop
SELECT agtcomm INTO vtestagtcomm +
  FROM tmpagtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode
 
This way it is not looking at all the where parameters which might slow it down.
 
Not sure if this helps. I usually trace it as well to see what is slowing it 
down.
 
 
Dan Goldberg
 
 
 
From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com] 
Sent: Monday, April 24, 2017 6:14 AM
To: rbase-l@googlegroups.com
Subject: [RBASE-L] - Thoughts on speeding up a cursor?
 
I inherited a monster program.  It's 800 physical lines of code, separated like 
this:

100 lines of pre-processing code before we set a cursor

600 lines of code that are within a DECLARE CURSOR that processes 40,000 
records.  We cannot use a "while" loop because it never completed, so we use a 
"goto / label" structure to move around, and it always completes fine.

100 lines of post-cursor code.


I am trying to speed up this cursor as it now takes hours to process.  There 
are no "run" statements within this program, no printing of reports other than 
post-cursor.

Within that cursor loop, there are many "goto" statements to move 

AW: [RBASE-L] - issue with receipt printer

[Armin Thoma]

Steve,


thank you for your advice. There are many thing to be tested. Actually no 
success with USB2, but connected with USB3 there are no problems!


Armin



Von: rbase-l@googlegroups.com  im Auftrag von Steve 
Johnson 
Gesendet: Montag, 24. April 2017 02:01
An: Armin Thoma
Betreff: Re: [RBASE-L] - issue with receipt printer

Armin,

With 7 POS stations and receipt printers at work, this seems to happen on one 
of them, seems like monthly.  ‎I will usually begin by checking 'See What's 
Printing', in Devices and Printers to see if there are multiple documents in 
cue. In your case, removing the USB cable is more like a reinstall printer lite 
that effectively resets the printer driver and probably the print spooler. If 
this happens frequently, I would try the brute force method, which is to 
disconnect the printer, then remove the printer in Devices and Printers, then 
reinstall the printer. In one case, I found the problem to be in the USB cable. 
Since you do get it to print, I doubt that it would be a corrupt printer file 
in the spooler which requires manually deleting the printer file in most cases.

If there is nothing in the,'See What's Printing'‎ and there are no files in the 
Printer folder, chances are the print job was sent to another printer, 
especially if there is a copy of the printer in Devices and Printers which will 
result in two printer icons with nearly identical names (usually PrinterName 
and PrinterName (copy1)). This usually happens when the user needs to select 
the printer before printing when multiple printers are needed, such as a tag 
printer in addition to the receipt printer and the system did not return to the 
default printer.

Hope this helps a little as printer drivers and the windows spooler can be 
frustrating to deal with.

Steve J

Sent from BlackBerry Passport SE
From: Armin Thoma
Sent: Thursday, April 20, 2017 4:25 PM
To: rbase-l@googlegroups.com
Reply To: rbase-l@googlegroups.com
Subject: [RBASE-L] - issue with receipt printer



Hi


Mostly no problem with printresult. But the printer likes the occasional 
boycott.

After pull the usb-conector and connect again the receipt finally comes out.

The print job is given to windows, but somtimes the printer does not accept it.

Did anyone have experience with this situation?

(Surley no RBase problem.)


RBase XE

printer: star TSP100 futurePRNT

driver: ok, spooler

port : USB2

hub:  no

cable: new

energymodi:  off

PC: hp elitebook 8740w, windows 7


Armin


--
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[Albert Berry]

Karen - wild thought. Would a compound index work here? 
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)

This would enable an index only retrieval. 

Albert
> On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L 
>  wrote:
> 
> Dan:
> 
> I had actually posted here on the list a few years ago when, as the business 
> grew, our cursor (which used to process about 25K rows) started randomly 
> crashing in the 30K or 40K range.  Several people here recommended to replace 
> the while loop with goto/label, so that's what I did.  The goto works fine, 
> so I'm not interested in revisiting a while loop.
> 
> I'm not understanding what you're suggesting on a temp table.  I would have 
> to create a temp table that would hold probably 30K rows, and my "select 
> into" would simply operate against a temp table rather than the permanent 
> table.  Are you saying selecting against a temp table would be faster than a 
> permanent table?
> 
> One thing that I've asked permission to try -- that is to avoid a "declare 
> cursor" altogether, which puts an hours-long "cursor lock" against a very 
> heavily used table.
> I'm thinking I could create a 40K row temp table with the policyID I'm to 
> process (the PK), with an autonumber column, such as:
>  1
>1222  2
>3535  3
> 
> Then using my goto/label block, I could (just quick code here, not 100% right)
>set var vcount int = 1
>label top
>select policyid into vid from temptable where autonumbercol = .vcount
>if vid is null then ; quit ; return
>select    into .  from policytable where policyid = .vid(this 
> replaces the "fetch")
>-- do all the "cursor" loop stuff
>set var vcount = (.vcount + 1)
>goto top
>  
> I don't know if this will speed up the code, but it prevents the routine from 
> putting ANY locks on the main table.
> 
> Karen
> 
> 
> 
> -Original Message-
> From: Dan Goldberg 
> To: rbase-l 
> Sent: Mon, Apr 24, 2017 8:34 am
> Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
> 
> I would find out why the while loop never completes. I have 9 level while 
> loops for my BOM to break down the assemblies into a parts list that runs 
> every night and it always runs.
>  
> There are many tricks on speeding up processing. Sometimes using temp tables 
> to reduce the amount of items in the where clause usually speeds things up. 
> This is only one of them I use.
>  
> Example, maybe use a temp table for the select statement below. I am assuming 
> the select statement runs many times.
>  
> --create temp table to hold values filtering out the standard items
> Create temp table tmpagtcomm (agentno integer, policy_no text, covcode 
> integer)
> Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where 
> polyr = 1 and agtcomm < 0 and paidtoagton is not null
>  
>  
> --While loop
> SELECT agtcomm INTO vtestagtcomm +
>   FROM tmpagtcomm +
>   WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
>   AND covcode = .vcovcode
>  
> This way it is not looking at all the where parameters which might slow it 
> down.
>  
> Not sure if this helps. I usually trace it as well to see what is slowing it 
> down.
>  
>  
> Dan Goldberg
>  
>  
>  
> From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com 
> ] 
> Sent: Monday, April 24, 2017 6:14 AM
> To: rbase-l@googlegroups.com 
> Subject: [RBASE-L] - Thoughts on speeding up a cursor?
>  
> I inherited a monster program.  It's 800 physical lines of code, separated 
> like this:
> 
> 100 lines of pre-processing code before we set a cursor
> 
> 600 lines of code that are within a DECLARE CURSOR that processes 40,000 
> records.  We cannot use a "while" loop because it never completed, so we use 
> a "goto / label" structure to move around, and it always completes fine.
> 
> 100 lines of post-cursor code.
> 
> 
> I am trying to speed up this cursor as it now takes hours to process.  There 
> are no "run" statements within this program, no printing of reports other 
> than post-cursor.
> 
> Within that cursor loop, there are many "goto" statements to move around 
> within that cursor loop.  
> My assumption:  when the program hits a "goto" command, it must run through 
> every line of code, one line at a time, to find the "label".  It would go all 
> the way to the end of the program, and if it cannot find the label, it then 
> goes back up to line 1 of the program and scans every line until it finally 
> hits the label.   In this program, sometimes these labels are after the goto, 
> sometimes they are "above" it.  
> 
> So question 1:  is my assumption correct?
> 
> If it is:  Let's say for readability that a line has been separated into 
> multiple lines, such as this:
> SELECT agtcomm INTO vtestagtcomm +
>   FROM agtcomm 

Re: [RBASE-L] - Thoughts on speeding up a cursor?

[karentellef via RBASE-L]

Dan:

I had actually posted here on the list a few years ago when, as the business 
grew, our cursor (which used to process about 25K rows) started randomly 
crashing in the 30K or 40K range.  Several people here recommended to replace 
the while loop with goto/label, so that's what I did.  The goto works fine, so 
I'm not interested in revisiting a while loop.

I'm not understanding what you're suggesting on a temp table.  I would have to 
create a temp table that would hold probably 30K rows, and my "select into" 
would simply operate against a temp table rather than the permanent table.  Are 
you saying selecting against a temp table would be faster than a permanent 
table?

One thing that I've asked permission to try -- that is to avoid a "declare 
cursor" altogether, which puts an hours-long "cursor lock" against a very 
heavily used table.
I'm thinking I could create a 40K row temp table with the policyID I'm to 
process (the PK), with an autonumber column, such as:
     1
   1222  2
   3535  3

Then using my goto/label block, I could (just quick code here, not 100% right)
   set var vcount int = 1
   label top
   select policyid into vid from temptable where autonumbercol = .vcount
   if vid is null then ; quit ; return
   select    into .  from policytable where policyid = .vid(this 
replaces the "fetch")
   -- do all the "cursor" loop stuff
   set var vcount = (.vcount + 1)
   goto top
 
I don't know if this will speed up the code, but it prevents the routine from 
putting ANY locks on the main table.

Karen

 

 

 

-Original Message-
From: Dan Goldberg 
To: rbase-l 
Sent: Mon, Apr 24, 2017 8:34 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?



I would find out why the while loop never completes. I have 9 level while loops 
for my BOM to break down the assemblies into a parts list that runs every night 
and it always runs.
 
There are many tricks on speeding up processing. Sometimes using temp tables to 
reduce the amount of items in the where clause usually speeds things up. This 
is only one of them I use.
 
Example, maybe use a temp table for the select statement below. I am assuming 
the select statement runs many times.
 
--create temp table to hold values filtering out the standard items
Create temp table tmpagtcomm (agentno integer, policy_no text, covcode integer)
Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where 
polyr = 1 and agtcomm < 0 and paidtoagton is not null
 
 
--While loop
SELECT agtcomm INTO vtestagtcomm +
  FROM tmpagtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode
 
This way it is not looking at all the where parameters which might slow it down.
 
Not sure if this helps. I usually trace it as well to see what is slowing it 
down.
 
 
Dan Goldberg
 
 
 
From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 6:14 AM
To: rbase-l@googlegroups.com
Subject: [RBASE-L] - Thoughts on speeding up a cursor?
 
I inherited a monster program.  It's 800 physical lines of code, separated like 
this:

100 lines of pre-processing code before we set a cursor

600 lines of code that are within a DECLARE CURSOR that processes 40,000 
records.  We cannot use a "while" loop because it never completed, so we use a 
"goto / label" structure to move around, and it always completes fine.

100 lines of post-cursor code.


I am trying to speed up this cursor as it now takes hours to process.  There 
are no "run" statements within this program, no printing of reports other than 
post-cursor.

Within that cursor loop, there are many "goto" statements to move around within 
that cursor loop. 
My assumption:  when the program hits a "goto" command, it must run through 
every line of code, one line at a time, to find the "label".  It would go all 
the way to the end of the program, and if it cannot find the label, it then 
goes back up to line 1 of the program and scans every line until it finally 
hits the label.   In this program, sometimes these labels are after the goto, 
sometimes they are "above" it. 

So question 1:  is my assumption correct?

If it is:  Let's say for readability that a line has been separated into 
multiple lines, such as this:
SELECT agtcomm INTO vtestagtcomm +
  FROM agtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode AND polyr = 1 AND agtcomm < 0 +
  AND paidtoagton IS NOT NULL


As it searches for a matching "label", is RBase evaluating 5 lines of code, one 
at a time?  Or is it "smart" enough to know it's one command and evaluates it 
just once?

So IOW: if I was to retype this command so that it takes just one really long 
line, or maybe just 2 lines, would it be "quicker" for RBase to search for a 
label?   I wouldn't normally be so anal about it, 

RE: [RBASE-L] - Thoughts on speeding up a cursor?

[Dan Goldberg]

I would find out why the while loop never completes. I have 9 level while loops 
for my BOM to break down the assemblies into a parts list that runs every night 
and it always runs.

There are many tricks on speeding up processing. Sometimes using temp tables to 
reduce the amount of items in the where clause usually speeds things up. This 
is only one of them I use.

Example, maybe use a temp table for the select statement below. I am assuming 
the select statement runs many times.

--create temp table to hold values filtering out the standard items
Create temp table tmpagtcomm (agentno integer, policy_no text, covcode integer)
Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where 
polyr = 1 and agtcomm < 0 and paidtoagton is not null


--While loop
SELECT agtcomm INTO vtestagtcomm +
  FROM tmpagtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode

This way it is not looking at all the where parameters which might slow it down.

Not sure if this helps. I usually trace it as well to see what is slowing it 
down.


Dan Goldberg



From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 6:14 AM
To: rbase-l@googlegroups.com
Subject: [RBASE-L] - Thoughts on speeding up a cursor?

I inherited a monster program.  It's 800 physical lines of code, separated like 
this:

100 lines of pre-processing code before we set a cursor

600 lines of code that are within a DECLARE CURSOR that processes 40,000 
records.  We cannot use a "while" loop because it never completed, so we use a 
"goto / label" structure to move around, and it always completes fine.

100 lines of post-cursor code.


I am trying to speed up this cursor as it now takes hours to process.  There 
are no "run" statements within this program, no printing of reports other than 
post-cursor.

Within that cursor loop, there are many "goto" statements to move around within 
that cursor loop.
My assumption:  when the program hits a "goto" command, it must run through 
every line of code, one line at a time, to find the "label".  It would go all 
the way to the end of the program, and if it cannot find the label, it then 
goes back up to line 1 of the program and scans every line until it finally 
hits the label.   In this program, sometimes these labels are after the goto, 
sometimes they are "above" it.

So question 1:  is my assumption correct?

If it is:  Let's say for readability that a line has been separated into 
multiple lines, such as this:
SELECT agtcomm INTO vtestagtcomm +
  FROM agtcomm +
  WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
  AND covcode = .vcovcode AND polyr = 1 AND agtcomm < 0 +
  AND paidtoagton IS NOT NULL


As it searches for a matching "label", is RBase evaluating 5 lines of code, one 
at a time?  Or is it "smart" enough to know it's one command and evaluates it 
just once?

So IOW: if I was to retype this command so that it takes just one really long 
line, or maybe just 2 lines, would it be "quicker" for RBase to search for a 
label?   I wouldn't normally be so anal about it, but when you're doing this 
40,000 times.


Karen




--
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.