SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance ISSUE by USING CURSOR HELP !!!!


Performance ISSUE by USING CURSOR HELP !!!!

Author
Message
ERIC CRUDELI
ERIC CRUDELI
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 503
Hello Mate,

I have the following TSQL but I meet a performance issue on Phase 3. Phase 1 and Phase 2 take about 4 min. The table TEMP2# have 1317832 records. I would to replace the cursor to improve the performance but I don't know how to do this. I'm not a expert Crying

Could you help me ?

I have another question I use @@identity actually but if I use SCOPE_IDENTITY() do you I can improve the insert.

Thanks,

Eric

CREATE TABLE #TEMP1
(
patId integer NULL,
ancienPatId integer null
)



PRINT 'Find patient with no phone number checked SMS - Phase 1'

INSERT INTO #temp1
SELECT DISTINCT easilyPatient.pat_id,easilyPatient.pat_ancien_id

FROM [DI628SW].[NOYAU_PATIENT].[noyau].[Patient] AS easilyPatient WITH (NOLOCK)

LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS easilyTelPatient WITH (NOLOCK)

ON easilyPatient.pat_id = easilyTelPatient.pat_id

LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS easilyTel WITH (NOLOCK)

ON easilyTelPatient.tel_id = easilyTel.tel_id

WHERE (easilyTel.tel_envoi_sms = 0 OR easilyTel.[tel_envoi_sms] is null) AND easilyPatient.pat_ancien_id is not null


SELECT COUNT(*) FROM #TEMP1
--Select * from #Temp1

CREATE TABLE #TEMP2
(
cnetPatId integer,
cnetNumTel varchar(40),
easilyTypeNumTelId integer,
cnetnumeroPrincipal bit not null,
cnetSMS integer not null,
cnetCommentaire varchar(40),
easilyPatId integer null
)


PRINT 'Matching PATIENT - PHASE 2'

INSERT INTO #temp2

SELECT CNet.pat_id,LEFT(CNet.[NUM_TEL_REEL], 18) AS CNETNumTel ,
CASE CNet.[TYPE_NUM_TEL_ID]

WHEN 1 THEN 1

WHEN 2 THEN 4

WHEN 3 THEN 3

WHEN 4 THEN 0

WHEN 5 THEN 5

END AS easilyTelCat,

CNet.[NUMERO_PRINCIPAL],CNet.[SMS],Cnet.[COMMENTAIRE],tmp1.patId

FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)
INNER JOIN #temp1 AS tmp1

ON tmp1.ancienPatId = CNet.pat_id

WHERE (CNet.[SMS] = 1)
and (LEFT(CNet.[NUM_TEL_REEL], 18) not in (select tel_numero collate SQL_Latin1_General_CP1_CI_AS FROM
[DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS TP WITH (NOLOCK)INNER JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS T WITH (NOLOCK)
ON TP.TEL_ID = T.TEL_ID WHERE t.tel_envoi_sms = 1 AND tmp1.patid = TP.pat_id))


SELECT COUNT(*) FROM #TEMP2
--Select * from #temp2

--DROP TABLE #TEMP1
--DROP TABLE #TEMP2

PRINT 'Insert missing phones - Phase 3'
DECLARE @cnetPatId integer
DECLARE @cnetNumTel varchar(40)
DECLARE @easilyTypeNumTelId integer
DECLARE @cnetnumeroPrincipal bit
DECLARE @cnetSMS bit
DECLARE @easilyPatId integer
DECLARE @cnetCommentaire VARCHAR(40)
DECLARE @bdEasilyTelephone VARCHAR(100)
DECLARE @bdEasilyTelephonePATIENT VARCHAR(100)
DECLARE @siteCode VARCHAR(10)


DECLARE myCursor CURSOR FOR
SELECT etPatId,cnetNumTel,easilyTypeNumTelId,cnetnumeroPrincipal,cnetSMS,cnetCommentaire,easilyPatId
FROM #temp2



OPEN myCursor
FETCH NEXT FROM myCursor INTO @cnetPatId,@cnetNumTel,@easilyTypeNumTelId,@cnetnumeroPrincipal,@cnetSMS,@cnetCommentaire,@easilyPatId



WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] (tel_categorie,tel_numero,tel_envoi_sms,tel_principal,tel_commentaire,tel_administratif,tel_actif)

SELECT @easilyTypeNumTelId,@cnetNumTel,@cnetSMS,@cnetnumeroPrincipal,@cnetCommentaire, 0,1



DECLARE @idTelephone integer
SELECT @idTelephone = @@IDENTITY

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] (tel_id, pat_id)
SELECT @idTelephone, @easilyPatId

FETCH NEXT FROM myCursor INTO @cnetPatId,@cnetNumTel,@easilyTypeNumTelId,@cnetnumeroPrincipal,@cnetSMS,@cnetCommentaire,@easilyPatId
END



CLOSE myCursor
DEALLOCATE myCursor

DROP TABLE #TEMP1
DROP TABLE #TEMP2
Terry300577
Terry300577
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 516
have you ever used a while loop in SQL.. i tend to use these over cursors these days and find they perform a lot better.

here is a sample code showing a while loop:
IF EXISTS (   SELECT  * 
FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')
AND o.id = OBJECT_ID(N'tempdb..##TlbLog'))
DROP TABLE ##TlbLog


CREATE TABLE ##TlbLog
(
ID INT
,UserID INT
,Purpose VARCHAR(20)
,DateCreated DATETIME
,seen BIT DEFAULT 0
)

INSERT INTO ##TlbLog
(ID, UserID, Purpose, DateCreated) VALUES
(1, 500, 'login' ,'2013-03-24 14:39:43.273')
,(2, 501, 'login' ,'2013-03-24 14:39:43.277')
,(3, 502, 'login' ,'2013-03-24 14:39:43.277')
,(4, 503, 'login' ,'2013-03-24 14:39:43.277')
,(5, 500, 'logout' ,'2013-03-24 14:44:43.280')
,(6, 501, 'logout' ,'2013-03-24 14:44:43.280')
,(2, 501, 'login' ,'2013-03-24 14:55:43.277')
,(6, 501, 'logout' ,'2013-03-24 15:55:43.280')


DECLARE @count INT
,@rowNumber INT
,@tblID INT
,@tblUserID INT
,@tblPurpose VARCHAR(20)
,@tblDateCreated DATETIME

SELECT @count = COUNT(*), @rowNumber = 1 FROM ##TlbLog

WHILE @count >=@rowNumber
BEGIN
SELECT TOP 1 @tblID = ID
,@tblUserID = UserID
,@tblPurpose = Purpose
,@tblDateCreated = DateCreated
FROM ##TlbLog
WHERE seen = 0

--do whatever your looking to do


UPDATE ##TlbLog SET seen = 1 WHERE id = @tblID

SET @rowNumber = @rowNumber+1

END

SELECT *
FROM ##TlbLog


ERIC CRUDELI
ERIC CRUDELI
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 503
Hi,

I had a row "seen" but I got a error during Insert : The name or the number of column of the supplied values does not correspond to the definition of the table.

Thansk,

Eric

CREATE TABLE #TEMP2
(
cnetPatId integer,
cnetNumTel varchar(40),
easilyTypeNumTelId integer,
cnetnumeroPrincipal bit not null,
cnetSMS integer not null,
cnetCommentaire varchar(40),
easilyPatId integer null,
seen BIT DEFAULT 0
)


PRINT 'Matching avec les patients CNet'

INSERT INTO #temp2

SELECT CNet.pat_id,LEFT(CNet.[NUM_TEL_REEL], 18) AS CNETNumTel ,
CASE CNet.[TYPE_NUM_TEL_ID]

WHEN 1 THEN 1

WHEN 2 THEN 4

WHEN 3 THEN 3

WHEN 4 THEN 0

WHEN 5 THEN 5

END AS easilyTelCat,

CNet.[NUMERO_PRINCIPAL],CNet.[SMS],Cnet.[COMMENTAIRE],tmp1.patId

FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)
INNER JOIN #temp1 AS tmp1

ON tmp1.ancienPatId = CNet.pat_id

WHERE (CNet.[SMS] = 1)
and (LEFT(CNet.[NUM_TEL_REEL], 18) not in (select tel_numero collate SQL_Latin1_General_CP1_CI_AS FROM
[DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS TP WITH (NOLOCK)INNER JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS T WITH (NOLOCK)
ON TP.TEL_ID = T.TEL_ID WHERE t.tel_envoi_sms = 1 AND tmp1.patid = TP.pat_id))
Terry300577
Terry300577
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 516
i'd say either define the columns your inserting into or add the value 0 to the select statement so you are passing a value through to the "seen" column

maybe that will resolve your issue
Terry300577
Terry300577
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 516
ive made a couple assumptions but this should work:

CREATE TABLE #TEMP1
(
patId INTEGER NULL
, ancienPatId INTEGER NULL
)



PRINT 'Find patient with no phone number checked SMS - Phase 1'

INSERT INTO #temp1
SELECT DISTINCT
easilyPatient.pat_id
, easilyPatient.pat_ancien_id
FROM [DI628SW].[NOYAU_PATIENT].[noyau].[Patient] AS easilyPatient
WITH (NOLOCK)
LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS easilyTelPatient
WITH (NOLOCK) ON easilyPatient.pat_id = easilyTelPatient.pat_id
LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS easilyTel
WITH (NOLOCK) ON easilyTelPatient.tel_id = easilyTel.tel_id
WHERE (easilyTel.tel_envoi_sms = 0
OR easilyTel.[tel_envoi_sms] IS NULL)
AND easilyPatient.pat_ancien_id IS NOT NULL


SELECT COUNT(*)
FROM #TEMP1
--Select * from #Temp1

CREATE TABLE #TEMP2
(
cnetPatId INTEGER
, cnetNumTel VARCHAR(40)
, easilyTypeNumTelId INTEGER
, cnetnumeroPrincipal BIT NOT NULL
, cnetSMS INTEGER NOT NULL
, cnetCommentaire VARCHAR(40)
, easilyPatId INTEGER NULL
, Seen BIT DEFAULT 0
)


PRINT 'Matching PATIENT - PHASE 2'

INSERT INTO #temp2
SELECT CNet.pat_id
, LEFT(CNet.[NUM_TEL_REEL], 18) AS CNETNumTel
, CASE CNet.[TYPE_NUM_TEL_ID]
WHEN 1 THEN 1
WHEN 2 THEN 4
WHEN 3 THEN 3
WHEN 4 THEN 0
WHEN 5 THEN 5
END AS easilyTelCat
, CNet.[NUMERO_PRINCIPAL]
, CNet.[SMS]
, Cnet.[COMMENTAIRE]
, tmp1.patId
, 0
FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)
INNER JOIN #temp1 AS tmp1 ON tmp1.ancienPatId = CNet.pat_id
WHERE (CNet.[SMS] = 1)
AND (LEFT(CNet.[NUM_TEL_REEL], 18) NOT IN (
SELECT tel_numero COLLATE SQL_Latin1_General_CP1_CI_AS
FROM [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT]
AS TP WITH (NOLOCK)
INNER JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE]
AS T WITH (NOLOCK) ON TP.TEL_ID = T.TEL_ID
WHERE t.tel_envoi_sms = 1
AND tmp1.patid = TP.pat_id))


SELECT COUNT(*)
FROM #TEMP2
--Select * from #temp2

--DROP TABLE #TEMP1
--DROP TABLE #TEMP2

