SQL Server Performance

Weird Performance fix

Discussion in 'SQL Server 2005 General DBA Questions' started by bankboy, Mar 20, 2008.

  1. bankboy New Member

    Hi guys
    I was having a problem where my test query was taking over 8 minutes to return 2203 records...I have since 'fixed' the problem and it returns in under 1 second but I have no idea why it works now. I was hoping someone could shed some light on it for me.
    The code in both the original query and the 'fixed' query are identical....EXCEPT...that we added ' TOP (Select Count(*) FROM dbo.MasterProviders)' to the cursor declaration subquery.
    Does anyone know why adding 'TOP' to the SELECT would fix the problem?
    thanks in advance
    craig :)
    ...<snip>....
    DECLARE @curProvider CURSOR
    SET @curProvider = CURSOR FOR

    SELECT vProvider.concern_role_id, vProvider.operating_name, CR.ConcernRoleName
    FROM
    (
    SELECT TOP (SELECT COUNT(*) FROM dbo.MasterProviders) MP.Provider_Full_Name AS operating_name
    , CRAID.CONCERNROLEID AS concern_role_id
    FROM dbo.MasterProviders MP
    INNER JOIN dbo.CONCERNROLEALTERNATEID CRAID ON MP.EII_ID = CONVERT(float,CRAID.ALTERNATEID)
    WHERE CRAID.TYPECODE = 'CA0'
    ) vProvider
    LEFT OUTER JOIN dbo.CONCERNROLE CR ON vProvider.concern_role_id = CR.ConcernRoleID

    OPEN @curProvider
    FETCH NEXT FROM @curProvider INTO @CRID,@OPName, @CRName
    WHILE (@@FETCH_STATUS = 0 )
    BEGIN
    ...<snip>...
  2. Adriaan New Member

    Does the same problem occur if you run the query for the cursor all by itself?
    Are you manipulating the data in the cursor, or just looping through it reading values? Did you try opening the cursor as a snapshot?
    The JOIN expression that uses a CONVERT function is the most likely cause of the problem, since the function is forcing SQL to go through all the rows from the CONCERNROLEALTERNATEID table, ignoring any available index. Choosing the right data type is key to good performance!
    The TOP expression is brilliant, really - but have you checked to see if perhaps TOP 100 PERCENT has the same effect?
  3. bankboy New Member

    Hi Adriaan...thanks for the reply!
    1. When I run the cursor query by itself, the results are returned immediately. It is only when it is put into the cursor that the performance evaporates.
    2. I am manipulating the data after I put it into the cursor (parsing out strings eg. SET @Str1 = (SELECT SUBSTRING(@CRNAME, 1, @Found-1))
    3. Also, I tried the 'TOP 100 PERCENT' instead of my wacky 'TOP' query as you asked, but it takes over 8 minutes to complete too.
    I agree though...that the JOIN using the convert is probably part of the issue....and putting all of this into a temp table probably isn't helping my cause at all either...ha ha
    Thanks for your comments though, I appreciate the help...and I will look into opening the cursor as a snapshot to see what that does for me
  4. ashwini New Member

    Are you manipulating the data in the cursor, or just looping through it reading values? Did you try opening the cursor as a snapshot?
  5. bankboy New Member

    I am manipulating the data inside the cursor...and putting the results into a temp table. I am sure that the manipulation is causing the slowdown....but what I can't figure out is why adding the "SELECT TOP (SELECT COUNT(*) FROM dbo.MasterProviders) " to the query selection is causing the whole thing to speed up when everything else (including the data manipulation etc) stays the same.
    Any ideas as to why my solution worked would help me sleep better at night....ha ha
    Here is the full query, in case you were curious
    craig :)
    -- ===================================================
    CREATE TABLE #vProvider
    (
    Concern_Role_ID VARCHAR(20),-- PRIMARY KEY,
    Operating_Name VARCHAR(200) , -- full provider name: operating_name||legal_name
    Provider_Name VARCHAR(200), -- Provider Name
    Provider_Location VARCHAR(200), -- Provider Location
    Services VARCHAR(200), -- Training Type-Funding Type-SFS Program Code
    Contract_Number VARCHAR(200), -- Contract Number
    Concern_Role_Name VARCHAR(200) -- Original Provider Name separated with ^'s
    )
    ---SELECT @Str1 AS "Provider Name",@Str2 AS "Provider Location",@Str3 AS "Training Type-Funding Type-SFS Program Code",@Str4 AS "Contract Number"
    DECLARE @Str1 VARCHAR(200),@Temp VARCHAR(200),@Str2 VARCHAR(200),@Str3 VARCHAR(200),@Str4 VARCHAR(200),@Found INT,
    @CRName VARCHAR(200),@Count INT, @Error BIT,@curProvider CURSOR,@CRID VARCHAR(20),@OPName VARCHAR(200)

    SET @curProvider = CURSOR FOR

    SELECT vProvider.concern_role_id, vProvider.operating_name, CR.ConcernRoleName
    FROM
    (
    SELECT TOP (SELECT COUNT(*) FROM dbo.MasterProviders) MP.Provider_Full_Name AS operating_name
    , CRAID.CONCERNROLEID AS concern_role_id
    FROM dbo.MasterProviders MP
    INNER JOIN dbo.CONCERNROLEALTERNATEID CRAID ON MP.EII_ID = CONVERT(float,CRAID.ALTERNATEID)
    WHERE CRAID.TYPECODE = 'CA0'
    ) vProvider
    LEFT OUTER JOIN dbo.CONCERNROLE CR ON vProvider.concern_role_id = CR.ConcernRoleID

    OPEN @curProvider
    FETCH NEXT FROM @curProvider INTO @CRID,@OPName, @CRName
    WHILE (@@FETCH_STATUS = 0 )
    BEGIN
    -- Manipulate the data in a really clunky way

    --SET @CRName = 'Test1^Test2^Test3^Test4'
    SET @TEMP = @CRNAME
    SET @Count = 0
    SET @Error = 0
    SET @Count = (LEN(@TEMP) - LEN(REPLACE(@TEMP, '^', '')))
    IF @Count =0
    BEGIN
    SET @Str1 = (SELECT SUBSTRING(@CRNAME, 1, LEN(@TEMP)))
    SET @Str2 = 'Empty String'
    SET @Str3 = 'Empty String'
    SET @Str4 = 'Empty String'
    END
    ELSE IF @Count = 1
    BEGIN
    SET @Found = (SELECT CHARINDEX ('^',@CRName, 1))
    SET @Str1 = (SELECT SUBSTRING(@CRNAME, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@CRNAME, @FOUND+1, LEN(@CRName)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str2 = (SELECT SUBSTRING(@TEMP, 1, LEN(@TEMP)))
    SET @TEMP = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    SET @Str3 = 'Empty String'
    SET @Str4 = 'Empty String'
    END
    ELSE IF @Count = 2
    BEGIN
    SET @Found = (SELECT CHARINDEX ('^',@CRName, 1))
    SET @Str1 = (SELECT SUBSTRING(@CRNAME, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@CRNAME, @FOUND+1, LEN(@CRName)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str2 = (SELECT SUBSTRING(@TEMP, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str3 = (SELECT SUBSTRING(@TEMP, 1, LEN(@TEMP)))
    SET @TEMP = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    SET @Str4 = 'Empty String'
    END
    ELSE IF @Count = 3
    BEGIN
    SET @Found = (SELECT CHARINDEX ('^',@CRName, 1))
    SET @Str1 = (SELECT SUBSTRING(@CRNAME, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@CRNAME, @FOUND+1, LEN(@CRName)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str2 = (SELECT SUBSTRING(@TEMP, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str3 = (SELECT SUBSTRING(@TEMP, 1, @Found-1))
    SET @TEMP = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    SET @Found = (SELECT CHARINDEX ('^',@TEMP, 1))
    SET @Str4 = (SELECT SUBSTRING(@TEMP, @FOUND+1, LEN(@TEMP)))
    END
    ELSE
    BEGIN
    SELECT 'There was an error' AS ERROR_INFO, @CRID,@CRName
    SET @Error = 1
    END
    IF @Error = 0
    INSERT INTO #vProvider VALUES (@CRID,@OPName,@str1,@str2,@str3,@str4,@CRName)
    ---SELECT @Str1 AS "Provider Name",@Str2 AS "Provider Location",@Str3 AS "Training Type-Funding Type-SFS Program Code",@Str4 AS "Contract Number"
    -- ===================================================
    -- Close off the Cursor
    -- ===================================================
    FETCH NEXT FROM @curProvider INTO @CRID,@OPName, @CRName
    END -- WHILE}

    CLOSE @curProvider
    DEALLOCATE @curProvider
    -- ============================================
    -- Get rid of the temp table
    -- ============================================
    SELECT * FROM #vProvider
    DROP TABLE #vProvider
  6. Adriaan New Member

    You're not manipulating data in the resultset of the cursor - you're just reading data from the resultset - so by all means choose a snapshot as the cursor type.
    Other than that, there are probably better solutions to parsing than this. For starters, you can embed CHARINDEX evaluations inside SUBSTRING calls. You're also overly generous with SELECT here ... this will do just fine:
    SET @Str1 = SUBSTRING(@CRName, 1, CHARINDEX('^', @CRName, 1) - 1)
    You can also do this in-line, without having to go through a cursor, a variable, and a temp table:
    SELECT SUBSTRING(CRName_column, 1, CHARINDEX('^', CRName_column, 1) - 1)
    FROM your_derived_table
    I'm using a table variable here, but you can use the query that you created for the cursor as a derived table, or in 2005 as a CTE:
    Edit - This version works as long as the string contains three separators.
    DECLARE @t TABLE (my_column VARCHAR(MAX))
    INSERT INTO @t VALUES ( 'a^b^c^d')
    INSERT INTO @t VALUES ( '^^x^')
    SELECT T2.Str1, T2.Str2,
    SUBSTRING(T2.Rem2, 1, CHARINDEX('^', T2.Rem2, 1) -1) Str3,
    SUBSTRING(T2.Rem2, CHARINDEX('^', T2.Rem2, 1) + 1, LEN(T2.Rem2)) Str4
    FROM
    (SELECT T1.Str1,
    SUBSTRING(T1.Rem1, 1, CHARINDEX('^', T1.Rem1, 1) -1) Str2,
    SUBSTRING(T1.Rem1, CHARINDEX('^', T1.Rem1, 1) + 1, LEN(T1.Rem1)) Rem2
    FROM
    (SELECT SUBSTRING(my_column, 1, CHARINDEX('^', my_column, 1) - 1) Str1,
    SUBSTRING(my_column, CHARINDEX('^', my_column, 1) + 1, LEN(my_column)) Rem1
    FROM @t) T1) T2
    Results
    Str1 Str2 Str3 Str4
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    a b c d
    x
    Last edit - because I just realized the two last items can be split in one go.
  7. bankboy New Member

    Thanks Adriaan!
    I finally gave in to your advice (ha ha) and changed it to a snapshot cursor, and you were right....it made it fast.[:)]
    Also, thanks for the tips on the derived table etc...I'm going to try them out to see how they work.
    I still have no idea why adding "TOP x" to my cursor query helped with the speed so much, but perhaps using the TOP function alters the memory stack in the same way that a static Cursor does
    Thanks again...we appreciate your help

Share This Page