Speed up SQL

[Ruslan Zasukhin]

On 1/8/12 7:03 PM, "Beatrix Willius" <bwillius at gmx.de> wrote:

Hi Beatrix,

> why is the following SQL so very slow? It takes more than 5 seconds in a
> database with 10.000 records in the message table. What can I do to make it
> faster? The table structure is Mailbox -> Message -> Attachment.
> 
> 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
> 
> If I execute only
> 
> select RecID from Message where Message.MessageBody REGEX '(?i)osmer'
> 
> then the query takes less than 1 second. The same is true for a simple search
> condition like 
> 
>  [Subject] REGEX '(?i)osmer'
> 
> instead of the nested sql. I just tried the tune mode, which is very very
> unhelpful.

In vstudio?

And what shows Tune panel after this query ?
Please copy paste it here



-- 
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]