March 14, 2012 at 8:59 am
An execution plan recommended I create an index, so I did.
Now I'm trying to see if that query is faster or better, but how do I that when it's this huge and don't know what params to pass?
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 varbinary(8000),@P4 varbinary(8000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 bigint)
WITH ALLLEVELS AS (SELECT Links_23.SIID , Links_23.SVID , Links_24.TIID , Links_24.TVID , Links_24.TIX , Links_24.TCTID , Links_24.TNAME , Links_24.TNSID
FROM jcrsch.ICMSTJCRLV00001 Links_23, jcrsch.ICMSTJCRLR00001 LinkRel_25, jcrsch.ICMSTJCRLV00001 Links_24 WHERE (Links_23.LID = LinkRel_25.ALID) AND (Links_24.LID = LinkRel_25.DLID)) SELECT DISTINCT ALLLEVELS_4.TIID , 1 WSID , NodesTab_14.VID , NodesTab_14.CTID , NodesTab_14.UUID , NodesTab_14.COMPID , Properties_17.PROPVAL ORDERVAL18 , Properties_21.PROPVAL ORDERVAL22
FROM ALLLEVELS ALLLEVELS_4, jcrsch.ICMSTJCRLV00001 Links_1, jcrsch.ICMSTJCRN00001 NodesTab_14, (SELECT ibmcontentwcm_15.ITEMID , ibmcontentwcm_15.VERSIONID , ibmcontentwcm_15.ATTR0000001315 PROPVAL FROM jcrsch.ICMUT01580001 ibmcontentwcm_15
UNION ALL SELECT ibmcontentwcm_16.ITEMID , ibmcontentwcm_16.VERSIONID , ibmcontentwcm_16.ATTR0000001315 PROPVAL
FROM jcrsch.ICMUT01331001 ibmcontentwcm_16 ) Properties_17 , (SELECT ibmcontentwcm_19.ITEMID , ibmcontentwcm_19.VERSIONID , ibmcontentwcm_19.ATTR0000001065 PROPVAL FROM jcrsch.ICMUT01580001 ibmcontentwcm_19
UNION ALL SELECT ibmcontentwcm_20.ITEMID , ibmcontentwcm_20.VERSIONID , ibmcontentwcm_20.ATTR0000001065 PROPVAL FROM jcrsch.ICMUT01331001 ibmcontentwcm_20 ) Properties_21 WHERE (((((((ALLLEVELS_4.TCTID = @P0) OR (ALLLEVELS_4.TCTID = @P1)) AND ((Links_1.TCTID = @P2) AND (EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT NodesTab_2.IID ITEMID , NodesTab_2.VID VERSIONID , NodesTab_2.UUID PROPVAL FROM jcrsch.ICMSTJCRN00001 NodesTab_2 ) Properties_3 WHERE ((Properties_3.ITEMID = Links_1.TIID) AND (Properties_3.VERSIONID = Links_1.TVID)) AND (Properties_3.PROPVAL IN (@P3 , @P4 )))))) AND (ALLLEVELS_4.SIID = Links_1.TIID)) AND (((EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT ibmcontentwcm_5.ITEMID , ibmcontentwcm_5.VERSIONID , ibmcontentwcm_5.ATTR0000001125 PROPVAL FROM jcrsch.ICMUT01580001 ibmcontentwcm_5 UNION ALL SELECT ibmcontentwcm_6.ITEMID , ibmcontentwcm_6.VERSIONID , ibmcontentwcm_6.ATTR0000001125 PROPVAL FROM jcrsch.ICMUT01331001 ibmcontentwcm_6 ) Properties_7 WHERE ((Properties_7.ITEMID = ALLLEVELS_4.TIID) AND (Properties_7.VERSIONID = ALLLEVELS_4.TVID)) AND (Properties_7.PROPVAL IN (@P5 , @P6 )))) AND (EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT ibmcontentwcm_8.ITEMID , ibmcontentwcm_8.VERSIONID , ibmcontentwcm_8.ATTR0000001336 PROPVAL FROM jcrsch.ICMUT01583001 ibmcontentwcm_8 UNION ALL SELECT ibmcontentwcm_9.ITEMID , ibmcontentwcm_9.VERSIONID , ibmcontentwcm_9.ATTR0000001336 PROPVAL FROM jcrsch.ICMUT01334001 ibmcontentwcm_9 ) Properties_10 WHERE ((Properties_10.ITEMID = ALLLEVELS_4.TIID) AND (Properties_10.VERSIONID = ALLLEVELS_4.TVID)) AND (Properties_10.PROPVAL IN (@P7 , @P8 , @P9 , @P10 ))))) AND (EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT ibmcontentwcm_11.ITEMID , ibmcontentwcm_11.VERSIONID , ibmcontentwcm_11.ATTR0000001129 PROPVAL FROM jcrsch.ICMUT01580001 ibmcontentwcm_11 UNION ALL SELECT ibmcontentwcm_12.ITEMID , ibmcontentwcm_12.VERSIONID , ibmcontentwcm_12.ATTR0000001129 PROPVAL FROM jcrsch.ICMUT01331001 ibmcontentwcm_12 ) Properties_13
WHERE ((Properties_13.ITEMID = ALLLEVELS_4.TIID) AND (Properties_13.VERSIONID = ALLLEVELS_4.TVID)) AND (Properties_13.PROPVAL = @P11))))) AND (ALLLEVELS_4.TIID = NodesTab_14.IID)) AND ((Properties_17.ITEMID = ALLLEVELS_4.TIID) AND (Properties_17.VERSIONID = ALLLEVELS_4.TVID))) AND ((Properties_21.ITEMID = ALLLEVELS_4.TIID) AND (Properties_21.VERSIONID = ALLLEVELS_4.TVID))
ORDER BY ORDERVAL18 DESC, ORDERVAL22 DESC
March 14, 2012 at 9:17 am
Is the query not used very often? If it is used often enough, you can monitor it using SQL Profiler - there's a duration field in a Standard trace.
_________________________________
seth delconte
http://sqlkeys.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply