Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Expand / Collapse
Author
Message
Posted Saturday, January 29, 2011 2:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
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?
Post #1055738
Posted Saturday, January 29, 2011 5:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 41,559, Visits: 34,481
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1055746
Posted Saturday, January 29, 2011 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
Thanks a lot Gail...
Post #1055762
Posted Saturday, January 29, 2011 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
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?
Post #1055763
Posted Saturday, January 29, 2011 7:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 41,559, Visits: 34,481
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1055765
Posted Saturday, January 29, 2011 1:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
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?
Post #1055812
Posted Saturday, January 29, 2011 1:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 41,559, Visits: 34,481
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1055818
Posted Sunday, January 30, 2011 12:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
Oh.... thanks gail.. thanks a lot...
Post #1055868
Posted Sunday, January 30, 2011 12:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:41 PM
Points: 5,986, Visits: 6,932
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1055871
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse