1. Beatrix Willius
  2. Valentina Database ADK
  3. Sunday, March 19 2017, 08:16 AM
  4.  Subscribe via email
In most places I either get data on demand for showing the interface or the data is only for one record. One table with hierarchical paths needs to be loaded fully. Recently, I had the bad idea to put 1000 records into this database. I found some places in my code which makes loading this very slow. One place is loading this table:


Private Sub getRawData(theCursor as VCursor)

'get the data from the cursor, but only the RecIDs and the next column

call theCursor.FirstRecord
dim RecIDColumn, WhereClauseColumn, OtherColumn as Integer
for CheckColumn as Integer = 1 to 3
if theCursor.Field(CheckColumn).Name = "RecID" then
RecIDColumn = CheckColumn
Elseif theCursor.Field(CheckColumn).Name = "WhereClause" then
WhereClauseColumn = CheckColumn
elseif theCursor.Field(CheckColumn).Name <> "RecID" then
OtherColumn = CheckColumn
end if
next

Do
RecIDList.Append theCursor.Field(RecIDColumn).GetString
WhereList.Append theCursor.Field(WhereClauseColumn).GetString
DataList.Append theCursor.Field(OtherColumn).GetString
loop until not theCursor.NextRecord

End Sub


For 1000 records this takes 2 seconds! Any ideas how to speed this up?

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 think I found the problem. It's not the cursor but the functions I use. The cursor is:

MailboxCursor.setValues(1 , 2, 1, "select RecID, MailboxPath, WhereClause, PathSort(NaturalSort(MailboxPath)) from mailbox order by 4";)


PathSort is for sorting anything starting with Inbox together, then Sent. This doesn't seem to be the problem. But NaturalSort makes everything slow:

CREATE OR REPLACE FUNCTION NaturalSort(thePath STRING) RETURNS STRING BEGIN CASE when right(list_nthitem(thePath, 1, ':::'), 1) = 0 And right(list_nthitem(thePath, 1, ':::'), 2) = 0 And right(list_nthitem(thePath, 1, ':::'), 3) = 0 then Return thePath; when right(list_nthitem(thePath, 1, ':::'), 3) > 0 then Return replace(thePath, list_nthitem(thePath, 1, ':::'), Replace(list_nthitem(thePath, 1, ':::'), Right(list_nthitem(thePath, 1, ':::'), 3), concat('00', right(list_nthitem(thePath, 1, ':::'), 3)))); when right(list_nthitem(thePath, 1, ':::'), 2) > 0 then Return replace(thePath, list_nthitem(thePath, 1, ':::'), Replace(list_nthitem(thePath, 1, ':::'), Right(list_nthitem(thePath, 1, ':::'), 2), concat('000', right(list_nthitem(thePath, 1, ':::'), 2)))); when right(list_nthitem(thePath, 1, ':::'), 1) > 0 then Return replace(thePath, list_nthitem(thePath, 1, ':::'), Replace(list_nthitem(thePath, 1, ':::'), Right(list_nthitem(thePath, 1, ':::'), 1), concat('0000', right(list_nthitem(thePath, 1, ':::'), 1)))); END CASE END


Except that it's ugly as heck, the function doesn't really do anything complicated. Can I speed this up or would it be easier to just use an extra field?

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. # 1
Beatrix Willius Accepted Answer
Any thoughts on this issue?

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. # 2
Ivan Smahin Accepted Answer
The same function, but better formatted:


