A tale from a Stalker
Posted
by Peter Larsson
on SQL Team
See other posts from SQL Team
or by Peter Larsson
Published on Wed, 27 Apr 2011 14:16:11 GMT
Indexed on
2011/06/20
16:30 UTC
Read the original article
Hit count: 410
Today I thought I should write something about a stalker I've got. Don't get me wrong, I have way more fans than stalkers, but this stalker is particular persistent towards me.
It all started when I wrote about Relational Division with Sets late last year(http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx) and no matter what he tried, he didn't get a better performing query than me. But this I didn't click until later into this conversation. He must have saved himself for 9 months before posting to me again. Well...
Some days ago I get an email from someone I thought i didn't know. Here is his first email
Hi,
I want a proper solution for achievement the result. The solution must be standard query, means no using as any native code like TOP clause, also the query should run in SQL Server 2000 (no CTE use).
CREATE TABLE Nums(nbr INTEGER NOT NULL PRIMARY KEY, val INTEGER NOT NULL);
INSERT INTO Nums(nbr, val) VALUES (1, 0),(5, 7),(9, 4);
See the Result:
pre_nbr pre_val nbr val nxt_nbr nxt_val
----------- ----------- ----------- ----------- ----------- -----------
NULL NULL 1 0 5 7
1 0 5 7 9 4
5 7 9 4 NULL NULL
The goal is suggesting most elegant solution. I would like see your best solution first, after that I will send my best (if not same with yours)
Notice there is no name, no please or nothing polite asking for my help.
So, on the top of my head I sent him two solutions, following the rule "Work on SQL Server 2000 and only standard non-native code".
-- Peso 1
SELECT pre_nbr,
(
SELECT x.val
FROM dbo.Nums AS x
WHERE x.nbr = d.pre_nbr
) AS pre_val,
d.nbr,
d.val,
d.nxt_nbr,
(
SELECT x.val
FROM dbo.Nums AS x
WHERE x.nbr = d.nxt_nbr
) AS nxt_val
FROM (
SELECT (
SELECT MAX(x.nbr) AS nbr
FROM dbo.Nums AS x
WHERE x.nbr < n.nbr
) AS pre_nbr,
n.nbr,
n.val,
(
SELECT MIN(x.nbr) AS nbr
FROM dbo.Nums AS x
WHERE x.nbr > n.nbr
) AS nxt_nbr
FROM dbo.Nums AS n
) AS d
-- Peso 2
CREATE TABLE #Temp
(
Notice there are no indexes on #Temp table yet.
And here is where the conversation derailed. First I got this response back
--My 1st Slt
SELECT T2.*, T1.*, T3.*
FROM Nums AS T1
LEFT JOIN Nums AS T2
ON T2.nbr = (SELECT MAX(nbr)
FROM Nums
WHERE nbr < T1.nbr)
LEFT JOIN Nums AS T3
ON T3.nbr = (SELECT MIN(nbr)
FROM Nums
WHERE nbr > T1.nbr);
--My 2nd Slt
SELECT MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END) AS pre_nbr,
(SELECT val FROM Nums WHERE nbr = MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END)) AS pre_val,
N1.nbr AS cur_nbr, N1.val AS cur_val,
MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END) AS nxt_nbr,
(SELECT val FROM Nums WHERE nbr = MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END)) AS nxt_val
FROM Nums AS N1,
Nums AS N2
GROUP BY N1.nbr, N1.val;
/*
My 1st Slt
Table 'Nums'. Scan count 7, logical reads 14
My 2nd Slt
Table 'Nums'. Scan count 4, logical reads 23
Peso 1
Table 'Nums'. Scan count 9, logical reads 28
Peso 2
Table '#Temp'. Scan count 0, logical reads 7
Table 'Nums'. Scan count 1, logical reads 2
Table '#Temp'. Scan count 3, logical reads 16
*/
To this, I emailed him back asking for a scalability test
His response to that started to get nasty.
So now we have new rules. No ORDER BY because it's not standard SQL! Of course I asked him
Why do you have that idea? ORDER BY is not standard?
To this, his replies went stranger and stranger
What the stalker probably doesn't know, is that I and Mr Celko occasionally are involved in some conversation and thus we exchange emails. I don't know if this reference to Mr Celko was made to intimidate me either. So I answered him, still polite, this
The SELECT itself has a ”cursor under the hood”.
Now the stalker gets rude
Yes, Peso 2 is elementary but most performing queries are... And now is the time where I started to feel the stalker really wanted to achieve something else, so I wrote to him
Have a query that performs well, or a query that is super-portable?
While I awaited his answer, I posted him this query
-- Peso 3
Table 'Nums'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now I finally got a response from my stalker and now I also clicked who he was. This is his reponse
See: http://www.sqlservercentral.com/Forums/Topic468501-362-14.aspx
;WITH C AS
(
SELECT seq_nbr, k,
DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct
FROM [Sample]
CROSS JOIN
(VALUES (-1), (0), (1)
) AS D(k)
)
SELECT MIN(seq_nbr) AS pre_value,
MAX(CASE WHEN k = 0 THEN seq_nbr END) AS current_value,
MAX(seq_nbr) AS next_value
FROM C
GROUP BY grp_fct
HAVING min(seq_nbr) < max(seq_nbr);
These posts:
Wait a minute! His own solution is using both a CTE and a ranking function so his query will not work on SQL Server 2000! Bummer... The reference to "Me not like" are my exact words in a previous topic on SQLTeam.com and when I remembered the phrasing, I also knew who he was. See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159262 where he writes a query and posts it under my name, as if I wrote it.
So I answered him this (less polite).
After a few hours I get this email back. I don't fully understand it, but it's probably a language barrier.
So you think you are the only one coming up with this idea?<<
This is the result I get <<
See this post to correct it:
You offer that solution for current goals not me
Now I know you do not have a special solution. No problem.
No problem for me either. So I just answered him
I am not the first, and you are not the first to come up with this idea. So what is your problem? I am pretty sure other people have come up with the same idea before us.
Let's see if he returns... He did!
>> So what is your problem? <<
Regards
//Peso
© SQL Team or respective owner
