|
View previous topic :: View next topic
|
| Author |
Message |
yogalakshmi
New User
Joined: 20 Sep 2007
Posts: 17
Location: cheenai
|
|
|
|
Hi,
In a COBONLDB program there are ten singleton SELECTS on a same table for different criteria.Like,
Select COL
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val1'.
Select COL
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val2'.
And it goes on for ten different COL2 criterias.
Will there be any improvement If I change the above Selects into a Cursor. Like,
Select COL
FROM TABLE A
Where COL1='constant criteria'
AND COL2 IN ('val1','val2',...'val10'). |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
you may want to investigate what is being done with the results of the select.
(i.e. what routines PERFORM the paragraphs containing the SELECTs) |
|
| Back to top |
|
|
yogalakshmi
New User
Joined: 20 Sep 2007
Posts: 17
Location: cheenai
|
|
|
|
| If the row is found (when the SQLCODE of the SELECT is ZERO) , a cobol variable is set to true. Basically it acts like an existence check. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Performance impact could vary depending on how many rows contain the "constant value" and if col2 is defined in an index that is made up of col1/col2. |
|
| Back to top |
|
|
yogalakshmi
New User
Joined: 20 Sep 2007
Posts: 17
Location: cheenai
|
|
|
|
| The rows which contain the constant value are at the maximum of 15. There is no index for col2. But there is an index for col1 which is made up of four other columns.col1 is the first column in this index.Could you explain me how this would impact the performance. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is it possible that there would be multiple rows that contain the same value in col1 and col2? If yes, this is a consideration for successful execution.
The way the code is now, multiple rows will need to be read to confirm or reject if the col2 value is hit. If a new index could be added to the table that consists of only col1 and col2, the performance of the existance checking could be improved (again, the caution about duplicate col2 values - not a problem, just would need to be dealt with).
If a cursor is used and keyed by only col1, more rows will need to be processed. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Lakshmi,
If you query is for existence alone, you dont even need to retreive the a column value from the table. You could try like the following.
| Code: |
Select 1
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val1'
FETCH FIRST 1 ROWS ONLY
FOR READ ONLY |
Thank You,
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Sorry, but if col2 is not part of an index with col1, multiple rows must be read until a hit is found for that value or all of the rows matching the col1 value have been read with no hit.
Keep in mind that internally many rows will be read that are never passed back to the application. That can be (usually is) tremendous overhead. And when the performance problem is looked at, some programmer or dba answers "we're only reading 1 row". Ooops. . . |
|
| Back to top |
|
|
|
|
