September 18, 2007 at 7:20 am
I am trying to dissect all the queries that we are running – especially some long running jobs.
I would like to know in terms of performance:
EXEC SQL DECLARE EA-CRSR STATIC CURSOR FOR
SELECT A.CONTR_ROY_CODE
,A.FISCAL_PERIOD
,A.ACCR_TRNS_AMT
,B.PR_PCT
,A.ACCR_TYPE
FROM TRY_ROYALTY_ACCR A
,TRY_PR_ROYTY_TERM B
WHERE A.CONTRACT_ID = :WS-CONTRACT-ID
AND A.CONTR_ISBN = :WS-ISBN
AND A.CONTRACT_ID = B.CONTRACT_ID
AND A.CONTR_ISBN = B.ISBN
AND A.CONTR_ROY_CODE = B.ROYALTY_CODE
AND A.ROYTY_TERM_SEQ_NO = B.ROYTY_TERM_SEQ_NO
AND A.ESCALATION_LEVEL = B.ESCALATION_LEVEL
AND B.PR_NAME_ID = :WS-PROP-NAME-ID
AND A.FISCAL_PERIOD <= :WS-CP-FISCAL-PERIOD
AND A.ACCR_TYPE NOT IN ('SUSP', 'MAN ')
UNION ALL
SELECT A.CONTR_ROY_CODE
,A.FISCAL_PERIOD
,A.ACCR_TRNS_AMT
,B.PR_SPLIT_PCT
,A.ACCR_TYPE
FROM TRY_ROYALTY_ACCR A
,TRY_PROP_SUBL B
WHERE A.CONTRACT_ID = :WS-CONTRACT-ID
AND A.CONTR_ISBN = :WS-ISBN
AND B.SUBL_TRNS_TYPE = 'ME'
AND A.ME_TRNS_ID = B.SUBL_TRNS_ID
AND B.PR_NAME_ID = :WS-PROP-NAME-ID
AND A.FISCAL_PERIOD <= :WS-CP-FISCAL-PERIOD
AND A.ACCR_TYPE = 'MAN '
END-EXEC.
e. Usage of sum function
EXEC SQL SELECT SUM(B.ADVANCE_AMOUNT_DUE * A.PR_PCT)
INTO :WS-CONTRACT-ADVANCE:WS-NULL1
FROM TRY_PR_ADV_SCHED A
,TRY_ADVANCE_SCHED B
WHERE A.CONTRACT_ID = :WS-CONTRACT-ID
AND A.ISBN = :WS-ISBN
AND A.PR_NAME_ID = :WS-PROP-NAME-ID
AND A.CONTRACT_ID = B.CONTRACT_ID
AND A.ISBN = B.ISBN
AND A.ADVANCE_TYPE = B.ADVANCE_TYPE
AND A.ADV_SCHED_SEQ_NO = B.ADV_SCHED_SEQ_NO
END-EXEC.
f. Usage of Min Max Function:
EXEC SQL SELECT MIN(CROSS_DEDUCTION_ID)
INTO :WS-CROSS-DEDUCT-ID:NULL-IND
FROM TRY_X_DEDUCTION
WHERE CONTRACT_ID = :WS-CONTRACT-ID
AND ISBN = :WS-ISBN
AND PR_NAME_ID = :WS-PROP-NAME-ID
END-EXEC.
EXEC SQL SELECT ((CONVERT(CHAR(10), MAX(STATEMENT_DATE),21)))
INTO :WS-STMT-DATE:WS-NULL1
FROM TRY_CONT_STMT_STAT
WHERE CONTRACT_ID = :WS-CONTRACT-ID
AND STATEMENT_STATUS = 'FINAL '
END-EXEC.
September 19, 2007 at 12:52 am
Moosamca -
With the exception of the clustered index question, the rest of the SQL here looks to be something other than T-SQL (SQL Server dialect of SQL), perhaps Oracle?
As for clustered indexes vs. non-clustered indexes the key difference between the two is that a clustered index is "in row", the clustered index itself reside within the row/page where non-clustered indexes are stored seperately from the table...
As for the rest there are some basic things that most DBMS have in common:
A Cursor is similar to a for next loop where the bounds of the loop are determined by selecting rows from a table. Typically frowned upon if they can be avoided... typically much more expensive than a set based solution.
Unions are nothing more than gathering the specified data from multiple tables and returning it to the client as a single result set. Behavior may differ between DBMS, UNION on SQL Server will return distinct results (e.g. discard duplicate data), UNION ALL on SQL Server will return all rows and does not check for duplicates. UNIONS are usually used to put together similar data from two or more similar tables (e.g. gather all customers and vendors to do a mailing to all of your contacts).
SUM, MIN and MAX are SUM (add), minimum and maximum respectively, will return the sum, minimum or maximum value for the field/column specified based on the WHERE clause and any grouping.
Joe
September 19, 2007 at 6:32 am
It IS Oracle SQL.
Moosamca,
As for the Cursors, their options and usage is very different in SQL Server vs Oracle, so any suggestions/comments from this group will in most cases be negative because cursors perform poorly in most cases in SQL Server. In Oracle however, they are quite common, so common in fact that at the core, everything runs as a cursor.
So my suggestion to you is to post this question on an Oracle Forum.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply