Speed up SQL

[Ruslan Zasukhin]

On 1/9/12 7:46 AM, "Beatrix Willius" <bwillius at gmx.de> wrote:

Hi Beatrix,

Yes, tune info not helps too much ...
And it seems it loose some operation about

But I have take look on query again,
Why you use HAVING to filter records of Message table?
You can try do this in WHERE clause ?

WHERE 
    Message.MessageBody REGEX '(?i)osmer

Also keep in mind it is possible specify additional conditions even in the
JOIN clauses:

FROM 
        Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID AND
Message.MessageBody REGEX '(?i)osmer



======================
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
GROUP BY 
     Message.MailboxID, Message.RecID, Message.OriginatorFrom,
     Message.ReceiverTo, Message.MainSubject, Message.MainDateTime,
Message.AttachmentCount, Mailbox.MailboxPath
HAVING 
    RecID in (select RecID from Message where Message.MessageBody REGEX
'(?i)osmer') 
ORDER By [Date] ASC



======================
> Yes, in VStudio.
> 
FOUND in the QueryPool: NO
FOR SQL QUERY TIME = 929 :
     DO SELECT : TIME = 929 : ROWS = 2
       DO SIMPLE SELECT : TIME = 929 : ROWS = 2
 
        DO Build JoinTable : TIME = 3 : ROWS = 9519
         DO For each Table from 2 resolve its WHERE conditions that we can
do before  Joins : TIME = 0 :
        
       FOR Table [Message]  DO SelectAllRecords : TIME = 0 : ROWS = 9519
       FOR Table [Mailbox]  DO SelectAllRecords : TIME = 0 : ROWS = 11

           DO Joins : TIME = 3 : ROWS = 9519
               DO [Mailbox] INNER JOIN [Message] ON [Link_1](Sort Merge) :
TIME = 2 : ROWS = 9519

       FOR Table [tmp_table_17]  DO resolve WHERE statement : TIME = 414 :
ROWS = 2
          FOR Predicate [OR] TIME = 17 : ROWS = 2
             DO OR Non-Indexed operands : TIME = 17 : ROWS = 2

>   FOR Field [From]     DO FindRegEx : TIME = 12 : ROWS = 1
>   FOR Field [To]         DO FindRegEx : TIME = 1 : ROWS = 0
>   FOR Field [Subject]  DO FindRegEx : TIME = 3 : ROWS = 0

>   FOR EXPR  DO Scan of column : TIME = 0 : ROWS = 1
>   FOR EXPR  DO Scan of column : TIME = 0 : ROWS = 0
>   FOR EXPR  DO Scan of column : TIME = 1 : ROWS = 0
>   FOR EXPR  DO Scan of column : TIME = 0 : ROWS = 0

>   DO ORDER BY : TIME = 2 : ROWS = 2
>   DO Cursor Creation : TIME = 0 :
>   DO Record Locks : TIME = 0 :





 
> Sometimes I despair over you guys:
> 
> - "Tunes" reminds me of iTunes. What about "Query data" or something like
> this?

> - It was only possible to copy this data line by line. Please...

Please add request into mantis.
Sergey can you self this self ?


Beatrix, also you can enable TuneMode in the engine itself or in vserver.
Then you will get  Tune_xxxx.log file



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20120110/2ba6ddab/attachment.html>