PRINT 'Insert missing phones - Phase 3'
DECLARE @cnetPatId INTEGER
DECLARE @cnetNumTel VARCHAR(40)
DECLARE @easilyTypeNumTelId INTEGER
DECLARE @cnetnumeroPrincipal BIT
DECLARE @cnetSMS BIT
DECLARE @easilyPatId INTEGER
DECLARE @cnetCommentaire VARCHAR(40)
DECLARE @bdEasilyTelephone VARCHAR(100)
DECLARE @bdEasilyTelephonePATIENT VARCHAR(100)
DECLARE @siteCode VARCHAR(10)

DECLARE @count INT
DECLARE @rowNumber INT

WHILE @count >= @rowNumber
BEGIN
SELECT TOP 1 @cnetPatId = etPatId
, @cnetNumTel = cnetNumTel
, @easilyTypeNumTelId = easilyTypeNumTelId
, @cnetnumeroPrincipal = cnetnumeroPrincipal
, @cnetSMS = cnetSMS
, @cnetCommentaire = cnetCommentaire
, @easilyPatId = easilyPatId
FROM #temp2
WHERE seen = 0

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE]
(tel_categorie
, tel_numero
, tel_envoi_sms
, tel_principal
, tel_commentaire
, tel_administratif
, tel_actif)
SELECT @easilyTypeNumTelId
, @cnetNumTel
, @cnetSMS
, @cnetnumeroPrincipal
, @cnetCommentaire
, 0
, 1

DECLARE @idTelephone INTEGER
SELECT @idTelephone = @@IDENTITY

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT]
(tel_id
, pat_id)
SELECT @idTelephone
, @easilyPatId

UPDATE #TEMP2 SET Seen = 1 WHERE etPatId =@cnetPatId -- Assumed this is the key

SET @rowNumber = @rowNumber+1

END

DROP TABLE #TEMP1
DROP TABLE #TEMP2



Also when you post code try using the IFCode Shortcuts on the side. this helps make it easier to read and pull out teh sql code from the post :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117059 Visits: 45530
Terry300577 (3/25/2013)
have you ever used a while loop in SQL.. i tend to use these over cursors these days and find they perform a lot better.


http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Terry300577
Terry300577
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1967 Visits: 516
Interesting. i may have to reconsider my own usage of defaulting to while loops now.

Thanks for the pointer Gila
venoym
venoym
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 2082
The cursor can easily be replaced by a filtered select statement that includes an output clause into another temp table, which is then used to update the "seen" field. I don't have time right now to do it, but it would require usage of a ROW_NUMBER() function to filter the results to just row 1 and the OUTPUT clause to grab the identity value after insert.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2667 Visits: 3232
I don't think theres a performance benefit of using the @@IDENTITY or SCOPE_IDENTITY() however if you are going to have multiple processes hitting the same tame then you really should be using SCOPE_IDENTITY() as its considered to be thread safe.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33727 Visits: 17681
I can help you get rid of this cursor. It seems that all you actually need is to read up on the output clause. From the original code you posted it looks like you are inserting into INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] and then getting the identity value from that insert to use to insert into [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT].

This one the scenarios that OUTPUT will make your life a lot easier. http://msdn.microsoft.com/en-us/library/ms177564.aspx

If I am reading your code correctly I think that the following should be pretty close. I can't rest it because we don't have ddl. If you need more specific coding help please take a few minutes and read the link in my signature about best practices.


INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] (
tel_categorie
,tel_numero
,tel_envoi_sms
,tel_principal
,tel_commentaire
,tel_administratif
,tel_actif
)
OUTPUT INSERTED.YourIdentityColumn, INSERTED.easilyPatId
into [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] (tel_id, pat_id)
SELECT @easilyTypeNumTelId
,@cnetNumTel
,@cnetSMS
,@cnetnumeroPrincipal
,@cnetCommentaire
,0
,1



Also, watch those NOLOCK hints. They are very nasty and can cause some very unexpected results. Here a few articles on the topic.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search