March 25, 2013 at 3:54 am
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
March 25, 2013 at 4:09 am
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
March 25, 2013 at 4:47 am
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))
March 25, 2013 at 4:52 am
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
March 25, 2013 at 5:00 am
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 🙂
March 25, 2013 at 6:24 am
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.
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
March 25, 2013 at 6:28 am
Interesting. i may have to reconsider my own usage of defaulting to while loops now.
Thanks for the pointer Gila
March 25, 2013 at 7:16 am
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.
March 25, 2013 at 7:26 am
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
March 25, 2013 at 7:51 am
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/[/url]
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2013 at 9:38 am
Hello,
OUTPUT INSERTED.YourIdentityColumn, INSERTED.easilyPatId
My identy colunm is tel_id but for the second output is INSERTED.@easilyPadId (value get from cursor). Is it correct ?
Thanks
Eric
March 25, 2013 at 9:46 am
ERIC CRUDELI (3/25/2013)
Hello,OUTPUT INSERTED.YourIdentityColumn, INSERTED.easilyPatId
My identy colunm is tel_id but for the second output is INSERTED.@easilyPadId (value get from cursor). Is it correct ?
Thanks
Eric
I think you missed the point. You don't need those variables at all. What I posted would be the replacement for the ENTIRE cursor section. No variables, no cursors, no repetition.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2013 at 9:55 am
I'm lost :doze:
The Data source I have to use for Insert is in Temporary table #temp2
Where is the link ?
March 25, 2013 at 10:00 am
ERIC CRUDELI (3/25/2013)
I'm lost :doze:The Data source I have to use for Insert is in Temporary table #temp2
Where is the link ?
I'm sorry the code I posted wasn't very clear. Of course what I posted it 100% untested because we don't have ddl to work with.
See if this helps.
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 etPatId,cnetNumTel,easilyTypeNumTelId,cnetnumeroPrincipal,cnetSMS,cnetCommentaire,easilyPatId, 0, 1
FROM #temp2
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2013 at 10:55 am
I got some errors.
1 - easilyPatId unknow (easilyPadId come from #temp2)
2 -target table ' DI628SW.NOYAU_PATIENT.noyau. TELEPHONE_PATIENT ' of the clause OUTPUT INTO cannot be next to a relation (primary key, foreign key). Constraint of found reference: ' FK_TELEPHONE_PATIENT_PATIENT '. Note tel_id is primary key and Identity field from [TELEPHONE ]but also a primary key but not identity from [TELEPHONE_PATIENT]
I think there something wrong in Ouput but I don't know where ?
INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] (
tel_categorie
,tel_numero
,tel_envoi_sms
,tel_principal
,tel_commentaire
,tel_administratif
,tel_actif
)
OUTPUT INSERTED.tel_id, INSERTED.easilyPatId
into [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] (tel_id, pat_id)
SELECT
easilyTypeNumTelId,
cnetNumTel,
cnetSMS,
cnetnumeroPrincipal,
cnetCommentaire,
0,
1
FROM #temp2
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply