Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


does choosing indexed table col for where clause helps and does order of table matters?


does choosing indexed table col for where clause helps and does order of table matters?

Author
Message
rajemessage 14195
rajemessage 14195
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 329
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
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)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47263 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


rajemessage 14195
rajemessage 14195
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 329
questions are inside Q1)Q2)Q3)

yours sincerley
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
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)
rajemessage 14195
rajemessage 14195
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 329
ya bigger and smaller is number of rows.

yours sincerely
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
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)
rajemessage 14195
rajemessage 14195
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 329
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
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)
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2121
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.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search