sys(2015) 2

[mjcmkrsr]

Hi,

I tested SYS(2015) with 1'000'000 records created in 540 milliseconds ( ~ 1850 values per millisecond) and NO duplicates were found (see code below). According to the Help file, records created during the same millisecond would be identical. Hence my question: is SYS(2015) generating unique values?

CLOSE ALL 

LOCAL liStart, liEnd

CREATE CURSOR csrPKey (cSys2015 C(10))

liStart = SECONDS()

FOR i = 1 TO 1000000
	INSERT INTO csrPKey (cSys2015) VALUES (SYS(2015))

ENDFOR

liEnd = SECONDS()

WAIT WINDOW + STR((liEnd - liStart) * 1000) TIMEOUT 3

BROWSE 

select * FROM csrPKey ;
	GROUP BY 1 ;
	HAVING COUNT(cSys2015) > 1 ;
	INTO CURSOR csrDupes
	 
IF _tally > 0
	BROWSE
ELSE
	WAIT WINDOW "No duplicates found!" TIMEOUT 3
ENDIF 

CLOSE ALL
CLEAR ALL

MarK

 

[Mike Lewis]

For what it's worth, I'm seeing the same results (over several tests).

So it looks like you have answered your own question. I can't throw any further light on it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike,

I went a step further and tested with 10 000 000 records in 5120 milliseconds (~1900 records/millisecond) - same result - NO duplicates.

So why does the the Help file state

...

Calling SYS(2015) more than once during the same millisecond interval returns a unique character string.

MarK

 

[Chris Miller]

The point is that SYS(2015) is mainly just an alphanumeric conversion of system time. The uniquness guarantee given in the documentation holds true for a single computer. If you would use SYS(2015) you can get problems when multiple clients cause that default value to be the same, even more likely since Windows syncs system time with Microsofts time server by default.

The uniqueness on a single system is solved by VFP caching the last value until system time has increased to a point after it. If you create a series of very many values the last one is well in the future (meaning split seconds, perhaps, but, you know, aeons for a computer and lots of sequential ids). You could even verify that, when knowing the conversion. There are some routines around for that.

The simple test doesn't need to know the conversion. Just define a table with SYS(2015) as default value of a field on which you create a primary key index and then just do this on two or more computers:

use sharedtable shared
do while .t.
    append blank
enddo

That'll pretty immediately cause index uniqueness violations when the second computer starts appending records. If you don't start at the same time (for example by a scheduled task scheduled for the same time) then I would even say the first append blank of the second computer will already fail.

What "saves the day" or even makes this work for years, if not even for the whole lifetime of a database, is that you usually don't ever have that close encounter of adding records at the same time from different clients. Notice that its the client generating the SYS(2015) value, not the server the DBF is stored in a share, usually. If you'd use SYS(2015) generated by a single server, that would make it a viable unique id because of the double value prevention mechanism it has on a single computer. Ids geneerated wthin short times will then just look like a sequence.

What that also means is SYS(2015) is good for a single client solution, if you never need to merge data of sevaral clients in a central database.

Nevertheless, GUID is the far better carefree alternative. Some don't even trust that, but there's a lot of misunderstanding about GUID, not only because of the multiple platforms of OSes and databases but also multiple versions of definitions. One thing that's making it the far better choice is, it's not having the problem of SYS(2015).

Chriss

 

[mjcmkrsr]

Hi Chriss,

I tested your suggestion by running several instances of VFP accessing the same table on my PC.

Just define a table with SYS(2015) as default value of a field on which you create a primary key index and then just do this on two or more computers:
...
That'll pretty immediately cause index uniqueness violations when the second computer starts appending records. If you don't start at the same time (...) then I would even say the first append blank of the second computer will already fail.

... and you're right : the index uniqueness is violated !

Thanks for the insight.

MarK

 

[Chris Miller]

Oh, that's good to know, I would have predicted you need two separate clients and a server side dbf in a share both clients can access or at least a workgroup network where one client stores the dbf and the other sees it via network.

But two separate sessions would need to share a system wide last SYS(2015) value to prevent repeated values. I see, that's not happening, only each single process will not repeat a SYS(2015) value.
One solution therefore could be putting together a) GETENV('COMPUTERNAME'), b) _vfp.ProcessId c) SYS(2015) and make it unique that way. When you use one of the multithreading solutions available also the current threadid, maybe. Well, or just use a GUID.

Chriss