Java parameterized query vs simple query

mikehcumail · February 2, 2017, 8:09pm

Hi

I’m using java sdk(2.4.1) to query a Couchbase 4.5 cluster. I noticed that for the exact same query Simple query returns much faster than Parameterized query. When I use Parameterized query, it takes 2.5 sec to return. When I convert the query to a Simple query with the code below, it takes only 0.5 sec(which is around the same amount of time it takes in cbq-shell.

Before:

N1qlQueryResult result = bucket.query(N1qlQuery.parameterized(statement.toString(), params));

After:

String x = statement.toString();
for(String k : params.getNames()){
	Object o = params.get(k);
	x = x.replace("$" + k, o instanceof String? "'" + params.getString(k) + "'" : ((JsonArray)o).toString());
}
N1qlQueryResult result = bucket.query(N1qlQuery.simple(x));

Query:

select contentTag.experienceId, content.contentId, content.contentName, content.contentCategoryIds, content.md5, content.version, content.mimeType, content.size, content.priority, content.destinationPath, contentTag.supportedCarrierIds, contentTag.excludedCarrierIds, contentTag.supportedChannelIds, contentTag.excludedChannelIds from retailcloudPortalDev contentTag use index(contentTag_experienceId) inner join retailcloudPortalDev content on keys contentTag.contentId where contentTag.docType=‘contenttag’ and content.docType=‘content’ and (content.validityStartDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityStartDate,8,4)||’-’||CASE WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘jan’ THEN ‘01’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘feb’ THEN ‘02’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘mar’ THEN ‘03’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘apr’ THEN ‘04’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘may’ THEN ‘05’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘jun’ THEN ‘06’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘jul’ THEN ‘07’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘aug’ THEN ‘08’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘sep’ THEN ‘09’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘oct’ THEN ‘10’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘nov’ THEN ‘11’ WHEN LOWER(SUBSTR(content.validityStartDate,0,3))==‘dec’ THEN ‘12’ END||’-’||SUBSTR(content.validityStartDate,4,2), date_add_str(NOW_STR(‘0000-01-01’), 2, ‘day’), ‘day’)<=0) and (content.validityEndDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityEndDate,8,4)||’-’||CASE WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘jan’ THEN ‘01’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘feb’ THEN ‘02’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘mar’ THEN ‘03’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘apr’ THEN ‘04’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘may’ THEN ‘05’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘jun’ THEN ‘06’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘jul’ THEN ‘07’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘aug’ THEN ‘08’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘sep’ THEN ‘09’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘oct’ THEN ‘10’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘nov’ THEN ‘11’ WHEN LOWER(SUBSTR(content.validityEndDate,0,3))==‘dec’ THEN ‘12’ END||’-’||SUBSTR(content.validityEndDate,4,2), date_add_str(NOW_STR(‘0000-01-01’), -2, ‘day’), ‘day’)>=0) and (contentTag.experienceId is null or contentTag.experienceId=‘experience:com.samsung.sea.retailagent.hero2.b2c’ or contentTag.experienceId in [“experience:experience:com.samsung.sea.retailagent.hero2.b2c:20160630215123487”,“experience:experience:allsamsungexperience:20161128205825876”,“experience:experience:allsamsungexperience:20161129145921028”,“experience:experience:allsamsungexperience:20161128193621041”,“experience:experience:allsamsungexperience:20161128182943284”,“experience:experience:allsamsungexperience:20161128205022801”,“experience:experience:allsamsungexperience:20161128192431892”]) and (‘all’ in contentTag.supportedCarrierIds or every carrierId in [“carrier:spr”] satisfies carrierId in contentTag.supportedCarrierIds end) and not(any carrierId in [“carrier:spr”] satisfies carrierId in contentTag.excludedCarrierIds end) and (((‘all’ in contentTag.supportedChannelIds or (any channelId in contentTag.supportedChannelIds satisfies channelId=‘channel:bestbuy’ or contains(‘channel:bestbuy’, channelId || ‘>’) end)) and not(any channelId in contentTag.excludedChannelIds satisfies channelId=‘channel:bestbuy’ or contains(‘channel:bestbuy’, channelId || ‘>’) end)) or (any channelId in contentTag.supportedChannelIds satisfies channelId=‘channel:bestbuy’ end)) and (‘all’ in contentTag.supportedDeviceCodeIds or (any deviceCodeId in contentTag.supportedDeviceCodeIds satisfies deviceCodeId=‘devicecode:devicetype:mobile:hero2’ or deviceCodeId=‘devicetype:mobile’ end and not(any deviceCodeId in [“devicecode:devicetype:mobile:hero2”] satisfies deviceCodeId in contentTag.excludedDeviceCodeIds end))) and (contentTag.supportedStoreSetIds is not valued or ‘all’ in contentTag.supportedStoreSetIds or every storeSetId in [“storeset:none”] satisfies storeSetId in contentTag.supportedStoreSetIds end) and not(any storeSetId in [“storeset:none”] satisfies storeSetId in contentTag.excludedStoreSetIds end)

geraldss · February 2, 2017, 8:50pm

Try EXPLAIN for both to compare the explain plans. cc @daschl @ingenthr

mikehcumail · February 2, 2017, 9:11pm

Hi

Please see the explain plans for both cases below. They are different.
However, the queries are identical except one of them has place holders and
the other has actual values.

Thx
Mike Chu

Explain Plan for Simple Query

geraldss · February 2, 2017, 9:23pm

I don’t see the EXPLAINs…

mikehcumail · February 2, 2017, 9:33pm

Sorry, please see attached file.

ingenthr · February 2, 2017, 9:51pm

@mikehcumail: can you post via the forums web UI? MIME attachments in email replies aren’t separated out when you reply via email to the forums.

mikehcumail · February 3, 2017, 7:56am

Explain Plan for Simple Query

[{
		"plan": {
			"~children": [{
				"scan": {
					"index_id": "163c2a6dc3e4bd34",
					"index": "contentTag_experienceId",
					"using": "gsi",
					"#operator": "IndexScan",
					"spans": [{
						"Range": {
							"High": ["null"],
							"Inclusion": 3,
							"Low": ["null"]
						}
					}, {
						"Range": {
							"High": ["\"experience:com.samsung.sea.retailagent.hero2.b2c\""],
							"Inclusion": 3,
							"Low": ["\"experience:com.samsung.sea.retailagent.hero2.b2c\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161128182943284\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161128182943284\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161128192431892\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161128192431892\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161128193621041\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161128193621041\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161128205022801\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161128205022801\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161128205825876\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161128205825876\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:allsamsungexperience:20161129145921028\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:allsamsungexperience:20161129145921028\""]
						}
					}, {
						"Range": {
							"High": ["\"experience:experience:com.samsung.sea.retailagent.hero2.b2c:20160630215123487\""],
							"Inclusion": 3,
							"Low": ["\"experience:experience:com.samsung.sea.retailagent.hero2.b2c:20160630215123487\""]
						}
					}],
					"keyspace": "retailcloudPortalDev",
					"namespace": "default"
				},
				"#operator": "DistinctScan"
			}, {
				"#operator": "Parallel",
				"~child": {
					"~children": [{
						"as": "contentTag",
						"#operator": "Fetch",
						"keyspace": "retailcloudPortalDev",
						"namespace": "default"
					}, {
						"as": "content",
						"on_keys": "(`contentTag`.`contentId`)",
						"#operator": "Join",
						"keyspace": "retailcloudPortalDev",
						"namespace": "default"
					}, {
						"condition": "((((((((((((`contentTag`.`docType`) = \"contenttag\") and ((`content`.`docType`) = \"content\")) and (((`content`.`validityStartDate`) is not valued) or (date_diff_str(((((substr((`content`.`validityStartDate`), 8, 4) || \"-\") || case when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jan\") then \"01\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"feb\") then \"02\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"mar\") then \"03\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"apr\") then \"04\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"may\") then \"05\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jun\") then \"06\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jul\") then \"07\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"aug\") then \"08\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"sep\") then \"09\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"oct\") then \"10\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"nov\") then \"11\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"dec\") then \"12\" end) || \"-\") || substr((`content`.`validityStartDate`), 4, 2)), date_add_str(now_str(\"0000-01-01\"), 2, \"day\"), \"day\") <= 0))) and (((`content`.`validityEndDate`) is not valued) or (0 <= date_diff_str(((((substr((`content`.`validityEndDate`), 8, 4) || \"-\") || case when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jan\") then \"01\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"feb\") then \"02\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"mar\") then \"03\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"apr\") then \"04\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"may\") then \"05\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jun\") then \"06\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jul\") then \"07\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"aug\") then \"08\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"sep\") then \"09\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"oct\") then \"10\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"nov\") then \"11\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"dec\") then \"12\" end) || \"-\") || substr((`content`.`validityEndDate`), 4, 2)), date_add_str(now_str(\"0000-01-01\"), (-2), \"day\"), \"day\")))) and ((((`contentTag`.`experienceId`) is null) or ((`contentTag`.`experienceId`) = \"experience:com.samsung.sea.retailagent.hero2.b2c\")) or ((`contentTag`.`experienceId`) in [\"experience:experience:com.samsung.sea.retailagent.hero2.b2c:20160630215123487\", \"experience:experience:allsamsungexperience:20161128182943284\", \"experience:experience:allsamsungexperience:20161128192431892\", \"experience:experience:allsamsungexperience:20161128193621041\", \"experience:experience:allsamsungexperience:20161128205022801\", \"experience:experience:allsamsungexperience:20161128205825876\", \"experience:experience:allsamsungexperience:20161129145921028\"]))) and ((\"all\" in (`contentTag`.`supportedCarrierIds`)) or every `carrierId` in [\"carrier:spr\"] satisfies (`carrierId` in (`contentTag`.`supportedCarrierIds`)) end)) and (not any `carrierId` in [\"carrier:spr\"] satisfies (`carrierId` in (`contentTag`.`excludedCarrierIds`)) end)) and ((((\"all\" in (`contentTag`.`supportedChannelIds`)) or any `channelId` in (`contentTag`.`supportedChannelIds`) satisfies ((`channelId` = \"channel:bestbuy\") or contains(\"channel:bestbuy\", (`channelId` || \"\\u003e\"))) end) and (not any `channelId` in (`contentTag`.`excludedChannelIds`) satisfies ((`channelId` = \"channel:bestbuy\") or contains(\"channel:bestbuy\", (`channelId` || \"\\u003e\"))) end)) or any `channelId` in (`contentTag`.`supportedChannelIds`) satisfies (`channelId` = \"channel:bestbuy\") end)) and ((\"all\" in (`contentTag`.`supportedDeviceCodeIds`)) or (any `deviceCodeId` in (`contentTag`.`supportedDeviceCodeIds`) satisfies ((`deviceCodeId` = \"devicecode:devicetype:mobile:hero2\") or (`deviceCodeId` = \"devicetype:mobile\")) end and (not any `deviceCodeId` in [\"devicecode:devicetype:mobile:hero2\"] satisfies (`deviceCodeId` in (`contentTag`.`excludedDeviceCodeIds`)) end)))) and ((((`contentTag`.`supportedStoreSetIds`) is not valued) or (\"all\" in (`contentTag`.`supportedStoreSetIds`))) or every `storeSetId` in [\"storeset:none\"] satisfies (`storeSetId` in (`contentTag`.`supportedStoreSetIds`)) end)) and (not any `storeSetId` in [\"storeset:none\"] satisfies (`storeSetId` in (`contentTag`.`excludedStoreSetIds`)) end))",
						"#operator": "Filter"
					}, {
						"result_terms": [{
							"expr": "(`contentTag`.`experienceId`)"
						}, {
							"expr": "(`content`.`contentId`)"
						}, {
							"expr": "(`content`.`contentName`)"
						}, {
							"expr": "(`content`.`contentCategoryIds`)"
						}, {
							"expr": "(`content`.`md5`)"
						}, {
							"expr": "(`content`.`version`)"
						}, {
							"expr": "(`content`.`mimeType`)"
						}, {
							"expr": "(`content`.`size`)"
						}, {
							"expr": "(`content`.`priority`)"
						}, {
							"expr": "(`content`.`destinationPath`)"
						}, {
							"expr": "(`contentTag`.`supportedCarrierIds`)"
						}, {
							"expr": "(`contentTag`.`excludedCarrierIds`)"
						}, {
							"expr": "(`contentTag`.`supportedChannelIds`)"
						}, {
							"expr": "(`contentTag`.`excludedChannelIds`)"
						}],
						"#operator": "InitialProject"
					}, {
						"#operator": "FinalProject"
					}],
					"#operator": "Sequence"
				}
			}],
			"#operator": "Sequence"
		},
		"text": "select contentTag.experienceId, content.contentId, content.contentName, content.contentCategoryIds, content.md5, content.version, content.mimeType, content.size, content.priority, content.destinationPath, contentTag.supportedCarrierIds, contentTag.excludedCarrierIds, contentTag.supportedChannelIds, contentTag.excludedChannelIds from retailcloudPortalDev contentTag use index(contentTag_experienceId) inner join retailcloudPortalDev content on keys contentTag.contentId where contentTag.docType='contenttag' and content.docType='content' and (content.validityStartDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityStartDate,8,4)||'-'||CASE WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jan' THEN '01' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='feb' THEN '02' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='mar' THEN '03' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='apr' THEN '04' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='may' THEN '05' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jun' THEN '06' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jul' THEN '07' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='aug' THEN '08' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='sep' THEN '09' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='oct' THEN '10' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='nov' THEN '11' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='dec' THEN '12' END||'-'||SUBSTR(content.validityStartDate,4,2), date_add_str(NOW_STR('0000-01-01'), 2, 'day'), 'day')<=0) and (content.validityEndDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityEndDate,8,4)||'-'||CASE WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jan' THEN '01' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='feb' THEN '02' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='mar' THEN '03' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='apr' THEN '04' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='may' THEN '05' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jun' THEN '06' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jul' THEN '07' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='aug' THEN '08' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='sep' THEN '09' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='oct' THEN '10' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='nov' THEN '11' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='dec' THEN '12' END||'-'||SUBSTR(content.validityEndDate,4,2), date_add_str(NOW_STR('0000-01-01'), -2, 'day'), 'day')>=0) and (contentTag.experienceId is null or contentTag.experienceId='experience:com.samsung.sea.retailagent.hero2.b2c' or contentTag.experienceId in ["experience:experience:com.samsung.sea.retailagent.hero2.b2c:20160630215123487","experience:experience:allsamsungexperience:20161128182943284","experience:experience:allsamsungexperience:20161128192431892","experience:experience:allsamsungexperience:20161128193621041","experience:experience:allsamsungexperience:20161128205022801","experience:experience:allsamsungexperience:20161128205825876","experience:experience:allsamsungexperience:20161129145921028"]) and ('all' in contentTag.supportedCarrierIds or every carrierId in ["carrier:spr"] satisfies carrierId in contentTag.supportedCarrierIds end) and not(any carrierId in ["carrier:spr"] satisfies carrierId in contentTag.excludedCarrierIds end) and ((('all' in contentTag.supportedChannelIds or (any channelId in contentTag.supportedChannelIds satisfies channelId='channel:bestbuy' or contains('channel:bestbuy', channelId || '>') end)) and not(any channelId in contentTag.excludedChannelIds satisfies channelId='channel:bestbuy' or contains('channel:bestbuy', channelId || '>') end)) or (any channelId in contentTag.supportedChannelIds satisfies channelId='channel:bestbuy' end)) and ('all' in contentTag.supportedDeviceCodeIds or (any deviceCodeId in contentTag.supportedDeviceCodeIds satisfies deviceCodeId='devicecode:devicetype:mobile:hero2' or deviceCodeId='devicetype:mobile' end and not(any deviceCodeId in ["devicecode:devicetype:mobile:hero2"] satisfies deviceCodeId in contentTag.excludedDeviceCodeIds end))) and (contentTag.supportedStoreSetIds is not valued or 'all' in contentTag.supportedStoreSetIds or every storeSetId in ["storeset:none"] satisfies storeSetId in contentTag.supportedStoreSetIds end) and not(any storeSetId in ["storeset:none"] satisfies storeSetId in contentTag.excludedStoreSetIds end)"

Explain Plan for Parameterized Query

[{
	"plan": {
		"~children": [{
			"scan": {
				"index_id": "163c2a6dc3e4bd34",
				"index": "contentTag_experienceId",
				"using": "gsi",
				"#operator": "IndexScan",
				"spans": [{
					"Range": {
						"High": ["null"],
						"Inclusion": 3,
						"Low": ["null"]
					}
				}, {
					"Range": {
						"High": ["$parentExperienceId"],
						"Inclusion": 3,
						"Low": ["$parentExperienceId"]
					}
				}, {
					"Range": {
						"Low": ["null"],
						"Inclusion": 0
					}
				}],
				"keyspace": "retailcloudPortalDev",
				"namespace": "default"
			},
			"#operator": "DistinctScan"
		}, {
			"#operator": "Parallel",
			"~child": {
				"~children": [{
					"as": "contentTag",
					"#operator": "Fetch",
					"keyspace": "retailcloudPortalDev",
					"namespace": "default"
				}, {
					"as": "content",
					"on_keys": "(`contentTag`.`contentId`)",
					"#operator": "Join",
					"keyspace": "retailcloudPortalDev",
					"namespace": "default"
				}, {
					"condition": "((((((((((((`contentTag`.`docType`) = \"contenttag\") and ((`content`.`docType`) = \"content\")) and (((`content`.`validityStartDate`) is not valued) or (date_diff_str(((((substr((`content`.`validityStartDate`), 8, 4) || \"-\") || case when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jan\") then \"01\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"feb\") then \"02\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"mar\") then \"03\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"apr\") then \"04\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"may\") then \"05\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jun\") then \"06\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"jul\") then \"07\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"aug\") then \"08\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"sep\") then \"09\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"oct\") then \"10\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"nov\") then \"11\" when (lower(substr((`content`.`validityStartDate`), 0, 3)) = \"dec\") then \"12\" end) || \"-\") || substr((`content`.`validityStartDate`), 4, 2)), date_add_str(now_str(\"0000-01-01\"), 2, \"day\"), \"day\") <= 0))) and (((`content`.`validityEndDate`) is not valued) or (0 <= date_diff_str(((((substr((`content`.`validityEndDate`), 8, 4) || \"-\") || case when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jan\") then \"01\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"feb\") then \"02\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"mar\") then \"03\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"apr\") then \"04\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"may\") then \"05\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jun\") then \"06\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"jul\") then \"07\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"aug\") then \"08\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"sep\") then \"09\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"oct\") then \"10\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"nov\") then \"11\" when (lower(substr((`content`.`validityEndDate`), 0, 3)) = \"dec\") then \"12\" end) || \"-\") || substr((`content`.`validityEndDate`), 4, 2)), date_add_str(now_str(\"0000-01-01\"), (-2), \"day\"), \"day\")))) and ((((`contentTag`.`experienceId`) is null) or ((`contentTag`.`experienceId`) = $parentExperienceId)) or ((`contentTag`.`experienceId`) in $experienceIds))) and ((\"all\" in (`contentTag`.`supportedCarrierIds`)) or every `carrierId` in $carrierIds satisfies (`carrierId` in (`contentTag`.`supportedCarrierIds`)) end)) and (not any `carrierId` in $carrierIds satisfies (`carrierId` in (`contentTag`.`excludedCarrierIds`)) end)) and ((((\"all\" in (`contentTag`.`supportedChannelIds`)) or any `channelId` in (`contentTag`.`supportedChannelIds`) satisfies ((`channelId` = $fullChannelCodePath0) or contains($fullChannelCodePath0, (`channelId` || \"\\u003e\"))) end) and (not any `channelId` in (`contentTag`.`excludedChannelIds`) satisfies ((`channelId` = $fullChannelCodePath0) or contains($fullChannelCodePath0, (`channelId` || \"\\u003e\"))) end)) or any `channelId` in (`contentTag`.`supportedChannelIds`) satisfies (`channelId` = $fullChannelCodePath0) end)) and ((\"all\" in (`contentTag`.`supportedDeviceCodeIds`)) or (any `deviceCodeId` in (`contentTag`.`supportedDeviceCodeIds`) satisfies ((`deviceCodeId` = $deviceCodeId0) or (`deviceCodeId` = $deviceTypeId0)) end and (not any `deviceCodeId` in $deviceCodeIds satisfies (`deviceCodeId` in (`contentTag`.`excludedDeviceCodeIds`)) end)))) and ((((`contentTag`.`supportedStoreSetIds`) is not valued) or (\"all\" in (`contentTag`.`supportedStoreSetIds`))) or every `storeSetId` in $storeSetIds satisfies (`storeSetId` in (`contentTag`.`supportedStoreSetIds`)) end)) and (not any `storeSetId` in $storeSetIds satisfies (`storeSetId` in (`contentTag`.`excludedStoreSetIds`)) end))",
					"#operator": "Filter"
				}, {
					"result_terms": [{
						"expr": "(`contentTag`.`experienceId`)"
					}, {
						"expr": "(`content`.`contentId`)"
					}, {
						"expr": "(`content`.`contentName`)"
					}, {
						"expr": "(`content`.`contentCategoryIds`)"
					}, {
						"expr": "(`content`.`md5`)"
					}, {
						"expr": "(`content`.`version`)"
					}, {
						"expr": "(`content`.`mimeType`)"
					}, {
						"expr": "(`content`.`size`)"
					}, {
						"expr": "(`content`.`priority`)"
					}, {
						"expr": "(`content`.`destinationPath`)"
					}, {
						"expr": "(`contentTag`.`supportedCarrierIds`)"
					}, {
						"expr": "(`contentTag`.`excludedCarrierIds`)"
					}, {
						"expr": "(`contentTag`.`supportedChannelIds`)"
					}, {
						"expr": "(`contentTag`.`excludedChannelIds`)"
					}],
					"#operator": "InitialProject"
				}, {
					"#operator": "FinalProject"
				}],
				"#operator": "Sequence"
			}
		}],
		"#operator": "Sequence"
	},
	"text": "select contentTag.experienceId, content.contentId, content.contentName, content.contentCategoryIds, content.md5, content.version, content.mimeType, content.size, content.priority, content.destinationPath, contentTag.supportedCarrierIds, contentTag.excludedCarrierIds, contentTag.supportedChannelIds, contentTag.excludedChannelIds from retailcloudPortalDev contentTag use index(contentTag_experienceId) inner join retailcloudPortalDev content on keys contentTag.contentId where contentTag.docType='contenttag' and content.docType='content' and (content.validityStartDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityStartDate,8,4)||'-'||CASE WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jan' THEN '01' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='feb' THEN '02' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='mar' THEN '03' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='apr' THEN '04' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='may' THEN '05' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jun' THEN '06' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='jul' THEN '07' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='aug' THEN '08' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='sep' THEN '09' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='oct' THEN '10' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='nov' THEN '11' WHEN LOWER(SUBSTR(content.validityStartDate,0,3))=='dec' THEN '12' END||'-'||SUBSTR(content.validityStartDate,4,2), date_add_str(NOW_STR('0000-01-01'), 2, 'day'), 'day')<=0) and (content.validityEndDate is not valued or DATE_DIFF_STR(SUBSTR(content.validityEndDate,8,4)||'-'||CASE WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jan' THEN '01' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='feb' THEN '02' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='mar' THEN '03' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='apr' THEN '04' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='may' THEN '05' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jun' THEN '06' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='jul' THEN '07' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='aug' THEN '08' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='sep' THEN '09' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='oct' THEN '10' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='nov' THEN '11' WHEN LOWER(SUBSTR(content.validityEndDate,0,3))=='dec' THEN '12' END||'-'||SUBSTR(content.validityEndDate,4,2), date_add_str(NOW_STR('0000-01-01'), -2, 'day'), 'day')>=0) and (contentTag.experienceId is null or contentTag.experienceId=$parentExperienceId or contentTag.experienceId in $experienceIds) and ('all' in contentTag.supportedCarrierIds or every carrierId in $carrierIds satisfies carrierId in contentTag.supportedCarrierIds end) and not(any carrierId in $carrierIds satisfies carrierId in contentTag.excludedCarrierIds end) and ((('all' in contentTag.supportedChannelIds or (any channelId in contentTag.supportedChannelIds satisfies channelId=$fullChannelCodePath0 or contains($fullChannelCodePath0, channelId || '>') end)) and not(any channelId in contentTag.excludedChannelIds satisfies channelId=$fullChannelCodePath0 or contains($fullChannelCodePath0, channelId || '>') end)) or (any channelId in contentTag.supportedChannelIds satisfies channelId=$fullChannelCodePath0 end)) and ('all' in contentTag.supportedDeviceCodeIds or (any deviceCodeId in contentTag.supportedDeviceCodeIds satisfies deviceCodeId=$deviceCodeId0 or deviceCodeId=$deviceTypeId0 end and not(any deviceCodeId in $deviceCodeIds satisfies deviceCodeId in contentTag.excludedDeviceCodeIds end))) and (contentTag.supportedStoreSetIds is not valued or 'all' in contentTag.supportedStoreSetIds or every storeSetId in $storeSetIds satisfies storeSetId in contentTag.supportedStoreSetIds end) and not(any storeSetId in $storeSetIds satisfies storeSetId in contentTag.excludedStoreSetIds end)"
}]

daschl · February 3, 2017, 8:13am

The main difference I can spot is that the simple plan spans many more ranges than the parameterized one. @geraldss any idea why that could be? Is the planner working differently here for parameterized queries?

mikehcumail · February 3, 2017, 8:25am

The queries for both explain plans should be identical. The exact query is shown at the bottom of each explain plan. The only different between them is the Simple query is submitted through N1qlQuery.simple while the parameterized query is submitted through N1qlQuery.parameterized.

geraldss · February 3, 2017, 2:11pm

The EXPLAIN plans will not be identical in this case. Two issues initially:

When using CONTAINS(), the parameter should be the second argument, not the first. This is more efficient.

When using IN, the parameter should be on the left hand side of the IN, not on the right hand side. This is more efficient.

mikehcumail · February 3, 2017, 4:02pm

Hi Geraldss

Thx for your explanation. So, planner does work differently for
parameterized and simple queries. Unfortunately, in our use case,
parameters are on the right hand side of the IN operator. I think we should
convert the query or at least the IN clause to simple query, right?

Thx
Mike Chu

geraldss · February 3, 2017, 4:09pm

Mike,

Yes, it is perfectly fine to use a non-parametrized query.

Gerald

Topic		Replies	Views
Performance around MapGet vs N1QL Couchbase Server	10	1345	November 10, 2017
Performance Issue using Java SDK - N1Q1 Queries SQL++ java , n1ql	3	838	March 29, 2020
Expected performance for N1QL SQL++	16	4474	July 12, 2020
N1ql Performance issue with JAVA SDK Java SDK	6	1596	March 24, 2017
Will there be a difference in speed in querying using the QueryBuilder and with N1QL strings for Android? Couchbase Lite query , n1ql , android	3	1114	March 21, 2022

Java parameterized query vs simple query

Explain Plan for Simple Query

Explain Plan for Parameterized Query

Related topics