Thanks Chris,

I've been tuning a stored procedure that itterates down a tree structure  to accumulate a list of ids within that structure and then returns the details associated with those...

I have been doing some experimenting with routines to traverse down a tree structure with temporary tables vs table variables.

God knows how but the rowcount thing resolved itself

Good primer!

If I have a stored procedure that does multiple tasks, is there a way of getting the  overall cost of the entire procedure or do I have to manually...

CREATE TABLE #MyTable(<column name> <column Type> , etc)

INSERT #MyTable(<fields>

exec master.dbo.xp_msver

I would second the motion to investigate the peaks.

I work on a large web CMS that has a complicated caching mechanism.  If I get the CMS caching right the database...

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.

Frank,

I had a very brief look at it.

My thoughts are that one of the reasons for using C++ is its ability to use pointers.  The point about managed code is...

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...

OK, I've found it.

If you run SQL Profiler and look at the Stored Procedures events, if you run a stored procedure beginning with the letters sp_ then you will get...

So basically the real life query is some horrendous multi-table join with a vast number of records.

I would agree that a temporary table is the way to go but I...

I've tried running a SQL Trace on the MASTER and local database but cannot see any attempt to access the MASTER database.

I know that this is not conclusive because it...

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.

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...

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...

I could be picky here and say that in addition to the "bouncing through the master database" thing having a different prefix for none Microsoft procedures aids identification.

Bert De Haes,...