473,320 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Bad performance with like in cursor

Hello NG

I've defined a cursor like the following in my COBOL Programm:

DECLARE testc CURSOR FOR
SELECT ...
FROM ...
WHERE
field1 LIKE :hostvariable1
field2 LIKE :hostvariable2
field3 LIKE :hostvariable3
....
fieldN LIKE :hostvariableN
ORDER BY field3, field2, field1
OPTIMIZE FOR 16 ROWS
FOR FETCH ONLY

There is a big performance problem with that. I thougt if I fill all
hostvariables with '%' SQL would ignore it when I open my cursor, but I
think it doesn't.(Yes some hostvariables I'm filling with something like
'123%' or 'SMITH%', but that should work)

If there is a big problem I havn't seen? Does a LIKE '%' in Hostvariables to
be ingnored?

Is there a better way to do so, without declareing cursors for every special
need?(That works, I've testet it)
Or should I ask the COBOL programmers, is it maybe a COBOL specific problem?

Thanks for replys

Micha
Nov 12 '05 #1
7 7237
Micha,

DB2 makes all decisions when the query is compiled. at that point the
values are not available.
It seesm to me that what you really want here is a dynamic cursor.
I.e. glue together the statement once you have the values for the
hostvariables.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2
>> Hello NG

I've defined a cursor like the following in my COBOL Programm:

DECLARE testc CURSOR FOR
SELECT ...
FROM ...
WHERE
field1 LIKE :hostvariable1
field2 LIKE :hostvariable2
field3 LIKE :hostvariable3
....
fieldN LIKE :hostvariableN
ORDER BY field3, field2, field1
OPTIMIZE FOR 16 ROWS
FOR FETCH ONLY

There is a big performance problem with that. I thougt if I fill all
hostvariables with '%' SQL would ignore it when I open my cursor, but I
think it doesn't.(Yes some hostvariables I'm filling with something like
'123%' or 'SMITH%', but that should work)

If there is a big problem I havn't seen? Does a LIKE '%' in Hostvariables to be ingnored?

Is there a better way to do so, without declareing cursors for every special need?(That works, I've testet it)
Or should I ask the COBOL programmers, is it maybe a COBOL specific problem?
Thanks for replys

Micha
Micha,

DB2 makes all decisions when the query is compiled. at that point the
values are not available.
It seesm to me that what you really want here is a dynamic cursor.
I.e. glue together the statement once you have the values for the
hostvariables.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

DB2 for OS/390 can delay certain decisions about access path at run time
when a LIKE is used with a host variable, even in statically bound SQL. It
may change access path depending on where the % is in the string (before the
text or after the text).

However, DB2 will not totally ignore one of the predicates when it has a %
only. In fact, DB2 interprets % only as selecting all rows to satisfy that
predicate. You would probably be better off filling with a blank, but I am
not sure if that would help with performance.

As Segre mentioned, a dynamic cursor would work fine, or you can create
multiple static cursors.
Nov 12 '05 #3
Yes, know I know, that I want a dynamic cursor ;)
But I can't get one because its not allowed in that project.

I do now a little workaround

WHERE
(:flag1 = '1' and
field1 LIKE :hostvariable1) or
flag1<>'1'

I think DB2 is ignoring everythink if flag1 is not '1'. He doesn't look for
field1 LIKE :hostvariable1 because the hole expression can't be true.
I have a 99% better performance now(worst case) but I where happy if I cann
decrease it anymore.


"Serge Rielau" <sr*****@ca.eye-bee-m.com> schrieb im Newsbeitrag
news:bp**********@hanover.torolab.ibm.com...
Micha,

DB2 makes all decisions when the query is compiled. at that point the
values are not available.
It seesm to me that what you really want here is a dynamic cursor.
I.e. glue together the statement once you have the values for the
hostvariables.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4
I think this is the switch in question.

Bind ... [{NOREOPT | REOPT} VARS] ...

PM
Nov 12 '05 #5
"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
news:i_*******************@news20.bellglobal.com.. .
I think this is the switch in question.

Bind ... [{NOREOPT | REOPT} VARS] ...

PM

My understanding is that the paramters above to defer determination of an
access path based on the host-variable contents at run time are not
necessary for the LIKE statement.
Nov 12 '05 #6

I tried to find the right pieces of doc to 'clearly' proove or disproove
either hypothesis but in vain...

Anybody remembers if the old way of
col-x >= :hv1-low-values and col-x<=hv2-low-values
is better than like predicates?

Seems that COL LIKE host variable is now indexable stage 1 if special chars
are not used as the first char.
Anybody can try a
w like hv0-something and x like hv1-low-values and y like hv2-low-values to
see if it makes a difference?
That would make all the predicates indexable-stage-1.
Here is a dump of some links i gathered while investigating the current
thread.
(for those interested)
http://publib.boulder.ibm.com/cgi-bi...STHIT#FIRSTHIT
2.21.6 LIKE predicate
....SNIP...
| When the pattern specified in a LIKE predicate is a parmeter marker
| and a fixed-length character host variable is used to replace the
| parameter marker, specify a value for the host variable that is the
| correct length. If you do not specify the correct length, the select
| does not return the intended results. For example, if the host
| variable is defined as CHAR(10) and the value WYSE% is assigned to
| that host variable, the host variable is padded with blanks on
| assignment. The pattern used is 'WYSE%,' which requests DB2 to search
| for all values that start with WYSE and end with five blank spaces. If
| you intended to search for only the values that start with 'WYSE,' you
| should assign the value 'WYSE%%%%%%' to the host variable.
http://www.gabrielledb2.com/pp.pdf
Around page 32-33
EXPLAIN of LIKE
EXPLAIN shows the use of a matching index scan when a CHAR column is
compared to a host
SELECT PN, PNAME
FROM P
WHERE PNAME LIKE :PNAME;
Value checked at run time
< If value begins with "%" or " ",
- Scan leaf pages if cluster ratio >= 80 %
- Tablespace scan if cluster ratio < 80 %
< Character frequency considered when using reoptimization as discussed in
HV section

