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
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Looking through your SQL, there are a lot of joins.
It might be worth executing the SELECT statement within query analyzer &
checking out the execution plan, as this might give you some insight as to
whether there are any bottlenecks in the statement.
If this is run frequently enough, it might also be worth running the index
tuning wizard against this statement to see if it has any suggestions...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Peter
What is amount of data you are inserting into the temp table?
I'd create clustered index on myID column (very useful with range
searching )
Try to a add with recompile to stored procedure and look at query optimyzer
output whether or not there are some differences.
Try to avoid using = NULL with parameters instead use =0
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uCRzU2N4DHA.3104@.TK2MSFTNGP11.phx.gbl...

quote:

---

> 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
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

|||You might also be better simplifying the insert. You should not need to
insert all of the columns you require to be output, just the key ones
(hopefully a single column). You could then join this to your source tables,
& save a potentially huge amount of insert activity...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Thanks for your suggestions guys, I did a test as James suggested
firstly as the select stood, I then ran another test just requesting the
row_id, I think the best thing to do would be to retrieve the just the
row_id as James suggested and then use multiple selects to return the
other values removing the joins which seemed to be causing the heavy
cost.
If you have any better approaches then please let me know
Many thanks again
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!