While those are interesting results, in fact confusing results, I'm not
sure they are valid tests.... I got VERY different results. Look at
this.
You were testing the random number field, although what we care about is
the "testid" field. That's what we'll join in our other tables and
that's what we'll use to pull a single record. I ran slightly different
tests that compare a lookup on the PK field not the randomnumber field.
I created 4 tables, the same basic structure as you.
speed_identity_index - used an int field with an identity and a
clustered index
speed_identity_noindex - used an int field with an identity and no index
speed_uuid_index - used a 35 character char field and a clustered index
speed_uuid_noindex - used a 35 character char field and no index
Tests:
Insert tests
Just like you I didn't want to wait for a million records. I went with
10000, since there are no simultaneous users, I don't think the number
really matters that much, but we should mess around with it and see if
they do. I ran these tests twice and averaged the results
speed_identity_index 52145 milliseconds
------------------------------------------------------------
insert into speed_identity_index (randomstring,randomnumber)
VALUES ('#Rand()#', #RandRange(1,100000)#)
speed_identity_noindex 53376 milliseconds
------------------------------------------------------------
insert into speed_identity_noindex (randomstring,randomnumber)
VALUES ('#Rand()#', #RandRange(1,100000)#)
speed_uuid_index 58033 milliseconds
------------------------------------------------------------
insert into speed_uuid_index (id, randomstring, randomnumber)
VALUES ('#CreateUUID()#', '#Rand()#', #RandRange(1,100000)#)
speed_uuid_noindex 59505 milliseconds
------------------------------------------------------------
insert into speed_uuid_noindex (id, randomstring, randomnumber)
VALUES ('#CreateUUID()#', '#Rand()#', #RandRange(1,100000)#)
Insert results.... indexed identity is faster than an indexed UUID by
..6ms per insert (divide those results above by 10000 inserts). big deal
to me? no.
------------------------------------------------------------
Select tests
In this test, I selected a single record from the table. This tests the
speed of the lookup on the PK. Again, I did a loop 10000 times,
performing the same query. Not perfect, but at least it's scientific. I
chose to pull the ID=500 from the identity table and
id='9A70E140-5E34-4CA4-936C12D45B76B187' from the UUID table.
speed_identity_index 25857 milliseconds
------------------------------------------------------------
select * from speed_identity_index
where id=500
speed_identity_noindex 28201 milliseconds
------------------------------------------------------------
select * from speed_identity_noindex
where id=500
speed_uuid_index 24756 milliseconds
------------------------------------------------------------
select * from speed_uuid_index
where id='9A70E140-5E34-4CA4-936C12D45B76B187'
speed_uuid_noindex 29162 milliseconds
------------------------------------------------------------
select * from speed_uuid_noindex
where id='9A70E140-5E34-4CA4-936C12D45B76B187'
Results.... Indexed UUID was FASTER!
WTF! I wasn't expecting that.
Erik, try some more tests. What I'm totally lost with is why there were
different results with your tests when you look at other fields in the
table. That seems messed up to me, seems like if both tables have the
same number of records, if you look at a non-pk field for a random
number it should be the same regardless of what you use for your PK
field.
I'm going to try those tests you did and try mine again with more than
10000 loops.
Steve Nelson
Erik Voldengen wrote:
>
> 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
