SET NOCOUNT ON
-
-
Way to go David. I think this is a best practice as it improves the performance of the stored procedures by reducing the network traffic.
Francis
-
And here we we were thinking maybe no one cared about using it.
-
Sorry, half the post got lost for some reason.
It should have said that I have a stored procedure where SET NOCOUNT ON doesn't seem to work.
The procedure itterates down through a table with a parent_id, child_id relationship using temporary tables to accumulate the records.
The selections in the application database don't send ROWCOUNT messages but the ones for the TEMPDB tables do.
I was wondering why and how can I stop them?
Sorry for the missed post, I wasn't practicing DBA ZEN
-
Are all peices within the same scope. Same SP or in different ones, or maybe are you using dymanic SQL within you SP where the ones for the tempDB are returning. I use temp tables often and have not seen SET NOCOUNT ON not work, but no dynamic SQL and all my SP's even the grouped ones have the SET NOCOUNT ON parameter in them.
-
It is one long stored procedure. I am intending to break it out into smaller procedures later, but at this stage I am simply getting the procedure working.
What I am doing is
- Creating temporary tables.
- Itterating down a tree structure accessing the tables.
- Once the loop is complete I am joining the results to the main database table.
- Drop all temporary tables.
Once all this is working I will have to look at preventing recompiles. Any guidance on that would be greatly appreciated as well!
-
I have found that when I use more than one database in a stored procedure, I have to put SET NOCOUNT ON for each one.
USE TEMPDB
SET NOCOUNT ON
Code
USE MyDB
SET NOCOUNT ON
Code
USE TEMPDB
SET NOCOUNT ON
Code
I believe SET NOCOUNT ON is a setting for the 'Current' database, not for the script.
-SQLBill
-
The script doesn't switch to using TEMPDB. It simply references temporary tables.
CREATE TABLE #Tbl_Tree1(...etc)
INSERT #Tbl(... etc)
It is a shame that there isn't a global setting for SET NOCOUNT.
-
Not real sure but I think if you have "GO" anywhere in your script, you need to set nocount to on after the "GO". Of course, if you don't have any "GO"s, just ignore this post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
The actual procedure is shown below.
The idea is that it returns a "page" of records. A page defaulting to 10 records.
CREATE PROCEDURE dbo.usp_GetChildLinksNav
@lViewTreeId Int,
@lPageLength Int =10 ,
@lPageNo Int = 1 ,
@lLanguageID Int=1 ,
@lSortID Int=1 ,
@lYear Int = -1 ,
@lDateType Int = 0
AS
/*
* PROC:- usp_GetChildLinksNav
*
* Description:- Performs the page n of m retrieval on the usp_GetChildLinks proc.
*
* Arguments:-
* ===========
* @lViewTreeID The root of the tree for which branches will be retrieved.
* @lPageLength The number of links in a page of links.
* @lPageNo The page number requested. If this exceeds the actual number of
* pages available, it will be reset to the last page.
* @lLanguageID the id of the required language.
* @lSortID An identifier to identify the sort order for the records.
* 1 = Title , 2 = Date
*
* Variables:-
* ===========
* @lMaxRows The calculated number of records to retrieve. If this exceeds the
* actual number of records available then this will be reset to
* the actual number of records.
* @lTotalRows The actual number of archived news rows available.
* @lFirstRow The record number of the first record on the page.
* @lPages The actual number of pages available.
*
* Dependancies Proc: usp_GetChildLinks
*
*
* Modified Date: Author Description
* ============== =========== ===========
* 09-Mar-2004 David Poole Created
*/
SET NOCOUNT ON
DECLARE @lTotalRows INT
DECLARE @lMaxRows INT
DECLARE @lFirstRow INT
DECLARE @lPages INT
CREATE TABLE #Tbl_PageInfo1 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
CREATE TABLE #Tbl_PageInfo2 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
INSERT #Tbl_PageInfo1 ( Page_Id , Title , Updated_Date,Valid_From, Valid_Till, Position )
exec usp_GetChildLinks
@lViewTreeID ,
@lLanguageID ,
@lYear ,
@lDateType
-- How many links were actually returned?
SET @lTotalRows = @@ROWCOUNT
-- Calculate the maximum number of rows to return on the basis of the
-- page length and the page requested.
SET @lMaxRows = @lPageLength * @lPageNo
-- If the calculated rows exceeds the actual number of rows then
-- reset the calculated rows to the actual.
IF @lMaxRows > @lTotalRows OR @lMaxRows=0
SET @lMaxRows = @lTotalRows
-- Calculate the actual number of pages.
IF @lPageLength=0
SET @lPages = 1
ELSE
SET @lPages = CEILING ( CAST ( @lTotalRows AS REAL ) / CAST ( @lPageLength AS REAL) )
-- If the requested page number is after the last page, then set to the last page.
IF @lPageNo > @lPages
SET @lPageNo = @lPages
-- Calculate the first row number.
SET @lFirstRow = @lPageLength * ( @lPageNo - 1 ) + 1
SET @lPageLength = (@lMaxRows - @lFirstRow) +1
SET ROWCOUNT @lMaxRows
IF @lSortID = 1
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Title ASC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Title DESC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 2
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Valid_From DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Valid_From ASC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 3
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Valid_Till DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Valid_Till ASC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 4
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Updated_Date DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Updated_Date ASC, Page_ID DESC
GOTO EndProc
END
EndProc:
SET ROWCOUNT 0
-- Explicit Drop of temporary table objects.
DROP TABLE #Tbl_PageInfo1
DROP TABLE #Tbl_PageInfo2
GO
-
I'm not positive about this, but...
I looked at Batches, overview in the BOL and it says that CREATE command begins each batch. So, it seems there is an 'implied' GO after each CREATE command.
Try moving the SET NOCOUNT ON to the place after the last CREATE and before the INSERT.
-SQLBill
-
Perhaps changing the temp tables to tables variables would help as well since declaring table variables is not considered DML and my guess is therefore no implied GO statement.
By the way do you use SET NOCOUNT ON in the proc exec usp_GetChildLinks ?
Francis
-
Yes, all stored procedures that I write have SET NOCOUNT ON immediately after the header block.
I haven't tried declaring variables as table types before so I'll give it a whirl.
-
David,
Haven't seen any more from you on this, have you tried this solution as I suggested:
CREATE TABLE #Tbl_PageInfo2 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
SET NOCOUNT ON
INSERT #Tbl_PageInfo1 ( Page_Id , Title , Updated_Date,Valid_From, Valid_Till, Position )
exec usp_GetChildLinks
@lViewTreeID ,
@lLanguageID ,
@lYear ,
@lDateType
-SQLBill
-
Just out of curiosity are there any triggers that change the NOCOUNT behavior? I've had problems with this in the past where a trigger has played havoc with NOCOUNT settings.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply