| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-12-10, 10:33
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 5
|
|
Performance Issue with DB2 Select
|
|
I need to perform the following to determine if a row exists with vendor type of 'E'. I only need one occurance to execute my processing. I have 36,000 records that need to have this 'select' executed. Is there a way to improve the performance of this SQL?
EXEC SQL
SELECT COUNT(*)
INTO :WS-COUNT
FROM SCE7000
WHERE VNDRNOTE1KEY LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND (SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E')
END-EXEC
IF WS-COUNT > 0
PERFORM 8500-WRITE-OUTPUT
END-IF
|
|
07-12-10, 10:57
|
|
Registered User
|
|
Join Date: Jun 2007
Location: germany
Posts: 96
|
|
why don't you just select the vender or SELECT 1 into a host variable,
fetch first row only.
count, by definition, will go thru the complete table, and since you have substr, you are scanning the table.
whereas a select something with fetch first row only, will stop when 1 is found,
or continue scanning until table end if nothing is found.
__________________
Dick Brenholtz, Ami in Deutschland
|
|
07-12-10, 10:59
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
This is a query which was first came in my mind.
Code:
EXEC SQL
SELECT COALESCE(yes , no)
INTO :WS-EXISTS
FROM (SELECT 'No' FROM sysibm.sysdummy1
) AS q(no)
LEFT OUTER JOIN
(SELECT 'Yes'
FROM sysibm.sysdummy1
WHERE EXISTS
(SELECT 0
FROM SCE7000
WHERE VNDRNOTE1KEY
LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND
( SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
)
)
) AS r(yes)
ON 0=0
END-EXEC
or simplify it, like this.
(It will be neccesary to move 'No' to WS-EXISTS before executing the query.)
Code:
SELECT 'Yes'
INTO :WS-EXISTS
FROM sysibm.sysdummy1
WHERE EXISTS
(SELECT 0
FROM SCE7000
WHERE VNDRNOTE1KEY
LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND
( SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
)
)
|
Last edited by tonkuma; 07-12-10 at 11:03.
|
07-12-10, 11:42
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
Actually, this scenario is what global temporary tables were designed for. You insert your 36K records into the temp table, then join to your ecisting table based on these predicates you have already described. You could use a left outer join to your existing table, that way you could get all 36K records back with a notation whether they existed or not. Again, as told to you in an earlier post, all these substr functions on muli-part columns are going to kill your performance and you should put heat on your vendor over that.
Dave
|
|
07-12-10, 12:43
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Change singleton select by CURSOR
Quote:
Originally Posted by Deb Locy
I need to perform the following to determine if a row exists with vendor type of 'E'. I only need one occurance to execute my processing. I have 36,000 records that need to have this 'select' executed. Is there a way to improve the performance of this SQL?
EXEC SQL
SELECT COUNT(*)
INTO :WS-COUNT
FROM SCE7000
WHERE VNDRNOTE1KEY LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND (SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E')
END-EXEC
IF WS-COUNT > 0
PERFORM 8500-WRITE-OUTPUT
END-IF
|
Having 36000 rows in the table you don't need to worry about perfomance.
But it could be better to change singleton select by CURSOR, if it's a possible.
Lenny
|
|
07-12-10, 13:31
|
|
Registered User
|
|
Join Date: Jun 2007
Location: germany
Posts: 96
|
|
all well and good, but we still do not know the number of rows in SCE7000
Quote:
|
I have 36,000 records that need to have this 'select' executed
|
i interpret that statement to mean:
I have a trigger file of 36,000 records,
which means I have to execute this query against SCE7000 36,000 times.
the temp table solution seems to probably be the best,
I don't know at what point a base table (SCE7000) row quantity would provide a problem
with a join against the temp table populated with 36,000
on your toys (grin),
but we mainframers would be reluctant to do this with a large base table.
__________________
Dick Brenholtz, Ami in Deutschland
|
Last edited by dbzTHEdinosaur; 07-12-10 at 13:34.
Reason: added a grin, was not making fun of your equipment
|
07-12-10, 13:49
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 5
|
|
This is a one time run to remove SS #'s and in the future we will be converting from the mainframe. That is correct...the trigger file has 36,000 records. The table has 380,706 records in it. The 'exists' select worked as a Spufi but abends in my Cobol program. I am trying to debug it.
|
|
07-12-10, 13:58
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 5
|
|
The select with 'exists' is working and does seem to be faster. Thanks so much!!
|
|
07-12-10, 15:53
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
This is a query which was first came in my mind.
Code:
EXEC SQL
SELECT COALESCE(yes , no)
INTO :WS-EXISTS
FROM (SELECT 'No' FROM sysibm.sysdummy1
) AS q(no)
LEFT OUTER JOIN
(SELECT 'Yes'
FROM sysibm.sysdummy1
WHERE EXISTS
(SELECT 0
FROM SCE7000
WHERE VNDRNOTE1KEY
LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND
( SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
)
)
) AS r(yes)
ON 0=0
END-EXEC
or simplify it, like this.
(It will be neccesary to move 'No' to WS-EXISTS before executing the query.)
Code:
SELECT 'Yes'
INTO :WS-EXISTS
FROM sysibm.sysdummy1
WHERE EXISTS
(SELECT 0
FROM SCE7000
WHERE VNDRNOTE1KEY
LIKE 'SAGE' ||
:WS-SEL-VNDR-NBR || '%'
AND
( SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
)
)
|
Which one is better ?
Code:
AND
( SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
)
Or
Code:
AND NOT
( SUBSTR(VNDRNOTE1RSLT002,72,1) not = 'E'
and SUBSTR(VNDRNOTE1RSLT002,78,1) not = 'E'
)
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Performance Issue with DB2 Select
