Improve speed of SQL query

Sign In

Sign in to your account

If you are new here please register for an account

Username:

Password:

Запомнить меня

Забыли пароль?

Решено Improve speed of SQL query

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.

Комментарий

There are no comments made yet.

Ответы (2)

Ivan Smahin Ответ принят

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.

Комментарий

There are no comments made yet.

Beatrix Willius Ответ принят

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

Комментарий

There are no comments made yet.

Страница :
1

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

Чтобы оставить ответ, необходимо зарегистрироваться

Вы должны быть зарегистрированы, чтобы иметь возможность оставить ответ. Водите, используя форму входа или зарегистрируйтесь, если вы впервые на сайте. Зарегистрируйтесь здесь »

Забыли пароль?

Решено Improve speed of SQL query

Categories

Reports ADK

Reporting Solutions

Reports for...

Поддержка

О Компании