Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Random SQL
- Thread starter siuk
- Start date
By default, DB engines, retrieve data in index order (generally, the index associated with the Primary key of the table acceded). You can overlap this default order by adding an "ORDER BY" clause to your query but I don't think you can get the data in random order. I think the solution for you should be to put the data into an array and then access this array randomly. Water is not bad as long as it stays out human body ;-)
- Thread starter
- #3
I never heard about this "NewId()" sql function. Can you tell me more about it ? Does it exist in all DB or only in some ? What kind of DB do you use ? Oracle, SQLServer, MSAccess ? Water is not bad as long as it stays out human body ;-)
I'm interested in this as well, could you provide a link to documetation or examples?
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
The NewID() function is a SQL Server function (it may be available in other RDBMS' too) which generates a new GUID.
Usually you would set up a column as data type uniqueidentifier and specify NewID() as the default value.
You can look it up in SQL BOL for further info. --James
Usually you would set up a column as data type uniqueidentifier and specify NewID() as the default value.
You can look it up in SQL BOL for further info. --James
- Thread starter
- #7
here you go!
The NewID() function is a SQL Server function (it may be available in other RDBMS' too) which creates a unique value of type uniqueidentifier.
Usually you would set up a column as type uniqueidentifier and specify NewID() as the default value.
You can look it up in SQL BOL for further info. --James
Usually you would set up a column as type uniqueidentifier and specify NewID() as the default value.
You can look it up in SQL BOL for further info. --James
After a little search in SQL-server documentation, it seems that NewId() function returns a new "uniqueIdentifier" type variable that looks like this : "6F9619FF-8B86-D011-B42D-00C04FC964FF". After knowing that I'm not very sure of the result of the above Siuk query !!!! Water is not bad as long as it stays out human body ;-)
Ok, thats what I thought but wanted to make sure, thanks for the info James. My internet connectivity has been up and down today so I figured it would probably be less painful to ask here than go get it on my own 
I would have to agree with Targol that I don't see how this would pull out a random row unless it is dynamically assigning a new id each time you call that query(since it is in the order by but you don't actually have a column set up for GUIDs). If that is the case this might actually be more intensive than doing one query to get the recordcount than querying for a random record based on that count.
If it is not creating a fresh GUID for each record every time you query, than you should receive the same record back each and every time, unless the guid is assigned decrementally, than you would just receive the newest record.
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
I would have to agree with Targol that I don't see how this would pull out a random row unless it is dynamically assigning a new id each time you call that query(since it is in the order by but you don't actually have a column set up for GUIDs). If that is the case this might actually be more intensive than doing one query to get the recordcount than querying for a random record based on that count.
If it is not creating a fresh GUID for each record every time you query, than you should receive the same record back each and every time, unless the guid is assigned decrementally, than you would just receive the newest record.
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
couple of sources:
"Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian
"Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian
I did some research earlier and was going to post it, but between my connection difficulties and the fact that it looked like TT was having difficulties, I gave up after two attempts
Heres the higlight for those watching the thread:
The GUID is generated from the network card id and the system time. When you do the select statement above (another resource for this is the tips and tricks section at MSDN) it will basically create a new GUID for each individual row, re-order the table (of course using a temporary table as it does with every query), then send you the results in "random" order.
I am still questioning the efficiency of doing this though. Basically my issue is that if you have 10 queries on a table with 1000 records, your firing the NewID function 10,000 times, or in other words:
For Each Query
For Each Record
Call NewId()
Next
Next
I am considering running a test later tonight (if my girlfriend lets me stay in front of the computer that long) that will do an average time/query using NewId versus an average time of doing a count query, VBScript Random, then Nested Select query (SELECT Top 1 ... (SELECT Top "&randomnumber&"... order by ... desc))). I may extend this by doing a select * with a move random number, though I am sure this will be the slowest by far.
I will post the results afterwards, as well as the stup and code I used to test with.
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
Heres the higlight for those watching the thread:
The GUID is generated from the network card id and the system time. When you do the select statement above (another resource for this is the tips and tricks section at MSDN) it will basically create a new GUID for each individual row, re-order the table (of course using a temporary table as it does with every query), then send you the results in "random" order.
I am still questioning the efficiency of doing this though. Basically my issue is that if you have 10 queries on a table with 1000 records, your firing the NewID function 10,000 times, or in other words:
For Each Query
For Each Record
Call NewId()
Next
Next
I am considering running a test later tonight (if my girlfriend lets me stay in front of the computer that long) that will do an average time/query using NewId versus an average time of doing a count query, VBScript Random, then Nested Select query (SELECT Top 1 ... (SELECT Top "&randomnumber&"... order by ... desc))). I may extend this by doing a select * with a move random number, though I am sure this will be the slowest by far.
I will post the results afterwards, as well as the stup and code I used to test with.
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
Well, amazingly using the NewID method isn't half bad. In case anyone other than me is curious about this heres the setup for the test and the results:
Win 2000 Server sp3, p1.6, 512k RAM
MS SQL Server 2000 Enterprise, sp?
IIS5 of course
The database I am using is from a project I worked on a year ago. The table in question has:
1 seeded integer
21 varchar fields (total max chars: 21,130)
2 bit fields
1 unused image field
2 smalldatetime fields
It currently contains 1385 records.
The test code will be at the bottom.
I ran two pretests for the heck of it based on using a command object vs using the recordset.open method over 50 queries (SELECT * FROM Main). I then looped through the recordsets grabbing the values for the primary key, a numeric text field(varchar 30), and a text field (varchar 1000).
(I actually ran these tests many more times in the process of creating them, and I am to lazy to run the pretest2 more than a couple times so there will only be one set for the pretests in the results)
I ran two tests on random record selection.
The first test used the NewId query:
"SELECT Top 1 * FROM Main Order By NewID()"
The second test was a dual query, 1 for the recordcount and 1 for the a nested select statement:
"SELECT TOP 1 * FROM (SELECT TOP " & cInt(Rnd * recCount) & " * FROM Main ORDER BY rid DESC) DERIVEDTBL"
Both of these tests also had the same three fields grabbed from them (though not in a loop as there was only 1 record returned).
Here are the results, I hope the formatting fits(i'm doing it in the editor so I won't ruin the format of the log file):
And finally here is the code (with the connection string blanked out to save wear and tear on my server):
Before any one mentions it, yes there were numerous ways I could have made individual test more efficient, from using getrows to not using * in the queries, but I wanted to keep them as similar as possible.
Let me know if there are any questions,
-Tarwn
argh, after all that I only just now realized I wan't outputting the seconds witgh the start and end times...oh well, pretend
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
heres the setup for the test and the results:Win 2000 Server sp3, p1.6, 512k RAM
MS SQL Server 2000 Enterprise, sp?
IIS5 of course
The database I am using is from a project I worked on a year ago. The table in question has:
1 seeded integer
21 varchar fields (total max chars: 21,130
)2 bit fields
1 unused image field
2 smalldatetime fields
It currently contains 1385 records.
The test code will be at the bottom.
I ran two pretests for the heck of it based on using a command object vs using the recordset.open method over 50 queries (SELECT * FROM Main). I then looped through the recordsets grabbing the values for the primary key, a numeric text field(varchar 30), and a text field (varchar 1000).
(I actually ran these tests many more times in the process of creating them, and I am to lazy to run the pretest2 more than a couple times so there will only be one set for the pretests in the results)
I ran two tests on random record selection.
The first test used the NewId query:
"SELECT Top 1 * FROM Main Order By NewID()"
The second test was a dual query, 1 for the recordcount and 1 for the a nested select statement:
"SELECT TOP 1 * FROM (SELECT TOP " & cInt(Rnd * recCount) & " * FROM Main ORDER BY rid DESC) DERIVEDTBL"
Both of these tests also had the same three fields grabbed from them (though not in a loop as there was only 1 record returned).
Here are the results, I hope the formatting fits(i'm doing it in the editor so I won't ruin the format of the log file):
Code:
TestName NumQueries Start End Elapsed Avg/Record
PreTest1 50 1/21/2003 20:00 1/21/2003 20:01 21 0.42
PreTest2 50 1/21/2003 20:01 1/21/2003 20:02 67 1.34
Test1 50 1/21/2003 20:02 1/21/2003 20:02 2 0.04
Test2 50 1/21/2003 20:02 1/21/2003 20:02 1 0.02
Test1 100 1/21/2003 20:03 1/21/2003 20:03 4 0.04
Test2 100 1/21/2003 20:03 1/21/2003 20:03 1 0.01
Test1 200 1/21/2003 20:03 1/21/2003 20:03 8 0.04
Test2 200 1/21/2003 20:03 1/21/2003 20:03 2 0.01
Test1 500 1/21/2003 20:04 1/21/2003 20:04 19 0.038
Test2 500 1/21/2003 20:04 1/21/2003 20:05 6 0.012
Test1 1000 1/21/2003 20:05 1/21/2003 20:05 39 0.039
Test2 1000 1/21/2003 20:05 1/21/2003 20:05 12 0.012And finally here is the code (with the connection string blanked out to save wear and tear on my server):
Code:
<%
Option Explicit
Server.ScriptTimeout = 600
Dim strConnect
strConnect = "Provider=SQLOLEDB.1;Password=----;" & _
"Persist Security Info=False;User ID=----;" & _
"Initial Catalog=----;Data Source=----;"
%><!--#INCLUDE FILE="adovbs.inc" --><%
Dim objCommand
Dim sql_pretest, sql_newid, sql_count, sql_randcount, sql_all
Dim rs_pretest, rs_newid, rs_count, rs_randcount, rs_all
sql_pretest = "SELECT * FROM Main"
Dim numTestLoops, loopCounter
Dim start_time, end_time
Dim dummy_key, dummy_int, dummy_string
numTestLoops = 50 'default value, will be changed dependant on workhorse call
'---------------------------- PreTests --------------------------------------------------
'The pretests are timed on executing a query, looping through the rows accessing the primary_id (integer),
' a non-key numeric field, and a text (varchar) field
' all objects will be set to nothing in each loop and re-instantiated
'-------- PreTest 1: objCommand to select *
Function PreTest1()
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_pretest
objCommand.CommandType=adCmdText
Set rs_pretest = objCommand.Execute
rs_pretest.MoveFirst
Do Until rs_pretest.EOF
dummy_key = rs_pretest("rid")
dummy_int = rs_pretest("main_size")
dummy_string = rs_pretest("title")
rs_pretest.MoveNext
Loop
Set rs_pretest = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- PreTest 2: rs.Open to select *
Function PreTest2
'Grab the Start time
start_time = Now()
'Do The Test
For loopCounter = 0 to numTestLoops - 1
Set rs_pretest = Server.CreateObject("ADODB.Recordset")
rs_pretest.Open sql_pretest, strConnect, adOpenStatic, adLockOptimistic, adCmdText
rs_pretest.MoveFirst
Do Until rs_pretest.EOF
dummy_key = rs_pretest("rid")
dummy_int = rs_pretest("main_size")
dummy_string = rs_pretest("title")
rs_pretest.MoveNext
Loop
Next
'Grab the end time
end_time = Now()
End Function
'---------------------------- Random Record Tests ---------------------------------------
'-------- Test 1: Select a random record from a table using NewID() function
Function Test1
'Prep the sql statement
sql_newid = "SELECT Top 1 * FROM Main Order By NewID()"
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_newid
objCommand.CommandType=adCmdText
Set rs_newid = objCommand.Execute
dummy_key = rs_newid("rid")
dummy_int = rs_newid("main_size")
dummy_string = rs_newid("title")
Set rs_newid = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- Test 2: Select a random record using 2 queries
Function Test2
'Prep the sql statement
Dim recCount
sql_count = "SELECT Count(*) as num FROM Main"
Randomize
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_count
objCommand.CommandType=adCmdText
Set rs_count = objCommand.Execute
recCount = rs_count("num")
Set rs_count = Nothing
sql_randcount = "SELECT TOP 1 * FROM (SELECT TOP " & cInt(Rnd * recCount) & " * FROM Main ORDER BY rid DESC) DERIVEDTBL"
'response.write sql_randcount
objCommand.CommandText=sql_randcount
Set rs_randcount = objCommand.Execute
dummy_key = rs_randcount("rid")
dummy_int = rs_randcount("main_size")
dummy_string = rs_randcount("title")
Set rs_randcount = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- Test 3: Select a random record by selecting * then moving x number of records forward
Function Test3
'prep the sql
'sql_all = "SELECT * FROM MA"
'there is no need to do this test because it can't even select everything from the table fast enough, let alone move through it
End Function
'------------------------------------------------ Workhorse 0----------------------------
Function workhorse(testname,numloops)
numTestLoops = numLoops
Response.Write "Starting " & testname & ":<br>"
Response.Flush
Select Case lcase(testname)
case "pretest1"
PreTest1
case "pretest2"
PreTest2
case "test1"
Test1
case "test2"
Test2
case "test3"
Test3
End Select
Dim sec, avg
sec = DateDiff("s",start_time, end_time)
avg = sec/numTestLoops
Response.Write testname & " has completed " & numTestLoops & " queries against the database.<br>"
Response.Write "Start: " & start_time & "<br>"
Response.Write "End: " & end_time & "<br>"
Response.Write "Total Time: " & sec & "s<br>"
Response.Write "Average: " & avg & "<br><hr>"
'append the results to our text file
Dim fso, fil
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set fil = fso.OpenTextFile(Server.MapPath("dbEfficLog.txt"), 8)
fil.WriteLine(testname & vbTab & numTestLoops & vbTab & FormatDateTime(start_time,2) & " " & FormatDateTime(start_time,4) & vbTab & FormatDateTime(end_time,2) & " " & FormatDateTime(end_time,4) & vbTab & sec & vbTab & avg)
fil.close
Set fil = Nothing
Set fso = Nothing
End Function
workhorse "PreTest1",50
workhorse "PreTest2",50
workhorse "Test1",50
workhorse "Test2",50
workhorse "Test1",100
workhorse "Test2",100
workhorse "Test1",200
workhorse "Test2",200
workhorse "Test1",500
workhorse "Test2",500
workhorse "Test1",1000
workhorse "Test2",1000
%>Before any one mentions it, yes there were numerous ways I could have made individual test more efficient, from using getrows to not using * in the queries, but I wanted to keep them as similar as possible.
Let me know if there are any questions,
-Tarwn
argh, after all that I only just now realized I wan't outputting the seconds witgh the start and end times...oh well, pretend
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924
very nice!!
now get back to work![[lol]](/data/assets/smilies/lol.gif "[lol] [lol]")
_______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio
now get back to work
_______________________________________________[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"
;alert(Nstr); }[/sub]_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
