Table Variables As An Alternative to Cursors

Question

Table Variables As An Alternative to Cursors

Viewing 5 posts - 1 through 6 (of 6 total)

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

guarddata SSCertifiable Points: 6583 More actions · Answer 1

Good test Jeremy! My experience has been similar. The cursors of version 6.5 were very poor performers...so bad that I didn't even attempt to use them in version 7.0. Accepting a challenge from another program, we ran similar tests in SQL2K and found that cursors are very acceptable when the processing must be done a row at a time.

For mass updates, we have also used SET ROWCOUNT ###. When the updates are based on indexed values, this provides a cursor-like approach but still updates sets of data at once and keeps the transactions small. We find this especially useful in purging from our huge tables.

SET ROWCOUNT 1000

select count(*) from <TABLE>

WHILE @@ROWCOUNT > 0

BEGIN

BEGIN TRAN

DELETE FROM TABLE WHERE <Condition>

COMMIT TRAN

END

Guarddata-

ikramkahn SSC Journeyman Points: 87 More actions · Answer 2

I am thoroughly impressed Jeremy. I am a web developer with limited SQL programming experience, so do not know all the shortcuts yet. I do have a question for you , which might lay some arguments to rest. We noticed performance degradation when table variables are used and the tables are large. We also increased the memory on the server and that increased performance. Have you done any testing with your server memory maxed out?

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 3

I haven't done any memory related stress testing.

I have done lots of concurrent runs of this type - each month we get around 20 update files which we run many of them concurrently. We find that when we run 3 or more concurrently we do see a reduction in throughput but I guess this is an I/O limitation rather than memory - we are running RAID 5 on a 4 processor Dell server and max 1.5 Gb RAM for SQL Server - the server is used for other processing as well.

Jeremy

jscontreras SSC-Addicted Points: 430 More actions · Answer 4

Hi Jeremy,

I'm currently working on SQL Server 7 using a cursor sp that is killing my DTS. I'm looking to rewrite this sp maybe using rowcounts, but before I do that do cursors kill performance on SQL Server 7? not 100% sure on this.

Thanks

JMC

tulcanla SSCommitted Points: 1541 More actions · Answer 5

I would have a different type of experience.

I have 4 possibilities

1 cursor

2 temp table created with "into" statement with no index

3 temp table created before with indexes

4 datatype table

I am dealing with a datawarehouse of 75 G

After experiments, for me the best was the datatype table, after that the already created temp table with indexes and at the and the last 2.

LMT