Custom RowsCopy() Performance

[Ricky Wallace]

Hello,

I have two datawindows with a different number of columns (we'll call them
dw_a and dw_b). The dw_b datawindow has a subset of the dw_a columns. I
want to copy the rows from dw_a to dw_b, but I can't use rowsCopy() because
of the different number of columns. So I attempted creating my own
rowsCopy() function.

It works, but its performance is terrible for large sets of data, and it
seems to be exponentially slower as it copies (i.e. the first few hundred
rows copy quickly, then it gets slower and slower).

My datawindow can have potentially a few thousand rows.

My code is as follows. Does anyone have any tips on making it faster? Or a
different way to copy the rows?

Thanks!
-Ricky Wallace

LONG i, j, ll_row
STRING ls_col_nm, ls_data_type

/** Reset the datawindow. **/
dw_b.reset()

/** Loop through the rows in dw_a. **/
FOR i = 1 TO dw_a.rowCount()

/** For each row on dw_a, create a row on dw_b. **/
ll_row = dw_b.insertRow(0)

/** Loop over the number of columns in dw_b. **/
FOR j = 1 TO LONG(dw_b.Describe("DataWindow.Column.Count"))

/** Get this column's name and data type. **/
ls_col_nm = dw_b.Describe('#' + STRING(j) + '.Name')
ls_data_type = dw_b.Describe('#' + STRING(j) + '.ColType')

/** Based on the data type, get the data value from dw_a. **/
IF (LEFT(ls_data_type, 4) = 'char') THEN
/** String **/
dw_b.setItem(ll_row, ls_col_nm, dw_a.getItemString(i, ls_col_nm))

ELSEIF (ls_data_type = 'date') THEN
/** Date **/
dw_b.setItem(ll_row, ls_col_nm, dw_a.getItemDate(i, ls_col_nm))

ELSEIF (ls_data_type = 'datetime') THEN
/** Datetime **/
dw_b.setItem(ll_row, ls_col_nm, dw_a.getItemDateTime(i, ls_col_nm))

ELSEIF (ls_data_type = 'int') OR (ls_data_type = 'long') OR
(LEFT(ls_data_type, 7) = 'decimal') OR &
(ls_data_type = 'number') OR (ls_data_type = 'real') THEN
/** Integer/Long **/
dw_b.setItem(ll_row, ls_col_nm, dw_a.getItemNumber(i, ls_col_nm))
END IF

NEXT
NEXT

[Jeremy Lakeman]

Firstly you need to reduce the number of information functions you're
calling in your main loop.
You should create some local variables for the rowcount, column count,
column names and types so you don't have to keep looking the up.

However there are some much faster ways to access data in a datawindow
control.

You could try copying entire columns at once using this syntax:
dw_b.object.column_name.primary = dw_a.object.column_name.primary

Or copy the entire block of data at once, provided the columns are
defined in the same order:
dw_b.object.data[1,dw_a.rowcount(),1,ll_cols] =
dw_a.object.data[1,dw_a.rowcount(),1,ll_cols]

[John Olson [Team Sybase]]

Ricky,

just thinking out loud... create dw_c that looks like dw_a. Do a rowscopy
from a to c. Dynamically destroy the columns in c that don't exist in b. Now
you have b. Or if you don't want to use c then do a rowscopy from c to b.

Regards,
John

"Ricky Wallace" <rwal...@argosys.com> wrote in message
news:46b63b07$1@forums-1-dub...

[Ricky Wallace]

Hi John,

Thanks for the reply!

I gave it a try and ran into a problem. In your example, I do not want to
use dw_c, so I took your final step and attempted to do a rowsCopy() from
dw_c to dw_b. Unfortunately it failed, and I believe it is because when
destroying columns in dw_c, the column IDs for the remaining columns are
retained. So, in the base case, if there is one column in dw_c and one
matching column in dw_b, rowsCopy() fails because the column's ID in dw_c is
(in my case) 8, whereas the column's ID in dw_b's is 1.

Aside from the base case, in most other cases, the columns in dw_b might be
in a different order than they are in dw_a. My next thought was to try to
change the column IDs in dw_c to match the ones in dw_b, but the
Powerbuilder help file indicates that I cannot change the column IDs
dynamically (probably for good reason!)

Finally, (the story unfolds...) since I am creating dw_b's syntax on the
fly, I thought about making its column IDs match up with the ones in dw_a,
but I'm not sure how to do that, since the column IDs seem to go in order of
how they are declared in the table() declaration. Unless there's an ID
field that I can specify in the table() declaration that I don't know about.
Is there?

Or do you have any other suggestions?

Thanks again!
-Ricky

"John Olson [Team Sybase]" <john.olson@nospam_teamsybase.com> wrote in
message news:46b66ad2$1@forums-1-dub...

[Philip Salgannik]

You HAVE to use dot notation for doing these massive assignments of column
data for all rows...

"Ricky Wallace" <rwal...@argosys.com> wrote in message
news:46b63b07$1@forums-1-dub...

