Vikram,
Several observations about this command:
First, this command seems to have been run through some kind of translator that
has taken every string between "" symbols and replaced it with an incrementing
string of SYS_B_xx where xx is an incrementing number.
Because of this, it is a bit hard to assist with the question you ask because
there is critical information in the query that you have masked out and that
information may be the key to the solution. The most critical missing piece of
data is whatever SYS_B_67 was originally. As you will see from the remaining
notes, the rest of the strings really aren't important.
1) You comment on disallowing unqualified queries and say that this didn't help
this query. Well, that option is not related as this is NOT an unqualified
query. There is a qualification that is being specified by the person who
is issuing this query.
'536870993' LIKE "SYS_B_67"
Now, the issue here is what is SYS_B_67. If it is really a search that looks
something like the following
%zzz%
Then, you simply have a terribly inefficient query. You are searching in a
character field for where some set of characters (zzz in my example) is
somewhere in the field. This requires a table scan to look at each record
and the data in that field to see if the string matches.
If it is really a search that looks more like
zzz%
Then, you can get an efficient search out of this. This is to find a value
where the string STARTS with a set of characters (zzz in my example) and has
other data after it. If like this, then indexes will be used in the search
if there is an index on field 536870993. If there is no index on that field
one could be added and will make a difference. REMEMBER, the search cannot
have a leading % for the index to be useful. If there is a leading % like in
the first example, the index will not be used even if present.
There is no other qualifier that the customer is supplying to further limit
the search so the entire indexing scheme hinges on this one field.
2) All the comparisons of field 112 to a value are things the AR System adds
onto the search because you have a row level security field (field 112) on
the form and you do not have global access to the entries, and the system is
checking to see if you have access to rows it is retrieving.
At first glance, the search is not efficient as it is a LIKE "%xxx%" type
search. So, if that was the only search criteria, it is correct, this would
be an inefficient criteria. However, the design is for this to be an
ADDITIVE criteria, not the primary criteria. The primary criteria is the
search the user provides, this is just an additional test. In the role of
additional test, efficiency is not significant as only one index can be used
for a search. The row would already have been retrieved to apply this
criteria as additional qualification.
So, the entire clause with Ored tests of field 112 can be discounted from any
qualification you are trying to check performance of.
Now, if that clause is the only clause in the WHERE, that means you have
allowed unqualified queries and someone is issuing an unqualified query
(which is a table scan).
3) Whoever the user is in this scenario is a member of 67 groups (SYS_B_00
through SYS_B_66). This is a lot of groups for a single user to be a member
of. Not disallowed, and the system will work and it does support that. It
is just an observation that it is a large number. Typically, a user is a
member of a small number of groups and this clause is significantly shorter.
Hopefully, this gives you the information you need. What I really feel is the
challenge here is that the user is qualifying field 536870993 to look for a
string ANYWHERE in the field and that is causing the problem.
Solutions:
1) If this really should be an = instead of a LIKE "%zzz%", you may check the
default QBE setting for the field and change it from Anywhere to Leading or
Matching. The default QBE match criteria (a view property of the field) is
Anywhere. So, a user may not be aware if they are using the query screen and
putting in a value for that field what is happening. Changing the
definition will change the command issued.
2) If a user is doing this in a query bar, educate the user about the cost of
this type of operation.
3) If workflow is issuing this command, determine whether the workflow could
use a leading or equal search.
4) Is there other criteria you can use on some other field(s) on the form to
help limit the scope of the rows that you need to search across?
Of course, if what you need to do is a search for that string anywhere in the
field AND there are no other criteria you can use to search other fields to
limit which rows are looked at, then you are stuck with the performance of the
operation you need to perform.
Good luck with your situation and with finding a solution for long query.
Doug Mueller
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of vikram_k
Sent: Monday, February 28, 2011 1:29 AM
To: [email protected]
Subject: Huge queries hogging up the database
hi all,
I had recently put some posts regarding AR Performance and I am thankful
that most of my problems are now resolved. But there is one issue which is a
bottle neck I am seeing a huge oracle query like
SELECT
T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C536871025,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021,C536870957
FROM T28
WHERE (((T28.C112 LIKE :"SYS_B_00") OR ((T28.C112 LIKE :"SYS_B_01") OR
((T28.C112 LIKE :"SYS_B_02") OR ((T28.C112 LIKE :"SYS_B_03") OR ((T28.C112
LIKE :"SYS_B_04") OR ((T28.C112 LIKE :"SYS_B_05") OR ((T28.C112 LIKE
:"SYS_B_06") OR ((T28.C112 LIKE :"SYS_B_07") OR ((T28.C112 LIKE :"SYS_B_08")
OR ((T28.C112 LIKE :"SYS_B_09") OR ((T28.C112 LIKE :"SYS_B_10") OR
((T28.C112 LIKE :"SYS_B_11") OR ((T28.C112 LIKE :"SYS_B_12") OR ((T28.C112
LIKE :"SYS_B_13") OR ((T28.C112 LIKE :"SYS_B_14") OR ((T28.C112 LIKE
:"SYS_B_15") OR ((T28.C112 LIKE :"SYS_B_16") OR ((T28.C112 LIKE :"SYS_B_17")
OR ((T28.C112 LIKE :"SYS_B_18") OR ((T28.C112 LIKE :"SYS_B_19") OR
((T28.C112 LIKE :"SYS_B_20") OR ((T28.C112 LIKE :"SYS_B_21") OR ((T28.C112
LIKE :"SYS_B_22") OR ((T28.C112 LIKE :"SYS_B_23") OR ((T28.C112 LIKE
:"SYS_B_24") OR ((T28.C112 LIKE :"SYS_B_25") OR ((T28.C112 LIKE :"SYS_B_26")
OR ((T28.C112 LIKE :"SYS_B_27") OR ((T28.C112 LIKE :"SYS_B_28") OR
((T28.C112 LIKE :"SYS_B_29") OR ((T28.C112 LIKE :"SYS_B_30") OR ((T28.C112
LIKE :"SYS_B_31") OR ((T28.C112 LIKE :"SYS_B_32") OR ((T28.C112 LIKE
:"SYS_B_33") OR ((T28.C112 LIKE :"SYS_B_34") OR ((T28.C112 LIKE :"SYS_B_35")
OR ((T28.C112 LIKE :"SYS_B_36") OR ((T28.C112 LIKE :"SYS_B_37") OR
((T28.C112 LIKE :"SYS_B_38") OR ((T28.C112 LIKE :"SYS_B_39") OR ((T28.C112
LIKE :"SYS_B_40") OR ((T28.C112 LIKE :"SYS_B_41") OR ((T28.C112 LIKE
:"SYS_B_42") OR ((T28.C112 LIKE :"SYS_B_43") OR ((T28.C112 LIKE :"SYS_B_44")
OR ((T28.C112 LIKE :"SYS_B_45") OR ((T28.C112 LIKE :"SYS_B_46") OR
((T28.C112 LIKE :"SYS_B_47") OR ((T28.C112 LIKE :"SYS_B_48") OR ((T28.C112
LIKE :"SYS_B_49") OR ((T28.C112 LIKE :"SYS_B_50") OR ((T28.C112 LIKE
:"SYS_B_51") OR ((T28.C112 LIKE :"SYS_B_52") OR ((T28.C112 LIKE :"SYS_B_53")
OR ((T28.C112 LIKE :"SYS_B_54") OR ((T28.C112 LIKE :"SYS_B_55") OR
((T28.C112 LIKE :"SYS_B_56") OR ((T28.C112 LIKE :"SYS_B_57") OR ((T28.C112
LIKE :"SYS_B_58") OR ((T28.C112 LIKE :"SYS_B_59") OR ((T28.C112 LIKE
:"SYS_B_60") OR ((T28.C112 LIKE :"SYS_B_61") OR ((T28.C112 LIKE :"SYS_B_62")
OR ((T28.C112 LIKE :"SYS_B_63") OR ((T28.C112 LIKE :"SYS_B_64") OR
((T28.C112 LIKE :"SYS_B_65") OR (T28.C112 LIKE
:"SYS_B_66")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
AND (T28.C536870993 LIKE :"SYS_B_67")) ORDER BY :"SYS_B_68" ASC
I was told to disallow unqualified search which I did and yet I am seeing
this kind of query.
Can anyone help me understand what exactly is this query doing and what can
be done to avoid it...
Thanks,
Vikram
--
View this message in context:
http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31030153.html
Sent from the ARS (Action Request System) mailing list archive at Nabble.com.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
