1 (edited by AD1408 2020-06-30 12:09:26)

Topic: Very slow speed?

Hi all,


I have the linked private project and it got really slow after adding some data. Data added mostly by copy/paste from webpages.
Application run very slow... Adding / editing data also very slow. Could somebody tell what's causing this kind of speed degradation please?


I ave uploaded it to external file hoster as it's file size bit big (25.4MB). Please see the link below.


file removed on 30 June 2020

Adam
God... please help me become the person my dog thinks I am.

Re: Very slow speed?

Form1.ProjectTreeView3

3 (edited by AD1408 2020-06-29 11:49:55)

Re: Very slow speed?

sibprogsistem wrote:

Form1.ProjectTreeView3


Thanks... Could you please tell me what's the issue with Form1.ProjectTreeView3? Is there a solution?

Adam
God... please help me become the person my dog thinks I am.

4 (edited by sibprogsistem 2020-06-29 12:35:23)

Re: Very slow speed?

AD1408 wrote:
sibprogsistem wrote:

Form1.ProjectTreeView3


Thanks... Could you please tell me what's the issue with Form1.ProjectTreeView3? Is there a solution?

http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&item=6782&download=0

Post's attachments

Attachment icon Безымянный.png 166.26 kb, 109 downloads since 2020-06-29 

Re: Very slow speed?

Thank you very much sibprogsistem ......
Truly appreciated.......


Lets see if anybody can offer a solution for the calculated field used which was slowing down the app.
It was a useful counter that showed how many items within each node.

Adam
God... please help me become the person my dog thinks I am.

6 (edited by derek 2020-06-29 19:04:51)

Re: Very slow speed?

