March 31, 2016 at 8:21 am
i am using this query to find out duplicates in my table. Please tell me what kind of algorithm is used here.
SELECT * FROM Students s join (select student_fullname, student_dob from Students GROUP BY student_fullname, student_dob HAVING count(*) > 1) a on s.student_fullname = a.student_fullname and s.student_dob=a.student_dob order by s.student_fullname"
March 31, 2016 at 8:27 am
What do you mean by 'what kind of algorithm'? What are you trying to determine?
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
March 31, 2016 at 8:34 am
i am not trying to determine anything here. just wanted to know the algorithm used here. its like i have a give presentation on my application. so doing some homework here. since u asked the question, what must i determine here?
March 31, 2016 at 8:44 am
No I'm not asking you to determine something, I'm asking you what you're trying to do and what, specifically and precisely, you're after, as your question, as it stands, cannot be answered.
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
March 31, 2016 at 8:47 am
i think i am looking for this answer
http://sqlserverplanet.com/optimization/sql-server-join-algorithms
March 31, 2016 at 8:55 am
No way, from the query, to determine the join algorithm used. SQL is a declarative language. You tell SQL what the results should be, it figures out how to execute the query to get the results.
Look at the execution plan for the join type used.
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
March 31, 2016 at 9:35 am
Keep in mind that the join algorithm can change any time the circumstances change. You don't have any guarantee that the same query will always use the same algorithm.
March 31, 2016 at 9:39 am
what should my answer be if the question is " which algorithm are u using"? (impressive answer)
March 31, 2016 at 9:49 am
hlsc1983 (3/31/2016)
what should my answer be if the question is " which algorithm are u using"? (impressive answer)
I was going to give you the "correct" answer, but I'll lead you to the article on how queries are processed so you can figure it on your own.
https://technet.microsoft.com/en-us/library/ms190623(v=sql.105).aspx
March 31, 2016 at 12:00 pm
hlsc1983 (3/31/2016)
what should my answer be if the question is " which algorithm are u using"? (impressive answer)
"I have no idea. I asked online for someone to give me an answer, but they just told me to go and do some research instead of giving an answer."
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
March 31, 2016 at 2:31 pm
hlsc1983 (3/31/2016)
what should my answer be if the question is " which algorithm are u using"? (impressive answer)
Why not explain to us what your query is doing? Here, by the way, is your query reformatted to make it easier to read:
SELECT
*
FROM
Students s
join (select
student_fullname,
student_dob
from
Students
GROUP BY
student_fullname,
student_dob
HAVING
count(*) > 1) a
on s.student_fullname = a.student_fullname and
s.student_dob = a.student_dob
order by
s.student_fullname;
March 31, 2016 at 2:52 pm
You are performing a equijoin between Students and a filtered aggregation of Students that contains only repeated combination of name and dob.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 1, 2016 at 4:39 am
hlsc1983 (3/31/2016)
what should my answer be if the question is " which algorithm are u using"? (impressive answer)
I'd be impressed if you replied along the lines of SQL being a declarative language not an imperative one and as such the query is not using an algorithm.
April 1, 2016 at 10:18 am
Most likely a LOOP join will be used. That is not guaranteed, of course, but it is likely.
If you want to be sure what join algorithm will be used, code it in the query yourself(!):
SELECT *
FROM Students s
INNER LOOP JOIN (
select student_fullname, student_dob from Students GROUP BY student_fullname, student_dob HAVING count(*) > 1) a
ON s.student_fullname = a.student_fullname and s.student_dob=a.student_dob
order by s.student_fullname
That code will use a loop join algorithm, period ... unless I'm just pulling an elaborate April Fool's day answer!?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply