December 18, 2012 at 8:42 am
ganeshmuthuvelu (12/18/2012)
I am the OP here. It appears that the thread became a discsussion for whether indexes are needed on temp tables or not. I can say for sure that the answer is "yes, it is needed", particularly when large volume of data is on the temp tables and joins are done.Now, if you all can be kind enough to read my original question and help me find a way to make SQL Server find out such missing indexes that would help performance, it will be helpful. I have summaized it again below.
1) Let's say "#t1" is created in session id 100 and #t1 has no indexes on any columns and has 100,000 rows in it.
2) The end user has his "own, custom" stored procedure that works on the data in the #t1 and joins #t1 with another perm. table say "MainData".
3) The temp. table "#t1" and the stored procedure are created by the end user.
4) Whereas the perm. table "MainData" (part of the application) is properly indexed, the "OrderID" column in "#t1" is not indexed which is used in the join with "MainData" and this causes slow performance. If the #t1 is indexed on "OrderID", the performance is great.
5) Now, at the end of the SP execution or before, I would like SQL Server to say, index on "OrderID" column in "#t1" is "recommended".
6) Again, please do note that the temp. table "#t1" is dynamic in nature, and the application will have no knowledge about this and that's the reason the index on "OrderId" can be "pre" created. Therefore, I want the app to suggest to the user that index on "OrderID" column in "#t1" is recommended.
I can make the application run some queries at the end (in the same session id 100 where the #t1 is created) to make the index suggestions to the user.
Any ideas?
(emphasis added)
Yes. Don't try to do this. At least partially because it's actually impossible.
Automating index creation, especially on temp tables, is going to be overly complex, and is unlikely to accomplish what you need.
First, any index creation on temp tables has to be in the same scope as the creation of the temp table. That means you'd be writing dynamic code to create your dynamic indexes on dynamic temp tables. That, by itself, is going to be a nightmare to write, worse than a nightmare to maintain, and worse than that to document.
Stored procedures can't take user-interaction on-the-fly. They're not application code. You can't pause a proc, tell the user "index blah blah blah is recommended", have them create the index, and then continue on with the proc. They simply don't work that way. Thus, step 5 is actually impossible to do with temp tables.
You could do something like this with global temp tables. But those have horrible problems with name-collision, as well as other issues, and I don't recommend them.
It sounds to me like what you really need is user-bound "perm tables". Just plain old ordinary tables, with a scope that limits them so that only one user has access to them at a time. You could do that by placing them in a user-specific database or user-specific schema within one database. If you want to avoid putting them in normal databases, with the implications for backup, DR, etc., that this carries with it, then create them in tempdb, but create them as persisted tables instead of temp tables.
On the other hand, it might be advantageous to have them in the normal database and DR scope, so that work-interruptions by things like power outages, server crashes, etc., don't necessarily force a user to start over again from scratch. If the tables are going to be as large as you are saying, then it follows that they probably take a fair amount of work to create and use, and having them inside DR scope for point-in-time recovery might be a good thing.
Using persisted tables bypasses your problem with not being able to get index suggestions for temp tables, since you already know how to get index suggestions for regular tables.
It also makes step 5, providing the user with the index suggestions and asking for analysis on them, very easy to do, instead of impossible.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply