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
Showing posts with label build. Show all posts
Showing posts with label build. Show all posts
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.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!
Defining an Action in SSAS
Hi,
I have the need to build an Action on a measure that belongs to a cube that behaves like a DrillThrough Action that returns details related to a measure.
The problem is that the details data should come from a table that is not inside the cube but it is in an SQLServer DB.
So, it is possible to define an Action that could retrieve data from an external DB?
If so, how I'll write the action in BIDS (Target Type, Action Type, Action expression, etc.)?.
Thank you.
You would need to create a rowset action. I don't have sample code, but hopefully someone else will be able to provide you with that information.
|||
Thank you for the answer,
but you said to create a rowset action, can I use a "statement" action instead?
I mean, is there any reason that let you suggest me to use a rowset action instead of a statement action?
Please let me understand better. Thank you!
Sunday, February 19, 2012
default value for a parameter to select all
Hello,
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||
In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
default value for a parameter to select all
Hello,
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
default value for a parameter to select all
Hello,
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
Subscribe to:
Posts (Atom)