1. Beatrix Willius
  2. Valentina Database ADK
  3. Saturday, March 16 2024, 03:22 PM
  4.  Subscribe via email
I have a seemingly simple SQL query:

select RecID, MailboxPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox order by 4

For thousands of records the query is quite slow. The problem are PathSort and the order.

MailboxPathNatSort is a VarChar field with a method. PathSort is defined as a function. Both have rather long definitions. Can I combines the method with the function? What else can I do to make the query faster?

I've attached some screenshots from Valentina Studio.
Attachments (2)
Comment
There are no comments made yet.
Ivan Smahin Accepted Answer
Hi Beatrix,

>> Can I combines the method with the function?
Sure

>> What else can I do to make the query faster?
- probably PathSort() can be improved/simplified;
- to have a material column and fill it by the trigger (onInsert, onUpdate) instead of calculate it again and again doing such select query.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Beatrix Willius Accepted Answer
Yeah, I can do a trigger if I can't improve the functions. But I don't think that I can simplify them.

PathSort sorts mailboxes so that Inbox is followed by Sent, Views and the rest of the mailboxes:



CREATE OR REPLACE FUNCTION PathSort(thePath STRING) RETURNS STRING
BEGIN
CASE
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'inbox' THEN
RETURN CONCAT('1:', REPLACE(thePath, ':::', CHAR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent' THEN
RETURN CONCAT('2:', REPLACE(thePath, ':::', CHAR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent messages' THEN
RETURN CONCAT('2:', REPLACE(thePath, ':::', CHAR(10)));

WHEN thePath = 'Trash' THEN
RETURN CONCAT('7:', REPLACE(thePath, ':::', CHAR(10)));

WHEN thePath = 'Spam' THEN
RETURN CONCAT('3:', REPLACE(thePath, ':::', CHAR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'views' THEN
RETURN CONCAT('4:', REPLACE(thePath, ':::', CHAR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'Mailboxes' THEN
RETURN CONCAT('5:', REPLACE(thePath, ':::', CHAR(10)));

ELSE
RETURN CONCAT('6:', REPLACE(thePath, ':::', CHAR(10)));
END CASE;
END


And MailboxPathNatSort is a custom natural sort:


CASE
WHEN RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1) = 0 AND RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2) = 0 AND RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3) = 0 THEN
MailboxPath
WHEN RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3) > 0 AND RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3) = TRIM(RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3)) THEN
REPLACE(MailboxPath, list_nthitem(MailboxPath, 1, ':::'), REPLACE(list_nthitem(MailboxPath, 1, ':::'), RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3), CONCAT('00', RIGHT(list_nthitem(MailboxPath, 1, ':::'), 3))))
WHEN RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2) > 0 AND RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2) = TRIM(RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2)) THEN
REPLACE(MailboxPath, list_nthitem(MailboxPath, 1, ':::'), REPLACE(list_nthitem(MailboxPath, 1, ':::'), RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2), CONCAT('000', RIGHT(list_nthitem(MailboxPath, 1, ':::'), 2))))
WHEN RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1) > 0 AND RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1) = TRIM(RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1)) THEN
REPLACE(MailboxPath, list_nthitem(MailboxPath, 1, ':::'), REPLACE(list_nthitem(MailboxPath, 1, ':::'), RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1), CONCAT('0000', RIGHT(list_nthitem(MailboxPath, 1, ':::'), 1))))
END
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
  • 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