big database? lazarus / 16-Databases ---------------------------------------------------------------------- majid.ebru · 2017-06-12 11:10 Hi i have a big(large) database. when i want to open it,my program take long to open it. . how can i  use trackbar to show this delay? ---------------------------------------------------------------------- Handoko · 2017-06-12 11:36 I think what you want is TProgressBar not TTrackbar. Read more: http://lazarus-ccr.sourceforge.net/docs/lcl/comctrls/ttrackbar.html http://lazarus- ccr.sourceforge.net/docs//lcl/comctrls/tprogressbar.html To use ProgressBar you need to provide values for Max and Position. It depends on the case and the database you use, you maybe cannot get the values for max and position before the database has opened. This page has explanation for it: http://forums.devart.com/viewtopic.php?t=15237 ---------------------------------------------------------------------- HeavyUser · 2017-06-12 12:13 Quote from: majid.ebru on June 12, 2017, 01:10:25 pmHi i have a big(large) database. when i want to open it,my program take long to open it. . how can i  use trackbar to show this delay? I have no idea! what do you use to open it? ---------------------------------------------------------------------- mangakissa · 2017-06-12 13:01 Set packetrecords to 50 to load the first 50 records. After sccolling down other records will be uploaded to the dataset. This increases the speed of your tdbrid showing records. ---------------------------------------------------------------------- majid.ebru · 2017-06-12 13:18 Quote from: Handoko on June 12, 2017, 01:36:37 pmThis page has explanation for it: http://forums.devart.com/viewtopic.php?t=15237 this link is very good and i learn some code. thank you Handoko. but : 1 - i don't use table and i just have(use) DBGrid and it doesn't "FetchAll"? 2 - when i close dbgrid and run it , show Error("DBGrid is nok Active " ---------------------------------------------------------------------- mangakissa · 2017-06-13 06:37 Quotei have a big(large) database. 1 - i don't use table and i just have(use) DBGrid and it doesn't "FetchAll"? table -> dataset - > dbgrid, so option 1 is not true. ---------------------------------------------------------------------- Thaddy · 2017-06-13 06:57 For really big databases controls that suggest precision, like scrollbars and trackbars, are completely unsuited and will slow down your code extremely. Better to use an hourglass or a dialog or bar like Microsoft's BusyWaiting or Android's bouncing bar. ---------------------------------------------------------------------- majid.ebru · 2017-06-13 21:12 Quote from: mangakissa on June 13, 2017, 08:37:53 amQuotei have a big(large) database. 1 - i don't use table and i just have(use) DBGrid and it doesn't "FetchAll"? table -> dataset - > dbgrid, so option 1 is not true. ?!?! what is it? ---------------------------------------------------------------------- JanRoza · 2017-06-14 06:11 Quote?!?! what is it? Every DBGrid component has a datasource property which has to be filled in to link that DBGrid to the data. The datasource can point to a Tdatasource and a Tdatasource points to a Ttable or a TSQLquery. So: table -> dataset - > dbgrid ---------------------------------------------------------------------- ertank · 2017-06-14 21:27 Quote from: majid.ebru on June 12, 2017, 01:10:25 pmHi i have a big(large) database. when i want to open it,my program take long to open it. . how can i  use trackbar to show this delay? - What database server? - How "big" is your database? You know big is relative. About your "long open"; 1- Are you trying to open connection to database which is long? 2- Are you trying to open an SQL statement (a table or a query) which is long? 2a- How many records are there in table(s) you read from? 2b- How many records you fetch out of that total record count in your table(s) (if your user prefer to see them all) 2c- Even if you change filtering parameters in your SQL statement, do you need to close and open your SQL more than once in your application? Above questions are relevant in a way that it maybe possible that you can do some improvement in your database/table to make it open faster. Assuming you are running an SQL statement taking long to execute: As far as I know it is not quite possible to display a "progress bar" kind of thing when opening an SQL statement automatically. There is a command sent to database server and a reply received most of the time. No information exchange in between. I am not aware of advanced servers like ORACLE. They may have some solutions. Anyway, you may "guess" how long it takes to run your SQL and fake users as if it is making a progress (running and not stuck). You can do so by using a thread (worker thread I will call it) to execute your query. While query is executed in a worker thread, update your user interface like each second and make a progress of 5% or some number more suitable to your use. Once worker thread completed, display results immediately. You do not need to make progress wait up to 100%. Unfortunately, I am no thread expert at all. So, I will not be able to help you writing some sample code. All I know is that such an approach may convince your user that query is running and he/she should wait while progress bar is advancing. ---------------------------------------------------------------------- majid.ebru · 2017-06-15 11:27 - What database server? --------> no server and it is local - How "big" is your database? You know big is relative. --------> i think it it big About your "long open"; 1- Are you trying to open connection to database which is long? --------> i think read recordes 2- Are you trying to open an SQL statement (a table or a query) which is long? --------> i use zconnect and zquery.SQL.text is long 2a- How many records are there in table(s) you read from? --------> more than 3650 (10 * 365 = each day * days of year) recordes 2b- How many records you fetch out of that total record count in your table(s) (if your user prefer to see them all) --------> All record 2c- Even if you change filtering parameters in your SQL statement, do you need to close and open your SQL more than once in your application? --------> YES ---------------------------------------------------------------------- mangakissa · 2017-06-16 06:15 How many seconds does it take to read your entire table? How many fields? Your'e using SQLLite. When opening the table in another SQLLite browser, it's still slow? ---------------------------------------------------------------------- JD · 2017-06-16 08:51 Quote from: majid.ebru on June 15, 2017, 01:27:05 pm- What database server? --------> no server and it is local - How "big" is your database? You know big is relative. --------> i think it it big About your "long open"; 1- Are you trying to open connection to database which is long? --------> i think read recordes 2- Are you trying to open an SQL statement (a table or a query) which is long? --------> i use zconnect and zquery.SQL.text is long 2a- How many records are there in table(s) you read from? --------> more than 3650 (10 * 365 = each day * days of year) recordes 2b- How many records you fetch out of that total record count in your table(s) (if your user prefer to see them all) --------> All record 2c- Even if you change filtering parameters in your SQL statement, do you need to close and open your SQL more than once in your application? --------> YES Sorry to point this out but given the details you provided, your database is not big at all. I have bigger databases in production & they are quite fast. Like mangakissa pointed out, pagination is probably the answer to your problems. Retrieve records in small chunks and let the user decide if they want to see more records. Internet search engines use a similar strategy. Like Thaddy said, forget the trackbar, use numbers instead. Get a recordcount and do something like Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Page intRecordsSent of intTotalRecords This is much faster to display on the screen. Use filtering to limit the number of records returned. Don't try to fill a grid with 10,000 records at once (I exaggerate) because that is eye strain for the user and puts too much load on your database. Use prepared statements and parameterized queries to speed up the requests sent to the database. If it is the connection/opening the database that takes the time, use a connection pool. Creating a new connection everytime you want data is expensive. A connection pool allows you to reuse already existing connections. The speed benefits can be immense. Cheers & good luck, JD ---------------------------------------------------------------------- Handoko · 2017-06-16 09:06 I think what OP want to do is related with http://forum.lazarus.freepascal.org/index.php/topic,37181.msg249116.ht ml#msg249116 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. I am not good in database programming. Can anyone tell me will it take a long time to load 3650 records x 65 fields? If size of each cell is 2 bytes, then the file size could be 463 MB. Wow What would you suggest for this case? ---------------------------------------------------------------------- taazz · 2017-06-16 09:11 Quote from: Handoko on June 16, 2017, 11:06:28 amI 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. ---------------------------------------------------------------------- JD · 2017-06-16 09:27 Quote from: taazz on June 16, 2017, 11:11:24 amQuote from: Handoko on June 16, 2017, 11:06:28 amI 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. ---------------------------------------------------------------------- taazz · 2017-06-16 09:55 Quote from: Handoko on June 16, 2017, 11:06:28 amIf size of each cell is 2 bytes, then the file size could be 463 MB. Wow 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. ---------------------------------------------------------------------- mangakissa · 2017-06-19 07:14 Quote from: HandokoHe 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. ---------------------------------------------------------------------- mirce.vladimirov · 2017-06-19 10:26 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TheQuery.close;TheQuery.SQL.Clear;TheQuery.SQL.text:='select * from thetable where field1=123 and field2=456 and field3="sometext" ';TheQuery.SQL.Open;  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...). ---------------------------------------------------------------------- Handoko · 2017-06-24 06:20 Quote from: taazz on June 16, 2017, 11:55:47 amQuote from: Handoko on June 16, 2017, 11:06:28 amIf size of each cell is 2 bytes, then the file size could be 463 MB. Wow 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 QuoteSQLite 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 · 2017-06-24 12:07 Quote from: Handoko on June 24, 2017, 08:20:46 amQuote from: taazz on June 16, 2017, 11:55:47 amQuote from: Handoko on June 16, 2017, 11:06:28 amIf size of each cell is 2 bytes, then the file size could be 463 MB. Wow 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. Quote from: Handoko on June 24, 2017, 08:20:46 am 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. Quote from: Handoko on June 24, 2017, 08:20:46 amOn their home page, they said QuoteSQLite 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. Quote from: Handoko on June 24, 2017, 08:20:46 amI 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. ---------------------------------------------------------------------- Handoko · 2017-06-24 15:20 Thank you for the explanation. ---------------------------------------------------------------------- GAN · 2017-06-24 17:41 QuoteIt 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. ---------------------------------------------------------------------- taazz · 2017-06-24 17:46 Quote from: GAN on June 24, 2017, 07:41:34 pmQuoteIt 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? ---------------------------------------------------------------------- rvk · 2017-06-24 19:09 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 · 2017-06-24 20:51 Quote from: rvk on June 24, 2017, 09:09:38 pmMultiple 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. Quote from: rvk on June 24, 2017, 09:09:38 pmBeginning 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.