Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zion99
Posting Yak Master
141 Posts |
 Posted - 2009-04-01 : 08:50:22
|
Hi All,To improve performance, I have a stored proc where in I have specified the foll:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED I am creating a temp table in this proc and later on using this table to display records.Could you please suggest if my approach is correct?I am wondering whether using isolation level - 'Read Uncommitted' can cause any issue with the data in temp table.Whenever a stored proc is executed by the user, will a unique temp table created automatically ?Note: this stored proc is a part of SQL report which can be run by a user. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-01 : 08:55:36
|
| yes the temp table is unique per connection which is your sproc.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
 |
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2009-04-01 : 08:58:57
|
| could you please suggest if setting the transaction isolation level will make any difference?I am inserting data from CTE into the temp table and then displaying it. There are 7 CTEs and each returning roughly 400 records. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-01 : 09:09:40
|
| setting read uncommitted will enable you to get dirty reads of your data. i have no idea if that's a valid option for your db.common rule of thumb dictates that uncommitted is not ok for most business apps.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
zion99
Posting Yak Master
141 Posts |
Posted - 2009-04-01 : 09:17:21
|
| sorry forgot to add further details. this database is actually a data warehouse updated only once in a day. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-01 : 09:47:01
|
| if you're database is simply read only then read uncommitted won't really help your performance because shared locks are compatible with other shared locks.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
|
|
|
READ UNCOMMITTED & temp tables