<HTML>
<HEAD>
<TITLE>Re: Speed up SQL</TITLE>
</HEAD>
<BODY>
<FONT FACE="Lucida Grande"><SPAN STYLE='font-size:11pt'>On 1/9/12 7:46 AM, "Beatrix Willius" <<a href="bwillius@gmx.de">bwillius@gmx.de</a>> wrote:<BR>
<BR>
Hi Beatrix,<BR>
<BR>
Yes, tune info not helps too much ...<BR>
And it seems it loose some operation about <BR>
<BR>
But I have take look on query again, <BR>
Why you use HAVING to filter records of Message table?<BR>
You can try do this in WHERE clause ?<BR>
<BR>
WHERE <BR>
</SPAN><FONT SIZE="1"><SPAN STYLE='font-size:9pt'>Message.MessageBody REGEX '(?i)osmer</SPAN></FONT><SPAN STYLE='font-size:11pt'> <BR>
<BR>
Also keep in mind it is possible specify additional conditions even in the JOIN clauses:<BR>
<BR>
</SPAN><FONT SIZE="1"><SPAN STYLE='font-size:9pt'>FROM <BR>
Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID AND Message.MessageBody REGEX '(?i)osmer<BR>
</SPAN></FONT><SPAN STYLE='font-size:11pt'><BR>
<BR>
<BR>
======================<BR>
</SPAN><FONT SIZE="1"><SPAN STYLE='font-size:9pt'>SELECT Message.MailboxID, Message.RecID, Message.OriginatorFrom as [From], Message.ReceiverTo as [To], Message.MainSubject as [Subject], <BR>
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] <BR>
FROM <BR>
Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID <BR>
GROUP BY <BR>
Message.MailboxID, Message.RecID, Message.OriginatorFrom,<BR>
Message.ReceiverTo, Message.MainSubject, Message.MainDateTime, Message.AttachmentCount, Mailbox.MailboxPath <BR>
HAVING <BR>
RecID in (select RecID from Message where Message.MessageBody REGEX '(?i)osmer') <BR>
ORDER By [Date] ASC<BR>
</SPAN></FONT><SPAN STYLE='font-size:11pt'><BR>
<BR>
<BR>
======================<BR>
<FONT COLOR="#0000FF">> Yes, in VStudio.<BR>
> <BR>
</FONT>FOUND in the QueryPool: NO<BR>
FOR SQL QUERY TIME = 929 :<BR>
DO SELECT : TIME = 929 : ROWS = 2<BR>
DO SIMPLE SELECT : TIME = 929 : ROWS = 2<BR>
<BR>
DO Build JoinTable : TIME = 3 : ROWS = 9519<BR>
DO For each Table from 2 resolve its WHERE conditions that we can do before <FONT COLOR="#0000FF"> Joins : TIME = 0 :<BR>
<BR>
FOR Table [Message] DO SelectAllRecords : TIME = 0 : ROWS = 9519 <BR>
FOR Table [Mailbox] DO SelectAllRecords : TIME = 0 : ROWS = 11<BR>
<BR>
DO Joins : TIME = 3 : ROWS = 9519<BR>
DO [Mailbox] INNER JOIN [Message] ON [Link_1](Sort Merge) : TIME = 2 : ROWS = 9519<BR>
<BR>
FOR Table [tmp_table_17] DO resolve WHERE statement : TIME = 414 : ROWS = 2<BR>
FOR Predicate [OR] TIME = 17 : ROWS = 2<BR>
DO OR Non-Indexed operands : TIME = 17 : ROWS = 2<BR>
<BR>
> FOR Field [From] DO FindRegEx : TIME = 12 : ROWS = 1<BR>
> FOR Field [To] DO FindRegEx : TIME = 1 : ROWS = 0<BR>
> FOR Field [Subject] DO FindRegEx : TIME = 3 : ROWS = 0<BR>
<BR>
> FOR EXPR DO Scan of column : TIME = 0 : ROWS = 1<BR>
> FOR EXPR DO Scan of column : TIME = 0 : ROWS = 0<BR>
> FOR EXPR DO Scan of column : TIME = 1 : ROWS = 0<BR>
> FOR EXPR DO Scan of column : TIME = 0 : ROWS = 0<BR>
<BR>
> DO ORDER BY : TIME = 2 : ROWS = 2<BR>
> DO Cursor Creation : TIME = 0 :<BR>
> DO Record Locks : TIME = 0 :<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
> Sometimes I despair over you guys:<BR>
> <BR>
> - "Tunes" reminds me of iTunes. What about "Query data" or something like <BR>
> this?<BR>
</FONT><BR>
<FONT COLOR="#0000FF">> - It was only possible to copy this data line by line. Please...<BR>
</FONT><BR>
Please add request into mantis.<BR>
Sergey can you self this self ?<BR>
<BR>
<BR>
Beatrix, also you can enable TuneMode in the engine itself or in vserver.<BR>
Then you will get Tune_xxxx.log file <BR>
<BR>
<BR>
<BR>
-- <BR>
Best regards,<BR>
<BR>
Ruslan Zasukhin<BR>
VP Engineering and New Technology<BR>
Paradigma Software, Inc<BR>
<BR>
Valentina - Joining Worlds of Information <BR>
<a href="http://www.paradigmasoft.com">http://www.paradigmasoft.com</a><BR>
<BR>
[I feel the need: the need for speed]<BR>
</SPAN></FONT>
</BODY>
</HTML>