SEO

Hi Manish Negandhi, thank you for the previous response, i have some
doubts about this:

(1)Remove "cursor" from the stored proc and apply temp table logic
there. i.e. write a code to apply the logic using temp table instead
of cursors. cursors perform very slow compare to the plain sql

i thought that cursors were better than temp tables, i am little
confused about this, in a forum of MSSQL recommend me this:

>>>>>>>>>
You can avoid several seek/scans by using CASE expressions in a single
query
instead of separate aggregate subqueries. For example:
SELECT
@iTOTAL_BAZ = 0,
@iTOTAL_EKT = 0,
@iTOTAL_IUS = 0
SELECT
@iTOTAL_BAZ = SUM(CASE WHEN B.corpo_id = 'BAZ' THEN 1 ELSE 0
END),
@iTOTAL_EKT = SUM(CASE WHEN B.corpo_id = 'EKT' THEN 1 ELSE 0
END),
@iTOTAL_IUS = SUM(CASE WHEN B.corpo_id = 'IUS' THEN 1 ELSE 0 END)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid = B.MIN_id
WHERE
A.PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID = @CUR_NID
I think you can get the most performance improvement by using a set-
based
insert/update instead of a cusor. With Microsoft SQL Server, you
could
probably eliminate the temp tables and use techniques like derived
tables
but I don't know what you can and can't do in Sybase

>>>>>>>>>>>

(2)Get showplan for the proc and find out if any queries inside the
proc preforms "Table Scan". You can find showplan for a proc without
actually executing it by turning "set fmtonly" to on

i am so sorry but i dont understand nothing about this, you mean that
first i have to avoid cursors and then execute the sp like this:

set fmtonly;
exec PR_.....

Thank you so much for your help, I really appreciate your time on this.

"Jorge Reyes" <jorg_reyes@hotmail.com> wrote in message
news:74ee24dd-c468-4adb-8aba-54f5a1e328e6@y21g2000hsf.googlegroups.com...
> Hi Manish Negandhi, thank you for the previous response, i have some
> doubts about this:
>
> (1)Remove "cursor" from the stored proc and apply temp table logic
> there. i.e. write a code to apply the logic using temp table instead
> of cursors. cursors perform very slow compare to the plain sql
>
> i thought that cursors were better than temp tables, i am little
> confused about this, in a forum of MSSQL recommend me this:
>
>>>>>>>>>>
> You can avoid several seek/scans by using CASE expressions in a single
> query
> instead of separate aggregate subqueries. For example:
> SELECT
> @iTOTAL_BAZ = 0,
> @iTOTAL_EKT = 0,
> @iTOTAL_IUS = 0
> SELECT
> @iTOTAL_BAZ = SUM(CASE WHEN B.corpo_id = 'BAZ' THEN 1 ELSE 0
> END),
> @iTOTAL_EKT = SUM(CASE WHEN B.corpo_id = 'EKT' THEN 1 ELSE 0
> END),
> @iTOTAL_IUS = SUM(CASE WHEN B.corpo_id = 'IUS' THEN 1 ELSE 0 END)
> FROM #TMP_UB_TOTGRALBYSIDNID AS A
> LEFT JOIN usr_mines AS B ON A.MINid = B.MIN_id
> WHERE
> A.PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE_AUX AND
> A.SID = @CUR_SID AND A.NID = @CUR_NID
> I think you can get the most performance improvement by using a set-
> based
> insert/update instead of a cusor. With Microsoft SQL Server, you
> could
> probably eliminate the temp tables and use techniques like derived
> tables
> but I don't know what you can and can't do in Sybase
>
>>>>>>>>>>>>
>
> (2)Get showplan for the proc and find out if any queries inside the
> proc preforms "Table Scan". You can find showplan for a proc without
> actually executing it by turning "set fmtonly" to on
>
> i am so sorry but i dont understand nothing about this, you mean that
> first i have to avoid cursors and then execute the sp like this:
>
> set fmtonly;
> exec PR_.....
>
> Thank you so much for your help, I really appreciate your time on this.


(1) To get the execution plan odf a stored procedure without actual
execution:

set fmtonly on
go
set showplan on
go
exec <stored procedure arguments-as-needed>
go

(2) Case statements have been in ASE for quite a while. SQL derived tables
(AKA in-line views) came with 12.5.3.

(3) Although similar, what's good for MS SQL Server may not be good for
Sybase ASE and vice versa.