Use CompiledQuery.Compile to improve LINQ to SQL performance



After reading DLinq (Linq to SQL) Performance and in particular
Part 4  I had a few questions.

If CompiledQuery.Compile gives so much benefits, why not to do it for all Linq To Sql queries? Is any essential disadvantages of compiling all select queries? What are conditions, when compiling makes whose performance, for how much percentage? World be good to have default on application config level or on DBML level to specify are all select queries to be compiled? And the same questions about Entity Framework CompiledQuery Class.

However in comments I’ve found answer  of the author ricom

6 Jul 2007 3:08 AM
Compiling the query makes it durable. There is no need for this, nor is there any desire, unless you intend to run that same query many times.
SQL provides regular select statements, prepared select statements, and stored procedures for a reason.  Linq now has analogs.

Also from 10 Tips to Improve your LINQ to SQL Application Performance

If you are using CompiledQuery make sure that you are using it more than once as it is more costly than normal querying for the first time. The resulting function coming as a CompiledQuery is an object, having the SQL statement and the delegate to apply it.  And your delegate has the ability to replace the variables (or parameters) in the resulting query.

However I feel that many developers are not informed enough about benefits of Compile.
I think that tools like FxCop and Resharper should check the queries  and suggest if compiling is recommended.

Related Articles for LINQ to SQL:
MSDN How to: Store and Reuse Queries (LINQ to SQL)
10 Tips to Improve your LINQ to SQL Application Performance

Related Articles for Entity Framework:
MSDN: CompiledQuery Class
Exploring the Performance of the ADO.NET Entity Framework – Part 1

Exploring the Performance of the ADO.NET Entity Framework – Part 2
ADO.NET Entity Framework 4.0: Making it fast through Compiled Query

Combining javascript files

I’ve read Combining Client Scripts into a Composite Script and wanted to use it.
Then I’ve read Julian Jelfs concerns ScriptManager.CompositeScript issues

However the article Combining javascript files with Ajax toolkit library describes workarounds, that make the solution workable.
You also can use Script reference profiler:

Related posts:

Using ScriptManager with other frameworks

MSDN documentation: CompositeScriptReference

The older implementations, that has been superseded by CompositeScript class:

Combining, Compressing, Minifying ASP.NET ScriptResource and HTML Markups

Message during Edit and Continue doesn’t give an option to edit.

During my Edit and Continue session I received a message
Microsoft Visual Studio
Modifying a catch handler around an active statement will prevent the debug session from continuing while Edit and Continue is enabled.

I would expect that Visual Studio give me option to edit, but stop Edit and Continue or Cancel, but it only disallow edit .

 I’ve posted the suggestion to MS Connect

Related requests on Connect:

Google Documents have very limited table support

I am using Google Documents for text editing and generally it works quite well.

However I’ve found, that table support is very limited.
I’ve tried to copy and paste single row of the table, but didn’t found how to do it properly.(the issue was reported to Google almost 3 years ago)
I’ve switched to Google new spreadsheet and done my table quite easy.
However when I tried to paste it to Google doc, it was copied as plain text, not as a table(the issue also was reported more than 2 years ago).
Finally I’ve pasted Google spreadsheet into MS Word(it also was copied as plain text),in MS Word converted text to table, then copied the table from MS Word document and posted it to Google Document.
It is a long way to copy table from one Google application to another with the help of MS Word.

I’ve reported the problem but I do not expect an answer.

It’s funny that Google Docs Known Issues are listed on 1 small page (only 8 known issues.

Lookup Tables with fallback support

Our database has a few lookup tables that uses similar approach to implemented by .Net Framework for localized resources:

At run time, ASP.NET uses the resource file that is the best match for the setting of the CurrentUICulture property. If there is no match for the current UI culture, ASP.NET uses resource fallback. It starts by searching for resources for a specific culture. If those are not available, it searches for the resources for a neutral culture. If these are not found, ASP.NET loads the default resource file.

Let’s assume that we have a look-up table with Time Zones for different countries, states within the countries and post codes within state.
To reduce maintenance of lookup table, if most of the country has one timezone, but only a few states(or post codes) have different timezones, we are not storing information about all states/postcodes, but storing default timezone for the country and then exceptional  states/postcodes:

Country Code State Code Post Code Time Zone Comment(not a part of database table)
CC1 Null Null 1 Simple -the same Time Zone for the whole country
CC2 ST1 Null 2 In the country different states have different zones
CC2 ST2 Null 3 regardless of postcodes
CC3 ST1 1001 2 In the same state different post codes have different time zones
CC3 ST1 1002 3
CC4 null 1001 2 Country doesn’t have states, but different post codes have different time zones
CC4 null 1002 3
CC5 null null 2 Most of states in the country have one time zone, but some states have different time zones
CC5 ST55 null 3
CC6 ST5 null 2 Most of postcodes in the state have one time zone, but some postcodes have different time zones
CC6 ST5 2007 3

The algorithm to retrieve the the timezone is the following
1. Try to retrieve the row for specified country, state and postcode. If record found, we return the time zone.
2.  If record not found, try to retrieve the row for specified country and state. If record found, we return the time zone.
3.  If record not found, try to retrieve the row for specified country. If record found, we return the time zone.
4.  If record not found, throw exception- we expect that at least 1 record per country should exist.

This approach works quite well, but I have some  concerns about performance. Usually the table is stored as cached singleton with LINQ to SQL generated and loaded table.
I am not sure how good LINQ to SQL generated entities work with queries to retrieve records for specified partial keys.
I world be interesting to see some benchmarks for LINQ to SQL loaded into memory collections similar as it was done for DataTable .
I am appreciate any suggestions regarding improving performance, as well as the regarding whole fallback approach.

Generated tests assign enums to enum constructor

When Visual Studio generates tests, for initial value for enums it uses enum constructor.
MyClass.TracingMode tracingMode = new MyClass.TracingMode(); // TODO: Initialize to an appropriate value
It looks strange.
Is it worth to suggest in MS Connect to change the default?

Does StackOverflow make my blog redundant?

A few years ago I wrote a post describing how I creating many of my blog posts.
When I had a new question/issue that required some research, Usually I do a Google search, open a few articles and extract what is useful from them. Usually I was not able to find exact answer, so I had to  make a solution of my problem based on the ideas from different articles add my own ideas, combined the ideas from different sources, and finally describe my sollution in a blog post.

Recently I realized, that from top 10 result of Google search, pages from StackOverflow usually give me the most relevant answer to my questions- often exact, what I wanted to ask.

If I am unlucky to find satisfactory result, and my issue is not very urgent, instead of doing research by myself, I can ask a question on StackOverflow and there is a good chance, that next day someone will give me an idea, or (even better) tell that it possible duplicate of question, and refer me to appropriate thread.

If I wouldn’t have any responses from community and I would have to solve my problem by myself, I will answer my own question, and it will be seen by more people, than a post in my blog.

It’s definitely good that StackOverflow has answers for many needed issues.
Is it bad, that I will write less posts in my blog? Actually not, some of my posts will published as questions/answers is StackOverflow instead of in my blog.