Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Performance ISSUE by USING CURSOR HELP !!!! Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 3:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:47 AM
Points: 68, Visits: 334
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

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
Post #1434797
Posted Monday, March 25, 2013 4:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
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

Post #1434804
Posted Monday, March 25, 2013 4:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:47 AM
Points: 68, Visits: 334
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))
Post #1434818
Posted Monday, March 25, 2013 4:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
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
Post #1434820
Posted Monday, March 25, 2013 5:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
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
Post #1434824
Posted Monday, March 25, 2013 6:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1434867
Posted Monday, March 25, 2013 6:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
Interesting. i may have to reconsider my own usage of defaulting to while loops now.

Thanks for the pointer Gila
Post #1434872
Posted Monday, March 25, 2013 7:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 983, Visits: 1,623
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.
Post #1434892
Posted Monday, March 25, 2013 7:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1434898
Posted Monday, March 25, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 13,110, Visits: 11,941
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 Moden's 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)
Post #1434924
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse