Cursor - trying to find another method

Question

Cursor - trying to find another method

steve-lauzon

SSC Veteran

Points: 283
More actions
April 6, 2010 at 1:20 pm

#217531

Hi all... this is my first post in the forums. I hope I'm following the guidelines
I need help!
I got a table that contains a list of several regions (RegionName, Min, Max, NumberToGenerate)
Ex : Region 1, 0, 5000, 100
Region 2, 1700, 2300, 50
I need to generate randomly X numbers (numbertogenerate) within the specified scope (between min and max) for each region
After the number is generated, I have to check in another table to see if that number exist and if it does, add it to a ListedTable and if not, to a NonListed table. There are several other conditions to check but for the sake of this example, I'll keep it simple 🙂
I have to loop within the specified region until I reach the listed numbers to generate. When reached, I move to the next region
I tried using cursors which works fine but really slow.
I have no clue how to conceive that procedure using batchs - I'm missing something.
I know you have limited information, but I would appreciate some hints. I can give more info if needed
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

I do think using a cursor is the right strategy.

Post suggest there are a number of secondary processes and tables involved, may be the process is slow because some of the other queries e.g. check if number exists in other table - are slow.

I would work in making sure all queries involved perform well.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

steve-lauzon (4/6/2010)
Hi all... this is my first post in the forums. I hope I'm following the guidelines
I need help!
I got a table that contains a list of several regions (RegionName, Min, Max, NumberToGenerate)
Ex : Region 1, 0, 5000, 100
Region 2, 1700, 2300, 50
I need to generate randomly X numbers (numbertogenerate) within the specified scope (between min and max) for each region
After the number is generated, I have to check in another table to see if that number exist and if it does, add it to a ListedTable and if not, to a NonListed table. There are several other conditions to check but for the sake of this example, I'll keep it simple 🙂
I have to loop within the specified region until I reach the listed numbers to generate. When reached, I move to the next region
I tried using cursors which works fine but really slow.
I have no clue how to conceive that procedure using batchs - I'm missing something.
I know you have limited information, but I would appreciate some hints. I can give more info if needed
Thanks

Ummmm.... why does this need to be done randomly? I'm asking for two reasons... does it really need to be done randomly and there are several methods to pull this off randomly.

Also, take a peek at the first link in my signature line below... it would help us help you a whole lot faster.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve-lauzon SSC Veteran Points: 283 More actions · Answer 3

Thanks for the answers

1. Yes it needs to be generated randomly. It's a statistical application and the first rule is everything has to be generated randomly

2. Thanks also for the link. I will do so in my future post.

3. I will check my other queries to make sure that they are optimized.

Thanks again

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

steve-lauzon (4/6/2010)
2. Thanks also for the link. I will do so in my future post.

Heh... if you do it on this post, I might be able to provide a high speed alternative to the cursor code you're currently using. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Based on my understanding of your requirements:

declare @t table (

RegionName varchar(10) not null primary key clustered,

[Min] int not null,

[Max] int not null,

[NumberToGenerate] int not null

)

insert into @t

select 'Region 1', 0, 5000, 15union all

select 'Region 2', 1700, 2300, 25union all

select 'Region 3', 13, 55, 14

declare @maxNumberToGenerate int

select @maxNumberToGenerate = max(NumberToGenerate) from @t

declare @num table (number int not null primary key clustered)

insert into @num

select number

from

-- Number table function F_TABLE_NUMBER_RANGE available here

-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

dbo.F_TABLE_NUMBER_RANGE(1,@maxNumberToGenerate)

order by

number

select

b.RegionName,

a.number,

-- Generate random number in range of Min to Max

RandNo =

(convert(bigint,convert(varbinary(7),newid()))%([max]-[Min]+1))+[Min]

from

@num a

join

@t b

on a.number <= NumberToGenerate

order by

b.RegionName,

a.number

Results:

RegionName number RandNo

---------- ----------- --------------------

Region 1 1 4725

Region 1 2 2763

Region 1 3 1661

Region 1 4 794

Region 1 5 3244

Region 1 6 1766

Region 1 7 3613

Region 1 8 4361

Region 1 9 585

Region 1 10 447

Region 1 11 4242

Region 1 12 336

Region 1 13 230

Region 1 14 1029

Region 1 15 1149

Region 2 1 1710

Region 2 2 1744

Region 2 3 1762

Region 2 4 1953

Region 2 5 1996

Region 2 6 1909

Region 2 7 1993

Region 2 8 1864

Region 2 9 2218

Region 2 10 2044

Region 2 11 1890

Region 2 12 2004

Region 2 13 1723

Region 2 14 1944

Region 2 15 2132

Region 2 16 1769

Region 2 17 1848

Region 2 18 1878

Region 2 19 2193

Region 2 20 1858

Region 2 21 2293

Region 2 22 2124

Region 2 23 1787

Region 2 24 1714

Region 2 25 1844

Region 3 1 41

Region 3 2 48

Region 3 3 26

Region 3 4 18

Region 3 5 18

Region 3 6 52

Region 3 7 41

Region 3 8 33

Region 3 9 25

Region 3 10 22

Region 3 11 19

Region 3 12 33

Region 3 13 19

Region 3 14 52

(54 row(s) affected)

Garadin One Orange Chip Points: 29613 More actions · Answer 6

I made a very similar one before I re-read it and thought that he needed those numbers to exist in another table. Maybe he does, maybe not.

DECLARE @T TABLE(

Regionint,

MinVint,

MaxVint,

NumToGenint)

INSERT INTO @T(Region, MinV, MaxV, NumToGen)

VALUES(1,0,5000,100),(2,1700,2300,50)

;WITH

t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y),

RNG AS (SELECT Region, NumToGen, MinV + ABS(CHECKSUM(NEWID()))%(MaxV-MinV) RV

FROM @t

)

SELECT Region, N, RV

FROM RNG, Tally

WHERE N <=NumToGen

ORDER BY Region, N

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

steve-lauzon SSC Veteran Points: 283 More actions · Answer 7

Wow!

Thanks for the help guys... you gave me good insights!

I'll keep you posted

Thanks