1. Beatrix Willius
  2. Valentina Database ADK
  3. Tuesday, May 02 2017, 06:24 AM
  4.  Subscribe via email
Me again with a speed problem.

In a listbox I show the main data on-demand when the listbox rows show. But for house-keeping I need some keys in a dictionary. Why does this take 5 seconds for 10.000 rows?


dim HasNextRecord as Boolean = DataCursor.FirstRecord
dim currentPosition as Integer = 1
dim ValDataField as VField = DataCursor.Field(DataField)
dim ValDataProviderField as VField = DataCursor.Field(DataProviderField)
dim DataValue, DataProviderValue as String
while HasNextRecord and currentPosition <= myRowCount

DataValue = ValDataField.GetString
DataProviderValue = ValDataProviderField.GetString

RowIDInformation(currentPosition) = new Dictionary
RowIDInformation(currentPosition).Value(DataField) = DataValue
RowIDInformation(currentPosition).Value(DataProviderField) = DataProviderValue

if currentDataFieldValue = 0 then
currentDataFieldValue = val(DataValue)
elseif currentDataFieldValue <> val(DataValue) then
currentDataFieldValue = -1
end if

currentPosition = currentPosition + 1
HasNextRecord = DataCursor.NextRecord
wend


I've had a look with Instruments at what happens here. It told me that 76% of all time is spent in

HasNextRecord = DataCursor.NextRecord

Of course, I could load the necessary data on-demand. But when a user does a Cmd-A then I need the information.

Any ideas?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Comment
There are no comments made yet.
Beatrix Willius Accepted Answer
I forgot: Xojo 20171, Valentina 6.latest, 8 GB database on SATA harddisk. El Capitan.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Ivan Smahin Accepted Answer
Generally, cursor navigation is a bit more complex than simply choosing next record. It loads values and probably, you have some calculations in select list which makes it slow. Could you show the original sql statement?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Beatrix Willius Accepted Answer
That's the basic query:

SELECT Message.MailboxID,Message.RecID,Message.OriginatorFrom as [From],Message.ReceiverTo as [To],Message.MainSubject as [Subject],Message.MainDateTime as [Date],Message.AttachmentCount as [Att],MailboxName(Mailbox.Mailboxpath) as [Mailbox],lower(Message.OriginatorFrom) as [Order_From],lower(Message.ReceiverTo) as [Order_To],lower(Message.MainSubject) as [Order_Subject]

FROM (Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID)

ORDER By [Att] ASC, [Order_Subject] ASC
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Ivan Smahin Accepted Answer
So, please try to exclude MailboxName() function and see - does it improve performance?
BTW, could you show that function?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Beatrix Willius Accepted Answer
The function seems to be the problem:

create or replace function MailboxName(theMailboxPath String) returns string begin case when lower(list_nthitem(theMailboxPath, 1, ':::')) = 'inbox' or lower(list_nthitem(theMailboxPath, 1, ':::')) = 'sent' or lower(list_nthitem(theMailboxPath, 1, ':::')) = 'sent messages' then return list_nthitem(theMailboxPath, 1, ':::'); else return list_nthitem(theMailboxPath, list_itemcount(theMailboxPath, ':::'), ':::'); end case end

Out of an mailbox path like "account:::some folder:::other folder" it picks the "other folder". When you have "Inbox:::something" then the result is "Inbox".

The database I'm testing with has 10 records in the mailbox table.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
Beatrix Willius Accepted Answer
Guys, can you pretty please disable your aggressive emojis in the forum?
Comment
What are you talking about?
  1. Ivan Smahin
  2. 3 years ago
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 6
Ivan Smahin Accepted Answer
Well, it could be solution to have one more field "Mailbox" (not calculated one but regular) in Mailbox table. And afterInsert (probably afterUpdate as well) trigger for that table which will call MailboxName() function and store result into that field.
It will be done once - not all the time.

Something like this one:

CREATE OR REPLACE TRIGGER tr1 AFTER INSERT ON Mailbox FOR EACH ROW
BEGIN
UPDATE Mailbox SET MailboxFld = Mailboxpath( new.Mailboxpath ) where Mailboxpath = new.Mailboxpath
END;



SELECT
Message.MailboxID,
Message.RecID,
Message.OriginatorFrom as [From],
Message.ReceiverTo as [To],
Message.MainSubject as [Subject],
Message.MainDateTime as [Date],
Message.AttachmentCount as [Att],
MailboxFld as [Mailbox],
lower(Message.OriginatorFrom) as [Order_From],
lower(Message.ReceiverTo) as [Order_To],
lower(Message.MainSubject) as [Order_Subject]

FROM (Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID)

ORDER By [Att] ASC, [Order_Subject] ASC
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 7
Beatrix Willius Accepted Answer
Added a field with a method:

if (lower(list_nthitem(MailboxPath, 1, ':::')) = 'inbox' or lower(list_nthitem(MailboxPath, 1, ':::')) = 'sent' or lower(list_nthitem(MailboxPath, 1, ':::')) = 'sent messages', list_nthitem(MailboxPath, 1, ':::'), list_nthitem(MailboxPath, list_itemcount(MailboxPath, ':::'), ':::'))

and now the data from the cursor loads very fast. That's really really odd.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 8
Ivan Smahin Accepted Answer
Do you see speed improvement because of calculated field instead of calling UDF in query select list
or because of "if(,,)" instead of "case"?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 9
Beatrix Willius Accepted Answer
@Ivan: I hope it's not the if instead of the case.

Regarding emojis: in the text below is a ":" followed by an "o", which makes an emoji


"account:::some folder:::other folder"
Comment
use CODE tag
  1. Ruslan Zasukhin
  2. 3 years ago
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 10
Ruslan Zasukhin Accepted Answer
Beatrix,

This is not odd if you get speedup thanks to usage of Calculated Fields. In fact this is one of reasons they exists.

Btw, you have made it indexed? I think yes.
Thanks to this on disk is created one more INDEX -- which helps get better speed of search.
In the same time, it will make slower a little operations that modify records.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 11
Beatrix Willius Accepted Answer
Hm... I don't get this reasoning, Ruslan. I always thought that an index would help with the speed of getting a cursor. Here the problem was looping through the cursor. Why would an index help here? Also, the database has about 10 records in the table where I added the method.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 12
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories