Toad World® Forums

Random Integers and Avoiding Key Collisions


#1

HI there

Just getting started with Benchmark Factory sp apologies in advance for the stupid question - maybe I’m coming at this completely the wrong way

I want to test a stored procedure that executes a lot of comoplicated stored procedures. This Main stored procedure takes a single integer parameter value (“key Value”)

To provide a list of viable Key values to be passed to the the SPROC I have created a lookup table of 1 million rows (1 million unique key vals) each with a sequential rownum for use as a means of generating a good keynnum value for the parameter to the sproc. Table looks like this (the keyvals are non sequential and basically random trying to to randomly pick a keyval would have a high miss rate)

RowNo KeyVal
1 3625525

2 7366366

3 7356535

etc

The plan is to pass a random number to the BF scalability job that calls the stored procedure (I want to test something like $BFURandRange(1,1000000) ) and then the agent uses that random number to go to the lookup table table and get the key value for the matching RowNo value and pass it to the sproc via the bound sproc parameter value

However the problem is that it just picks the same number every time and so I end up erroring out after 5 rows. How can I make it pick a different random number for Rowno each time so that I can get a different keyVal each time?

And to really put the bonus icing on the cake how can I guarantee that it never picks the same random number more than once?
Thanks a million for any insight - as I say, maybe I’m coming at this completely the wrong way

Cheers

Barry


#2

Can you please post a screenshot of your SQL Statement that is using the $BFURandRange function? My thought is that the $BFURandRange function is in the SQL statement which is getting prepared and the the prepared statement executed multiple times. This would cause the same value, the initial value, of $BFURandRange to be used in all the SQL executions. If this is the case, try putting the $BFURandRange in a bind parameter.


#3

HI There

Thanks a lot for your reply. I have been binding the parameter but it may well be that I don’t understand the execution method options properly and have been choosing the wrong one???

Image 01 shows the master sproc, the @keyseedIIN is supposed to be an everchanging random number between 1 and 1 million which allows the master sproc to retrieve the matching KeyVal for use by sub sprocs

Many Thanks for your time

Bench01.jpeg

Bench02.jpeg


#4

HI there

Many thanks for taking an interest. it’s a relief to know that what I’m trying to do is seen to be achievable at least !!

I posted a more detailed reply with snips a few minutes ago but it seems to have gone for admin review first. Hopefully it’s released soon :slight_smile:

Many Thanks

B

EDIT: Have added the screenies to existing post. Is this sufficient info?

Thanks

again

B


#5

HI All

Just as an update if any body read this, I ended up (so as to get on with things) having to move the random number selection part into a wrapper sproc and calling that from BF instead to get the valid key values. This works fine for my needs but includes an extra seek and delete which I don’t need as part of my measurements .

If anybody ever see this and has a comment on what I was doing worng then I’d be delighted to hear it

many Thanks

B


#6

I was able to reproduce the issue you had with the random number and found that there is a bug with using the BFScripts in transactions within a SQL Scalability test. I am glad you already found a work around! We are working on a fix for this issue.


#7

Hi Kevin

Thanks for the reply. Is the bug general or applicable only to a scalability tests? Becaue if the latter I could of course try a differnt kind of test. I’m guessing the former though :smiley:

Thanks

B


#8

Checked and it does affect the other tests. It when a Prepare and Execute transaction is used in a test. Before the test is executed BMF determines if the prepared statement will be used elsewhere in the test and if not, sets a flag to removed the prepared statement after execution, which resets the BFScripts. This check does not check if the statement is the top level statement and is executed more than once. The prepared statement should be used in subsequent executions but is not. The issue has been logged and assigned to be fixed in the next release. If you need something sooner, a hotfix can be done sooner. You just need to contact support.


#9

Hello Kevin

Thanks a lot for the work and insight. I’ll plough on with my wrappers for now!

Many Thanks

B