Hi all,
(Appologies for the HTML mail, but it made the formatting below much
easier…)
Well, I haven’t seen anything on the list for a little while, so I thought I
would chime in 
When I am searching on multiple custom fields there is a serious performance
problem. The query is show on screen as follows:
Current search criteria
Queue = Client Issues [delete]
CF.Client Issues.{Client Reference} = 94 [delete]
CF.Client Issues.{Client Reference} = 49 [delete]
CF.Client Issues.{Client Reference} = 97 [delete]
CF.Client Issues.{Client Reference} = 110 [delete]
CF.Client Issues.{Client Reference} = 71 [delete]
CF.Client Issues.{Client Reference} = 53 [delete]
The SQL that it generates (with seachbuilder 0.86 and 0.89_2) is:
(formatted to make it easier to read, and some extraneous brackets removed
for the same reason)
SELECT count(main.id)
FROM ((((((Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_6
ON ( main.id = TicketCustomFieldValues_6.Ticket)
AND( (TicketCustomFieldValues_6.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_3
ON ((TicketCustomFieldValues_3.CustomField = ‘40’))
AND( main.id = TicketCustomFieldValues_3.Ticket))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_2
ON ( main.id = TicketCustomFieldValues_2.Ticket)
AND( (TicketCustomFieldValues_2.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_5
ON ((TicketCustomFieldValues_5.CustomField = ‘40’))
AND( main.id = TicketCustomFieldValues_5.Ticket))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_4
ON ( main.id = TicketCustomFieldValues_4.Ticket)
AND( (TicketCustomFieldValues_4.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_1.Ticket)
AND( (TicketCustomFieldValues_1.CustomField = ‘40’)))
WHERE main.EffectiveId = main.id
AND main.Type = ‘ticket’
AND ( ( TicketCustomFieldValues_1.Content = ‘49’
OR TicketCustomFieldValues_2.Content = ‘94’
OR TicketCustomFieldValues_3.Content = ‘97’
OR TicketCustomFieldValues_4.Content = ‘110’
OR TicketCustomFieldValues_5.Content = ‘71’
OR TicketCustomFieldValues_6.Content = ‘53’ )
AND main.Queue = ‘5’ );
When this gets thrown against our current (postgres) database, it takes 29
seconds to run. An almost identical query is then executed to actually get
the rows that were just counted. The problem here is clear to see: six left
joins of the same table.
When this is simplified to the following:
SELECT count(main.id)
FROM (Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_6.Ticket)
AND(TicketCustomFieldValues_6.CustomField = ‘40’))
WHERE (main.EffectiveId = main.id)
AND main.Type = ‘ticket’
AND ( ( TicketCustomFieldValues_1.Content = ‘49’
OR TicketCustomFieldValues_2.Content = ‘94’
OR TicketCustomFieldValues_3.Content = ‘97’
OR TicketCustomFieldValues_4.Content = ‘110’
OR TicketCustomFieldValues_5.Content = ‘71’
OR TicketCustomFieldValues_6.Content = ‘53’ )
AND main.Queue = ‘5’ );
The query goes down to 6.7 seconds. Already a significant improvement. If we
can further simplify this to:
SELECT count(main.id)
FROM (Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_1.Ticket)
AND(TicketCustomFieldValues_1.CustomField = ‘40’))
WHERE (main.EffectiveId = main.id)
AND main.Type = ‘ticket’
AND ( TicketCustomFieldValues_1.Content = ‘49’
OR TicketCustomFieldValues_1.Content = ‘94’
OR TicketCustomFieldValues_1.Content = ‘97’
OR TicketCustomFieldValues_1.Content = ‘110’
OR TicketCustomFieldValues_1.Content = ‘71’
OR TicketCustomFieldValues_1.Content = ‘53’ )
AND main.Queue = ‘5’;
The execution time goes down to 4.8 seconds. Better still. However, if we
can simplify this to the following:
SELECT count(main.id)
FROM Tickets main,
TicketCustomFieldValues as TicketCustomFieldValues_1
WHERE main.id = TicketCustomFieldValues_1.Ticket
AND TicketCustomFieldValues_1.CustomField = ‘40’
AND main.EffectiveId = main.id
AND main.Type = ‘ticket’
AND ( TicketCustomFieldValues_1.Content = ‘49’
OR TicketCustomFieldValues_1.Content = ‘94’
OR TicketCustomFieldValues_1.Content = ‘97’
OR TicketCustomFieldValues_1.Content = ‘110’
OR TicketCustomFieldValues_1.Content = ‘71’
OR TicketCustomFieldValues_1.Content = ‘53’ )
AND main.Queue = ‘5’;
Thebn we can get the excution times down to 6.4 milliseconds. This is
roughly 5000 times faster than the original query.
That’s the good news! The bad news is that I don’t know how to modify
SearchBuilder to produce this new optimised SQL.
But, I do hope that this analysis can help someone else
If you want any further information, please mail me,
Cheers,
Paul