Performance ISSUE by USING CURSOR HELP !!!!

Question

Performance ISSUE by USING CURSOR HELP !!!!

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Syntactically it appears to be just fine. If there are logic issues I can't do much to help because there is no ddl to work with. Please take a few minutes and read the first in my signature for best practices when posting questions.

_______________________________________________________________

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

ERIC CRUDELI (3/25/2013)
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

Hi Eric

You are using four-part naming for your tables and referring to two different servers, DI81SW & DI628SW. Are both of these linked servers? Can you identify which server you are connected to when you are running this code?

Cheers

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 3

ERIC CRUDELI

SSCommitted

Points: 1786

March 28, 2013 at 2:14 am

#1601483

I ran the script on DI628SW

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

ERIC CRUDELI (3/28/2013)
I ran the script on DI628SW

This is where it will be run from always?

The other server - DI81SW - this is a linked server?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 5

ERIC CRUDELI

SSCommitted

Points: 1786

March 28, 2013 at 2:26 am

#1601487

Yes all the time on DI628SW. DI81SW server linked

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

ERIC CRUDELI (3/28/2013)
Yes all the time on DI628SW. DI81SW server linked

Thanks. Final question:

SELECT COUNT(*)

FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)

WHERE (CNet.[SMS] = 1)

How many rows are returned by this?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 7

4 millions from DI81SW (#TEMP1)

I have to insert 1.3 millions (#TEMP2)

I used the following script but I'm anxious because I have to insert 1.3 millions of line :doze:

I made some tests to insert 100000 and it takes 24 minutes. I'm worried about SQL BUFFER !!!

CREATE TABLE #TEMP1

(

patId integer NULL,

ancienPatId integer null

)

PRINT 'PHASE 1 - Select patients from EASILY with no phone flag SMS - DI628SW'

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

)

CREATE NONCLUSTERED INDEX [IX_temp2] ON #temp2 (cnetPatId, seen ASC)

PRINT 'PHASE 2 - Matching with patients CNET - DI81SW'

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

PRINT 'PHASE 3 - Insert missing phones in EASILY'

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

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

WHILE @count >=@rowNumber

-- and @rowNumber < 100000

BEGIN

SELECT TOP 1 @cnetPatId = cnetPatId

, @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 cnetPatId = @cnetPatId

SET @rowNumber = @rowNumber+1

--SELECT @rowNumber

END

DROP TABLE #TEMP1

DROP TABLE #TEMP2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Please run this query and post the result:

SELECT COUNT(*)

FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)

WHERE (CNet.[SMS] = 1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 9

ERIC CRUDELI

SSCommitted

Points: 1786

March 28, 2013 at 3:51 am

#1601519

The result is 1329861

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

ERIC CRUDELI (3/28/2013)
4 millions from DI81SW (#TEMP1)
I have to insert 1.3 millions (#TEMP2)
I used the following script but I'm anxious because I have to insert 1.3 millions of line :doze:

Are these numbers correct? The logic of the whole process looks to me to be "find patients AND/OR phone numbers where SMS is not permitted: copy them to a new table". The row counts seem veryhigh.

I made some tests to insert 100000 and it takes 24 minutes.

I'm sure we will be able to improve on that 😉

TSQL coding is best accomplished in three stages

1. Make it work (confirm that the output is correct)

2. Make it fast

3. Make it pretty (formatting, documentation)

Before proceeding with step 2, are you absolutely sure that step 1, the output, is correct?

Have you checked your existing tables for duplicates? This might be a first stage:

SELECT tel_numero, COUNT(*)

FROM [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE]

GROUP BY tel_numero

HAVING COUNT(*) > 2

If rows are returned by this query, check a sample of telephone numbers in the output against the source table, see if the data is realistic.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden