Speed up SQL

[Beatrix Willius]

Have been playing around with my SQL again.

Using Ruslans advice I get the SQL below, which takes from 0.6 seconds (okay) to 3 seconds (not okay) for the same data.

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 and Message.MessageBody 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' OR RecID in (select MailID from Attachment where Attachment.AttachmentName REGEX '(?i)osmer') 

ORDER By [Date] ASC

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')

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

PS: The SQL Builder of VStudio is really only capable of creating simple SQL. Even lowly MS Access has a better builder.

Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals