June 17, 2014 at 9:17 am
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
June 17, 2014 at 9:24 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2014 at 12:18 pm
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
June 18, 2014 at 9:15 am
questions are inside Q1)Q2)Q3)
yours sincerley
June 18, 2014 at 10:38 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 8:03 am
ya bigger and smaller is number of rows.
yours sincerely
June 23, 2014 at 8:07 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2014 at 1:26 am
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
June 24, 2014 at 7:29 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2014 at 10:10 am
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
Don't fear failure, fear regret.
June 24, 2014 at 11:32 pm
As I said in my last post it makes absolutely zero difference which column or table is on which side of an equality check.
In q1) I wanted to ask about index and where clause which has constant on right side, not about the side of an equality.
Kindly put q1) or ans 1) to indecate which question u are answering.
yours sincerely
June 25, 2014 at 7:23 am
rajemessage 14195 (6/24/2014)
As I said in my last post it makes absolutely zero difference which column or table is on which side of an equality check.
In q1) I wanted to ask about index and where clause which has constant on right side, not about the side of an equality.
Kindly put q1) or ans 1) to indecate which question u are answering.
yours sincerely
Your question does not make sense to me. A constant or a column, it makes ZERO difference. The optimizer does not care if you have col1 = 1 or 1 = col1. It is the exact same thing.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 8:16 am
Fortunately the construction of the execution plan (for example which table is used as the outer or inner input on a nested loop or hash join) isn't dependent on how the developer specified the join order within the SQL statement. The query optimizer uses a cost based algorithm and takes indexes and table statistics into consideration to make that decision for us.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 25, 2014 at 8:23 am
Eric M Russell (6/25/2014)
Fortunately the construction of the execution plan (for example which table is used as the outer or inner input on a nested loop or hash join) isn't dependent on how the developer specified the join order within the SQL statement. The query optimizer uses a cost based algorithm and takes indexes and table statistics into consideration to make that decision for us.
It may not matter the order the joins are in for the optimizer. But from a 'Best Practice' standpoint, I always try, when possible, to have my inner joins listed first. I think part of the initial question was what way should it be done.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 25, 2014 at 8:37 am
below86 (6/25/2014)
But from a 'Best Practice' standpoint, I always try, when possible, to have my inner joins listed first.
How or why is this "best practice"? I create joins in the order that the data is logically associated regardless of the type of join.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply