Hi all,
The db that I took over is full of !@.##$@., unnormalized tables, cursors, you name it and it has it :(.
There is this cursor that opens a temp table, fetches the key and then deletes from the production table using that key for every row in the temp table.
I want to change it to something like
delete from A
where exists (select 1
from B
where B.ID1 = A.ID1 and
B.ID2 = A.ID2)
Now, I'm thinking that this query would secuentially scan A and compare the key to what B has and that is a waste of time. Is there a way to do it the other way around ? Scan the rows on table B and then delete them from table A ?
I haven't really played with sql in some time, maybe the answer is trivial but I can't see it right now.
Thanks in advance
Luis TorresWhen you submit the query, SQL Server will generate a plan. That plan will often automagically turn the query "inside out" as you've described if the optimizer determines that is more efficient.
-PatP|||My guess is that this will do quite well
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable00(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
CREATE TABLE myTable99(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
GO
INSERT INTO myTable00(ID1, ID2, Col3)
SELECT 1 , 1, 'a' UNION ALL
SELECT 33 , 1, 'a' UNION ALL
SELECT 555, 1, 'a' UNION ALL
SELECT 777, 1, 'a'
DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(ID1, ID2, Col3)
SELECT @.x, 1, 'a'
SELECT @.x = @.x + 1
END
GO
SET SHOWPLAN_TEXT ON
GO
SET NOCOUNT OFF
GO
DELETE FROM l
FROM myTable99 l
INNER JOIN myTable00 r
ON l.ID1 = r.ID1
AND l.ID2 = r.ID2
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE myTable00
DROP TABLE myTable99
GO
And produces
StmtText
--------------------------------------------------------
|--Clustered Index Delete(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCES:([r].[ID2], [r].[ID1]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[myTable00].[PK__myTable00__37BBEBC3] AS [r]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435] AS [l]), SEEK:([l].[ID1]=[r].[ID1] AND [l].[ID2]=[r].[ID2]) ORDERED FORWARD)
(6 row(s) affected)|||Thank you Pat and Brett for your answer :), The inner join makes a lot of sense now that I see it :)
Luis Torres
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Tuesday, March 27, 2012
Friday, March 9, 2012
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.developersdex.com ***
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...
> 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.developersdex.com ***
> 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
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.developersdex.com ***
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...
> 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.developersdex.com ***
> 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
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...
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!
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:|||You might also be better simplifying the insert. You should not need to
> 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!
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!
Subscribe to:
Posts (Atom)