Senior Nelson wrote:
> Try a test, compare a search on a million records in SQL server
I ran this code for a few minutes until I got bored and aborted.
I am sure it added quite a few records - actually, it added 44000
records to each table:
<cfloop from="1" to="999998" index="i">
<cfset request.rs = Rand()>
<cfquery datasource="#attributes.dsn#">
insert into uuidtest (testid, randomstring, randomnumber)
VALUES ('#CreateUUID()#', '#Rand()#', #RandRange(1,100000)#)
</cfquery>
<cfquery datasource="#attributes.dsn#">
insert into inttest (randomstring,randomnumber)
VALUES ('#Rand()#', #RandRange(1,100000)#)
</cfquery>
</cfloop>
The inttest table uses an autonumber field. This was using SQL server 7.
While 44000 is a far cry from 1 million, the numbers are pretty interesting.
Here's some query data:
Queries
testuuid (Records=196, Time=12027ms)
SQL =
select * from uuidtest
where randomstring like '%007%'
testuuid2 (Records=201, Time=17255ms)
SQL =
select * from uuidtest
where randomnumber < 500
testuuid3 (Records=0, Time=20710ms)
SQL =
select * from uuidtest
where randomnumber = 500
testint (Records=218, Time=3034ms)
SQL =
select * from inttest
where randomstring like '%007%'
testint2 (Records=204, Time=6790ms)
SQL =
select * from inttest
where randomnumber < 500
testint3 (Records=2, Time=581ms)
SQL =
select * from inttest
where randomnumber = 500
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