[Ricky Wallace]

Hi Philip,

Thanks for the response! What sort of dot notation are you talking about?

My problem is that I do not know at compile-time what the column names in my
source or target datawindows are (I'm trying to do this generically in an
ancestor script).

I know I could use the column.Primary notation to copy the column values,
but I don't know what the column name is; same goes if I try to do the
#<column_id>.Primary notation. Can I stick in a variable somewhere with the
column's name? The same goes for the .Data[] array, which I've been trying
to understand for a while.

Do you have any sample code?

Thanks!
-Ricky

"Philip Salgannik" <philema...@comcast.net> wrote in message
news:46b68353$1@forums-1-dub...

[Ricky Wallace]

OK, I heavily searched the newsgroups and found what is apparently some
"unsupported" code that allows you to get a DWObject reference from a column
name. So, using the following code, it seems to work lightning fast (I
think Philip posted this code at some point). Thanks for all the help
everyone.

-Ricky

LONG i, ll_column_count
STRING ls_col_nm
DWObject ldwo_a, ldwo_b

ll_column_count = LONG(dw_b.Describe("Datawindow.Column.Count"))

FOR i = 1 TO ll_column_count
ls_col_nm = dw_b.Describe("#" + STRING(i) + '.Name')
ldwo_a = dw_a.Object.__get_attribute(ls_col_nm, TRUE)
ldwo_b = dw_b.Object.__get_attribute(ls_col_nm, TRUE)
ldwo_b.Primary = ldwo_a.Primary
NEXT

"Ricky Wallace" <rwal...@argosys.com> wrote in message

news:46b687cd$1@forums-1-dub...

[Philip Salgannik]

The way you did this should go in the rules of these newsgroups for what is
expected from those posting questions :-)))

FWIW, you don't have to rely on undocumented features, though.
Lookup "Syntax for data in a block of rows and columns" in Datawindow
reference:
Description

A DataWindow data expression accesses data in a range of rows and columns
when you specify the starting and ending row and column numbers.

Syntax

dwcontrol.Object.Data {.buffer } {.datasource } [ startrownum,
startcolnum, endrownum, endcolnum ]

"Ricky Wallace" <rwal...@argosys.com> wrote in message

news:46b695d0$1@forums-1-dub...

[jan_...@yahoo.dk]

//Following code will probably do the job.
// It will compare the column names in adFrom with the column names in
adTo and if the name
// exists in both, and have same datatypes - data will be copied

long ll_totCols,ll_col,ll_toColNo,ll_totRows
string ls_colName,ls_fromColType,ls_toColNo
ll_totCols=long(adFrom.describe("dataWindow.column.count"))
ll_totRows=adFrom.rowCount()
if ll_totRows = 0 then return 1
for ll_col=1 to ll_totCols
ls_colName=adFrom.describe("#"+string(ll_col)+".Name")
ls_toColNo=adTo.describe(ls_colName+".id")
if ls_toColNo <> '!' then
ll_toColNo=long(ls_toColNo)
if mid(adFrom.describe("#"+string(ll_col)+".colType"),1,4) &
= mid(adTo.describe("#"+string(ls_toColNo)+".colType"),1,4) then
adTo.object.data[1,ll_toColNo,ll_totRows,ll_toColNo]&
=adFrom.object.data[1,ll_col,ll_totRows,ll_col]
end if
end if
next
return 1

[Ricky Wallace]

That's great information; thanks! Here's my final code using the syntax you
suggested (just in case anyone else wants to use it).

-Ricky

LONG i, ll_column_count, ll_dwa_col_id, ll_row_count
STRING ls_col_nm

ll_row_count = dw_a.rowCount()

ll_column_count = LONG(dw_b.Describe("Datawindow.Column.Count"))

FOR i = 1 TO ll_column_count

/** Get the Column Name. **/
ls_col_nm = dw_b.Describe("#" + STRING(i) + ".Name")

/** Get the column ID from dw_a. **/
ll_dwa_col_id = LONG(dw_a.Describe(ls_col_nm + ".ID"))

/** Copy this column's values from the dw_a to dw_b. **/
dw_b.Object.Data[1, i, ll_row_count, i] = &
dw_a.Object.Data[1, ll_main_col_id, ll_row_count, ll_dwa_col_id]

NEXT

"Philip Salgannik" <PhilipS...@work.com> wrote in message
news:46b73677@forums-1-dub...

[Ricky Wallace]

ack...I didn't do a great job replacing my code with the "generic" code
below -- of course I don't name my datawindows dw_a and dw_b :). The last
line should read:

dw_b.Object.Data[1, i, ll_row_count, i] = &

dw_a.Object.Data[1, ll_dwa_col_id, ll_row_count, ll_dwa_col_id]
^^^^^^^^^^^^^

-Ricky

"Ricky Wallace" <rwal...@argosys.com> wrote in message

news:46b75c9d$1@forums-1-dub...