StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>After a lot of digging it seems LINQ to SQL cannot be convinced to generate the <code>CONVERT(BIT, 0)</code> into the query. However, it can be forced to use a parameter instead of a literal in the WHERE clause, namely by compiling the query first, as follows:</p> <pre><code>private static string QueryCompiled(Context context) { var compiled = CompiledQuery.Compile( (Context c, bool isDeleted) => (from article in c.OutboundArticles where article.IsDeleted == isDeleted select article.Text).Single()); return compiled(context, false); } </code></pre> <p>When we run this query, the following SQL is generated:</p> <pre><code>SELECT [t0].[Text] FROM [OutboundArticle] AS [t0] WHERE [t0].[IsDeleted] = @p0 -- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False] -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 4.0.30319.1 </code></pre> <p>Note the comment, @p0 seems to be typed appropriately for SQL Server Compact to actually use the index. I've verified this with the program below. The program first fills a new DB with 1000000 rows and then queries it with either the compiled or the ordinary query. On my machine, the timings are obvious (average from 3 runs, first discarded):</p> <blockquote> <p>Ordinary query on DB with index: ~670ms</p> <p>Compiled query on DB with index: ~30ms</p> </blockquote> <p>In both cases the query is only executed exactly once, so the compiled query does not have any advantage from the actual compilation. Further evidence that the compiled query actually uses the index while the ordinary does not comes when we manually delete the index in the DB and then run the same queries again (average from 3 runs, first discarded):</p> <blockquote> <p>Ordinary query on DB without index: ~680ms</p> <p>Compiled query on DB without index: ~630ms</p> </blockquote> <pre><code>using System; using System.Data.Linq; using System.Data.Linq.Mapping; using System.Diagnostics; using System.IO; using System.Linq; internal static class Program { private static void Main() { var dataFile = CreateDatabase(); using (var context = new Context(dataFile)) { Console.WriteLine("Executing query:"); // Modify this to see the difference between compiled and uncompiled queries const bool compiled = true; Stopwatch watch = new Stopwatch(); context.Log = Console.Out; watch.Start(); if (compiled) { Console.WriteLine("Result: " + QueryCompiled(context)); } else { Console.WriteLine("Result: " + QueryNormal(context)); } watch.Stop(); Console.WriteLine("Elapsed milliseconds: " + watch.ElapsedMilliseconds); } } private static string CreateDatabase() { var dataFile = Path.Combine(".", "DB.sdf"); bool databaseExists; using (var context = new Context(dataFile)) { databaseExists = context.DatabaseExists(); if (!databaseExists) { Console.WriteLine("Creating database (only done on the first run)..."); context.CreateDatabase(); } } if (!databaseExists) { const int articleCount = 1000000; const int batchSize = 10000; var random = new Random(); for (int batchStart = 0; batchStart < articleCount; batchStart += batchSize) { using (var context = new Context(dataFile)) { for (int number = batchStart; number < batchStart + batchSize; ++number) { context.OutboundArticles.InsertOnSubmit( new OutboundArticle() { Text = new string((char)random.Next(32, 128), random.Next(32)), IsDeleted = number != articleCount / 2 }); } context.SubmitChanges(); } } using (var context = new Context(dataFile)) { context.ExecuteCommand( "CREATE INDEX IX_OutboundArticle_IsDeleted ON OutboundArticle(IsDeleted)"); } } return dataFile; } private static string QueryNormal(Context context) { return (from article in context.OutboundArticles where !article.IsDeleted select article.Text).Single(); } private static string QueryCompiled(Context context) { var compiled = CompiledQuery.Compile( (Context c, bool isDeleted) => (from article in c.OutboundArticles where article.IsDeleted == isDeleted select article.Text).Single()); return compiled(context, false); } } [Table] internal sealed class OutboundArticle { [Column(IsPrimaryKey = true, IsDbGenerated = true)] private int Id; [Column(CanBeNull = false, DbType = "NVARCHAR(32) NOT NULL")] internal string Text; [Column] internal bool IsDeleted; } internal sealed class Context : DataContext { internal Table<OutboundArticle> OutboundArticles; internal Context(string fileName) : base(fileName) { this.OutboundArticles = this.GetTable<OutboundArticle>(); } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload