I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute.
I was thinking to use SQL server because I will have 7,2 billions records every day. But when I tested the writing speed on my PC (Dual Core E6300, @ Gb RAM, HDD 500Gb SATA) I found that Visual Foxpro 9 is registering 1 million records 100 times faster than SQL Server 2008.
SQL Server, 2.55 minutes:
declare @i int=1 while @i<1000001 begin insert into dbo.sensor (sensorid, datetime, value) values (1,getdate(),@i) set @i=@i+1 end
VFP9, 2 seconds:
? DATETIME () FOR i=1 TO 1 000 000 STEP 1 INSERT INTO Table1 (sensorid, dtime, value, status) VALUES (i, date(), i, "S") ENDFOR ? DATETIME()
I mention that there was no Index, and the table will be read only. What is wrong?