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 |
|
manishrawat
Starting Member
9 Posts |
 Posted - 2002-01-07 : 19:37:39
|
| We have a SP which called very often. It uses Table variable and has a join with another physical table in Update statement. This update is very very time consuming and takes 90% execituon time of SP as shown in trace. SQL in SP is ------------ update Accountant set [FirstName]=t.[FirstName],[LastName]=t.[LastName],[Password]=t.[Password], from Accountant a inner join @Accounts t on a.UserName = t.UserName The Table varible has approx 25000 rows at run time. In execution plan it shows Table scan (for Table variable ) and table Spool. What can be done to improve the performance of it..?? Can we define index on a field of Table variable?? Please help.. Manish |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-07 : 20:11:48
|
| Forgive me for not answering your question, but WHY are you using a table variable with 25,000 rows in a stored procedure "which is called very often"? How is this table variable being populated? If it's called that often, shouldn't this be a real table? Then you definitely could define indexes.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
 |
|
|
Garth
SQLTeam Author
119 Posts |
Posted - 2002-01-07 : 22:51:38
|
| Did you create a PK on the temp table...DECLARE @Accounts table( UserName varhcar(10) PRIMARY KEY, [Password]...)Garthwww.SQLBook.com |
|
|
|
Garth
SQLTeam Author
119 Posts |
Posted - 2002-01-07 : 23:09:14
|
| On second thought, that probably won't help becauseyou still need the actual data.Try a covered index...DECLARE @Accounts table( UserName varchar(10), [Password] varchar(10), FirstName varchar(10), LastName varchar(10), UNIQUE (UserName,[Password],FirstName,LastName)) Garthwww.SQLBook.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-08 : 04:54:25
|
| Think I would agree with AjarnMark here in that you should look at the design.You are updating 25000 rows of Accountant very often which sounds like a problem in itself. How do you get on with contention on that table?==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Table variable Join expensive??