January 27, 2010 at 3:00 am
Hi Sql freaks,
This may sound wierd, but sill Questioning
Can a derived table be Indexed ?
For ex :
Select * from Employer Emp
Inner join
(Select employeeID from Employee) Emply
on Emp.employeeID=Emply.employeeID
I want indexing on Emply.employeeID
Thanks,
Gaurav Kothari
January 27, 2010 at 3:13 am
Check the following link
http://www.eggheadcafe.com/software/aspnet/30929844/index--doesn-it-still-e.aspx
January 27, 2010 at 3:20 am
Thanks Mr. Vyas
I have gone through the site.
Your prompt response saved a lot of time.
Thanks Again,
Gaurav Kothari
January 27, 2010 at 7:29 am
A derived table, despite the name, is not a real table, it's just an interim resultset that can be references as if it were a table. If you want an index on EmployeeID, then you need to apply it to the Employee table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2010 at 8:19 am
Based on the name of your column and the name of your table, this sounds like it should probably be the primary key. If it is, and has been designated as the primary key, then it is already indexed and the index will be used.
Tim Januario
February 13, 2010 at 5:59 pm
Tim Januario-145496 (2/13/2010)
Based on the name of your column and the name of your table, this sounds like it should probably be the primary key. If it is, and has been designated as the primary key, then it is already indexed and the index will be used.
The default behavior of SQL server is to apply a Clustered Index to the Primary Key. You may or may not already have an index on that field, depending on if you used the default behavior or not. Check your Employee table indexes. If no index exists on the employeeid field - create one there.
Edit: fixed a typo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 14, 2010 at 1:29 am
CirquedeSQLeil (2/13/2010)
The default behavior of SQL server is to apply a Clustered Index to the Primary Key. You may or may not already have on index on that field, depending on if you used the default behavior or not.
Maybe I'm misunderstanding you, however...
A primary key will always have an associated index. Clustered if the default behaviour was accepted, nonclustered if it was overridden.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2010 at 1:44 am
GilaMonster (2/14/2010)
CirquedeSQLeil (2/13/2010)
The default behavior of SQL server is to apply a Clustered Index to the Primary Key. You may or may not already have on index on that field, depending on if you used the default behavior or not.Maybe I'm misunderstanding you, however...
A primary key will always have an associated index. Clustered if the default behaviour was accepted, nonclustered if it was overridden.
I should have specified clustered index. Thanks for pointing that out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 15, 2010 at 12:41 am
Thanks to all who have responded.
This indeed helped me and hope that this discussion ahead will help a lot like me.
--Gaurav
February 15, 2010 at 6:28 am
Just to clarify, in the case of your query, where you are only using a single column as such in your subquery, the type of index - clustered or non-clustered - is totally irrelevant. They would both behave exactly the same in this case. The only time the clustered index would give you any additional benefits is if you were selecting columns that weren't covered by the index. When an index is a covering index, meaning that it contains all of the necessary data to satisfy your request, it behaves as a clustered index would. In your case it doesn't matter what type of index as long as employeeid is the first column in the index.
On the other hand, you may want to look at your employer table. I assume that you probably have an employerid column which is the primary key on that table as well as an employeeid which has a foreign key relationship with your employee table. A fkey relationship is always a good candidate for an index (which doesn't mean that it should always have an index, just that you should look at it a bit more). In that case, depending on how many nulls you have in that column, an index on the empoyer.employeeid column may be beneficial and since you are doing a select * from that table, the clustered / non-clustered difference could have an impact on performance because the database would first need to match up which records from employer have a corresponding employee record sorted by emplyer.employeeid and then do a lookup to the clustered index to return the rest of the fields. Honestly though, just based on the names of your tables, I think we can safely assume that as long as you are correctly applying foreign keys to your tables, or at least using primary keys in the ways that we have mentioned, you are probably good.
This really seems like a query that, regardless of index types, the engine is probably coming up with a very efficient plan. If you are having performance problems which are getting you to look at indexing, you may be looking at a red herring where indexing isn't the problem at all. If your employer and employee tables both have a million records in them (doubtful, but...), then you may just need to throttle your query with some ranging techniques such as SELECT TOP 100 * FROM employer INNER JOIN ( .. ) WHERE employer.employerid > @MINEMPLOYERID (or even better using a CTE which is very well documented in books online for this exact purpose). The fact of the matter is, that it can take a good amount of time to read a lot of data from a large table and indexing doesn't help that. The best thing you can do is limit the data you are looking at which causes HUGE performance benefits.
If your application is going to put something on the screen to be read by someone of the human variety rather than of the computer variety, you should only return the amount of data that the human-type person can reasonably look at at one time. This not only gives huge improvements in performance on large tables or complex queriies, but if you have indexed your tables correctly, performance remains stable even as the tables grow, meaning that doubling the table doesn't double the query execution times. Simply indexing the tables can't do this for you. This is one of the fundamental commandments of good database programming: Thou shalt only return the minimum amount of data necessary.
Tim Januario
February 15, 2010 at 9:45 am
gaurav.kothari (2/15/2010)
Thanks to all who have responded.This indeed helped me and hope that this discussion ahead will help a lot like me.
--Gaurav
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply