VLDB - SQL SERVER versus VFoxPro

Question

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?

Answer 1

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.

Answer 2

Edit the application do that it bulk inserts the data into the table using the bulk insert provider and you'll get much better results. This will perform much better than using a loop to insert one row at a time. You can simulate this by creating a text file with the values you want to insert then using either the BULK INSERT statement in T/SQL or the BCP command line app to bulk load the data.

If you are bulk loading data into a production table then leave recovery at full. If you are bulk loading into a staging table then doing processing on it you can change the recovery from full to BULK LOGGED or SIMPLE to reduce the amount of data going into the transaction log. NEVER bulk load data directly into a production table without using the FULL recovery model.

Answer 3

A FoxPro DBF isn't really an option on grounds of size alone. A DBF file in VFP is limited to 2GB. It looks like your data is going to be at least 20 bytes per row, which means a day's worth of data is over 100GB. I suggest you forget about using DBFs.

DBMS write performance is only one aspect of the problem and the actual speed will depend on the entire stack, the way you read and cache the data, etc. Your test doesn't really indicate very much.

Have you looked at any of the various Complex Event Processing (CEP) solutions available? There are techniques you should be using to optimise the storage and manipulation of streaming data. Some of the CEP solutions available include: Oracle CEP, Microsoft StreamInsight, StreamBase and the OsiSoft PI system.