Toggle Submenu
  1. Forums
  2. Recent
  3. Kategorien
  4. Tags
  5. Benutzer
Antwort hinzufügen Antworten ansehen (12)

Gelöst Getting data from cursor slow

  1. Beatrix Willius
    Beatrix Willius
  2. Valentina Database ADK
  3. Dienstag, Mai 02 2017, 06:24 AM
  4.  Abonnieren via E-Mail
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
Kommentar
There are no comments made yet.
Antworten (12)
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
I forgot: Xojo 20171, Valentina 6.latest, 8 GB database on SATA harddisk. El Capitan.
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Ivan Smahin
Ivan Smahin Akzeptierte Antwort
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?
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Ivan Smahin
Ivan Smahin Akzeptierte Antwort
So, please try to exclude MailboxName() function and see - does it improve performance?
BTW, could you show that function?
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
Guys, can you pretty please disable your aggressive emojis in the forum?
Kommentar
Ivan Smahin
What are you talking about?
  1. Ivan Smahin
  2. vor etwa 6 Jahren
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Ivan Smahin
Ivan Smahin Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Ivan Smahin
Ivan Smahin Akzeptierte Antwort
Do you see speed improvement because of calculated field instead of calling UDF in query select list
or because of "if(,,)" instead of "case"?
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
@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"
Kommentar
Ruslan Zasukhin
use CODE tag
  1. Ruslan Zasukhin
  2. vor etwa 6 Jahren
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Ruslan Zasukhin
Ruslan Zasukhin Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
Beatrix Willius
Beatrix Willius Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
  1. vor über einem Monat
  2. Valentina Database ADK
  3. Permalink zu dieser Antwort
  • Seite :
  • 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