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).
We have a table with consecutive keys (nbr) that is not exact sequence. We need bringing all values related with nearest key in the current key row.
See the DDL:
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
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
nbr INT,
val INT
)
INSERT #Temp
(
nbr,
val
)
SELECT nbr,
val
FROM dbo.Nums
ORDER BY nbr
SELECT pre.nbr AS pre_nbr,
pre.val AS pre_val,
t.nbr,
t.val,
nxt.nbr AS nxt_nbr,
nxt.val AS nxt_val
FROM #Temp AS pre
RIGHT JOIN #Temp AS t ON t.ID = pre.ID + 1
LEFT JOIN #Temp AS nxt ON nxt.ID = t.ID + 1
DROP TABLE #Temp
Notice there are no indexes on #Temp table yet.
And here is where the conversation derailed. First I got this response back
Now my solutions:
--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
What if you try with a Nums table with 100,000 rows?
His response to that started to get nasty.
I have to say Peso 2 is not acceptable.
As I said before the solution must be standard, ORDER BY is not part of standard SELECT.
Try this without ORDER BY:
Truncate Table Nums
INSERT INTO Nums (nbr, val) VALUES (1, 0),(9,4), (5, 7)
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
Standard Select = Set-based (no any cursor)
It’s free to know, just refer to Advanced SQL Programming by Celko or mail to him if you accept comments from him.
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
What do you mean?
The SELECT itself has a ”cursor under the hood”.
Now the stalker gets rude
But however I mean the solution must no containing any order by, top...
No problem, I do not like Peso 2, it’s very non-intelligent and elementary.
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
So what is your goal?
Have a query that performs well, or a query that is super-portable?
My Peso 2 outperforms any of your code with a factor of 100 when using more than 100,000 rows.
While I awaited his answer, I posted him this query
Ok, here is another one
-- Peso 3
SELECT MAX(CASE WHEN d = 1 THEN nbr ELSE NULL END) AS pre_nbr,
MAX(CASE WHEN d = 1 THEN val ELSE NULL END) AS pre_val,
MAX(CASE WHEN d = 0 THEN nbr ELSE NULL END) AS nbr,
MAX(CASE WHEN d = 0 THEN val ELSE NULL END) AS val,
MAX(CASE WHEN d = -1 THEN nbr ELSE NULL END) AS nxt_nbr,
MAX(CASE WHEN d = -1 THEN val ELSE NULL END) AS nxt_val
FROM (
SELECT nbr,
val,
ROW_NUMBER() OVER (ORDER BY nbr) AS SeqID
FROM dbo.Nums
) AS s
CROSS JOIN (
VALUES (-1),
(0),
(1)
) AS x(d)
GROUP BY SeqID + x.d
HAVING COUNT(*) > 1
And here is the stats
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.
It beats the hell out of your queries….
Now I finally got a response from my stalker and now I also clicked who he was. This is his reponse
Why you post my original method with a bit change under you name? I do not like it.
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:
Posted Tuesday, April 12, 2011 10:04 AM
Posted Tuesday, April 12, 2011 1:22 PM
Why post a solution where will not work in SQL Server 2000?
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).
Like I keep track of all topics in the whole world… J
So you think you are the only one coming up with this idea?
Besides, “M S solution” doesn’t work.
This is the result I get
pre_value current_value next_value
1 1 5
1 5 9
5 9 9
And I did nothing like you did here, where you posted a solution which you “thought” I should write
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159262
So why are you yourself using ranking function when this was not allowed per your original email, and no cte? You use CTE in your link above, which do not work in SQL Server 2000.
All this makes no sense to me, other than you are trying your best to once in a lifetime create a better performing query than me?
After a few hours I get this email back. I don't fully understand it, but it's probably a language barrier.
>>Like I keep track of all topics in the whole world… J
So you think you are the only one coming up with this idea?<<
You right, but do not think you are the first creator of this.
>>Besides, “M S Solution” doesn’t work.
This is the result I get <<
Why you get so unimportant mistake?
See this post to correct it:
Posted 4/12/2011 8:22:23 PM
>> So why are you yourself using ranking function when this was not allowed per your original email, and no cte? You use CTE in your link above, which do not work in SQL Server 2000. <<
Again, why you get some unimportant incompatibility?
You offer that solution for current goals not me
>> All this makes no sense to me, other than you are trying your best to once in a lifetime create a better performing query than me? <<
No, I only wanted to know who you will solve it.
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.
I used this technique all the way back to 2007, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911
Let's see if he returns... He did!
>> So what is your problem? <<
Nothing
Thanks for all replies; maybe we have some competitions in future, maybe.
Also I like you but you do not attend it. Your behavior with me is not friendly. Not any meeting…
Regards
//Peso
