* * *

Author Topic: [SOLVED] Is SQLite a better choice than StringGrid  (Read 812 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 622
    • http://www.quickhash-gui.org
[SOLVED] Is SQLite a better choice than StringGrid
« on: September 17, 2017, 02:30:20 am »
I have a program that inserts data into a StringGrid. Each row comprises several cells for each of the several columns. The program is often used for hundreds of thousands of files, and so the StrinGrid often comprises hundreds of thousands of rows. Once complete, the user might sort the grid by a column click and so on. And he can then save the grid to a CSV file.

Though it generally works OK, I'm wondering if it might be better to utilise SQLite. I'm not sure StringGrid is designed for mass data storage, even on a temporary basis. So, while the program finds the data about the files, rather than inserting it into the StringGrid row by row as it goes, I wonder if it is better (faster) to insert it into the SQLite database, and then upon completion, populate the StringGrid from the SQLite database. But perhaps that is just taking twice as long and perhaps wouldn't bring any major advantage? Or, better still, have the SQLIte database displayed somehow (not sure if possible?) directly into the programs form and avoid the use of StringGrid entirely. Though I'm sure that would be better, I am unsure if it would be as easy?  Would rewriting existing code that utlises a StringGrid be a major overhaul to insert it, sort it, display it, in SQLite?

I ask if this sounds like something worth considering? Do those with experience feel this would add speed or usage benefits over a standard StringGrid?
« Last Edit: September 17, 2017, 04:26:22 pm by Gizmo »
Lazarus 1.6.4 and fpc 3.0.2 - Linux Mint 17 LTS, Windows 7 and Mac Yosemite
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

molly

  • Hero Member
  • *****
  • Posts: 2050
Re: Is SQLite a better choice than StringGrid
« Reply #1 on: September 17, 2017, 04:38:16 am »
Because you are one of our selected hero members with a very unique problem, the developers gave you a small present. It is called TDBGrid which more or less automates things for you (and you alone   :-X ).  :)

But, you utilize such a dbgrid by connecting it to a database.

You might want to have a read at the SQLdb tutorial.

I can't judge if using sqlite would be a better choice for you because there isn't really enough information available to be able to judge. If your structures are simple (and can live with the restrictions) , you could also opt for tdbf

Though, if you are new to database programming, then it is advised to start with reading the tutorials and studying the examples. Don't go and rewrite your application until you have enough experience and are confident enough.

Examples are available in fpc/lazarus folders.
« Last Edit: September 17, 2017, 04:50:50 am by molly »

ASerge

  • Sr. Member
  • ****
  • Posts: 477
Re: Is SQLite a better choice than StringGrid
« Reply #2 on: September 17, 2017, 08:12:16 am »
StringGrid is not suitable for mass storage of data, because it itself stores data.
Use other controls. For example, DBGrid with the repository in the database. Or your own storage format and, for example, DrawGrid (I often use the ListView in a virtual mode).
In addition, if you have to display more than 50 records (especially hundreds or thousands), it's better to show only 50 and give the user a good tool for clarification (filtering, etc.).

Gizmo

  • Hero Member
  • *****
  • Posts: 622
    • http://www.quickhash-gui.org
Re: Is SQLite a better choice than StringGrid
« Reply #3 on: September 17, 2017, 04:26:13 pm »
Molly

Quote
the developers gave you a small present. It is called TDBGrid which more or less automates things for you

That looks perfect! Exactly what I was hoping for! I'll do some research as you state, and yes, although I am not unfamiliar with DB queries etc, I am not experienced at all at coding with it. So I'll perhaps create a small trial demo project to see how I get on. Thanks as always
Lazarus 1.6.4 and fpc 3.0.2 - Linux Mint 17 LTS, Windows 7 and Mac Yosemite
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

bee

  • Sr. Member
  • ****
  • Posts: 307
Re: [SOLVED] Is SQLite a better choice than StringGrid
« Reply #4 on: September 17, 2017, 05:06:07 pm »
Some people had already answered your question about string grid, by using db grid instead.

However, I don't think sqLite is a suitable solution for your data capacity. sqLite is more for small to medium data capacity. Well, about hundreds of tables or files. But you said you got hundreds of thousands of files, I think your data is quite big. You should use a real RDBMS for that such as Maria DB, Firebird SQL, or Postgre SQL which are capable to handle thousands to millions of tables.
« Last Edit: September 17, 2017, 05:08:21 pm by bee »
-Bee-

A long time pascal lover.

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 656
Re: [SOLVED] Is SQLite a better choice than StringGrid
« Reply #5 on: September 18, 2017, 07:10:32 am »
hello,
to read CSV files , you can try to use also  tSdfDataset component.
Quote
SDF is a delimited text file format used in Delphi (and later FreePascal) that is very much like, but not the same, as CSV. (.sdf is also the extension of Microsoft SQL Server data files, therefore it may not be a good idea to use the .sdf extension for your files).
FreePascal offers the TSdfDataSet, which stores data in SDF format. Note: FPC 2.6.x and earlier store sdfdataset data in a format that is not completely CSV or completely SDF compatible. Sdfdataset is planned to be rewritten to store data in CSV format.
As indicated, SDF differs from CSV. Depending on the flavour of CSV, this format may be close enough to what a reading application expects to function.
i have made a small project which uses this component to read a big csv file (+20000 lines of 22 fields).
In attachment, this project (based on an Otorres project).
50 ms to read and display the csv file  ::)
Friendly, J.P

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus