A tale from a Stalker

Wed Apr 27, 2011 by Peter Larsson in algorithms, miscellaneous

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

Legacy Comments


ms65g 2011-04-27	re: A tale from a Stalker You are 40 years old really?? But this post shows your age is not more than 8!

ms65g 2011-04-27	re: A tale from a Stalker Ms65g: no using as any native code Peso: folowing the rule "Work on SQL Server 2000 and only native code".

Tara Kizer 2011-04-27	re: A tale from a Stalker This is hilarious. Thanks for the laughs, Peter!

re: A tale from a Stalker 2011-04-29	re: A tale from a Stalker @Tara Ajab avatari dari taraye jende! Malome be dadan kheyli alaghe dari, kash mitonesti ye dastam be ma bedi. :)) @Peso Ame jende ridam ro kaseye saret, kheylii khari. :)) Malome ta solakh khonet atish gherefe ke in posto zadi :))

Ryan S. 2012-02-18	re: A tale from a Stalker It seems that this is one of the better ways in which a person could stalk you... lol However, if your intellectual debate should ever turn towards a more physical or even violent type, you should learn to defend yourself. I read this blog to help me with issues that arise on the website I help run. It just so happens that that website is one for the Indianapolis Jiu Jitsu Academy I train at. It is run by Marcello Monteiro, a BJJ black belt. Studying under him will allow you to better protect and defend yourself should you ever need to. I thought I'd share this with you.