Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The ARRAY In SQL Server 2000 Expand / Collapse
Author
Message
Posted Friday, January 20, 2006 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 14, 2011 5:55 AM
Points: 16, 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.
Post #252309
Posted Friday, January 20, 2006 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 8, 2014 9:16 AM
Points: 13, Visits: 62

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

 




Post #252314
Posted Friday, January 20, 2006 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 31, 2013 11:41 PM
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
Post #252324
Posted Friday, January 20, 2006 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 30, 2009 5:36 PM
Points: 12, Visits: 43

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!

Post #252333
Posted Friday, January 20, 2006 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 8:48 AM
Points: 35, Visits: 189
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.
Post #252378
Posted Friday, January 20, 2006 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2008 9:55 PM
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(....)"

Post #252393
Posted Friday, January 20, 2006 11:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, 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




Post #252481
Posted Friday, January 20, 2006 11:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, 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




Post #252482
Posted Friday, January 20, 2006 12:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #252493
Posted Saturday, January 21, 2006 8:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #252604
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse