Linq supports multi-query
Turns out Linq supports getting sub-queries along with the main query. It does though through what they call a SelectMany action. You can also use the let clause to do the same thing.
I've got a part of my code for converting the database to Wix that currently performs a 2nd query based on results from the first query. I thought I would put a little effort into optimizing this part of the code since most of my time is spent waiting on the 2 queries.
I did a time trial using 3 options:
- To a SQL server
- To a SQL server with 1 query only
- To a DataSet
I started out as a bunch of small queries for each part I needed to convert. This was the easiest way to incrementally build the full functionality. This took about 17 minutes to convert the 8012 items. I then rolled up all of the small queries into the top most query. This produced the most ugly looking query anyone has probably ever seen. (It is tacked on to the bottom of this post) Anyhow, this only took 5 minutes. A huge improvement over the small queries.
I assumed this meant that the round trip time for each query was eating up all of the time. So thinking this I created a hybrid of the 2 in that I would use a DataSet to cache all of the tables I needed for all of the original small queries. I guessed that this would run almost as fast as the single query version since all of the data was now cached and the round trip cost would be much lower. This method took 9 minutes. Although I think this method is a bit more readable.
I wonder if there is a way to make this giant query smaller or more efficient.
var components = from c in _db.Components
join cg in _db.ComponentGroups on c.ComponentGroupId equals cg.Id
join d in _db.Directories on c.DirectoryId equals d.Id
let ShortcutsPerComponent = from s in _db.Shortcuts
join sp in _db.Shortcut_Platform_XRefs on s.Id equals sp.ShortcutId
join plat in _db.Platforms on sp.PlatformId equals plat.Id
where ((c.Id == s.ComponentId) && (s.Enabled))
select new ShortcutTuple
{
Platform = plat.Name,
Shortcut = s
}
let sortedFiles = from fi in _db.Files
join fileXplat in _db.FilePlatformXrefs on fi.Id equals fileXplat.FileId
join plat in _db.Platforms on fileXplat.PlatformId equals plat.Id
where fi.ComponentId == c.Id
let namespaces = from hn in _db.HelpNamespaces
where (hn.File_Collection == fi.Id)
select hn
let HelpFiles = from hf in _db.HelpFiles
where (hf.File_HxS == fi.Id) && (hf.Enabled)
let index = (from f in _db.Files
where (f.Id == hf.File_HxI) && (f.Enabled)
select new HelpFileTuple
{
Enabled = f.Enabled,
FileName = f.SourceFileName,
Guid = f.Component.ComponentGUID
}).SingleOrDefault()
select new HelpFileData
{
Id = hf.HelpFileKey,
FileName = hf.HelpFileName,
Lang = hf.LangID,
Index = index,
HxR = new HelpFileTuple
{
Enabled = hf.File_HxRFile.Enabled,
FileName = hf.File_HxRFile.SourceFileName,
Guid = hf.File_HxRFile.Component.ComponentGUID
},
HxQ = new HelpFileTuple
{
Enabled = hf.File_HxQFile.Enabled,
FileName = hf.File_HxQFile.SourceFileName,
Guid = hf.File_HxQFile.Component.ComponentGUID
},
Samples = new HelpFileTuple
{
Enabled = hf.File_SamplesFile.Enabled,
FileName = hf.File_SamplesFile.SourceFileName,
Guid = hf.File_SamplesFile.Component.ComponentGUID
}
}
select new FileTuple
{
SourceFileName = fi.SourceFileName,
Guid = fi.Component.ComponentGUID,
Source = String.Concat(fi.Source.Path, fi.SourcePath),
Platform = plat.Name,
Key = fi.IsKeyFile,
Id = fi.Id,
Compressed = fi.UnCab,
ComponentId = c.Id,
NameSpaces = namespaces,
HelpFiles = HelpFiles
}
let conditions = from cxos in _db.ComponentOsXrefs
join os in _db.OsDefinitions on cxos.OsId equals os.Id
where cxos.ComponentId == c.Id
select os.Condition
let directoryIds = from f in _db.Features
join di in _db.Directories on f.KeyComponentDirectoryId equals di.Id
where f.KeyComponentGuid == c.ComponentGUID
select di.DirectoryName
select new
{
Id = c.Id,
WixFileBaseName = cg.WixFileBaseName,
Guid = c.ComponentGUID,
Attributes = c.Attributes,
Directory = d.DirectoryName,
Condition = c.Condition,
Shortcuts = ShortcutsPerComponent,
Files = sortedFiles,
OsConditions = conditions,
CreateFolders = directoryIds
};
Comments
- Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=8330