Table var maximum row number

Question

Table var maximum row number

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Matt Miller (6/23/2008)
Gail - in 2005, if you knew it was going to recompile - would you FORCE the recompile on a given statement?

If I know absolutely, for sure that a statement will recompile, yes. I've got a few procs like that in my current system. They use a temp table created elsewhere and hence recompile every time they run

It is theoretically supposed to speed things up by signifying to not even bother caching. I just haven't played with this enough to know if this amounts to anything or not.

On 2005 I'm not sure. Haven't done any significant tests. On SQL 2000 I've experienced severe compile-blocking before, where multiple users were recompiling a proc and trying to cache the plan and getting blocked by other users recompiling the proc and trying to cache the plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

You are right about table variables can't be used together with INSERT ... EXEC since this is a SQL 2000 forum.

But in SQL 2005 you can use a table variable with INSERT ... EXEC.

begin tran

DECLARE@Target TABLE

(

spid int,

ecid int,

status nvarchar(100),

loginname nvarchar(100),

hostname nvarchar(100),

blk int,

dbname nvarchar(100),

cmd nvarchar(100),

request_id int

)

insert@target

execsp_who

select*

from@target

rollback tran

select*

from@target

As you can see, a table variable is not affected by transactions.

N 56°04'39.16"
E 12°55'05.25"

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 3

I find table variables are great for filtering out data usnig IN or joins. Queries which need to run against a base selection.. say filter to "n products".. I make a table variable with a ProductID int and set PRIMARY KEY (ProductID).. populate it with desired products.. then code relevant steps in the query to filter using "WHERE ProductID IN (SELECT ProductID from @tablevar)"

Usually v fast.. personally I don't use them for anything else as we handle large amounts of data. Also, when using table variables without a primary key set, usually every single time I see a decrease in performance over just using a hash temp table.

Oh and as for how much is "too much". Well some of our queries handle millions of rows.. so speaking from a bit of experience.. well it's just common sense. Are you talking tens of thousands of rows? Probably don't use a table variable in that case. For gargantuan amounts of data (millions of rows) use a hash temp table or better still, consider a "real" table which you "if exists.. drop/create" in the query (and then index of course).

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

GilaMonster (6/24/2008)
Matt Miller (6/23/2008)
Gail - in 2005, if you knew it was going to recompile - would you FORCE the recompile on a given statement?
If I know absolutely, for sure that a statement will recompile, yes. I've got a few procs like that in my current system. They use a temp table created elsewhere and hence recompile every time they run
It is theoretically supposed to speed things up by signifying to not even bother caching. I just haven't played with this enough to know if this amounts to anything or not.
On 2005 I'm not sure. Haven't done any significant tests. On SQL 2000 I've experienced severe compile-blocking before, where multiple users were recompiling a proc and trying to cache the plan and getting blocked by other users recompiling the proc and trying to cache the plan.

Cool! Sounds in line with what I was envisioning. I may be spending quite a bit more time on tunning certain large things, so I'm thinking this is about to start popping up a fair amount.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Heh... best way to prevent recompiles in 2k5... is to... use 2k :hehe:

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