Hi Adam,
It might not be the only (or not even the main) reason why your project is running slowly but I'm wondering why you are storing all your .rtf files in the database itself rather than holding them externally and just calling them as and when you need.
Your pictures are saved as external links so I'm wondering why you didn't do the same thing (unless there's something specific that prevented you).  In fact, you don't even need to hold them as link files.
In the attached (very basic) example, the .rtf files are automatically named according to the table record_count (I've used a hard-coded path but that was just laziness on my part!!) and then stored outside of the database.
It might be worth giving something like that a go and see if you notice any improvement.
Derek.

Post's attachments

Attachment icon adam.zip 340.37 kb, 302 downloads since 2020-06-29 

Re: Very slow speed?

Hi Derek,


Thank you very much for the suggestion...
Truly appreciated...


I added more RTF and couldn't get it working. Please see the attached file.


Another question, is it possible to apply your suggestion to existing database or data needs to be re-entered.

Post's attachments

Attachment icon Adam_SavingRTFasLINKED.zip 10.57 kb, 269 downloads since 2020-06-30 

Adam
God... please help me become the person my dog thinks I am.

8 (edited by derek 2020-06-30 11:22:56)

Re: Very slow speed?

Hi Adam,
If you add 2 new .rtfs for each record (your 'additional' and 'references and links'),  the old script will simply overwrite the first 'save' with the second and third 'saves'.
As a suggestion, I'd still use the 'record_count' as the base but append 'main', 'additional' and 'refs and links' to the file name to give uniqueness;  this has the added benefit that you can go into the 'rtfstore' folder and easily sort the files by the main part of the filename (record_count) but also group them by 'mains', 'additionals' and 'refs and links' which may be useful.
With regard to your second point, as a one-off you could probably comment out the 'loadrtf' section of the script and bring in the existing .rtf file that is stored as part of the DB and then immediately save it (which would name it and save it in the rtfstore folder).  Do that for all of the existing .rtf files you have, then un-comment out the 'loadrtf' section of the script and finally use something like SQLiteStudio to remove the old stored .rtf files from the DB. 
In theory, that should work (but when were theory and practice ever the same!!).
Of course, take a backup of your sqlite.db before doing anything!
Derek.

Post's attachments

Attachment icon Adam_SavingRTFasLINKED.zip 344.69 kb, 290 downloads since 2020-06-30 

Re: Very slow speed?

Thank you very much Derek...
Truly appreciated...

Adam
God... please help me become the person my dog thinks I am.

10 (edited by AD1408 2020-07-01 08:57:10)

Re: Very slow speed?

Hi again Derek,


I wanted delete linked image and RTF file record using the script below which I got from one of your post (img file delete) but could't get it working somehow. Any suggestion?


procedure Form1_btnProjectDELETE_OnClick (Sender: TObject; var Cancel: boolean);
begin
deletefile(extractfiledir(application.exename) + sqlexecute('select img01_filename from Projects where id =' +form1.tgProjects.sqlvalue));

deletefile(extractfiledir(application.exename) + sqlexecute('select prRTF_filename from Projects where id =' +form1.tgProjects.sqlvalue));
deletefile(extractfiledir(application.exename) + sqlexecute('select prRTFadditional_filename from Projects where id =' +form1.tgProjects.sqlvalue));
deletefile(extractfiledir(application.exename) + sqlexecute('select prRTFreference_filename from Projects where id =' +form1.tgProjects.sqlvalue));
end;

Also, RTF pages on page control doesn't seems to be updating correctly. After clicking on grid item one, which has content all 3 rtf; clicking second grid item which has refs only tab rtf shows. However it shows main as well.
After delete also doesn't clear rtf pages.

Post's attachments

Attachment icon linkedrtf1.png 22.09 kb, 125 downloads since 2020-07-01 

Adam
God... please help me become the person my dog thinks I am.

Re: Very slow speed?

AD1408 wrote:

Hi again Derek,


I wanted delete linked image and RTF file record using the script below which I got from one of your post (img file delete) but could't get it working somehow. Any suggestion?


procedure Form1_btnProjectDELETE_OnClick (Sender: TObject; var Cancel: boolean);
begin
deletefile(extractfiledir(application.exename) + sqlexecute('select img01_filename from Projects where id =' +form1.tgProjects.sqlvalue));

deletefile(extractfiledir(application.exename) + sqlexecute('select prRTF_filename from Projects where id =' +form1.tgProjects.sqlvalue));
deletefile(extractfiledir(application.exename) + sqlexecute('select prRTFadditional_filename from Projects where id =' +form1.tgProjects.sqlvalue));
deletefile(extractfiledir(application.exename) + sqlexecute('select prRTFreference_filename from Projects where id =' +form1.tgProjects.sqlvalue));
end;

Also, RTF pages on page control doesn't seems to be updating correctly. After clicking on grid item one, which has content all 3 rtf; clicking second grid item which has refs only tab rtf shows. However it shows main as well.
After delete also doesn't clear rtf pages.


I have used the following for clearing rtf pages after delete but doesn't seems to work either. RTF pages cleared only after clicking on page control tabs

procedure Form1_Button3_OnAfterClick (Sender: TObject);
begin
form1.RichEdit1.Clear;
form1.RichEdit2.Clear;
form1.RichEdit3.Clear;
end;
Adam
God... please help me become the person my dog thinks I am.

Re: Very slow speed?

Hi,
Try it like this (see attached).
Clicking the delete button on Form1 will physically delete any .rtf files in the 'rtfstore' folder for the selected row, delete the record from the table and refresh the richedit fields in the tabsheets on Form1.
Derek.

Post's attachments

Attachment icon Adam_SavingRTFasLINKED 2.zip 343.76 kb, 303 downloads since 2020-07-01 

13 (edited by AD1408 2020-07-01 17:58:45)

Re: Very slow speed?

Tank you very much Derek....


Looks like my one cell brain failed me again. I couldn't apply your kind sample to my private project. I couldn't see what I'm missing. Work version is attached. I'm sorry for bothering you this many times.


I tried to apply project/health db, both linked rtf and file deletion without a success.


--------
edit: I got linked RTF delete working. I was using extractfiledir instead of extractfilepath
edit 2 Sorted out linked image delete too...


Once again, thank you very much for your kind help Derek..................

Adam
God... please help me become the person my dog thinks I am.

Re: Very slow speed?

derek wrote:

Hi,
Try it like this (see attached).
Clicking the delete button on Form1 will physically delete any .rtf files in the 'rtfstore' folder for the selected row, delete the record from the table and refresh the richedit fields in the tabsheets on Form1.
Derek.


Hi Derek,
Could you please help on the use of delete button. You have used delete button action without confirmation. When delete confirmation checkbox used and cancelled the delete by clicking on 'No' button on confirmation dialog, RTF record  would not be deleted. I couldn't alter your script to do this.

Adam
God... please help me become the person my dog thinks I am.

15 (edited by derek 2020-07-24 11:43:56)

Re: Very slow speed?

Hi Adam,
Try it like this (please see attached - done a very quick test and it seems to be okay).
Basically, with regard to the 'delete' button, it now asks for confirmation and I've changed its associated action in the script from being 'onclick' to being 'onafterclick' and use 3 edit fields (hidden) to hold the rtf file names (as the 'delete' action will have already removed the relevant row from the grid and so lose the links).
Hope that makes sense.
Derek.

Post's attachments

Attachment icon Adam_SavingRTFasLINKED 2.zip 341.91 kb, 278 downloads since 2020-07-23 

Re: Very slow speed?

Thank you very much for your kind help Derek..................
Truly appreciated...............



I'm using for db images the following:

deletefile(extractfiledir(application.exename) + Form1.DBImage1.dbField);

It seems to be working but if not correct, please correct it.

Adam
God... please help me become the person my dog thinks I am.

17 (edited by AD1408 2020-07-28 18:57:33)

Re: Very slow speed?

Hi Derek,


I couldn't get linked image files delete with confirmation working even I thought I did.
Your kind help would be appreciated....


Edit:
When using treview with table grid and rich edit, I couldn't get RTF and dbImage files deleted with delete onAfterClick working.

Post's attachments

Attachment icon Adam_SavingRTFasLINKED 3 Delete with Confirmation.zip 9.01 kb, 270 downloads since 2020-07-28 

Adam
God... please help me become the person my dog thinks I am.

18 (edited by derek 2020-07-29 23:49:38)

Re: Very slow speed?

Hi Adam,
The problem deleting the linked image files is because you are deleting the row before trying to delete the linked image;  you therefore lose your reference to the file you're trying to delete.
You need to hold it (img01_filename) temporarily in a variable (or as I do, in an edit field (hidden once you check it's working okay)).  I'm guessing that's what you were trying to do with your hidden dbimage2 on form1 (but that would contain img01 rather than img01_filename) which is why the linked file wasn't being deleted.
But there is a bigger problem wholly unrelated to the deletions which you can see if you do the following;
1.  add a few new records with rtf files
2.  scroll up and down form1.tablegrid1 and you can see the relevant text displays correctly
3.  add an image to any of the records and save it
4.  now when you scroll up and down, the image is displayed (where relevant) but none of the rtf information displays.
5.  close and restart the application - the rtf and image information displays correctly.
Repeat  Steps 3 and 4 and the problem re-occurs.
If you check the raw data with something like SQLiteStudio, the data is all correct.
What's more puzzling is that if you try it with just the rtf's, it's fine and if you try it with just the images, it's fine.  The above problem only occurs when you are trying to display both rtf's and images (see examples in the attachment).
In other applications, I've done exactly what you're trying to do with a memo field and an image with no problem which leaves me to suspect  that the rtf object on Form1 is not reloading correctly (despite trying to clear / deselect in the script) under certain conditions (ie when there's an image file involved).  This happens irrespective of whether the image is stored in the database or as a link file.
Unless anyone else has any ideas, I think you might need to ask Dmitry to have a look.
Sorry I can't be of more help,
Derek.

Post's attachments

Attachment icon adam.zip 1.01 mb, 279 downloads since 2020-07-29 

Re: Very slow speed?

Hi Derek,


Thank you very much for your kind helps and explanation...
I'm using a checkbox as delete conformer. Delete button becomes active with use of checkbox. It's primitive workaround.
I hope Dmitry implements linked RTF and deletion options for RTF and linked DB Image files... hopefully sooner.

Adam
God... please help me become the person my dog thinks I am.

Re: Very slow speed?

Derek,
I cannot replicate your problem with both rtf and images. I added records with rtf's. Then go back and added images. I did various combinations and the scrolling shows both the image and rtf just fine. I'm on version 6.3. Don't know if that has anything to do with it.

Re: Very slow speed?

Hi EHW,
I'd written it on 6.4 but have now tried it on 6.3 with the same problem.
I'll try it out on PCs with different versions of Windows as and when I can and see if that might be the issue (for whatever reason).
Thanks for the feedback - good to know that it does work (in principle at least).
Derek.