June 29, 2005 at 6:51 am
Dear All,
This is probably a daft question but why does execution of a select using sp_cursorexecute perform worse than execution of a select in ISQLW, and why does the sp_cursorexecute not appear to used a non clustered index used in ISQLW. We use PeopleSoft to generate the queries. Data below is obtained by using Query Profiler and monitoring application server queries.
Comments and suggestions would be much appreciated...
Cheers,
Steve.....
__________________________________________________________________________________
/**Run in ISQLW The select below runs in less than 20 seconds... and uses a non-clustered index on a table.field PS_CM_ACCTG_LINE.JOURNAL_DATE**/
/**If the non-clustered index is dropped then the select runs in about 6min 20sec (depending on system load). 95% of the query cost is a clusterd
index scan on PS_CM_ACCTG_LINE) when the JOURNAL_DATE index has been dropped.
**/
SELECT (CONVERT(CHAR(10),B.JOURNAL_DATE,121)), A.LINE_DESCR, B.FOREIGN_CURRENCY, B.FOREIGN_AMOUNT, B.BUSINESS_UNIT_GL,
B.ACCOUNT, B.OPERATING_UNIT, B.PROJECT_ID, B.ACTIVITY_ID, B.ORDER_NO, B.ORDER_INT_LINE_NO
FROM PS_JRNL_LN A, PS_CM_ACCTG_LINE B
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT_GL
AND A.JOURNAL_ID = B.JOURNAL_ID
AND A.JOURNAL_DATE = B.JOURNAL_DATE
AND A.JOURNAL_LINE = B.JOURNAL_LINE
AND A.LEDGER = B.LEDGER
AND B.JOURNAL_DATE >='Dec 12 2004 12:00:00:000AM'
AND B.JOURNAL_DATE <= 'Dec 15 2004 12:00:00:000AM'
AND A.LINE_DESCR LIKE '%'
AND B.DEBIT_CREDIT = 'DR'
/**The SQL statements below are taken from SQL server query profiler, when the same select above is run via a PIA query (OXPO_L28).**/
/**The first fetch occurs 11 minutes after sp_cursorexecute is called. This is far slower than the query above.**/
/** Question.. Why is the execution as a stored proceedure so slow when compared to the ISQL select and why does the stored proceedure **/
/** appear not to used the non-clustered index when one is provided? **/
declare @P1 int
set @P1=1159
declare @P2 int
set @P2=28688
declare @P3 int
set @P3=8193
exec sp_cursorprepare @P1 output, N'@P1 datetime,@P2 datetime', N'SELECT (CONVERT(CHAR(10),B.JOURNAL_DATE,121)), A.LINE_DESCR, B.FOREIGN_CURRENCY,
B.FOREIGN_AMOUNT, B.BUSINESS_UNIT_GL, B.ACCOUNT, B.OPERATING_UNIT, B.PROJECT_ID, B.ACTIVITY_ID, B.ORDER_NO, B.ORDER_INT_LINE_NO FROM PS_JRNL_LN A,
PS_CM_ACCTG_LINE B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT_GL AND A.JOURNAL_ID = B.JOURNAL_ID AND A.JOURNAL_DATE = B.JOURNAL_DATE AND
A.JOURNAL_LINE = B.JOURNAL_LINE AND A.LEDGER = B.LEDGER AND B.JOURNAL_DATE >= @P1 AND B.JOURNAL_DATE <= @P2 AND A.LINE_DESCR LIKE ''%''
AND B.DEBIT_CREDIT = ''DR'' ORDER BY 1, 5, 6, 7, 8, 9, 10, 11', 1, @P2 output, @P3 output
select @P1, @P2, @P3
--** Run at 11:28:25.353
declare @P1 int
set @P1=180151370
declare @P2 int
set @P2=16
declare @P3 int
set @P3=1
declare @P4 int
set @P4=187
exec sp_cursorexecute 1159, @P1 output, @P2 output, @P3 output, @P4 output, 'Dec 12 2004 12:00:00:000AM', 'Dec 15 2004 12:00:00:000AM'
select @P1, @P2, @P3, @P4
--** Run at 11:28:25.730
/***Start of data Return ***/
exec sp_cursorfetch 180151370, 2, 0, 187
--** Run at 11:39:35.900
June 29, 2005 at 7:27 am
AND A.LINE_DESCR LIKE ''%''
-this condition in making the querry to run REALLY SLOW . Better take it out : )
Because the condition starts with "%SomeTextOrNot" the querry optimizer will do a table scan ... and will NOT use your index. However if you condition is something like "SomeText%" than the index will be used.
The execution time is different because when you use QA you hard code your values so the execution plan is better
Vasc
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply