18 replies to this topic

[Marco.Bega]

Hi,
is it possible with Pro-iv 4.6 to do a the following select :

$STRINGA = '%001%'
SQL
SELECT * FROM TABLE_NAME WHERE AN_COD LIKE :$STRINGA
ENDSQL

Because nothing rows return me.

if i put this select it all ok.
SQL
SELECT * FROM TABLE_NAME WHERE AN_COD LIKE '%001%'
ENDSQL
I don't put SQL DYNAMIC because pro-iv return an error.



Thanks
Marco

[Joseph Bove]

Marco,

I'm almost definite that you need 5.5 in order to do the SQL DYNAMIC.

I don't think you'll get it to work in 4.6 - unless you used a nasty CASE statement.

hth,

Joseph

[mdexter]

Your problem might just be quoting. Have you tried $STRINGA = "'%001%'" (i.e., putting the single quotes inside of double quotes)? Otherwise, if you need SQL DYNAMIC, Joseph is correct. This was added post-4.6. You can definitely do this with SQL DYNAMIC. HTH. Mark Dexter

[Neil Hunter]

Oracle or SQL?

Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless

If you running SQL you might need to pad % for the length of the field ie if AN_COD is A6 then $STRINGA = '%001%%'

[Rob Donovan]

Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless

ProIV only selects the columns that you modify in logic... (with a few exceptions, like arrays and variables that might get inherited from other table reads)

Rob D.

[Neil Hunter]

Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless

ProIV only selects the columns that you modify in logic... (with a few exceptions, like arrays and variables that might get inherited from other table reads)

Rob D.

In 4.6 ?

From what little i've read in trace files and from Oracle Trace files, the whole table is selected.

On the other hand I might be wrong

[Rob Donovan]

hmm...

Dont have a 4.6 version around to test on, but that is what 5.0 does.

Maybe it was a 5.0 enhancment.

However, you do have to test with a very simple function, ie just an update with a single table with lots of columns on it, and in BW logic assign one of the columns... and see what the trace file says....

I would be interested to know what the results are....if anyone can test it on 4.6

Rob D.

[Neil Hunter]

Only 4.6 machine I have access to is at a client. I'll try this when I get a chance to get on site. Would also love to know the result, as they also are always on the lookout for improving performance on their machine

[Cleve Haynes]

My experience with 4.6 was if you use transparent mode SQL, it only fetches the fields you refer to in the function:

SQL
SELECT FROM TABLENAME
WHERE SOMETHING = 'A_VALUE'
ENDSQL

However, if you use full function mode SQL (this is where you define the fields to select), it will return the explict fields you define in your select statement - so this full function mode example should select all fields:

SQL
SELECT *
FROM TABLENAME
WHERE SOMETHING = 'A_VALUE'
ENDSQL

Whereas this full function mode example will just select the specified fields:

SQL
SELECT FIELD1, FIELD2, FIELD3
FROM TABLENAME
WHERE SOMETHING = 'A_VALUE'
ENDSQL

Cleve

[Rob Donovan]

Only 4.6 machine I have access to is at a client. I'll try this when I get a chance to get on site. Would also love to know the result, as they also are always on the lookout for improving performance on their machine

There is quite a bit to test and look at regarding performance with SQL, in this area.

Sometimes it takes a bit of work to figure out why ProIV is requesting all fields, but if you manage to write the function in a certain way, you can get great performance gains from this....

One area you should look into is Arrays. If you have file defs with large arrays, this can slow down reads alot. You need to use some tricks like an alternate file def with the arrays specified as individual fields (ie Balance1, Balance2, Balance3 instead of Balance(3)...

Although ProIV tries to figure out what fields you need , it does not try to figure out which array elements you are using, so it drags back all the data from Oracle for that array.

Rob.

[Richard Bassett]

Yup - AFAIK V4.6 has always minimized the set of columns selected for "transparent" and "Type-1-full-function" SQL to those that are actually "used" by your function. This is a significant advantage of Type-1 over Type-2 - and it also decouples your code from possible changes to the actual database definitions.

Cleve - your first example is "Type-1" full function mode rather than "transparent" - and the others "Type-2".

As Rob pointed out though, you have to remember that if a filevar occurs in more than one referenced filedef (note that this includes using the same filedef more than once) then ProIV has to treat that filevar as "used" even if it's not mentioned in logic/fields/headers etc.

Marco - although I haven't used anything exactly like your original example, I would have expected it to work fine - if it doesn't it seem to me there's a bug of some kind. Are you on Oracle or something else?

As I understood it, the purpose of SQL DYNAMIC is to vary the whole where clause - you don't need it to vary a single bound value.

[George Macken]

Hi

this is an example of LIVE code from our application - working on both
ORACLE 7 with pro-iv 4.6
ORACLE 9i with pro-iv 5.5

ER_NAME is type ALPHA on the ORACLE table ERDATA

IF $ER.SURNAME # '' THEN $WILD.SURNAME = '%' + $ER.SURNAME + '%';

SELECT FROM ERDATA WHERE ER_NAME LIKE :$WILD.SURNAME

hth

George

[Richard Bassett]

Although ProIV tries to figure out what fields you need , it does not try to figure out which array elements you are using, so it drags back all the data from Oracle for that array.

In general ProIV couldn't possibly figure out what elements are used, because the "normal" of an array is with a variable subscript. To "know" what subset of the possible subscripts might actually be used requires a "global" understanding of the function and its execution that only the programmer has (we hope).

If all subscripts are numeric literals then, as Rob's post suggests, it's not clear you really needed an array anyway. I would sympathize with ProIV's apparent decision that this was not a special case worth optimizing for.

[Rob Donovan]

Just to explain why....

There is a large application used at many sites in Australia, that has their balance file with lots of field arrays like BAL(40), and PARTBAL(70), to hold different balances.

Some places loop through looking at all Bal arrays, while some updates just use say BAL(9)...

It made a huge difference in speed because of the Array.

Agreed that the underlying design was a bit 'wierd', but that was something that was unlikely to change as the whole system was based around it

It would be nice if there was some more optimization done around this, since it was giving us so much of a gain... but the work around made the code a bit messy....

Rob.

[Joseph Bove]

Guys,

Follow up question to the ProIV traces and what is selected.

Is it the case that if you do not embed any SQL, but rely instead on ProIV converting a SEL-ONLY, SEL-PARTIAL or SEL-RANGE to SQL that it will select all columns every time?

From what I've seen in trace logs, I've thought this to be the case. However, based on my read of this thread, maybe I've not looked closely enough.

If the answer to my question is yes, then it seems that the best programming practice would be to embed SQL in every Update, Report, and LSCALL. Even if the SQL is no different than the SEL logic, it would be beneficial nearly always.

Thanks in advance,

Joseph