* * *

Author Topic: big database?  (Read 1213 times)

JD

  • Hero Member
  • *****
  • Posts: 1564
Re: big database?
« Reply #15 on: June 16, 2017, 11:27:32 am »
I am not good in database programming. Can anyone tell me will it take a long time to load 3650 records x 65 fields?
depends on the field types if they are numeric then no not really I have loaded 10.000.000 records of 12 numeric fields in under 4 seconds (not paged) and with no special coding. If on the other hand all fields are text based and some short of translation happens between server and client (eg from utf8 to ansi locale or smoething) this will get out of hand faster.

I agree. Text fields, memos & blobs will bring the query to its knees very quickly.
Windows (10, 7) - Lazarus 1.6.4/FPC 3.0.2 (also Delphi occasionally)
Linux Mint Cinnamon  - Lazarus 1.4/FPC 2.6.4
Indy 10.6 series; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB

taazz

  • Hero Member
  • *****
  • Posts: 3825
Re: big database?
« Reply #16 on: June 16, 2017, 11:55:47 am »
If size of each cell is 2 bytes, then the file size could be 463 MB. Wow :o
What would you suggest for this case?
Stop trying to load everything in memory and use a good database engine like flashfiler or firebird embedded. In today database market there is no reason to write your own database you will spend a decade or more fine tuning it only to bring it to the same level as existing databases and you will always be behind in features, use cases and test cases. Use an sql database and stay away from data containers like sqlite.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 684
Re: big database?
« Reply #17 on: June 19, 2017, 09:14:58 am »
Quote from: Handoko
He said that contains 65 fields and he want to shows it in a grid. He needs to load all data, because he want to do searching directly on the grid. On that thread I suggested him to load all the data into a TStringGrid instead of using TDBGrid. I guess he is programming something like astrology calendar.
As I told before, TStringgrid is not the place to load a large of data. Besides the memory, Stringgrid is a stand alone component for visualization. Searching to a value is extremely slow.  In that case a container like TList (generics or not) can help you.
DBGrid has the same problem. But is linked to datasource (linked to dataset) and searching on dataset is (very) easy and the results are giving back to DBGrid.
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

mirce.vladimirov

  • Full Member
  • ***
  • Posts: 213
Re: big database?
« Reply #18 on: June 19, 2017, 12:26:16 pm »
You say 3650 records with only 65 fields is big ? That's not big, That's just a weak little kitten, that should not bother you at all.
Wich database are you using ? MySQL/Maria, Firebird, SQLite are the most used among users of Lazarus but you can use almost any other existing database via ODBC.
Applications that my company is producing can handle many hundreds of thousends of records and most my tables (i say most) are 50 to 60 fields per record. We build realy, realy, realy, realy complex queries which complete just in seconds, but I'm using MySQL and FirebirdSQL.

So, tell us, which DataBase are you using ?
As I said, you dont have a  big problem, it is a small one, but we can't help you here unless you tell us wich database you are using.

In addition i will paste a short piece of code for you to analise :
Code: Pascal  [Select]
  1. TheQuery.close;
  2. TheQuery.SQL.Clear;
  3. TheQuery.SQL.text:='select * from thetable where field1=123 and field2=456 and field3="sometext" ';
  4. TheQuery.SQL.Open;
  5.  

For 3650 records this query should be completed within 1-5 seconds depending on your hardware.
The 3650 records are baby toy.

EDIT:
As some friends posted above, blobs can significantly lagg the database, depending on what's stored inside of them (usualy blobs are used to store entire files like pictures, videos, mp3's, pdf's, etc...).
 
« Last Edit: June 19, 2017, 12:36:52 pm by mirce.vladimirov »

 

Recent

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