CREATE OR REPLACE FUNCTION NaturalSort(thePath STRING) RETURNS STRING
BEGIN
CASE when
right( list_nthitem( thePath, 1, ':::' ), 1 ) = 0
And right( list_nthitem( thePath, 1, ':::' ), 2 ) = 0
And right( list_nthitem( thePath, 1, ':::' ), 3 ) = 0
then
Return thePath;
when
right( list_nthitem( thePath, 1, ':::' ), 3 ) > 0
then
Return replace
(
thePath,
list_nthitem( thePath, 1, ':::' ),
Replace
(
list_nthitem( thePath, 1, ':::' ),
Right( list_nthitem( thePath, 1, ':::' ), 3 ),
concat
(
'00',
right( list_nthitem( thePath, 1, ':::' ), 3 )
)
)
);
when
right( list_nthitem( thePath, 1, ':::' ), 2 ) > 0
then
Return replace
(
thePath,
list_nthitem( thePath, 1, ':::' ),
Replace
(
list_nthitem( thePath, 1, ':::' ),
Right( list_nthitem( thePath, 1, ':::' ), 2 ),
concat
(
'000',
right( list_nthitem( thePath, 1, ':::' ), 2 )
)
)
);
when
right( list_nthitem( thePath, 1, ':::' ), 1 ) > 0
then
Return replace
(
thePath,
list_nthitem( thePath, 1, ':::' ),
Replace
(
list_nthitem( thePath, 1, ':::' ),
Right( list_nthitem( thePath, 1, ':::' ), 1 ),
concat
(
'0000',
right( list_nthitem( thePath, 1, ':::' ), 1 )
)
)
);
END CASE
END
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Ruslan Zasukhin Accepted Answer
Hi Beatrix,

1) You can use in Vstudio TUNE mode in SQL Editor, to see some times of each step.
Kind of profiles.

2) Also I think it will show that ORDER BY 4 -- is slow part.
You have no index, so each time ORDER BY should build some index on the fly.

Most probably we also can improve this area ..


3) You can improve it, if this function calculates stable result for a record.
Then yes - you can use Calculated Field and index it.
Then order by will use it.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Ivan Smahin Accepted Answer
You see - you are doing


list_nthitem( thePath, 1, ':::' )


many times. It will be faster (probably fast enough) to calculate it once:


CREATE OR REPLACE FUNCTION NaturalSort(thePath STRING) RETURNS STRING
BEGIN
DECLARE firstItem String;
SET firstItem = list_nthitem( thePath, 1, ':::' );

CASE when
right( firstItem, 1 ) = 0
And right( firstItem, 2 ) = 0
And right( firstItem, 3 ) = 0
then
Return thePath;
when
right( firstItem, 3 ) > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
Right( firstItem, 3 ),
concat
(
'00',
right( firstItem, 3 )
)
)
);
when
right( firstItem, 2 ) > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
Right( firstItem, 2 ),
concat
(
'000',
right( firstItem, 2 )
)
)
);
when
right( firstItem, 1 ) > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
Right( firstItem, 1 ),
concat
(
'0000',
right( firstItem, 1 )
)
)
);
END CASE
END
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
Ivan Smahin Accepted Answer
Next iteration:
It is easy to see that


right( firstItem, 1 ) = 0
right( firstItem, 2 ) = 0
right( firstItem, 3 ) = 0


will be calculated in most of cases. Probably you should calculate them once as well.


CREATE OR REPLACE FUNCTION NaturalSort(thePath STRING) RETURNS STRING
BEGIN
DECLARE firstItem String;
SET firstItem = list_nthitem( thePath, 1, ':::' );

DECLARE last_1_Symbol, last_2_Symbols, last_3_Symbols String;
SET last_1_Symbol = right( firstItem, 1 );
SET last_2_Symbols = right( firstItem, 2 );
SET last_3_Symbols = right( firstItem, 3 )

CASE when
last_1_Symbol = 0
And last_2_Symbols = 0
And last_3_Symbols = 0
then
Return thePath;
when
last_3_Symbols > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
last_3_Symbols,
concat
(
'00',
last_3_Symbols
)
)
);
when
last_2_Symbols > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
last_2_Symbols,
concat
(
'000',
last_2_Symbols
)
)
);
when
last_1_Symbol > 0
then
Return replace
(
thePath,
firstItem,
Replace
(
firstItem,
last_1_Symbol,
concat
(
'0000',
last_1_Symbol
)
)
);
END CASE
END
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 6
Ivan Smahin Accepted Answer
Also you can try lpad function:


select lpad( 'a', 5, '0')
=> '0000a'


instead of tricky replace/concat...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 7
Ivan Smahin Accepted Answer
And probably better to have a calculated field with lpad instead of whole procedure.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 8
Beatrix Willius Accepted Answer
Thanks a lot for the ideas, Ivan. Obviously, I'm a total newbie in SQL functions.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 9
  • 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