Cursor runs faster on Physical tables versus Temp Tables?

Question

question

Slick84 asked Jul 25, '11

Cursor runs faster on Physical tables versus Temp Tables?

Why would a cursor run faster on physical table than on a temporary table? My tempdb is no slouch either, sits on a 4 disk RAID 10.

table tempdb temporary-table temporary-tables

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Martin 1 commented · Jul 26, 2011 at 03:53 AM

What cursor options do you have?

0 ·

Answer 1 · 2011-07-25T12:06:41Z

ThomasRushton answered Jul 25, '11

Does your temporary table have the same indexes as your "physical" table? Are you really making fair comparisons - are you clearing down the buffers between running on "physical" vs running on temp table?

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 commented · Jul 26, 2011 at 06:02 AM

Your physical tables' data may already reside in RAM. By clearing the buffers you make the data have to be accessed from disk - a much slower medium.

1 ·

Slick84 commented · Jul 25, 2011 at 01:57 PM

Yep, same indexes as the physical table. Not clearing down the buffers. Im not sure what that means?

0 ·

Fatherjack ♦♦ commented · Jul 25, 2011 at 11:37 PM

I think @thomasrushton is referring to `DBCC DROPCLEANBUFFERS` which is used, after the `CHECKPOINT` command, to empty the cache and give a test environment a known starting point.

0 ·

Slick84 commented · Jul 26, 2011 at 06:22 AM

Thanks guys for the comments. Though clearing the buffers on a production environment seems a bit risky and a big no-no, it seems I wouldnt be doing a fair comparision between two. Anyhow, I've gotten some good information here, thank you all again.

0 ·

Answer 2 · 2011-07-25T12:12:20Z

Pavel Pawlowski answered Jul 25, '11

As @ThomasRushton mentioned are the tables really comparable? What about the file group configuration on which the physical table resides. You mentioned 4 discs RAID 10 for tempdb, but not mentioned configuration of the RAID level and discs for file group on which the physical table is placed.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski commented · Jul 26, 2011 at 04:16 AM

If your data file group is sitting on 24 drives RAID 10, then depending on the load on the drives, it will be much faster for reading than 4 drives RAID 10 you have for tempdb. The load also applies to tempdb.. if the server is under load the there is hi usage of tempdb, the reads from tempdb will be slower.

1 ·

Slick84 commented · Jul 25, 2011 at 02:03 PM

Ive got 6 filegroups with 1 file in each. 1 primary filegroup, 2 for CI's and 3 for NCI's sitting on a 24 RAID 10. TempDB sits on its own volume as mentioned above.

0 ·

Slick84 commented · Jul 26, 2011 at 06:21 AM

Thank you guys for the comments.

0 ·

Answer 3 · 2011-07-25T12:29:48Z

Grant Fritchey answered Jul 25, '11

Less contention for resources on the physical DB vs. tempdb? It's hard to know anything without code & structures.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Jul 25, 2011 at 02:04 PM

Hmmmm.....

0 ·

question