private Map<PhysicalStructure, DeployedPhysicalStructure> buildStructureMap(
Set<DeployedPhysicalStructure> deployedStructures) throws Exception {
Map<PhysicalStructure, DeployedPhysicalStructure> structureMap =
new HashMap<PhysicalStructure, DeployedPhysicalStructure>();
for (DeployedPhysicalStructure p : deployedStructures) {
structureMap.put(p.getStructure(), p);
}
return structureMap;
}
@Override
public boolean thisQueryUsesAtLeastOneOfTheseStructures(
String query, List<PhysicalStructure> whichStructuresToInclude) throws Exception {
List<String> projectionNamesUsedInThePlan;
String rawExplainOutput;
if (experimentCache != null
&& experimentCache.getExplain(query, new PhysicalDesign(whichStructuresToInclude))
!= null) {
rawExplainOutput =
experimentCache.getExplain(query, new PhysicalDesign(whichStructuresToInclude));
projectionNamesUsedInThePlan =
dbDeployer
.getQueryPlanParser()
.searchForPhysicalStructureNamesInRawExplainOutput(rawExplainOutput);
log.status(LogLevel.DEBUG, "query plan with this allowed projections fetched from cache.");
} else { // we need to measure those!
Set<String> allDeployedStructureNames =
microsoftDeployer.retrieveAllDeployedStructuresBaseNamesFromDB(queryConnection);
Set<DeployedPhysicalStructure> deployedStructures =
microsoftDeployer.retrieveDeployedStructuresFromDB(
queryConnection, allDeployedStructureNames, new HashSet<String>());
Set<DeployedPhysicalStructure> includeStructures = new HashSet<DeployedPhysicalStructure>();
Set<DeployedPhysicalStructure> excludeStructures = new HashSet<DeployedPhysicalStructure>();
List<PhysicalStructure> missingStructures = new ArrayList<PhysicalStructure>();
// DY: I know this is dumb way.. will fix later
for (PhysicalStructure p : whichStructuresToInclude) {
boolean isFound = false;
for (DeployedPhysicalStructure deployed : deployedStructures) {
if (p.equals(deployed.getStructure())) {
isFound = true;
break;
}
}
if (!isFound) {
missingStructures.add(p);
}
}
if (!missingStructures.isEmpty()) {
microsoftDeployer.deployDesign(missingStructures, false);
allDeployedStructureNames =
microsoftDeployer.retrieveAllDeployedStructuresBaseNamesFromDB(queryConnection);
deployedStructures =
microsoftDeployer.retrieveDeployedStructuresFromDB(
queryConnection, allDeployedStructureNames, new HashSet<String>());
}
for (DeployedPhysicalStructure p : deployedStructures) {
if (whichStructuresToInclude.contains(p.getStructure())) {
includeStructures.add(p);
} else {
excludeStructures.add(p);
}
}
// disable indexes in the excludeList.
for (DeployedPhysicalStructure p : includeStructures) {
MicrosoftDeployedPhysicalStructure msp = (MicrosoftDeployedPhysicalStructure) p;
if (msp.isDisabled()) {
if (!msp.enableStructure(queryConnection)) {
throw new Exception("Failed to enable a structure: " + msp.getName());
}
}
}
for (DeployedPhysicalStructure p : excludeStructures) {
MicrosoftDeployedPhysicalStructure msp = (MicrosoftDeployedPhysicalStructure) p;
if (!msp.isDisabled()) {
if (!msp.disableStructure(queryConnection)) {
throw new Exception("Failed to enable a structure: " + msp.getName());
}
}
}
// // disable indexes in the excludeList.
// for (DeployedPhysicalStructure p : deployedStructures) {
// if (excludeList.contains(p.getName())) {
// MicrosoftDeployedPhysicalStructure msp = (MicrosoftDeployedPhysicalStructure)p;
// if (!msp.disableStructure(conn)) {
// throw new Exception("Failed to disable a structure: " + msp.getName());
// }
// disabledStructures.add(msp);
// }
// }
// get first query plan.
Statement stmt = queryConnection.createStatement();
stmt.executeUpdate("SET SHOWPLAN_ALL ON");
ResultSet rs = stmt.executeQuery(query);
StringBuilder queryPlan = new StringBuilder();
// retrieve query plan.
while (rs.next()) {
queryPlan.append(rs.getString(1) + "\n");
}
rs.close();
stmt.executeUpdate("SET SHOWPLAN_ALL OFF");
stmt.close();
rawExplainOutput = queryPlan.toString();
projectionNamesUsedInThePlan =
dbDeployer
.getQueryPlanParser()
.searchForPhysicalStructureNamesInRawExplainOutput(rawExplainOutput);
// rollback disabled indexes.
// for (MicrosoftDeployedPhysicalStructure msp : disabledStructures) {
// msp.enableStructure(conn);
// }
int originalSize = allDeployedStructureNames.size();
allDeployedStructureNames.removeAll(projectionNamesUsedInThePlan);
int numberOfUnusedProjections = allDeployedStructureNames.size();
if (originalSize != numberOfUnusedProjections) {
log.status(
LogLevel.DEBUG,
(originalSize - numberOfUnusedProjections)
+ " out of "
+ originalSize
+ " projections were used for query "
+ query);
return true;
} else {
log.status(
LogLevel.DEBUG,
"None of the " + originalSize + " projections were used for query " + query);
log.status(
LogLevel.DEBUG,
"allowed projections were "
+ StringUtils.Join(
StringUtils.ElemStringify(new ArrayList<String>(allDeployedStructureNames)),
",")
+ " ");
log.status(
LogLevel.DEBUG,
"used projections were "
+ StringUtils.Join(
StringUtils.ElemStringify(new ArrayList<String>(projectionNamesUsedInThePlan)),
",")
+ " ");
log.status(LogLevel.DEBUG, "query plan was " + rawExplainOutput);
return false;
}
}
return false;
}
@Override
protected String getQueryPlan(String query, List<PhysicalStructure> physicalStructuresToInclude)
throws Exception {
Set<String> allDeployedStructureNames =
microsoftDeployer.retrieveAllDeployedStructuresBaseNamesFromDB(queryConnection);
Set<DeployedPhysicalStructure> deployedStructures =
microsoftDeployer.retrieveDeployedStructuresFromDB(
queryConnection, allDeployedStructureNames, new HashSet<String>());
Set<DeployedPhysicalStructure> includeStructures = new HashSet<DeployedPhysicalStructure>();
Set<DeployedPhysicalStructure> excludeStructures = new HashSet<DeployedPhysicalStructure>();
List<PhysicalStructure> missingStructures = new ArrayList<PhysicalStructure>();
for (PhysicalStructure p : physicalStructuresToInclude) {
if (!deployedStructures.contains(p)) missingStructures.add(p);
}
if (!missingStructures.isEmpty()) {
microsoftDeployer.deployDesign(missingStructures, false);
allDeployedStructureNames =
microsoftDeployer.retrieveAllDeployedStructuresBaseNamesFromDB(queryConnection);
deployedStructures =
microsoftDeployer.retrieveDeployedStructuresFromDB(
queryConnection, allDeployedStructureNames, new HashSet<String>());
}
for (DeployedPhysicalStructure p : deployedStructures) {
if (physicalStructuresToInclude.contains(p.getStructure())) {
includeStructures.add(p);
} else {
excludeStructures.add(p);
}
}
log.status(
LogLevel.DEBUG,
String.format(
"Measuring latency: # structures to include = %d, exclude = %d, missing = %d, total # of structures = %d",
includeStructures.size(),
excludeStructures.size(),
missingStructures.size(),
deployedStructures.size()));
// disable indexes in the excludeList.
for (DeployedPhysicalStructure p : includeStructures) {
MicrosoftDeployedPhysicalStructure msp = (MicrosoftDeployedPhysicalStructure) p;
if (msp.isDisabled()) {
if (!msp.enableStructure(queryConnection)) {
throw new Exception("Failed to enable a structure: " + msp.getName());
}
}
}
for (DeployedPhysicalStructure p : excludeStructures) {
MicrosoftDeployedPhysicalStructure msp = (MicrosoftDeployedPhysicalStructure) p;
if (!msp.isDisabled()) {
if (!msp.disableStructure(queryConnection)) {
throw new Exception("Failed to enable a structure: " + msp.getName());
}
}
}
// get first query plan.
Statement stmt = queryConnection.createStatement();
stmt.executeUpdate("SET SHOWPLAN_ALL ON");
ResultSet rs = null;
try {
rs = stmt.executeQuery(query);
} catch (Exception e) {
e.printStackTrace();
System.out.println("Query execution error:");
System.out.println(query);
throw e;
}
StringBuilder queryPlan = new StringBuilder();
// retrieve query plan.
while (rs.next()) {
queryPlan.append(rs.getString(1) + "\n");
}
rs.close();
stmt.executeUpdate("SET SHOWPLAN_ALL OFF");
stmt.close();
return queryPlan.toString();
}