Recent

Author Topic: big database?  (Read 12159 times)

JD

  • Hero Member
  • *****
  • Posts: 1848
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 - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

taazz

  • Hero Member
  • *****
  • Posts: 5368
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: 1131
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 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

mirce.vladimirov

  • Sr. Member
  • ****
  • Posts: 256
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 »

Handoko

  • Hero Member
  • *****
  • Posts: 5130
  • My goal: build my own game engine using Lazarus
Re: big database?
« Reply #19 on: June 24, 2017, 08:20:46 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.

Can you please explain more, why SQLite is not a good option?

I am open to hear from different point of view and I like to learn something new. Database - I knew it many years ago, but it is the thing I know least. I googled about the database performance comparison and I landed on this page:

https://sqlite.org/speed.html

On the link above, SQLite doesn't look so bad. The first test "Test 1: 1000 INSERTs" synchronous version of SQLite is the worst. But on the other tests, SQLite seems good, even better than MySQL on several tests. That page is in SQLite official website, is the test biased?

On their home page, they said
Quote
SQLite is the most used database engine in the world.

Is it true, SQLite is the most used one?

I am thinking to spend more time to learn database programming. But still have not decided which one should I learn: Firebird, MySQL or SQLite. Any experience how they compare to each others?

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: big database?
« Reply #20 on: June 24, 2017, 02:07:50 pm »
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.

Can you please explain more, why SQLite is not a good option?

SQLite is what I call a data container. It gives me the impression that it saves everything to text and does conversions on the fly when needed if you search a bit about how it compares two number you will see that it smells a lot like string comparison. Now is this going to work? On most cases it might.
It does not support multiple connections not even from the same application.
Last time I checked it did not had any of the basic features of a database triggers/views/store procedures etc.


I am open to hear from different point of view and I like to learn something new. Database - I knew it many years ago, but it is the thing I know least. I googled about the database performance comparison and I landed on this page:

https://sqlite.org/speed.html

On the link above, SQLite doesn't look so bad. The first test "Test 1: 1000 INSERTs" synchronous version of SQLite is the worst. But on the other tests, SQLite seems good, even better than MySQL on several tests. That page is in SQLite official website, is the test biased?

I have no idea but I'll be positive the test posted is 100% accurate. It still compares it self with mysql a database which did not support transactions up until a few years back (out of the box at least). MySQL was a really bad starting point for databases I heard it got better in version 4 and 5 but still I wouldn't trust it with my data even behind a website for which it was designed for.

On their home page, they said
Quote
SQLite is the most used database engine in the world.

Is it true, SQLite is the most used one?

Since it is used from all android devices as the data container of choice for its small size and requirements on CPU power, I guess, if it is not the most used it certainly the most available.
I seem to recall that IOS also comes with it pre-installed but I'm not sure if it does its "use" became even bigger.

I am thinking to spend more time to learn database programming. But still have not decided which one should I learn: Firebird, MySQL or SQLite. Any experience how they compare to each others?

Depends entirely on your needs do you need something to keep data in and possibly query / sort them? then any would do. Do you need fine control over what is saved where under what conditions and constrains? then you will be served better with a more powerful database like firebird or even postgresql. you should really download as many databases as you can firebird, postgresql, msSQL etc and their showcase databases and take a closer look on their abilities defining relations constrains actions and what they offer for your development. 

Always keep in mind that "most used" has nothing to do with "better" and everything to do with easy of access.
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

Handoko

  • Hero Member
  • *****
  • Posts: 5130
  • My goal: build my own game engine using Lazarus
Re: big database?
« Reply #21 on: June 24, 2017, 05:20:46 pm »
Thank you for the explanation.

GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: big database?
« Reply #22 on: June 24, 2017, 07:41:34 pm »
Quote
It does not support multiple connections not even from the same application.
Last time I checked it did not had any of the basic features of a database triggers/views/store procedures etc.

Yes, SQLite support multiple connections, I use it in my programs.
SQLite have triggers and views, but not stored procedures.

Best regards,
GAN.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: big database?
« Reply #23 on: June 24, 2017, 07:46:37 pm »
Quote
It does not support multiple connections not even from the same application.
Last time I checked it did not had any of the basic features of a database triggers/views/store procedures etc.

Yes, SQLite support multiple connections, I use it in my programs.
SQLite have triggers and views, but not stored procedures.

Best regards,
GAN.
You got my interest. Any chance to see a small demo with multiple connections for 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

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: big database?
« Reply #24 on: June 24, 2017, 09:09:38 pm »
Multiple connection isn't a problem with SQLite.
Simultaneous writes from those connections are. SQLite locks the database when writing so that's something to consider.

When to use SQLite and when not to use SQLite.

Beginning with version 3.7.0 you can use WAL (Write-Ahead Logging) but that's only available from one computer and not in network situations.

SQLite has it's advantages but as said, isn't a full substitute for a "real" RDBMS. But it is evolving over time. It hovers between being a very (very) advanced INI-substitute and a "real" RDBMS.

SQLite certainly does have its quirks as mentioned, especially if you look at it from a database-engine angle.
 

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: big database?
« Reply #25 on: June 24, 2017, 10:51:18 pm »
Multiple connection isn't a problem with SQLite.
Simultaneous writes from those connections are. SQLite locks the database when writing so that's something to consider.

When to use SQLite and when not to use SQLite.

Progress!! well a form of it.
 
Beginning with version 3.7.0 you can use WAL (Write-Ahead Logging) but that's only available from one computer and not in network situations.

SQLite has it's advantages but as said, isn't a full substitute for a "real" RDBMS. But it is evolving over time. It hovers between being a very (very) advanced INI-substitute and a "real" RDBMS.

SQLite certainly does have its quirks as mentioned, especially if you look at it from a database-engine angle.
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

 

TinyPortal © 2005-2018