Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The ARRAY In SQL Server 2000


The ARRAY In SQL Server 2000

Author
Message
mmarovic
mmarovic
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 76
Again article suggesting replacing a cursor with one row at time processing loop. That's not more efficient solution. It would be nice if author would test the solution before claiming its superiority.
davida
davida
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 108

I have been told that using TT is more efficient than using TV when building dynamic TSQL queries, does anyone have any opinions on the validity of this statement?

thanks

 





sheepoo
sheepoo
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 34
Great Article and very good idea to simulate ARRAY functionality. A for the CURSOR simulation that has already been discussed earlier and different users have had different experiences with the approach: still I prefer the WHILE struct since, to me, it works better than a CURSOR struct
Trish Middleton
Trish Middleton
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 54

I just spent two days determining if the table variable approach was faster than the CURSOR approach.  It was a no-brainer.  The CURSOR approach was faster.  And it became even faster when I created my CURSOR with LOCAL FAST_FORWARD.

With the TV approach your reads go up significantly.  Then duration time might go down a little but not enough to make up for the increase in reads.  And it was very inconsistent.  Somtimes it went up and sometimes it went down.

With LOCAL FAST_FORWARD added to by CURSORs my reads and duration dropped significantly.

Here are my results on the CURSOR test.

CURSOR without LOCAL FAST_FORWARD

CPU---250

READS---14320

DURATON---750

CURSOR with LOCAL FAST_FORWARD

CPU -- 172

READS -- 10216

DURATION -- 283

The DURATION went from 750 to 283!!!!!

I spent the rest of the day changing all of our CURSORS to LOCAL FAST_FOWARD!


Tonja Blackwell
Tonja Blackwell
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 209
Great article. I my shop it is punishable by being forced to turn on an overhead light in your office if you use a cursor. I tebd to use while loops and temp tables. I will definitely give this a try though.
Todd Parker
Todd Parker
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5

This is the second article in a row that pushes the use of @tables.  There is little benefit if any, of using these instead of #tables.  In my experience @tables are alot harder to work with and dont work well with joins.  In the system I work with, I often need to modify existing code.  About half of the time I am up against @tables, the other half, #tables.  After struggling a few times, now my first step after I open the SP is to change all the @tables to #tables.  Just my opinion.

If you want to jump on board with temp tables and leave variable tables out, the syntax is similar, only replace "Declare @myTable table(....)"  with "Create table #myTable(....)"


TDuffy
TDuffy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 57

TVs are not 100% memory objects...

http://support.microsoft.com/?kbid=305977

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

 

Terry





TDuffy
TDuffy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 57

ONe more advantage to TVs over temp tables is the optmizers ability to use the TV in determining an execution plan, where often it can not do so with a temp table.

I'm sure we have all had code with a temp table, and displaying the execution plan returns an error because the temp table does not exist. Replacing the temp tables with TVs (where possible) solves the issue, and greatly improves performance of the query.

Terry





Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
Pretty well written article, but I'm confused. I thought "Arrays" where called "Tables" in SQL Server.

Why do people keep saying SQL server doesn't support arrays? Maybe it's correct to say SQL Server doesn't support multi-dimensional arrays...

It seems like it's usually in reference to passing in multiple values to a stored procedure, or something along those lines. OK, that's sort of fair, if you're thinking of input parameters. However, in you think about it differently, there is another way of "passing" data into a stored procedure, and that's by storing it in a table. That's SQL programming right there.

And what do cursors have to do with arrays, besides the fact that arrays are usually "looped" through? In SQL, you can access all or some members of the array without looping! It's called set based processing. Tables don't live in memory necessarily, and that does make them different than arrays. But I might add that TV and TT don't either.

So maybe it's a paradigm problem.

Signature is NULL
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880

The author of the article wrote: "but using temporary table you are increasing disk activity since TV (Table Variable) is created in memory as opposed to Temp tables (created in TempDb)."

Now we know how rumors and myths get started.  I'd like to suggest that you do just a little more research...  Please refer to the Microsoft web site which states the following...

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4:
A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.
If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

The website is:
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

On top of that, if the table variables are large, the following comes into play (from the same website)...

Q3: What are some of the drawbacks of table variables?

A3:
These are some of the drawbacks as compared to temporary tables:

Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

And, yes, I agree that cursors should be replaced but NOT that way.   Show me the resource differences between a "firehose" cursor and the table variable/While loop method... I think you'll be surprised how little the difference is...



--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search