Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: WHILE LOOP INSERT Much Faster outside SProc

    Hi

    I have a little matching rotine which breaks up a Full Name into it's component words as part of a matching SP

    Trouble is when I run the WHILE LOOP INSERT inside an Store Proc it takes around 15 Minutes to run this part alone

    When I run the exact same WHILE LOOP INSERT code from iSQL Query window it takes less than a second !!!

    Can anyone give me any clues or pointers what to look at (Locks,Hints etc..) nothing I do seems to make a difference

    Unfortunately I only have .dbo permission here so can't run the profiler or anything

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


    Code:
       CREATE TABLE dbo.NCNameWords
        ( 
         Policy_No VARCHAR(20) NULL,
         word  VARCHAR(50) NULL,
         LastPos TINYINT NULL,
         NameLen TINYINT NULL
        )
     
       CREATE TABLE dbo.NCPolicys
        ( 
         PolType  CHAR(1) NULL, 
         Policy_No VARCHAR(20) NULL,
         NCName  VARCHAR(200) NULL,
         PostCode VARCHAR (50) NULL,
         RenewalDate DATETIME NULL,
         Agency VARCHAR (50) NULL
        )
    Code:
    DECLARE @SafetyTrap INT
    SET @SafetyTrap = 20
    INSERT INTO MatchLog (LogText) Values ('BEGIN Split Non Comm Names Outside SP')
    INSERT INTO NCNameWords
    SELECT 
    Policy_No -- Policy_no
    ,LEFT(NCName,CHARINDEX(' ',NCName,0)) word
    ,LEN(LEFT(NCName,CHARINDEX(' ',NCName,0)))+2 LastPos
    ,LEN(rtrim(NCName)) NameLen
    FROM NCPolicys
     
    WHILE @@ROWCOUNT > 0 AND @SafetyTrap > 0
    BEGIN
     SET @SafetyTrap = @SafetyTrap - 1
     INSERT INTO MatchLog (LogText) Values ('EXECUTING DO WHILE LOOP ' + CONVERT(VARCHAR(5),@SafetyTrap))
      INSERT INTO NCNameWords
      SELECT 
      ncp.Policy_No,-- Policy_no
      SUBSTRING(ncp.NCName ,dt.MAXLastPos ,  CHARINDEX(' ',ncp.NCName,dt.MAXLastPos)-dt.MAXLastPos)  word
      ,MAXLastPos + LEN(SUBSTRING(ncp.NCName ,dt.MAXLastPos ,  CHARINDEX(' ',ncp.NCName,dt.MAXLastPos)-dt.MAXLastPos))+1 LastPos
      ,dt.NameLen NameLen
      FROM NCPolicys ncp
       JOIN 
        (SELECT a.policy_no,MAX(a.lastpos) MAXLastPos , MAX(a.NameLen) NameLen
         FROM NCNameWords a
         GROUP BY a.policy_no
         HAVING MAX(lastpos)+1 < MAX(NameLen)
        ) dt
      ON ncp.policy_no = dt.policy_no
    END
    Code:
    LogID LogText >>>>>> SecondsTaken
    1 BEGIN PolicyMatch >>>>>> 31
    2 BEGIN POP NCPolicys >>>>>> 232
    3 BEGIN POP CPolicys >>>>>> 118
    4 BEGIN Split Non Comm Names In SP >>>>>> 0
    5 EXECUTING DO WHILE LOOP 19 >>>>>> 183
    6 EXECUTING DO WHILE LOOP 18 >>>>>> 173
    7 EXECUTING DO WHILE LOOP 17 >>>>>> 186
    8 EXECUTING DO WHILE LOOP 16 >>>>>> 0
    9 EXECUTING DO WHILE LOOP 15 >>>>>> 180
    10 EXECUTING DO WHILE LOOP 14 >>>>>> 173
    11 EXECUTING DO WHILE LOOP 13 >>>>>> 172
    12 EXECUTING DO WHILE LOOP 12 >>>>>> 0
    13 EXECUTING DO WHILE LOOP 11 >>>>>> 0
    14 EXECUTING DO WHILE LOOP 10 >>>>>> 1
    15 EXECUTING DO WHILE LOOP 9 >>>>>> 0
    16 EXECUTING DO WHILE LOOP 8 >>>>>> 0
    21 211356 Recs in the Commercial >>>>>> 0
    22 2078 Records in the Non Comm >>>>>> 0
    23 4103 Records in the NC Words >>>>>> 0
    24 1117 have been matched* >>>>>> 532
    _________________________________________
    25 BEGIN Split Non Comm Names Out SP >>>>>> 0
    26 EXECUTING DO WHILE LOOP 19 >>>>>> 0
    27 EXECUTING DO WHILE LOOP 18 >>>>>> 1
    28 EXECUTING DO WHILE LOOP 17 >>>>>> 0
    29 EXECUTING DO WHILE LOOP 16 >>>>>> 0
    30 EXECUTING DO WHILE LOOP 15 >>>>>> 0
    31 EXECUTING DO WHILE LOOP 14 >>>>>> 1
    32 EXECUTING DO WHILE LOOP 13 >>>>>> 0
    33 EXECUTING DO WHILE LOOP 12 >>>>>> 0
    34 EXECUTING DO WHILE LOOP 11 >>>>>> 0
    35 EXECUTING DO WHILE LOOP 10 >>>>>> 0
    36 EXECUTING DO WHILE LOOP 9 >>>>>> 1
    37 EXECUTING DO WHILE LOOP 8 >>>>>> 0
    Last edited by GWilliy; 07-29-09 at 07:38.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Investigation Update

    When I put the Code into a seperate SProc and EXECUTE it from inside the Main SProc it Runs in less than a second ?????

    I also logged the @@Trancount which was Zero and sp_lock which crazily said all I had was a TAB Lock on master.spt_values
    Last edited by GWilliy; 07-29-09 at 11:13.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Looks like your parsing is pretty static...

    and why wouldn't you use a set based method?

    Got sample data?

    What about names like

    Mr. Gates
    Prof. Gates
    Louis Gates, Jr.
    Prof. Louis Gates
    Prof. Henry Louis Gates, Jr.
    Prof. Racist
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Thanks for the interest Brett

    Looks like your parsing is pretty static...
    Not quite sure what you mean by this?

    and why wouldn't you use a set based method?
    I thought this was SET Based - It's considerably faster than The Cursor that it was used to replace.
    Are you saying I can split out all the words (infinite number) of a sentence/name in one SQL Statement with a FOR XML type trick ? (this is SQL2000 by the way.)

    Hrummmph - And I thought I was doing well reducing the 30minute cursor to less than a second

    What about names like....
    The Name LIKE match is sub-supported by a postcode AND/OR Renewal Date Joins with short & Generic Words deleted from final table before match.

    This should work with ANY available list of VarChar sentences - I may adapt and keep in personal code library for CSV slicing as it's sooooooo quick

    I'm really confused about why this code should take so long to run InLine yet will fly in it's own seperate SProc or in iSQL?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cursors are loops.
    Loops are loops.
    :. Neither are set based

    Please can you explain in simple English what you are trying to achieve
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I'm also a bit Loopy

    I simply wanna "break up a Full Name (VarChar(100)) into it's component words, That's all George
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Quote Originally Posted by gvee
    Please can you explain in simple English what you are trying to achieve
    I gave this a little thought & want to revise my answer.

    I can already Break up the Name into component words (or any other sentence space delimmited for that matter)

    What I want, what I really really want, is not to have to move the splitting code (above) into a sub procedure so that it works in 1 second insted of 15 minutes. This is because a) why create 2 SProcs when one will do b) Unless I find out why this is happening I will be tempted to repeat this in all simialar future code c) It's making me angry

    I don't think the splitting code can be done in SQL 2000 with one Statement "Set Based Method" (variable number of words in each record). Let me know if you think it can & I may have a play for fun, but it will not noticeably improve my performance on the current sub second while loop (outside the SProc).

    Apart from this problem the whole Matching routine is HyperFast even without worktable optimizing/indexing. The overall Fuzzy matching 2,000 Full Names (4,000 words) into 200,000 Company Names takes a couple of seconds .

    As an analogy, I feel a bit like I have taken my Skoda into a garage because of occasional misfiring and the (Extremely Knowledgeable) mechanics are talking about fitting Nitrous Oxide .

    Guys I don't mean to offend, we both know you can out SQL me anyday, but will you chill out a little

    I understand that without a proper testbed and sa/profiler access this is just too much of a pain to track down in this instance, and I will resort to the Sub Procedure Fix.

    But all I originaly asked was
    Quote Originally Posted by GWilliy
    Can anyone give me any clues or pointers what to look at (Locks,Hints etc..) nothing I do seems to make a difference
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How does this fair performance wise?
    Code:
    DECLARE @phrase varchar(100)
        SET @phrase = 'The quick brown fox jumps over the lazy dog'
    
    DECLARE @positions table (
       i       int
     , row_num int identity(1,1)
    )
    
    INSERT INTO @positions (i)
    SELECT number
    FROM   (
            SELECT number
            FROM   master..spt_values
            WHERE  number BETWEEN 1 AND Len(@phrase)
            AND    type = 'P'
           ) As numbers
    WHERE  SubString(@phrase, number, 1) = ' '
    ORDER
        BY number ASC
    
    SELECT the_start
         , the_end
         , SubString(@phrase, the_start, the_end - the_start)
    FROM   (
            SELECT Coalesce(a.i + 1, 0) As the_start
                 , Coalesce(b.i, Len(@phrase) + 1) As the_end
            FROM   @positions As a
             FULL
              JOIN @positions As b
                ON a.row_num = b.row_num - 1
           ) As x
    ORDER
        BY 1
    ?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •