Round-robin select via Stored Procedure

Round-robin select via Stored Procedure

Post by kskva.. » Sun, 16 Jul 2000 04:00:00



I have an application in which I need to select records in a round
robin fashion, but including only records that have a column set to a
number I pass the stored procedure. For example, with the following
table:

joe 2
ted 2
ned 3
ann 5
elk 5
tes 5
nev 3
tim 3
els 2

I need to be able to execute the stored procedure from a web page via
ado and get a reliable round robin selection. Like:

spchoose 2

and get 1 record per execution like:

joe
ted
els
joe
ted
els
.
.
.

I am doing this now by using a number in a third column to signify the
last selected record , but am getting eroneous results. E.G. I am
getting records like elk although I passed the sp a 2. Can anyone think
of a better way to do this? Or perhaps why I am getting incorrect
results. Below I post the SP, and the table I am running it on. Thanks
for any help!


AS












rrobin = '1' )


rrobin = '1' )
        begin


        end

if exists (select * from sysobjects where name = 'testing123')
drop table [testing123]
create table testing123 (id smallint)




        Begin



        End

        else

                Begin




                        Begin
                                if exists (select id from testing123

                                        Begin

                                        update engineers set rrobin = 0

                                        update engineers set rrobin = 1

                                        BREAK
                                        End
                                                else
                                                Begin

+ 1

                                                END
                        End

END


Table:

(1 row(s) affected)

id          group_id    rrobin
----------- ----------- -----------
3           1           0
4           1           0
46          3           1
35          1           1
18          1           0
20          1           0
21          1           0
24          1           0
37          4           1
39          4           0
25          1           0
42          2           1
38          4           0
40          1           0

In particular, if passing the sp a 1, I am getting record with id 37
prior to record with id 35.

I cant figure this out!

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Round-robin select via Stored Procedure

Post by Stefan Gustafsso » Sun, 16 Jul 2000 04:00:00



Quote:> I have an application in which I need to select records in a round
> robin fashion, but including only records that have a column set to a
> number I pass the stored procedure. For example, with the following
> table:

The following procedure works and it has the following nice properties:

* It does all its work as a transaction
* It avoids any possible deadlock situations
* It works regardless of the initial contents of the rrobin column

/SG


As



SET NOCOUNT ON

BEGIN TRAN

-- Find the last row
-- Use UPDLOCK to avoid possible deadlock

FROM   Engineers WITH(UPDLOCK)

-- Find the next row

    SELECT min(id)
    FROM   Engineers WITH(UPDLOCK)

    ),(
    SELECT min(id)
    FROM   Engineers WITH(UPDLOCK)

    ))

-- update the rrobin flag


-- Return the result

COMMIT TRAN

 
 
 

Round-robin select via Stored Procedure

Post by Joe Celk » Mon, 17 Jul 2000 04:00:00


Quote:>> I have an application in which I need to select records in a round

robin fashion, but including only records that have a column set to a
number I pass the stored procedure. For example, with the following
table:

 joe 2
 ted 2
 ned 3
 ann 5
 elk 5
 tes 5
 nev 3
 tim 3
 els 2

I need to be able to execute the stored procedure from a web page via
ado and get a reliable round robin selection. Like:

 spchoose 2

 and get 1 record per execution like:

 joe
 ted
 els
 joe
 ted
 els
 ... <<

I am doing this at night before going to bed, so pardon the fuzzy prose
and bad code; I hope the idea is easy to understand.  This sounds like
a job for a cursor, since you are passing one row at a time, in a
particular order to host program.  Let me assume the table looks like
this:

CREATE TABLE Engineers
(id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(5) NOT NULL,
 grp_id INTEGER NOT NULL
        CHECK (grp_id >= 0));

INSERT INTO Engineers VALUES (1, 'joe', 2);
INSERT INTO Engineers VALUES (13, 'ted', 2);
INSERT INTO Engineers VALUES (23, 'ned', 3);
INSERT INTO Engineers VALUES (47 'ann', 5);
INSERT INTO Engineers VALUES (32, 'elk', 5);
INSERT INTO Engineers VALUES (96, 'tes', 5);
INSERT INTO Engineers VALUES (3, 'nev', 3);
INSERT INTO Engineers VALUES (43, 'tim', 3);
INSERT INTO Engineers VALUES (95, 'els', 2);

Then we need to build a cursor with a sorting column that will sort the
given starting group number, start_grp, first and "loop around" to the
lower group numbers when it finishes a sequential pass.

DECLARE CURSOR Chooser
FOR SELECT E1.*,
           MOD(grp_id + :start_grp, (SELECT MAX(grp_id) FROM Engineers))
           AS sort_col
      FROM Engineers AS E1
    ORDER BY sort_col, id;

Now you need some OPEN CURSOR and FETCH statements in a host program
WHILE loop that is exited when the last row is fetched.  After the last
row is passed to the host program, close the cursor and re-open it
inside a containing WHILE forever loop. I have not thought about how to
shut down the program.

I think this aproach will be faster and easier to maintain than trying
to keep track of a dozen variables, updating a single row to set a
flag, etc.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Round-robin select via Stored Procedure

Post by Stefan Gustafsso » Mon, 17 Jul 2000 04:00:00



> I am doing this at night before going to bed, so pardon the fuzzy prose
> and bad code; I hope the idea is easy to understand.  This sounds like
> a job for a cursor, since you are passing one row at a time, in a
> particular order to host program.  Let me assume the table looks like
> this:

Since the poster explicitly said that this was to be used from a web-page, I
most definitely do not think that a cursor is appropriate.

/SG

 
 
 

Round-robin select via Stored Procedure

Post by kskva.. » Mon, 17 Jul 2000 04:00:00


Stefan, this appears to work perfectly. Thanks for taking the time to
help me out! Im just getting into stored procedures, and can take all
the help I can get.

-Ken





> > I have an application in which I need to select records in a round
> > robin fashion, but including only records that have a column set to
a
> > number I pass the stored procedure. For example, with the following
> > table:

> The following procedure works and it has the following nice
properties:

> * It does all its work as a transaction
> * It avoids any possible deadlock situations
> * It works regardless of the initial contents of the rrobin column

> /SG


> As



> SET NOCOUNT ON

> BEGIN TRAN

> -- Find the last row
> -- Use UPDLOCK to avoid possible deadlock

> FROM   Engineers WITH(UPDLOCK)

> -- Find the next row

>     SELECT min(id)
>     FROM   Engineers WITH(UPDLOCK)

>     ),(
>     SELECT min(id)
>     FROM   Engineers WITH(UPDLOCK)

>     ))

> -- update the rrobin flag


> -- Return the result

> COMMIT TRAN

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Round-robin select via Stored Procedure

Post by Robert Banfiel » Wed, 19 Jul 2000 04:00:00


Hi,

I'd be interested to know why the use of a cursor would be inappropriate
when called from a web-page.

Thanks!  /RB

 
 
 

1. striping versus round robin

hi
I am about to upgrade from online 5 to 7 and am sitting with a query.
This is to be done on an HP K200 dual process unix box.
Do I use HP's disk striping to create a raw logical volume accross
my six disks or do I round robin my large tables across these disks ???
The large tables are 2,5 million rows and larger and the rowsize is
extremely large. +- 300k per row. These tables are also hard hit with
access. They also have +- 12 indexes on each one.
Can anybody help ?????

2. 3rd highest Value

3. Round Robin

4. Backup procedures using Archive Log files.

5. Round Robin IP problem?

6. HELP! A variant of my last question...

7. SQLNet Round Robin

8. 9i RAC and Shared Server Mode vs. Dedicated Server Mode

9. Round Robin DNS and interfaces file

10. SQLNet Round Robin

11. Round robin scheduler?