At Wednesday 12/7/2005 03:54 AM, Harald Wagener wrote:
Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )
For what it’s worth, we’ve experienced this when searching on Subject &
Content together (RT 3.4.2, Oracle 9). Either a subject search or a content
search is OK, but not both together. I haven’t had time to profile the
query, so I can’t shed any light on a solution.
I just had a look at our Oracle9 instance with our production RT on it
and tried to run that query but it contains atleast one mistake. Could
be the fact that we’re running RT-3.4.3 and not 3.2.2
So this is the query that is run on our instance when asking for subject
like ‘structuur’ and content matching ‘betrekking’.
SELECT COUNT (DISTINCT main.ID)
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((transactions_1.objecttype = ‘RT::Ticket’))
AND ((main.effectiveid = main.ID))
AND ((main.status != ‘deleted’))
AND ((main.TYPE = ‘ticket’))
AND ( (LOWER (main.subject) LIKE ‘%structuur%’)
AND ( (contains (attachments_2.content, ‘betrekking’) > 0)
AND (attachments_2.transactionid = transactions_1.ID)
AND (main.ID = transactions_1.objectid)
)
)
There are a couple of things to be noted:
- we modify the query if it searches for content (contains clause)
- there is an appropriate index for this.
- we must explicitly use wildcards if we want to use them (SB does the
reverse)
Execution time of this query is 350ms 
What would kill its performance is using wildcards at the front of the
search string (‘%betrekking%’), killing its index. This will be slow for
any database relying on indices.
See the following example from our dbiprof output:
#####[ 1 ]###########################################################
Count : 15
Total Time : 991.093903 seconds
Longest Time : 859.530297 seconds
Shortest Time : 0.000017 seconds
Average Time : 66.072927 seconds
Key 1 :
/* Formatted on 2005/12/09 10:57 (Formatter Plus v4.8.7) */
SELECT COUNT (DISTINCT main.ID)
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((transactions_1.objecttype = ‘RT::Ticket’))
AND ((main.effectiveid = main.ID))
AND ((main.status != ‘deleted’))
AND ((main.TYPE = ‘ticket’))
AND ( (main.queue = ‘27’)
AND ( (contains (attachments_2.content, ‘%fixed%’) > 0)
AND (attachments_2.transactionid = transactions_1.ID)
AND (main.ID = transactions_1.objectid)
)
)
And the corresponding entry from the first query:
#####[ 37 ]###########################################################
Count : 15
Total Time : 2.809959 seconds
Longest Time : 2.155377 seconds
Shortest Time : 0.000011 seconds
Average Time : 0.187331 seconds
Key 1 :
Hope this will help you Steve and maybe other users using Oracle with
problems searching on Content.
Joop
Joop van de Wege JoopvandeWege@mococo.nl
