Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shaik.zakeer
Posting Yak Master
117 Posts |
 Posted - 2009-10-19 : 02:50:44
|
| HIwill it hamper the performance, if we use more CTE's and Pivot in a sp?is there any other alternate instead of using CTE and pivot, which will not hamper the performance?Thanks Jack |
|
|
shaik.zakeer
Posting Yak Master
117 Posts |
Posted - 2009-10-20 : 03:14:05
|
| No one is here to reply this.............Thanks Jack |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-10-20 : 03:16:42
|
What do you expect us to answer?We cannot even see the existing code...
N 56°04'39.26"E 12°55'05.63" |
|
|
|
shaik.zakeer
Posting Yak Master
117 Posts |
Posted - 2009-10-20 : 04:30:22
|
| i have used CTE's and Pivot's more in sp's.code looks like ;WITH SQLTMP AS ( SELECT TS.tCode, TF.[Sequence], TF.Value,TS.Segmentid FROM Transaction HT INNER JOIN Event TE ON HT.Transactionid = TE.Transactionid INNER JOIN Segment TS ON TE.Eventid = TS.Eventid INNER JOIN Field TF ON TS.Segmentid = TF.Segmentid WHERE 1 = 1 AND HT.Transactionid = @aTransactionID AND TE.Transactionid = HT.Transactionid AND TS.Code in ('K1') ) INSERT INTO TEMP_UDSHL7_Contacts SELECT @aTransactionID AS TransactionID, DENSE_RANK() OVER(ORDER BY [1]) AS [RANK], CODE, [1] AS ID,[2] AS NAME,[4] AS [ADDRESS], [5] AS PH, [6] AS WORKPH,[3] AS RELATIONSHIP, [7] AS ROLE, ROW_NUMBER() OVER(ORDER BY [1]) AS [ROWNO] FROM ( SELECT CODE,SEQUENCE, VALUE, SEGMENTID FROM SQLTMP ) AS P PIVOT ( MIN(FIELDVALUE ) FOR SEQUENCE IN ( [1], [2], [3], [4], [5], [6], [7] ) ) AS PVTwill it reduce the performance.Thanks Jack |
|
|
|
|
|
|
|
|
will it hamper the performance?