1. Beatrix Willius
  2. Valentina Server
  3. Четверг, Декабрь 21 2023, 10:32 AM
  4.  Подписаться через email
Got a really weird problem with a query for Valentina Server 13.6.

Tables are mailbox for mailboxes, message for emails. The basic query gets information from the mailbox and the message table. Usually only one mailbox is shown with a where clause for the mailbox. The exception is "All Emails" where - as the name says - all emails are shown independent of the mailboxes.

The query for this has a placeholder where clause "(1)". This is super slow in Valentina Studio for Valentina Server.

The following query takes 10 seconds (!!!):

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',Mailbox.MailboxpathName 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) WHERE ( Message.username in ('sa', '') ) AND ( 1 ) ORDER By "Date" ASC

Notice the "AND (1)".

The query without this part takes 0,35 seconds:

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',Mailbox.MailboxpathName 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) WHERE ( Message.username in ('sa', '') ) ORDER By "Date" ASC

There is no such behavior for the Valentina ADK for Xojo. I could finagle the "AND (1)" out of my SQL building code - maybe. But I would like to know if this is a bug or my stupidity.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Hi Beatrix,

You wrote about placeholder - do you mean the binding?

...AND (:1)


BTW, it should be the warning about unused binding param in this case (because there is no placeholder in the query, but I suppose you provide some binding param).
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Could you try it in vStudio, and look at "tune info"? May be there are different execution plans or something like that.

vServer just passes the query to vKernel and takes the results back, sending them to the vClient. In the case of client-side cursor, vServer sends all results at once - one more reason for such speed.
Комментарий
There are no comments made yet.
Beatrix Willius Ответ принят
No, the placeholder is for the where query for the mailboxes and is literally a "( 1 )" because the value is always true.

I've attached screenshots from the fast and the slow query.
Вложения
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Hmm, it seems the optimizer was not able to recognize "AND (1)" as a redundant condition, moreover, it does not take into account that it is const condition and can be executed once...

Could you report it to the Mantis please?
I will check it, thank you for the logs.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Just a little note - you can put the only true or false at that placeholder - right?
So it can be optimized in your app


if( yourBindingParam == true )
sqlExecute( "theQuery without this bind param at all, since it is true and can not change the result")
else
// Do nothing, because no rows will be in the result
Комментарий
There are no comments made yet.
Beatrix Willius Ответ принят
I've made a ticket at http://www.valentina-db.com/bt/view.php?id=9304 .

The code is due for rewriting anyways. For now I've made a simple workaround to take out the "and (1)" from the SQL.
Комментарий
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.

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