January 29, 2011 at 2:38 am
consider a table
create table company(com_id int primary key,cname varchar(150),clocation varchar(50),cphone varchar(50))
here com_id will be clustered index since primary key..
if i use
select cname,clocation from company where com_id=24
query... should i create non-clustered index for the columns in select ie cname,clocation.... in which case should i use non-clustered index?
January 29, 2011 at 5:30 am
No need for additional indexes here, the where clause is on the clustered index key. You would consider creating indexes when your queries filter or join on other columns
This may be worth reading http://www.sqlservercentral.com/articles/Indexing/68636/
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
January 29, 2011 at 7:15 am
Thanks a lot Gail...
January 29, 2011 at 7:26 am
With this one i attached two image files which depicts two execution plan of 2 queries which gives same results. which execution plan is better one.. how to calculate the perfect execution in terms of cost?. any suggestion pls?
January 29, 2011 at 7:36 am
Cost is an estimate, it can be wrong, don't count on it. Usually the best query is the fastest one.
Post the actual execution plans, not pictures of them, there's lots of missing info. Save as .sqlplan files and upload.
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
January 29, 2011 at 1:25 pm
i couldnt upload the sql plan file due to server problem...
i m posting my queries.
Query 1:select a.ACCESS_ID as accessId,a.ROLE_ID as dmsRespId,a.USER_ID as dmsUserId,
(select NAME as wfRespName From HR_JOBS yy where yy.JOB_ID=a.ROLE_ID) as wfRespName,
(select FIRST_NAME as wfUserName From HR_PERSONS xx where xx.person_id=a.USER_ID) as wfUserName
from SY_DMS_AUTHORIZATION a where a.ACCESS_ID=6
Query 2: select a.ACCESS_ID as accessId,yy.JOB_ID as dmsRespId,a.USER_ID as dmsUserId,
yy.NAME as wfRespName ,
xx.FIRST_NAME as wfUserName
from SY_DMS_AUTHORIZATION a left outer join HR_JOBS yy on yy.JOB_ID=a.ROLE_ID and a.ACCESS_ID=6 left outer join HR_PERSONS xx on xx.person_id=a.user_id
Query 3: select a.ACCESS_ID as accessId,yy.JOB_ID as dmsRespId,a.USER_ID as dmsUserId,
yy.NAME as wfRespName ,
xx.FIRST_NAME as wfUserName
from SY_DMS_AUTHORIZATION a left outer join HR_JOBS yy on yy.JOB_ID=a.ROLE_ID and a.ACCESS_ID=6 left outer join HR_PERSONS xx on xx.person_id=a.user_id
all of them resulting same records....
which one is fastest?... How can i find out the query which results fastly?
January 29, 2011 at 1:32 pm
Can't see a difference between queries 2 and 3. I can't tell which will run faster, you'll have to do that. Turn Statistics Time on and run them.
p.s. Why the meaningless aliases? They don't help readability, rather the opposite. If you're going to alias the tables, use something indicative of the table, eg from SY_DMS_AUTHORIZATION AS sda left outer join HR_JOBS AS hj
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
January 30, 2011 at 12:43 am
Oh.... thanks gail.. thanks a lot...
January 30, 2011 at 12:53 am
In this specific case, there is a unique time to use a nonclustered index that repeats the left side of the clustered index... but you're not at that point.
That case would be when your table is very, very wide (say, near the 5-6k/record mark) and the majority of your queries accessing the table needs only a small handful of small fields (90% of your calls). As an example of a small handful: an ID column and three or four foreign key columns, also of numeric values, and perhaps a date.
Why? These field could be included in a non-clustered index for higher speed access then trying to deal with the full memory set. It's also redundant and puts a load on your insert/update/delete speeds. It's used primarily for speeding up reads on a light transactional system that's either poorly designed or deals with incredibly wide text data that's rarely used (which can also fall under the header of poorly designed, depending on who you ask).
However, you're not there with your example, so don't do that. I just wanted to throw this in there in case you see other designs that may have done it, so you're not suprised.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply