private string BuildSql(CohortQueryBuilderDependency dependency, ParameterManager parameterManager)
{
//if we are fully cached on everything
if (dependency.SqlFullyCached != null)
{
SetTargetServer(GetCacheServer(), " dependency is cached"); //run on the cache server
CountOfCachedSubQueries++; //it is cached
return(dependency.SqlFullyCached.Use(parameterManager)); //run the fully cached sql
}
switch (CacheUsageDecision)
{
case CacheUsage.MustUse:
throw new QueryBuildingException("Could not build final SQL because " + dependency + " is not fully cached and CacheUsageDecision is " + CacheUsageDecision);
case CacheUsage.Opportunistic:
//The cache and dataset are on the same server so run it
SetTargetServer(DependenciesSingleServer.GetDistinctServer(), "data and cache are on the same server");
return(dependency.SqlPartiallyCached?.Use(parameterManager) ?? dependency.SqlCacheless.Use(parameterManager));
case CacheUsage.AllOrNothing:
//It's not fully cached so we have to run it entirely uncached
SetTargetServer(DependenciesSingleServer.GetDistinctServer(), "cache and data are on seperate servers / access credentials and not all datasets are in the cache");
return(dependency.SqlCacheless.Use(parameterManager));
default:
throw new ArgumentOutOfRangeException();
}
}
private string BuildSql(CohortAggregateContainer container, ParameterManager parameterManager)
{
Dictionary <CohortQueryBuilderDependency, string> sqlDictionary;
//if we are fully cached on everything
if (Dependencies.All(d => d.SqlFullyCached != null))
{
SetTargetServer(GetCacheServer(), "all dependencies are fully cached"); //run on the cache server
//all are cached
CountOfCachedSubQueries = CountOfSubQueries;
sqlDictionary = Dependencies.ToDictionary(k => k, v => v.SqlFullyCached.Use(parameterManager)); //run the fully cached sql
}
else
{
string uncached = "CacheUsageDecision is " + CacheUsageDecision +
" and the following were not cached:" + string.Join(Environment.NewLine,
Dependencies.Where(d => d.SqlFullyCached == null));
switch (CacheUsageDecision)
{
case CacheUsage.MustUse:
throw new QueryBuildingException(
"Could not build final SQL because some queries are not fully cached and " + uncached);
case CacheUsage.Opportunistic:
//The cache and dataset are on the same server so run it
SetTargetServer(DependenciesSingleServer.GetDistinctServer(), "not all dependencies are cached while " + uncached);
CountOfCachedSubQueries = Dependencies.Count(d => d.SqlFullyCached != null);
sqlDictionary =
Dependencies.ToDictionary(k => k,
v => v.SqlFullyCached?.Use(parameterManager) ??
v.SqlPartiallyCached?.Use(parameterManager) ??
v.SqlCacheless.Use(parameterManager)); //run the fully cached sql
break;
case CacheUsage.AllOrNothing:
//It's not fully cached so we have to run it entirely uncached
SetTargetServer(DependenciesSingleServer.GetDistinctServer(), "not all dependencies are cached while " + uncached);
//cannot use any of the caches because it's all or nothing
CountOfCachedSubQueries = 0;
sqlDictionary =
Dependencies.ToDictionary(k => k, v => v.SqlCacheless.Use(parameterManager)); //run the fully uncached sql
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return(WriteContainers(container, TargetServer.GetQuerySyntaxHelper(), sqlDictionary, 0));
}
private void HandleDependency(CohortQueryBuilderDependency dependency, bool isPatientIndexTable, ITableInfo dependantTable)
{
_log.AppendLine($"Found dependant table '{dependantTable}' (Server:{dependantTable.Server} DatabaseType:{dependantTable.DatabaseType})");
//if dependencies are on different servers / access credentials
if (DependenciesSingleServer != null)
{
if (!DependenciesSingleServer.TryAdd(dependantTable))
{
//we can no longer establish a consistent connection to all the dependencies
_log.AppendLine($"Found problematic dependant table '{dependantTable}'");
//if there's no cache server that's a problem!
if (CacheServer == null)
{
throw new QueryBuildingException($"Table {dependantTable} is on a different server (or uses different access credentials) from previously seen dependencies and no QueryCache is configured");
}
//there is a cache server, perhaps we can dodge 'dependantTable' by going to cache instead
bool canUseCacheForDependantTable =
(isPatientIndexTable ? dependency.SqlJoinableCached : dependency.SqlFullyCached)
!= null;
//can we go to the cache server instead?
if (canUseCacheForDependantTable && DependenciesSingleServer.TryAdd(CacheServer))
{
_log.AppendLine($"Avoided problematic dependant table '{dependantTable}' by using the cache");
}
else
{
DependenciesSingleServer = null;
//there IS a cache so we now Must use it
if (CacheUsageDecision != CacheUsage.MustUse)
{
SetCacheUsage(CacheUsage.MustUse, $"Table {dependantTable} is on a different server (or uses different access credentials) from previously seen dependencies. Therefore the QueryCache MUST be used for all dependencies");
}
}
}
}
if (DependenciesSingleServer != null &&
CacheServer != null &&
CacheUsageDecision == CacheUsage.Opportunistic)
{
//We can only do opportunistic joins if the Cache and Data server are on the same server
bool canCombine = DependenciesSingleServer.AddWouldBePossible(CacheServer);
if (!canCombine)
{
SetCacheUsage(CacheUsage.AllOrNothing, "All datasets are on one server/access credentials while Cache is on a separate one");
}
}
}