Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dev2dev
Starting Member
48 Posts |
 Posted - 2008-01-03 : 06:59:16
|
| let me explain the scenario firstsay table1 has columns col1, col2 and col3where col1 and col2 together is PKand col1 is same for for entire table and col2 is unique (well some may get doubt why to use col+col2 for pk when col2 alone is unique, to clarify this doubt, we have two dbs where in one db col1 does not repeat (in other words there are multiple dbs created based on this col1) and in other db it repeats (all dbs merged together)) sample datacol1--col2==========a--1a--2a--3a--4a--5a--6a--7... so onnow my question, does both of the below queries takes same time ex1:select * from tabel1 select * from tabel1 where col1 = 'a'in other words, does WHERE clause has any effect in performance when the column used in the query will always the same value with what it is compared?does it has any effect when i join also?ex2:select a.* from tabel1inner join tabel2 a on a.col1 = b.col1 and a.col2 = b.col2select a.* from tabel1inner join tabel2 a on a.col2 = b.col2 -- note: col1 is not used in joinand please explain how it is same/different |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-03 : 07:13:02
|
| select a.* from tabel1inner join tabel2 a on a.col2 = b.col2This will NOT be efficient as there is no suitable INDEX that can assist the join. in effect you will be nearly getting a cross join. To see the effect of your queries/indices, have a look at the estimated/actual execution plans (Ctrl-K in Query Analyser) |
 |
|
|
dev2dev
Starting Member
48 Posts |
Posted - 2008-01-03 : 09:38:16
|
| thanks adrew, i just checked in Query analyser using ctl+k, i dont know how to read and understand these pictures/terminologythere were two difference in the 1st and 2ndin the 1st example1st one's physical operation was "Clustered Index Scan" (desc: scanning entire or a range of index)2nd one's physical operation was "Clustered Index Seek" (desc: scanning a particular range of rows of index)but rest of the things are same (cpu cost, i/o cost etc)in 2nd example1st one used "Merge Join" and the other used "Hash Join"again for testing purpose, i create two tables without index and test 2nd examplenow the 1st query is bit faster and execution plan is smallbut 2nd query shows lot new things like "Parallelism/Gather Stream" "Parallelism/Repartion Stream" "Bitmap/Bitmap Create"got to understand all these termsany links you can provide? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-03 : 11:14:25
|
| seek is better than scan.in phone book terms, seek uses the index at the back to quick jump (close) to the proper person-address-number page. scan means you read the entire book to find 1 phone number.not sure of the practical differences between "merge join" and "hash join"...(google them???)note re testing...make sure you have realistic volumes....small tables may be easier/simpler/faster to scan in bluk than for SQL to bother to try to use the indices.re the execution plan terms....searching here for advice from members tkizer, kristen, nr, peso and some of the other top posters would be a start. they have posted links to various books over time which they recommend....my own reading style is 99.9% fiction, so I can't recommend anything in particular. most on my know-how has been "picked up" from hanging around here. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
dev2dev
Starting Member
48 Posts |
Posted - 2008-01-04 : 03:45:42
|
Thanks for the discussionquote:
Originally posted by AndrewMurphy
not sure of the practical differences between "merge join" and "hash join"...(google them???)
Yes, i queried table with over 130K rows |
|
|
|
|
|
|
|
|
general optimization doubt