Speed up SQL

[Ruslan Zasukhin]

On 1/15/12 11:11 AM, "Beatrix Willius" <bwillius at gmx.de> wrote:

> If I remove the second embedded select then the query becomes very fast. But
> how do I add this do the from? Changing the from gives me the second SQL, but
> VStudio barfs at the group by clause with "Unexpected token: Group".
> 
> 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 Attachment ON Attachment.MailID = Message.RecID
> Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID  and
> (Message.MessageBody REGEX '(?i)osmer' or Attachment.AttachmentName REGEX
> '(?i)osmer')

May be try add ()  around last ON

 Message INNER JOIN Mailbox ON (Message.MailboxID = Mailbox.RecID  and
 (Message.MessageBody REGEX '(?i)osmer' or Attachment.AttachmentName REGEX
 '(?i)osmer'))


 
> GROUP BY Message.MailboxID, Message.RecID, Message.OriginatorFrom,
> Message.ReceiverTo, Message.MainSubject, Message.MainDateTime,
> Message.AttachmentCount, Mailbox.MailboxPath
> 
> HAVING [From] REGEX '(?i)osmer' OR [To] REGEX '(?i)osmer' OR [Subject] REGEX
> '(?i)osmer' 
> 
> ORDER By [Date] ASC

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