Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Are you sure that the insert in VFP succeeded? How much space does the VFP file have after the insert? –  Marian Aug 26 '11 at 12:47
1  
By @GB RAM, do you mean 2 GB RAM? –  StanleyJohns Aug 26 '11 at 13:16
4  
When looking at Visual Fox Pro you should keep in mind that Microsoft won't be doing any future development on this product while they will on SQL Server. –  mrdenny Aug 26 '11 at 14:42
2  
You are doing something that SQL Server isn't optimized for. 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. –  mrdenny Aug 26 '11 at 18:50
2  
SQL Server isn't "100 times slower than VFP" you aren't comparing like for like and you aren't really measuring anything that's useful for your specific problem. E.g. consider where your data will be sourced from. I suspect it may not be coming from an incrementing variable in a loop which is located in memory on the same machine as the database. Think about the entire architecture you need - not just the database component of it. –  sqlvogel Aug 29 '11 at 11:55

3 Answers 3

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.

share|improve this answer
    
That makes sense, and thanks for the whitepaper.I am going to read it next. –  StanleyJohns Aug 26 '11 at 13:19
    
What did you mean with " generate files to be bulk loaded" ? Thanks for whitepaper! –  Dorel HAVA Aug 26 '11 at 16:36
1  
It is better to load large amounts of data in bulk, so for this requirement you would have all the data to be loaded for the five minute interval in one file. This would be then be loaded in one go rather than row by row with insert statements. –  MartinC Aug 29 '11 at 10:34

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.

share|improve this answer

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.

share|improve this answer
    
+1, I have been working with VFP tables for a decade and while they are "fast" for "small" data, they will hit the wall at 2Gbyte in a hard and messy fashion. Any decent SQL engine should accomodate the poster's needs, and almost all of them come with a tool or process for bulk-loading data quickly. –  Avery Payne Sep 19 '12 at 18:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.