question

Slick84 avatar image
Slick84 asked

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.
tabletempdbtemporary-tabletemporary-tables
1 comment
10 |1200

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

What cursor options do you have?
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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 comments
10 |1200

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

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 Like 1 ·
Yep, same indexes as the physical table. Not clearing down the buffers. Im not sure what that means?
0 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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 comments
10 |1200

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

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 Like 1 ·
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 Likes 0 ·
Thank you guys for the comments.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Less contention for resources on the physical DB vs. tempdb? It's hard to know anything without code & structures.
1 comment
10 |1200

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

Hmmmm.....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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