http://publib.boulder.ibm.com/cgi-bi...RCHINDEX=INDEX
Stage 1, 2, 3 predicates related links :
Ranked Search Results for Book: dsnaph13 "DB2 UDB for OS/390 and z/OS V7
Application Programming and SQL Guide"
21 topics have matches for: stage 2

http://publib.boulder.ibm.com/cgi-bi...STHIT#FIRSTHIT
6.3.3.2 Summary of predicate processing
6.3.3.4.1 Default filter factors for simple predicates
http://publib.boulder.ibm.com/cgi-bi...165437#HDRD4I9
Col LIKE literal 1/10 (litteral, not host var...)
6.3.3.4.3 Interpolation formulas
http://publib.boulder.ibm.com/cgi-bi...65437#HDRD4I11
For LIKE or BETWEEN:
(High literal value - Low literal value) / (Total Entries)
....snip...
Interpolation for LIKE: DB2 treats a LIKE predicate as a type of BETWEEN
predicate. Two values that bound the range qualified by the predicate are
generated from the literal string in the predicate. Only the leading
characters found before the escape character ('%' or '_') are used to
generate the bounds. So if the escape character is the first character of
the string, the filter factor is estimated as 1, and the predicate is
estimated to reject no rows.
....snip...
etc. (other related stuff)
Other related links
'Programmers Only' column by Bonnie Baker
Predicate Evaluation: Part I, II and III

http://www.db2mag.com/db_area/archiv...grammers.shtml
http://www.db2mag.com/db_area/archiv...grammers.shtml
http://www.db2mag.com/db_area/archiv...grammers.shtml
http://www.db2mag.com/showArticle.jh...cleID=12803232

PM
Nov 12 '05 #7
"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
news:_g*********************@news20.bellglobal.com ...

I tried to find the right pieces of doc to 'clearly' proove or disproove
either hypothesis but in vain...

Anybody remembers if the old way of
col-x >= :hv1-low-values and col-x<=hv2-low-values
is better than like predicates?

Seems that COL LIKE host variable is now indexable stage 1 if special chars are not used as the first char.
Anybody can try a
w like hv0-something and x like hv1-low-values and y like hv2-low-values to see if it makes a difference?
That would make all the predicates indexable-stage-1.

In early releases of DB2 (version 1) for OS/390, BETWEEN was always better
than > and < on the same column. Also, a LIKE with a host variable would
produce an full index scan (matching columns = 0, i.e. not using the b-tree)
if the index was used because DB2 did not know where the % would be.

I later versions of the DB2, the optimizer converted the < and > to a
BETWEEN. Then DB2 produced the feature that evaluates the % at run time and
is treated like a BETWEEN if the % is at the end. This happens automatically
without deferring optimization for other statements in the program.
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mage | last post by:
Hello, I started to write my PostgreSQL layer. I tried pyPgSQL and PyGreSQL. I made a *very minimal* performance test and comparsion with the same thing in php. Table "movie" has 129 record and...
1
by: Kevin Frey | last post by:
Hello, I have a test database with table A containing 10,000 rows and a table B containing 100,000 rows. Rows in B are "children" of rows in A - each row in A has 10 related rows in B (ie. B has...
2
by: Private Pyle | last post by:
AIX 5.1, DB2 8.1.3 64-bit ESE 5 partitions 1 catalog, 4 data. I have a situation where I have to update 269,000,000 rows in a table with the value in another table with just about the same number...
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
7
by: P. Adhia | last post by:
Sorry for quoting an old post and probably I am reading out of context so my concern is unfounded. But I would appreciate if I can get someone or Serge to confirm. Also unlike the question asked in...
1
by: heming_g | last post by:
i see in this forum to make this routine using "DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n> ROWS ONLY) Prepare this one ONCE then EXECUTE in the loop. " it works . ...
0
by: Rajesh Jha | last post by:
I'm analyzing a query in which "SUBSTR' parameter is used. I replaced it by LIKE parameter. I tested the query in SPA(SQL Perfromance Analyzer) & found a saving of 50% in cost & 37.8% in CPU time. ...
16
by: marc_r_bertrand | last post by:
To all asp/db pros: The quiz code below works. But there is a problem when too many questions are answered (radio buttons clicked). I am not an asp pro. So, is there a pro out there or an...
9
by: Frank | last post by:
Hi, imagine there's a WEB application reading data from an Oracle database to visualize in using DataGrids in the clients browser. Yes, sounds simple, just create OracleConnection + OracleCommand...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.