Looks like this may be a customized piece of workflow that is running this
query
to search for record sets where the Assignee Group field contains values
ranging
from SYS_B_01 to SYS_B_66.
And what are all those colons doing there? Is this a query you have written in
a
search menu or a table field qualification? There are several possible syntax
errors in that query..
After correcting the syntax errors, one way of reducing the length of this
query
would be to create a computed group lets say:
SYS_B_00_66 = "SYS_B_00" OR "SYS_B_01" OR "SYS_B_02" OR "SYS_B_03" OR
"SYS_B_04"
OR "SYS_B_05" OR "SYS_B_06" OR "SYS_B_07" OR "SYS_B_08" OR "SYS_B_09" OR
"SYS_B_10" OR "SYS_B_11" OR "SYS_B_12" OR "SYS_B_13" OR "SYS_B_14" OR
"SYS_B_15"
OR "SYS_B_16" OR "SYS_B_17" OR "SYS_B_18" OR "SYS_B_19" OR "SYS_B_20" OR
"SYS_B_21" OR "SYS_B_22" OR "SYS_B_23" OR "SYS_B_24" OR "SYS_B_25" OR
"SYS_B_26"
OR "SYS_B_27" OR "SYS_B_28" OR "SYS_B_29" OR "SYS_B_30" OR "SYS_B_31" OR
"SYS_B_32" OR "SYS_B_33" OR "SYS_B_34" OR "SYS_B_35" OR "SYS_B_36" OR
"SYS_B_37"
OR "SYS_B_38" OR "SYS_B_39" OR "SYS_B_40" OR "SYS_B_41" OR "SYS_B_42" OR
"SYS_B_43" OR "SYS_B_44" OR "SYS_B_45" OR "SYS_B_46" OR "SYS_B_47" OR
"SYS_B_48"
OR "SYS_B_49" OR "SYS_B_50" OR "SYS_B_51" OR "SYS_B_52" OR "SYS_B_53" OR
"SYS_B_54" OR "SYS_B_55" OR "SYS_B_56" OR "SYS_B_57" OR "SYS_B_58" OR
"SYS_B_59"
OR "SYS_B_60" OR "SYS_B_61" OR "SYS_B_62" OR "SYS_B_63" OR "SYS_B_64" OR
"SYS_B_65" OR "SYS_B_66"
Use that computed group in your query which will reduce the length of the query.
Unchecking unqualified query search only means you cannot issue a query without
a qualification or a search criteria. It will do nothing to the length of this
particular query..
Using the computed group will reduce the length of the query to
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_66" + "%")
AND (T28.C536870993 LIKE "SYS_B_67")) ORDER BY...
Joe D'Souza
Remedy Developer / Consultant,
Shyle Networks,
New Jersey.
________________________________
From: vikram_k <[email protected]>
To: [email protected]
Sent: Mon, February 28, 2011 3:28:46 AM
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"
