VLDB - SQL SERVER versus VFoxPro 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&lt;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?
A good place to start would be looking at the SQL CAT team's whitepaper on data loading performance for SQL Server 2008.

The procedural loops in SQL Server are very slow, which would be a large part of the difference in timings.  It would be best to generate files to be bulk loaded in the databases to measure the performance differences.  Also the size of the database will very quickly require the use of multiple database files to spread the load which I'm not sure that FoxPro has any builtin support.