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

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.

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.

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

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.