Improve TSQL Stored Procedure for pairwise execution of procedure

Question

UPDATE: I rewrote the first part as a setbased approach:

declare @AllRunnersString nvarchar(200) ='2342;1228;1075;266;423;2849;1690;488;162;1153;1666'
DECLARE @pairs table
(p1 int,p2 int) 
insert into @pairs(p1, p2)
select sp1.value as p1, sp2.value as p2
from dbo.split(';', @AllRunnersString) sp1
inner join dbo.split(';', @AllRunnersString) sp2
on sp1.value <> sp2.value

This generates the pairs.

Head2Head takes the ids and returns a table with all events with both ids. @rtn table( eventid int, p1 int, p2 int, ldiff float, pdiff float, winner int )

I need to take all of these events and store them in a table for all of the combinations from @pairs.

Using a procedure based approach, I would run a cursor over the @pairs, and using a procedure calculate the values for each pair and insert into the final table.

Maybe there is a setbased approach to this part..

Have you considered writing SQL the way it is supposed t obe done, i.e. as a SET based operation? You basically write procedural code in a set oriented environment. — TomTom, Jun 02 '14 at 05:51
Yes you are right and I have adapted the code. Still, some coding 'manoeuvres' are harder than others to perform using set based logic — codervince, Jun 03 '14 at 02:40

score 0 · Answer 1 · edited May 23 '17 at 12:01

Your description is good to understand, still have good if your function getHead2Head's text is also available. Also give some data to justify your question.

I read you desciption, I have some suggestion as

why you use 2 loop, instead of make one loop .
Instead of while , good to use cursor in this case which you have better controlled, no set update at last for reset.

The main your issue to fast query, is related your each query and each where condition.

You can give primary key to your temp table, which will get fast result.(creating primary as simple as normal table)

Creating a Primary Key on a temp table - When?

Damien_The_Unbeliever · Answer 2 · 2014-06-02T10:28:03.287

It would be better to switch to passing the parameters using a table-valued parameter or XML, something that naturally supports multiple values rather than cramming them into a string, but since you already have that part and it's "working" for you, we'll leave it alone.

The following should be equivalent:

declare @AllRunnersString nvarchar(200)   
    ='2342;1228;1075;266;423;2849;1690;488;162;1153;1666'

declare @rtn table
(
   eventid int,
   player1 int,
   player2 int,
   lengthdiff float,
   placediff float,
   winner int
)

declare @playerids table
(
    playerid int
)

-- parse string into table
insert into @playerids(playerid)
   select value 
   from split(';', @AllRunnersString) 

insert into @rtn(eventid, player1, player2, lengthdiff, placediff, winner)
select eventid, player1, player2, lengthdiff, placediff, winner 
from
 @playerids p1
   inner join
 @playerids p2
   on
     p1.playerid != p2.playerid
   cross apply
    getHead2Head(p1.playerid, p2.playerid, 3)

There may also be further opportunities to improve this if we could eliminate the call to getHead2Head and include its content directly in this query.

Thanks!! - cross apply is what was missing from my limited SQL vocabulary! I ran the query with the example string and it took < 1m returning 430 combinations with results. Its quick enough to keep the getHead2Head in the mix... — codervince, Jun 02 '14 at 10:15

score 0 · Answer 3 · answered Jun 02 '14 at 07:58

You trie to rebuild a genuine Database task - building loops to match records - in TSQL. Do not use procedural code, use set code and let the Server do the loops and stuff. He will do much better, no offense. For example, if you want to randomly join partners with exactly one partner/candidate pair you can do something like this:

WITH player_combination
AS (
    --Cross Joins selects all combinations of mutually different players
    SELECT p.playerid [partner], c.playerid candidate,newid() random_id
    FROM @playerid p, @playerid c
    WHERE p.playerid<>c.playerid
)
SELECT pc.[partner],pc.candidate
FROM player_combination pc
    JOIN (SELECT [partner], max(random_id) random_id --Select the record with max random_id
            FROM player_combination
            GROUP BY [partner]) tmp ON tmp.[partner]=pc.[partner] AND tmp.random_id=pc.random_id

If you are ok with choosing the max id of a free player the statement is even easier:

WITH player_combination
AS (
    --Cross Joins selects all combinations of mutually different players
    SELECT p.playerid [partner], c.playerid candidate
    FROM @playerid p, @playerid c
    WHERE p.playerid<>c.playerid
)
SELECT pc.[partner],pc.candidate
FROM player_combination pc
    JOIN (SELECT [partner], max(candidate) candidate --Select the record with max candidate id
            FROM player_combination
            GROUP BY [partner]) tmp ON tmp.[partner]=pc.[partner] AND tmp.random_id=pc.random_id

I'm pretty sure that you can get rid of the getHead2Head function too.

Thanks. I believe this takes care of generating the partner-candidate combinations. The Head2Head returns a table of events for a pair which in turn is the difference between 2 shared attributes for partner and candidate. — codervince, Jun 02 '14 at 10:07

Improve TSQL Stored Procedure for pairwise execution of procedure

3 Answers3