September 22, 2011 at 2:51 am
Hi All,
I am hoping that you may be able to offer me some advice, suggestions, examples for how I can speed up a very slow cursor. It is currently taking about 4 hours to run over a few hundred thousand records which is ridiculous. I have read on the web that I need to replace witha set based query but I am afraid I really do not understand and I need to get this running by tonight...:(
I eagerly await your assistance.
Many thanks in advance.
Monty.
Cursor:
DECLARE @ACCOUNTID varchar (20)
DECLARE @CONTACTID varchar(20)
DECLARE @SOURCEACCID varchar(24)
DECLARE @DESTACCID varchar (24)
DECLARE @BRANCHID varchar (20)
DECLARE @TYPE varchar (12)
DECLARE @PREFIX01 varchar (12)
DECLARE @PREFIX02 varchar (12)
DECLARE @PREFIX03 varchar (12)
DECLARE @PREFIX04 varchar (12)
DECLARE @PREFIX05 varchar (50)
DECLARE @FNAME01 varchar (64)
DECLARE @FNAME02 varchar (64)
DECLARE @FNAME03 varchar (64)
DECLARE @FNAME04 varchar (64)
DECLARE @FNAME05 varchar (64)
DECLARE @FNAME06 varchar (8)
DECLARE @SNAME01 varchar (64)
DECLARE @SNAME02 varchar (64)
DECLARE @SNAME03 varchar (64)
DECLARE @SNAME04 varchar (64)
DECLARE @SNAME05 varchar (64)
/**********************************/
/*** THIS IS LEVEL 1 ACC MATCH ***/
/**********************************/
DECLARE c1 CURSOR READ_ONLY FOR
SELECT
CONSOURCE1.MASTERSOURCEACCID_LEV1 as ACCOUNTID, CONSOURCE1.RECORDID as SOURCEID,CONSOURCE1.SOURCECONID as SOURCEBRANCHID, 'Prospect' as SOURCERATING,
PREFIX01,PREFIX02,PREFIX03,PREFIX04,PREFIX05,
FNAME01,FNAME02,FNAME03,FNAME04,FNAME05,FNAME06,
SNAME01,SNAME02,SNAME03,SNAME04,SNAME05
from
con CONSOURCE1
inner join C_MATCH C_MATCH1 on CONSOURCE1.recordid = C_MATCH1.CONTACTID
inner join ADD_MATCH ADD_MATCH1 on CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID and 'CONTACT' = ADD_MATCH1.TYPE
OPEN c1
FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05
WHILE @@FETCH_STATUS = 0
BEGIN
/*** PUT CODE HERE TO MATCH UP ***/
insert into C_SCORE (SOURCEID,SOURCEBRANCHID,SOURCERATING,TARGETID,TARGETACCID,MASTERSOURCEACCID_LEV)
select
@CONTACTID,
@BRANCHID,
@TYPE,
CONTACT2.RECORDID,
CONTACT2.MASTERSOURCEACCID_LEV1,
1
from
con CONTACT2
inner join C_MATCH C_MATCH2 on CONTACT2.recordid = C_MATCH2.CONTACTID
where
/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
CONTACT2.MASTERSOURCEACCID_LEV1 = @ACCOUNTID
and
CONTACT2.RECORDID <> @CONTACTID
and
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
IsNull(@FNAME01,'') <> '' and @FNAME01 = C_MATCH2.FNAME01)
)
FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05
END
CLOSE c1
DEALLOCATE c1
September 22, 2011 at 3:03 am
I would like to help you, but we need more information. The T-SQL batch is not enough.
We need:
1) Table scripts (CREATE TABLE + CREATE INDEX)
2) Sample data
3) Expected results and natural language algorithm description
Read the first article linked in my signature and find out how to post this question to get the best help from the forums.
Gianluca
-- Gianluca Sartori
September 22, 2011 at 5:33 am
Hi Thank you for your reply.
I have read your post suggestions and am happy to try and create the information as requested. Just to clarify, presumably you require the code to create any of the tables referenced in the current cursor, and also content data for each of these tables. How many records for each table should I submit?
Monty
September 22, 2011 at 5:42 am
We don't need many of them, just enough to show us the expected results.
-- Gianluca Sartori
September 22, 2011 at 6:59 am
Hi,
I struggled a little with + QUOTENAME part of exporting the data, all I seemed to get was NULLS so I have added a rar file. This contains three files. The 1st "create_tables+data_Script.sql" will create the tables and add 100 records to each for you. I created this from a database called "WORK" so you may need to change this in the script to put it where you want to work from.
I have also included two versions of the current cursor. "cursor_full_fname01.sql" is the actual cursor that runs generating lvl1, lvl2 and lvl3 match results for the FNAME01 field. I have included the cut down version of "cursor_lev1only_fname01.sql" that only contains the lvl1 part of the cursor as I should be able to understand your recommendations and replicate them into these parts.
I really appreciate your help with this.
I hope this rar file works and gives you everything you need. Sorry I couldnt do it as requested in your post.
I look forward to hearing from you.
Monty
September 22, 2011 at 7:59 am
OK, I think this should solve your issue:
PLEASE read the code and try to understand it. If you don't understand it, DON'T use it, come back here and ask for clarification.
You'll have to maintain it, so be sure you understand how it works.
Regards
Gianluca
/**********************************/
/*** THIS IS LEVEL 1 ACC MATCH ***/
/**********************************/
WITH sourceData (
ACCOUNTID,
CONTACTID, BRANCHID, TYPE,
PREFIX01, PREFIX02, PREFIX03, PREFIX04, PREFIX05,
FNAME01, FNAME02, FNAME03, FNAME04, FNAME05, FNAME06,
SNAME01, SNAME02, SNAME03, SNAME04, SNAME05
)
AS (
SELECT CONSOURCE1.MASTERSOURCEACCID_LEV1 AS ACCOUNTID
, CONSOURCE1.RECORDID AS SOURCEID
, CONSOURCE1.SOURCECONID AS SOURCEBRANCHID
, 'Prospect' AS SOURCERATING
, PREFIX01
, PREFIX02
, PREFIX03
, PREFIX04
, PREFIX05
, FNAME01
, FNAME02
, FNAME03
, FNAME04
, FNAME05
, FNAME06
, SNAME01
, SNAME02
, SNAME03
, SNAME04
, SNAME05
FROM
con CONSOURCE1
INNER JOIN C_MATCH C_MATCH1
ON CONSOURCE1.recordid = C_MATCH1.CONTACTID
INNER JOIN ADD_MATCH ADD_MATCH1
ON CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID AND 'CONTACT' = ADD_MATCH1.TYPE
)
INSERT
INTO
C_SCORE (SOURCEID
, SOURCEBRANCHID
, SOURCERATING
, TARGETID
, TARGETACCID
, MASTERSOURCEACCID_LEV)
SELECT CONTACTID
, BRANCHID
, TYPE
, CA.*
FROM sourceData AS SRC
CROSS APPLY (
SELECT CONTACT2.RECORDID
, CONTACT2.MASTERSOURCEACCID_LEV1
, 1 AS MASTERSOURCEACCID_LEV
FROM
con CONTACT2
INNER JOIN C_MATCH C_MATCH2
ON CONTACT2.recordid = C_MATCH2.CONTACTID
WHERE
/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
CONTACT2.MASTERSOURCEACCID_LEV1 = SRC.ACCOUNTID
AND
CONTACT2.RECORDID <> SRC.CONTACTID
AND
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
isnull(SRC.FNAME01, '') <> ''
AND SRC.FNAME01 = C_MATCH2.FNAME01)
)
) AS CA;
/**********************************/
/*** THIS IS LEVEL 2 ACCC MATCH ***/
/**********************************/
WITH sourceData (
ACCOUNTID,
CONTACTID, BRANCHID, TYPE,
PREFIX01, PREFIX02, PREFIX03, PREFIX04, PREFIX05,
FNAME01, FNAME02, FNAME03, FNAME04, FNAME05, FNAME06,
SNAME01, SNAME02, SNAME03, SNAME04, SNAME05
)
AS (
SELECT CONSOURCE1.MASTERSOURCEACCID_LEV2 AS ACCOUNTID
, CONSOURCE1.RECORDID AS SOURCEID
, CONSOURCE1.SOURCECONID AS SOURCEBRANCHID
, 'Prospect' AS SOURCERATING
, PREFIX01
, PREFIX02
, PREFIX03
, PREFIX04
, PREFIX05
, FNAME01
, FNAME02
, FNAME03
, FNAME04
, FNAME05
, FNAME06
, SNAME01
, SNAME02
, SNAME03
, SNAME04
, SNAME05
FROM
con CONSOURCE1
INNER JOIN C_MATCH C_MATCH1
ON CONSOURCE1.recordid = C_MATCH1.CONTACTID
INNER JOIN ADD_MATCH ADD_MATCH1
ON CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID AND 'CONTACT' = ADD_MATCH1.TYPE
WHERE
MASTERSOURCEACCID_LEV2 <> MASTERSOURCEACCID_LEV1
)
INSERT
INTO
C_SCORE (SOURCEID
, SOURCEBRANCHID
, SOURCERATING
, TARGETID
, TARGETACCID
, MASTERSOURCEACCID_LEV)
SELECT CONTACTID
, BRANCHID
, TYPE
, CA.*
FROM sourceData AS SRC
CROSS APPLY (
SELECT
CONTACT2.RECORDID
, CONTACT2.MASTERSOURCEACCID_LEV2
, 2 AS MASTERSOURCEACCID_LEV
FROM
con CONTACT2
INNER JOIN C_MATCH C_MATCH2
ON CONTACT2.recordid = C_MATCH2.CONTACTID
WHERE
/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
CONTACT2.MASTERSOURCEACCID_LEV2 = SRC.ACCOUNTID
AND
CONTACT2.RECORDID <> SRC.CONTACTID
AND
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
isnull(SRC.FNAME01, '') <> ''
AND SRC.FNAME01 = C_MATCH2.FNAME01)
)
) AS CA;
/**********************************/
/*** THIS IS LEVEL 3 ACCC MATCH ***/
/**********************************/
WITH sourceData (
ACCOUNTID,
CONTACTID, BRANCHID, TYPE,
PREFIX01, PREFIX02, PREFIX03, PREFIX04, PREFIX05,
FNAME01, FNAME02, FNAME03, FNAME04, FNAME05, FNAME06,
SNAME01, SNAME02, SNAME03, SNAME04, SNAME05
)
AS (
SELECT CONSOURCE1.MASTERSOURCEACCID_LEV3 AS ACCOUNTID
, CONSOURCE1.RECORDID AS SOURCEID
, CONSOURCE1.SOURCECONID AS SOURCEBRANCHID
, 'Prospect' AS SOURCERATING
, PREFIX01
, PREFIX02
, PREFIX03
, PREFIX04
, PREFIX05
, FNAME01
, FNAME02
, FNAME03
, FNAME04
, FNAME05
, FNAME06
, SNAME01
, SNAME02
, SNAME03
, SNAME04
, SNAME05
FROM
con CONSOURCE1
INNER JOIN C_MATCH C_MATCH1
ON CONSOURCE1.recordid = C_MATCH1.CONTACTID
INNER JOIN ADD_MATCH ADD_MATCH1
ON CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID AND 'CONTACT' = ADD_MATCH1.TYPE
WHERE
MASTERSOURCEACCID_LEV3 <> MASTERSOURCEACCID_LEV2
AND
MASTERSOURCEACCID_LEV3 <> MASTERSOURCEACCID_LEV1
)
INSERT
INTO
C_SCORE (SOURCEID
, SOURCEBRANCHID
, SOURCERATING
, TARGETID
, TARGETACCID
, MASTERSOURCEACCID_LEV)
SELECT CONTACTID
, BRANCHID
, TYPE
, CA.*
FROM sourceData AS SRC
CROSS APPLY (
SELECT
CONTACT2.RECORDID
, CONTACT2.MASTERSOURCEACCID_LEV3
, 3 AS MASTERSOURCEACCID_LEV
FROM
con CONTACT2
INNER JOIN C_MATCH C_MATCH2
ON CONTACT2.recordid = C_MATCH2.CONTACTID
WHERE
/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
CONTACT2.MASTERSOURCEACCID_LEV3 = SRC.ACCOUNTID
AND
CONTACT2.RECORDID <> SRC.CONTACTID
AND
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
isnull(SRC.FNAME01, '') <> ''
AND SRC.FNAME01 = C_MATCH2.FNAME01)
)
) AS CA
-- Gianluca Sartori
September 22, 2011 at 8:49 am
Silly question. Is there an easy way for me to copy this code as it is formatted? When I copy and paste from the windo it all comes out in one line in by query editor?
September 22, 2011 at 9:12 am
Hi Gianluca Sartori,
Thank you very much for your asistance with this, and so quickly too.
I must thank you for an elegant looking solution. I am looking forward to running it against the full tables and cros comparing the results and the times with the cursor.
If I am totally honest, I do not understand the query at all. I think I understand the part of the With Select, but I dont understand its use, or reasoning as yet. But the Insert satement is complex for my limited understanding. It seems as though the CA.* in the 1st part of the select is going to be the fields CONTACT2.RECORDID, CONTACT2.MASTERSOURCEACCID_LEV1, 1 AS MASTERSOURCEACCID_LEV from the second part of the select, neat. I guess the only bit that I don't fully understand is the CROSS APPLY, as I have never used this command before. I will go and google the answer, but will I understand where and when to use it in the future to write my own queries, I'm still confused a little I'm afraid. My Head hurts...:-)
I am sitting down with one of the SQL programmers at head office tomorrow so I will see if they can explain the workings of this query in a little more detail to me.
I would hate to take up any more of your time educating me, but please feel free to add any comments that you feel may assist my understanding.
Once again thank you very much for this solution.
Regards
Monty
September 22, 2011 at 9:43 am
montyismobile (9/22/2011)
Silly question. Is there an easy way for me to copy this code as it is formatted? When I copy and paste from the windo it all comes out in one line in by query editor?
I guess that if you paste it into MS Word and then in SSMS you will have the formatting untouched.
-- Gianluca Sartori
September 22, 2011 at 9:46 am
montyismobile (9/22/2011)
Hi Gianluca Sartori,Thank you very much for your asistance with this, and so quickly too.
I must thank you for an elegant looking solution. I am looking forward to running it against the full tables and cros comparing the results and the times with the cursor.
If I am totally honest, I do not understand the query at all. I think I understand the part of the With Select, but I dont understand its use, or reasoning as yet. But the Insert satement is complex for my limited understanding. It seems as though the CA.* in the 1st part of the select is going to be the fields CONTACT2.RECORDID, CONTACT2.MASTERSOURCEACCID_LEV1, 1 AS MASTERSOURCEACCID_LEV from the second part of the select, neat. I guess the only bit that I don't fully understand is the CROSS APPLY, as I have never used this command before. I will go and google the answer, but will I understand where and when to use it in the future to write my own queries, I'm still confused a little I'm afraid. My Head hurts...:-)
I am sitting down with one of the SQL programmers at head office tomorrow so I will see if they can explain the workings of this query in a little more detail to me.
I would hate to take up any more of your time educating me, but please feel free to add any comments that you feel may assist my understanding.
Once again thank you very much for this solution.
Regards
Monty
You're welcome.
CROSS APPLY is a correlated subquery that can match fields from the outer query (SRC in this case).
WITH queryName AS ( SELECT something ) is a CTE (Common Table Expression), which allows you to define a query and use it anywhere in your statement.
I used a CTE and CROSS APPLY because this allowed me to change very few lines of your code.
If you have specific questions, don't hesitate to ask.
Regards
Gianluca
-- Gianluca Sartori
September 22, 2011 at 9:48 am
Gianluca Sartori (9/22/2011)
montyismobile (9/22/2011)
Silly question. Is there an easy way for me to copy this code as it is formatted? When I copy and paste from the windo it all comes out in one line in by query editor?I guess that if you paste it into MS Word and then in SSMS you will have the formatting untouched.
I remember reading something about some space being converted to another ascii code that you need to convert.
I'm sure there's a function or option that does that in SSMS but I can't remember which one!
September 23, 2011 at 10:51 am
Hi Gianluca Sartori,
I would like to thank you for all your help with this cursor issue. When tested against the same data set your query produced identical results to the Cursor. The difference was the spped. The cursor took 39mins, your query took 12 secs. What an amazing improvement in performance. Once again I am truely grateful.
Based upon this evidence, I would advise anyone else with slow apps to look at replacing cursors wherever possible.
You are a true Gent.
Thank you Sir.
Monty
September 23, 2011 at 10:55 am
montyismobile (9/23/2011)
Hi Gianluca Sartori,I would like to thank you for all your help with this cursor issue. When tested against the same data set your query produced identical results to the Cursor. The difference was the spped. The cursor took 39mins, your query took 12 secs. What an amazing improvement in performance. Once again I am truely grateful.
Based upon this evidence, I would advise anyone else with slow apps to look at replacing cursors wherever possible.
You are a true Gent.
Thank you Sir.
Monty
Well we need to take care of this then.
12 sec is still slow.
can you post the execution plans so we can take it down under 1s?
September 23, 2011 at 10:56 am
montyismobile (9/23/2011)
Hi Gianluca Sartori,I would like to thank you for all your help with this cursor issue. When tested against the same data set your query produced identical results to the Cursor. The difference was the spped. The cursor took 39mins, your query took 12 secs. What an amazing improvement in performance. Once again I am truely grateful.
Based upon this evidence, I would advise anyone else with slow apps to look at replacing cursors wherever possible.
You are a true Gent.
Thank you Sir.
Monty
You're welcome.
-- Gianluca Sartori
September 23, 2011 at 10:58 am
Ninja's_RGR'us (9/23/2011)
montyismobile (9/23/2011)
Hi Gianluca Sartori,I would like to thank you for all your help with this cursor issue. When tested against the same data set your query produced identical results to the Cursor. The difference was the spped. The cursor took 39mins, your query took 12 secs. What an amazing improvement in performance. Once again I am truely grateful.
Based upon this evidence, I would advise anyone else with slow apps to look at replacing cursors wherever possible.
You are a true Gent.
Thank you Sir.
Monty
Well we need to take care of this then.
12 sec is still slow.
can you post the execution plans so we can take it down under 1s?
I'm sure there's still much that can be done.
Can you also post indexes scripts?
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply