#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    Defining code for better performance


    Hi below is a SP that I am using to build a temp table and send back paginated results to ASP. The prcedure itself works fine and does the job although a little slow, I am sure the is a way that I can further define it so that it runs faster but SQL isn't my strong point and I was wondering if someone could help out.

    Thanks in advance
    Peter


    CREATE PROCEDURE dbo.cnms_employee_page
    (
    @Page INT,
    @RecsPerPage INT,
    @pagenumbers INT = NULL OUTPUT
    )
    AS
    SET NOCOUNT ON

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    myID INT IDENTITY,
    row_id INT,
    NETWORK_ID VARCHAR(3),
    CORP_ID VARCHAR(10),
    EMP_ID VARCHAR(10),
    LEV1_ID VARCHAR(10),
    LEV2_ID VARCHAR(10),
    LEV3_ID VARCHAR(10),
    EMP_LAST_NAME VARCHAR(25),
    EMP_FIRST_NAME VARCHAR(15),
    EMP_TITLE VARCHAR(30),
    CUSTOMER_ID VARCHAR(20),
    LOCATION_ID VARCHAR(10),
    START_DATE datetime,
    END_DATE datetime,
    username VARCHAR(50),
    action_date datetime,
    user_action CHAR(1),
    NETWORK_NAME VARCHAR(40),
    LEV1_NAME VARCHAR(20),
    LEV2_NAME VARCHAR(20),
    LEV3_NAME VARCHAR(20),
    CORP_NAME VARCHAR(40)


    )


    -- Insert the rows from tblItems into the temp. table


    INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID, LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID, LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action, NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)

    SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID, Employee.EMP_ID, Employee.LEV1_ID,
    Employee.LEV2_ID, Employee.LEV3_ID, Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
    Employee.CUSTOMER_ID, Employee.LOCATION_ID, Employee.START_DATE, Employee.END_DATE, Employee.username, Employee.action_date,
    Employee.user_action, Network.NETWORK_NAME, Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
    Corplvl3.LEV3_NAME

    FROM Employee INNER JOIN
    Network ON Employee.NETWORK_ID = Network.NETWORK_ID INNER JOIN
    Corporation ON Employee.NETWORK_ID = Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER JOIN
    CORPLVL1 ON Employee.NETWORK_ID = CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
    Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
    Corplvl2 ON Employee.NETWORK_ID = Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
    Employee.LEV1_ID = Corplvl2.LEV1_ID AND Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
    Corplvl3 ON Employee.NETWORK_ID = Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
    Employee.LEV1_ID = Corplvl3.LEV1_ID AND Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID = Corplvl3.LEV3_ID

    GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID, Employee.EMP_ID, Employee.LEV1_ID,
    Employee.LEV2_ID, Employee.LEV3_ID, Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
    Employee.CUSTOMER_ID, Employee.LOCATION_ID, Employee.START_DATE, Employee.END_DATE, Employee.username, Employee.action_date,
    Employee.user_action, Network.NETWORK_NAME, Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
    Corplvl3.LEV3_NAME
    ORDER BY Employee.row_id DESC

    SELECT pagenumbers = COUNT(row_id) FROM #TempItems


    -- Find out the first and last record we want
    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)

    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.myID >= @LastRec
    )
    FROM #TempItems
    WHERE myID > @FirstRec AND myID < @LastRec



    -- Turn NOCOUNT back OFF
    SET NOCOUNT OFF
    GO
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    not sure if this will compile, but the logic will work. You might also want to rethink the database layout. You are making many joins which will severly impact performance. It would be wise to make sure all the join columns are necessary, and if possible they should be in a clustered index... assuming these keys are some of the most frequently queried on.
    Code:
    CREATE PROCEDURE dbo.cnms_employee_page (
    @PageSize int,
    @PageNumber int
    )
    AS
    DECLARE @Ignore int
    DECLARE @LastID int
    
    IF @PageNumber > 1
      BEGIN 
            /* For pages > 1 compute how many records to ignore, 
            set ROWCOUNT and SELECT ID into @LastID */
            SET @Ignore = @PageSize * (@PageNumber - 1) + 1
            SET ROWCOUNT @Ignore 
            SELECT @LastID = e.row_id 
                   FROM dbo.Employee e inner join dbo.Network n on ( e.NETWORK_ID = n.NETWORK_ID )
                       inner join dbo.Corporation c on ( e.NETWORK_ID = c.NETWORK_ID and e.CORP_ID = c.CORP_ID )
                       inner join dbo.CORPLVL1 c1 on ( e.NETWORK_ID = c1.NETWORK_ID and e.CORP_ID = c1.CORP_ID and e.LEV1_ID = c1.LEV1_ID )
                       inner join dbo.Corplvl2 c2 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID )
                       inner join dbo.Corplvl3 c3 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID and e.LEV3_ID = c3.LEV3_ID )
                   ORDER BY e.row_id DESC
      END
    ELSE
      BEGIN
            /* For page #1 just set rowcount to pagesize */ 
            SET ROWCOUNT @PageSize
      END
     
    /* Set rowcount to @PageSize and 
    SELECT page for output (note the WHERE clause) */
    IF @LastID is null
    	select @LastID = max(e.row_id) FROM dbo.Employee e inner join dbo.Network n on ( e.NETWORK_ID = n.NETWORK_ID )
                       inner join dbo.Corporation c on ( e.NETWORK_ID = c.NETWORK_ID and e.CORP_ID = c.CORP_ID )
                       inner join dbo.CORPLVL1 c1 on ( e.NETWORK_ID = c1.NETWORK_ID and e.CORP_ID = c1.CORP_ID and e.LEV1_ID = c1.LEV1_ID )
                       inner join dbo.Corplvl2 c2 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID )
                       inner join dbo.Corplvl3 c3 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID and e.LEV3_ID = c3.LEV3_ID )
    
    SET ROWCOUNT @PageSize 
    	SELECT e.row_id, e.NETWORK_ID, e.CORP_ID, e.EMP_ID, e.LEV1_ID, 
    	e.LEV2_ID, e.LEV3_ID, e.EMP_LAST_NAME, e.EMP_FIRST_NAME, e.EMP_TITLE, 
    	e.CUSTOMER_ID, e.LOCATION_ID, e.START_DATE, e.END_DATE, e.username, e.action_date, 
    	e.user_action, Network.NETWORK_NAME, c.CORP_NAME, c1.LEV1_NAME, c2.LEV2_NAME, 
    	c3.LEV3_NAME
            FROM dbo.Employee e inner join dbo.Network n on ( e.NETWORK_ID = n.NETWORK_ID )
            	inner join dbo.Corporation c on ( e.NETWORK_ID = c.NETWORK_ID and e.CORP_ID = c.CORP_ID )
                    inner join dbo.CORPLVL1 c1 on ( e.NETWORK_ID = c1.NETWORK_ID and e.CORP_ID = c1.CORP_ID and e.LEV1_ID = c1.LEV1_ID )
                    inner join dbo.Corplvl2 c2 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID )
                    inner join dbo.Corplvl3 c3 on ( e.NETWORK_ID = c2.NETWORK_ID and e.CORP_ID = c2.CORP_ID and e.LEV1_ID = c2.LEV1_ID and e.LEV2_ID = c2.LEV2_ID and e.LEV3_ID = c3.LEV3_ID )		
            WHERE e.row_id <= @LastID
            ORDER BY e.row_id DESC
    SET ROWCOUNT 0
    
    GO
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0
    I've got to agree with abombss, those joins will be crippling the performance of the query. You need to look at the structure of the database to see if there are any changes you can make to improve moving through the data.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0
    Thanks guys,

    I have since rethought the structure and used multiple selects in replacement of the joins, and it has improved speed by about 100%:

    CREATE PROCEDURE dbo.cnms_employee_page
    (
    @Page INT,
    @RecsPerPage INT,
    @pagenumbers INT = NULL OUTPUT
    )
    AS
    SET NOCOUNT ON

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    myID INT IDENTITY,
    row_id INT,
    Network_ID varchar(3),
    Corp_ID varchar(10),
    Lev1_ID varchar(10),
    Lev2_ID varchar(10),
    Lev3_ID varchar(10)
    )


    -- Insert the rows from tblItems into the temp. table

    DECLARE @ROW_ID int, @NETID varchar(3), @CORPID varchar(10), @LVL1 varchar(10), @LVL2 varchar(10), @LVL3 varchar(10)
    INSERT INTO #TempItems (row_id,Network_ID,Corp_ID, Lev1_ID,Lev2_ID,Lev3_ID)

    SELECT row_id, Network_ID,Corp_ID, Lev1_ID,Lev2_ID, Lev3_ID FROM Employee

    SELECT @ROW_ID = row_id, @NETID = Network_ID, @CORPID = Corp_ID, @LVL1 = Lev1_ID, @LVL2 = Lev2_ID, @LVL3 = Lev3_ID FROM #TempItems WHERE myID = @page
    ORDER BY row_id
    ---------------------------------------------------------------------------------------------------

    SELECT * FROM Employee WHERE row_id = @ROW_ID

    SELECT * FROM Network WHERE Network_ID = @NETID

    SELECT * FROM Corporation WHERE Corp_ID = @CORPID AND Network_ID = @NETID

    SELECT * FROM Corplvl1 WHERE Corp_ID = @CORPID AND Network_ID = @NETID AND Lev1_ID = @LVL1

    SELECT * FROM Corplvl2 WHERE Corp_ID = @CORPID AND Network_ID = @NETID AND Lev1_ID = @LVL1 AND Lev2_ID = @LVL2

    SELECT * FROM Corplvl3 WHERE Corp_ID = @CORPID AND Network_ID = @NETID AND Lev1_ID = @LVL1 AND Lev2_ID = @LVL2 AND Lev3_ID = @LVL3
    -------------------------------------------------------------------------------------------------------

    SELECT pagenumbers = COUNT(row_id) FROM #TempItems

    -- Find out the first and last record we want
    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)

    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.myID >= @LastRec
    )
    FROM #TempItems
    WHERE myID > @FirstRec AND myID < @LastRec




    -- Turn NOCOUNT back OFF
    SET NOCOUNT OFF
    GO


    Thanks for taking the time to reply
    Peter
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    I am glad the performance improved but it seems that you could still get away with some major performance increases.

    First, why are you inserting the whole employee table into a temp table every time the sproc is executed? Can't you just use the row_id column the same way you are using the myId column and forget the whole temp table?

    You are also calculating how many records are left every time the results are paged. It would be far more efficient to get the total number of records once. And use the number of records per page, and the current page offset to calculate the records or pages left.

    You might want to even take a look at the logic from the sproc I posted. It should produce the exact same result set without a temp table at all.

IMN logo majestic logo threadwatch logo seochat tools logo