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 12»»

does choosing indexed table col for where clause helps and does order of table matters? Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2014 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 47, Visits: 143
hi,

In following querys, i tried to check the plan
i noticed both where same as far as the index usage is concerned

pls suggest what should be followed in general.



1)

update t1
set a, b, c, d .f
from t1 a
join t2 b on a.poleid=b.poleid
join t3 c on b.t3id = c.t31d
where b.poleid =1

2)

update t1
set a, b, c, d .f
from t1 a
join t2 b on a.poleid=b.poleid
join t3 c on b.t3id = c.t31d
where a.poleid =1

3)

update t1
set a, b, c, d .f
from t2 a
join t1 b on a.poleid=b.poleid
join t3 c on a.t3id = c.t31d
where a.poleid =1


--t2 keeps poles and poleid is pk
--t1 keeps poles report and does not have any index right now, we are planning for (includes indexes )

it is 2 times bigger than t1

q1) which query i should select, i checked the plan both uses indexs.
can using the b.poleid = 1 would be better because i have index on t2. or second query will alos work in same
way, in all advers condition.

q2) there is one guide line that is , to keep the small table first
, on the other hand msdn says that , it does it automatically.
if in this case there were more table in from clause
would it be better to keep smaller table first.

Q3) the third query uses t1 as second table in the join , it works fine but does not look
logical , is it correct syntactically and conventionally.


yours sincerely
Post #1582400
Posted Tuesday, June 17, 2014 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
What is the question here? Is it the title of your post?

Remember that by their very definition tables have no order. Indexing is one of the ways we can make retrieving data faster.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1582405
Posted Tuesday, June 17, 2014 12:18 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 @ 4:58 PM
Points: 42,466, Visits: 35,532
These may help

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
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 #1582511
Posted Wednesday, June 18, 2014 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 47, Visits: 143
questions are inside Q1)Q2)Q3)

yours sincerley
Post #1583038
Posted Wednesday, June 18, 2014 10:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
rajemessage 14195 (6/18/2014)
questions are inside Q1)Q2)Q3)

yours sincerley


Without some details there is no chance anybody can offer much here. We can't see your screen, we have no idea what your tables and index definitions are like. All we have is some pseudocode. I assume when you say "bigger" and "smaller" you are referring to the number of rows in the tables?

I still don't see any actual question in here. You have some interesting theoretical discussions but no actual question.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1583132
Posted Monday, June 23, 2014 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 47, Visits: 143
ya bigger and smaller is number of rows.

yours sincerely
Post #1585135
Posted Monday, June 23, 2014 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
rajemessage 14195 (6/23/2014)
ya begger and smaller is number of rows.

yours sincerely


But what is the question? In your original queries the only difference I can see is the order of the columns in join predicate. They are the same thing. The optimizer does not care the order you specify the columns.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1585139
Posted Tuesday, June 24, 2014 1:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 47, Visits: 143
1)

update t1
set a, b, c, d .f
from t1 a
join t2 b on a.poleid=b.poleid
join t3 c on b.t3id = c.t31d
where b.poleid =1

2)

update t1
set a, b, c, d .f
from t1 a
join t2 b on a.poleid=b.poleid
join t3 c on b.t3id = c.t31d
where a.poleid =1

3)

update t1
set a, b, c, d .f
from t2 a
join t1 b on a.poleid=b.poleid
join t3 c on a.t3id = c.t31d
where a.poleid =1



q1) which query i should select, i checked the plan both uses indexs.
can using the b.poleid = 1 would be better because i have index on t2. or second query will alos work in same
way, in all advers condition.

q2) there is one guide line that is , to keep the small table first
, on the other hand msdn says that , it does it automatically.
if in this case there were more table in from clause
would it be better to keep smaller table first.

Q3) the third query uses t1 as second table in the join , it works fine but does not look
logical , is it correct syntactically and conventionally.

yours sincerely
Post #1585405
Posted Tuesday, June 24, 2014 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
Why are you re-posting your original post? As I said in my last post it makes absolutely zero difference which column or table is on which side of an equality check. You even noted that the execution plans are identical, that is because there is no logical difference in the first two.

If you really want some help you should read the articles Gail suggested. Then come back and post some details instead of a vague query. Remember that indexing and performance have a LOT to do with how the table is defined. It is more than just a couple of column names thrown into a query. To help we would need to see the table structures and the indexes at the very least.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1585503
Posted Tuesday, June 24, 2014 10:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Visually this looks bad, see the pet pevee posts on formatting your code.
update t1
set a, b, c, d .f
from t1 a
join t2 b on a.poleid=b.poleid
join t3 c on b.t3id = c.t31d
where b.poleid =1

Doesn't this make it easier to read?
UPDATE t1 
SET a, b, c, d .f -- not sure what you are doing here, you have table references but not setting anything
FROM t1 a
INNER JOIN t2 b ON
a.poleid=b.poleid
INNER JOIN t3 c ON
b.t3id = c.t31d
WHERE b.poleid = 1
;

Visually I would always have the table I am updating as the first table(t1). Your other joins shouldn't matter, someone will correct me if I'm wrong. But I think your WHERE statement will limit the data the same no matter what order you put the other two in.

I hope this is what you are trying to ask about, it still isn't clear.
Post #1585582
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse