Recent

Author Topic: big database?  (Read 12162 times)

majid.ebru

  • Sr. Member
  • ****
  • Posts: 494
big database?
« on: June 12, 2017, 01:10:25 pm »
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

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: big database?
« Reply #1 on: June 12, 2017, 01:36:37 pm »
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

  • Sr. Member
  • ****
  • Posts: 397
Re: big database?
« Reply #2 on: June 12, 2017, 02:13:53 pm »
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?
I have no idea! what do you use to open it?

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: big database?
« Reply #3 on: June 12, 2017, 03:01:22 pm »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

majid.ebru

  • Sr. Member
  • ****
  • Posts: 494
Re: big database?
« Reply #4 on: June 12, 2017, 03:18:03 pm »
This page has explanation for it:
http://forums.devart.com/viewtopic.php?t=15237

this link is very good and i learn some code. :o :) ::)

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

  • Hero Member
  • *****
  • Posts: 1131
Re: big database?
« Reply #5 on: June 13, 2017, 08:37:53 am »
Quote
i 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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Thaddy

  • Hero Member
  • *****
  • Posts: 14210
  • Probably until I exterminate Putin.
Re: big database?
« Reply #6 on: June 13, 2017, 08:57:46 am »
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.
Specialize a type, not a var.

majid.ebru

  • Sr. Member
  • ****
  • Posts: 494
Re: big database?
« Reply #7 on: June 13, 2017, 11:12:27 pm »
Quote
i 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

  • Hero Member
  • *****
  • Posts: 672
    • http://www.silentwings.nl
Re: big database?
« Reply #8 on: June 14, 2017, 08:11:57 am »
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
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
       Lazarus 3.2 FPC 3.2.2
       CodeTyphon 8.40 FPC 3.3.1

ertank

  • Sr. Member
  • ****
  • Posts: 274
Re: big database?
« Reply #9 on: June 14, 2017, 11:27:18 pm »
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?

- 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

  • Sr. Member
  • ****
  • Posts: 494
Re: big database?
« Reply #10 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


mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: big database?
« Reply #11 on: June 16, 2017, 08:15:59 am »
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?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: big database?
« Reply #12 on: June 16, 2017, 10:51:12 am »
- 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][+][-]
  1. Page intRecordsSent of intTotalRecords
  2.  
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
« Last Edit: June 16, 2017, 11:00:34 am by JD »
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

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: big database?
« Reply #13 on: June 16, 2017, 11:06:28 am »
I think what OP want to do is related with
http://forum.lazarus.freepascal.org/index.php/topic,37181.msg249116.html#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 :o
What would you suggest for this case?
« Last Edit: June 16, 2017, 11:12:10 am by Handoko »

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: big database?
« Reply #14 on: June 16, 2017, 11:11:24 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.
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