What .Net Developers Should Know about MS SQL and IIS

Summary

In this post, I’m going to explain a couple techniques and tools that every developer should know.  If you are just graduating from college and you are looking for a .Net job, learn these simple techniques and advance your career.  If you’re a mid-level to advanced .Net developer and you don’t know these tools and techniques yet, learn them.  These tools and techniques will save you time and give you the edge to building better software.

SQL Profiler

Let’s assume you have built this outstanding program.  It’s a work of art.  It uses .Net Core 2.0 with IOC containers, Entity Framework Core 2.0 and other advanced technologies.  You’re testing your web-based software and you notice a pause when you click on a button or a page is loading.  The first thing that pops into my mind is: What is causing the slowdown?  Is it the database or the IIS server?  Finally, what can I do about it?

Let’s eliminate or confirm the database.  If you have installed the profiler tool in SQL (I’m going to assume you did, otherwise you’ll need to start the installer and install this tool), then go to the Tools menu and select SQL Server Profiler.  A new window will open and you’ll need to connect to your database instance as though you are opening the management studio itself.  Once you open the profiler, it’s time to execute that page that you are having issues with.  You can click on the stop button and use the eraser to clean up any records that have shown in the window already.  Get to the point where you are about to click the button to your web page.  Then hit the run button in the profiler and hit the web page.  Once the page loads, then hit the stop button in your profiler so nothing else will record.  Now you have records to analyze.  You’ll be surprised at the number of database calls EF will perform.

I used this project as a demo for the screenshot coming up:

https://github.com/fdecaire/MVCWithAutoFacIOC

Download the project, run the database create script (store_product_database_setup.sql) and run the application.  You should see something like this:

As you can see there is one call to the database and you can see the “select” command.  Click on the “BatchStarting” line and notice the detail in the window at the bottom:

Now you can scrape that query and paste it into the Server Management Studio and test the exact query that your Entity Framework is sending to SQL:

This will indicate if you are querying for too many fields, or the total records queried are crazy.  If you discover that your query result was a million records and your interface only shows the top 10 records, then you need to tweak your LINQ query to only ask for 10 records.  In this tiny example we have three records.  Let’s make it ask for 2 records.  Here’s my original LINQ query:

(from s in _db.Stores select s).ToList();

I changed it to:

(from s in _db.Stores select s).Take(2).ToList();

Re-run the program, capture the data in profiler and this is what I get:

Notice the “Top(2)” difference in the query.  This is the kind of performance tuning you should be aware of.  It’s very easy to create C# code and LINQ queries, only to never understand what is really going on behind the scenes.  Entity Framework takes your LINQ query and turns it into a string that represents a SELECT query and transmits that to MS SQL.  Then MS SQL queries the database, returns the results so that EF can turn it back into a list of objects.  With SQL profiler, you can get into the nuts and bolts of what is really going on and I would recommend you run profiler at least once after you have built your software and think it is ready for prime-time.  If you see a query pause, copy the profile SQL query into management studio and see if you can speed up the query and get the query results that you need.

Another tool you can use is the estimated execution plan tool.  The toolbar button looks like this:

This tool will break your query down into the pieces that will be executed to form the results.  In the case of my previous query, there is only one piece:

That piece of the query costs 100% of the execution time.  If your query included a union and maybe some sub-queries, this tool is very useful in determining which part of the query is costing you the most processing cycles.  Use this tool to decide which part of your query you want to focus your energy on.  Don’t waste time trying to optimize the portion of your query that only takes 2% of the execution time.  Maybe you can get that to go twice as fast, but the overall query will only be about 1% faster than before.  If you focus your energy on a section that takes 98% of your execution time, then you’ll be able to boost the performance in a noticeable manner.

Web Server Troubleshooting

If you’re using IIS, some day you’ll run into a problem where you don’t get any logs and your website or API crashes immediately (usually a 500 error).  This is always a difficult problem to troubleshoot, until you realize that there are only a hand-full of problems that cause this.  The most common problem is an issue with the XML formatting in your web.config file.  I can’t tell you how many times I have been bit by this problem!  The easiest way to test and troubleshoot this error is to open the IIS manager control panel, select your website and then click on one of the icons that displays a section of your web.config file, like “Modules”:

If there is an error, then the line number in the web.config file will be shown.  You’ll be able to look at the xml in the web.config and see your missing tag, extra quote or other symbol (sometimes it’s an “&”, “>” or “<” symbol inside your database connection string password or something).  Fix the web.config issue and go back to modules again.  If there is another error, then fix it and return again, until it works.

On .Net Core, there is an error log that can report startup errors.  This logging happens before your log program starts and is very handy for situations where you don’t get any logging.  When you publish your site to a folder in .Net Core (or Core 2.0), you’ll end up with a bunch of dlls, some config files and a web.config file.  The web.config file is mostly empty and might look like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <system.webServer>
 <handlers>
 <add name="aspNetCore" path="*" verb="*" modules="AspNetCoreModule" resourceType="Unspecified" />
 </handlers>
 <aspNetCore processPath="dotnet" arguments=".\Website.dll" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" />
 </system.webServer>
</configuration>

Change your “stdoutLogFile” parameter to point to a file location that you can find.  I usually set mine to “C:\logs\myapplication_logging_error.txt” or something like that.  Then I run the program until it crashes and check in the c:\logs directory to see if the file exists.  If it does, it usually contains information about the crash that can be used to troubleshoot what is going on.  I’m assuming at this point in your troubleshooting, the website or API works from Visual Studio and you are having issues with the deployed application.  If you are having issues with executing your application in Visual Studio, you should be able to zero in on the error in VS using breakpoints and other tools.

For NLog there is an error log for the error log.  This is where the errors go when there is an error detected in the NLog code.  Usually caused by a configuration error.  At the top of your nlog.config file should be something like this:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 autoReload="true"
 internalLogLevel="Warn"
 internalLogFile="C:\logs\myapplication_nlog.txt">

As you can see, there is an “internalLogFile” parameter.  Set this to a location and filename that you can find.  Then execute your program and see if that log file appears.  If it does, then open it up and examine the contents.  Usually it’s an exception error involving the NLog logger.

Application Pools

The application pool type must match the .Net version that you are using.  For .Net Core and Core 2.0, you’ll need to use “no managed code”.  For .Net, you’ll set your pool to “.Net Framework v4.0”.  If you are unfortunate enough to be using really obsolete legacy code, you can set it to “.Net Framework v2.0”.

When you deploy your Core 2.0 application, you may need to cycle the application pool.  You’ll run into a problem where you deploy new code and the “bug fix” or “new feature” does not show up.  Cycle the app pool and see if it fixes your problem.  The application pool might be using a copy of the previous program in memory.

Sites

Inside your site settings, there is an ASP settings section:

You can turn on debugging flags inside here.  For your local development system, turn it all on:

Leave these settings off for any production system.  You do not want to advertise the line numbers of your code (and your code) when you have a crash.  For development purposes, you want to know all the data you can get from an exception.  If these settings are on, you’ll get the full dump of the exception message in your browser and then you can return to Visual Studio to find the exact line of code where the error occurred.

I hope this saves you some time when a frustrating deployment problem occurs.  The trick is to learn the most common causes of deployment problems so you don’t spend all day troubleshooting a tiny error instead of working on that next big chunk of code.

 

 

 

 

Fluent NHibernate “Not Supported By” Exception

Summary

So my company has successfully switched to using Fluent NHibernate for our production systems.  So far the results are astounding.  One thing our developers have learned is that there is no “perfect” ORM.  Not yet anyway.  Each seems to have a flaw or two.  NHibernate has a couple of issues that can be worked around.  One issue, I mentioned in an earlier post regarding the 1200 parameter limit.  While unexpected, it was easy to see why it was an issue.  The issue I want to talk about today came up when one of our developers was working with a group by query involving other query operations like order by and take, etc. So I’m going to talk about one issue and show a solution.


The Setup

I’m going to use my standard sampledata database in MS SQL server with 3 tables: Product, ProductType and Store.  Here’s the ERD:



I put some seed data into these tables, it doesn’t matter what you put in the tables.  There is a SQL script in the project (named “CreateTables.txt”), you can copy the text inside this file and execute it inside a query window in MS SQL Server Management Studio.


The Ugly

Here’s the offending LINQ that I setup in my console app:

var query =
    (from p in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).OrderBy(u => u.name).ToList();

If you attempt to execute this query, you’ll get an error like this:


This error only occurred when I added the “OrderBy” onto the query.  So it appears that a combination of query parameters can cause this error to occur, or possibly there is an issue with the anonymous types generated in the “select new” part of the query.  


The Fix

I dug around for an answer to this problem and found this Stack Overflow issue:

Linq query nhibernate; not supported exception

That’s where I discovered a solution to this query.  I added the “AsEnumerable()” before the “OrderBy” to fix this problem:

var query =
    (from in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).AsEnumerable().OrderBy(u => u.name).ToList();


Now the query executes and produces the expected results.

You can download the sample project here:
FluentNHibernateGroupBlogPost.zip

 

Fluent NHibernate: Removing a Relationship Connection From a Many-to-Many

In my last blog post I forgot to show how to disconnect a student from a class but leave both the student and the class records intact.  So I quickly ninja’d something together to demonstrate how simple it is to perform this vital function.  Here’s my code:

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    // delete connection between student and class
    var student = (from s in db.Query<Student>()
                    where s.Name == “New Student
                    select s).FirstOrDefault();

    using (db.BeginTransaction())
    {
        foreach (var temp in student.Class)
        {
            if (temp.Name.Trim() == “CSC 300“)
            {
                student.Class.Remove(temp);
                break;
            }
        }
                    
        db.Transaction.Commit();
    }

}

Firsrt, I queried for the student I wished to disconnect.  Then I searched through the list of Classes that the student was assigned to, searching for the one class to disconnect.  Once I found the Class (be aware of the Trim() since the Name is a character field) I removed it from the list and committed the transaction.  This was all that was necessary to make the StudentClass record delete connecting “New Student” and “CSC 300”.

As Hannibal Smith would say “I love it when a plan comes together!”



 

Entity Framework 6 vs. NHibernate smackdown!

It’s time for a performance test between EF-6 and NHibernate.  Previously I did a smackdown between EF-6 and LINQ-to-SQL (see this article: Entity Framework 6 vs. LINQ-toSQL smackdown!).  This time I’m going to show the performance of inserts between EF-6 and NHibernate.

I used the same tables and data as in the previous blog posting.  The code I used for NHibernate to insert records looks like this (I also created code for select, update and delete):

using (db.BeginTransaction())
{
    for (int j = 0; j < 10; j++)
    {
        for (int i = 0; i < 1000; i++)
        {
            Person personRecord = new Person()
            {
                first = firstnames[i],
                last = lastnames[i],
                department = 1
            };

            db.Save(personRecord);
        }
    }
    db.Transaction.Commit();
}

Here are the results from the CRUD tests comparing EF-6 with NHibernate (I also threw in the latest test run of LINQ-to-SQL for comparison):


OK, now for a few caveats:

1. I ran each test 5 times and chose the fastest time.
2. LINQ-to-SQL is not optimized for maximum speed.
3. These times are based on my machine at the time that I ran these programs.
4. These tests are only for general cases.
5. I put the select query in a loop to run 1000 times to obtain a measurement.

Generally speaking it appears that NHibernate is quite a bit faster than either EF-6 or LINQ-to-SQL for CRUD operations.  The select query is not a very good test.  In fact, the bulk of the time could be attributed to the looping code itself since my guess is that a select is all done within the ORM once the data has been cached.  A more effective test would involve 5 to 10 large tables of data using multiple types of select cases (like subqueries and outer joins, order by’s and group by’s).  Another test that should be performed as a baseline, would be a direct sql query without the aid of an ORM.  This should be faster than all of these ORMs.

To obtain the code, download this zip file containing all 3 test projects:

ORMSpeedTestProjects.zip

If you download and use these projects, don’t forget to change the configuration settings for each project to point to your database!


 

Entity Framework 6 vs. LINQ-to-SQL smackdown!

Today, I’m doing my own performance testing on Entity Framework 6 and LINQ to SQL.  I created two tables in a sample SQL server database.  Here is the ERD for the two tables I created:

Next, I created two console application projects.  One to test LINQ to SQL and the other with EF-6.  You can download the LINQ to SQL test project here: linq2sqltest.zip and the EF6 test project here: ef6test.zip.

I started with the LINQ to SQL test using a basic insert loop like this:

for (int j = 0; j < 10; j++)
{
    for (int i = 0; i < 1000; i++)
    {
        person personRecord = new person()
        {
            first = firstnames[i],
            last = lastnames[i],
            department = 1
         };


         db.persons.InsertOnSubmit(personRecord);
    }
}

db.SubmitChanges();

I used the same code for the normal EF6 test:

for (int j = 0; j < 10; j++)
{
    for (int i = 0; i < 1000; i++)
    {
        person personRecord = new person()
        {
            first = firstnames[i],
            last = lastnames[i],
            department = 1
         };


         db.persons.Add(personRecord);
    }
}

db.SaveChanges();

I am using EF 6 version 6.0.1 as of this writing.  Microsoft has indicated that they are working on a bug fix version of EF6 that will be version 6.0.2.  There is no expected date when that version will become available, so keep your eyes open for this one.

In order to make the test measurable, I downloaded two text files full of first and last names with at least 1000 rows.  Then I ran the loop through 10 times to make it 10,000 rows of data inserted into the person table.  You can download the text files from the census like I did (if you want to include more names) by going to this stack overflow article and click on the suggested links: Raw list of person names.

I also attempted to move the db.SaveChanges(); inside the loop to see what effect that would have on the timing, and received the expected result of slower-than-dirt!  So I did some research to find a method to speed up the inserts and came across this stack overflow hint on bulk inserts: EF codefirst bulk insert.  By changing the configuration of the context before the inserts were performed, I was able to increase the insert speed significantly:

db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.ValidateOnSaveEnabled = false;

Here are the final results of all four tests:

The first test is the base-line LINQ-to-SQL.  I did not attempt to optimize this query (and I’m sure there is a way to make it go faster as well).  My interest was to see if I can make EF6 perform as fast or faster than straight LINQ-to-SQL.  The first test is the slow test where I put the SaveChanges() inside the loop (epic fail).  T2 is the second test, which is shown in the code above.  The third test (T3) is the code where I added the two configuration changing lines before running the insert loops.  All timings are in seconds.  As you can see from the results LINQ-to-SQL, un-optimized ran for 18.985 seconds to insert 10,000 records.  EF6 in test 3 ran for 2.371 seconds.  Just using the same code in EF6 produced a poor result at just over 46 seconds.  Be conscious of the time that EF6 is going to take some extra work to make it perform.

UPDATE: I have since written a test for NHibernate inserts and re-ran the above tests.  The LINQ-to-SQL test was able to perform the same inserts at a measured time of 3.49 seconds.  Because of this discrepency, I re-ran all tests several times and it seems that all the measurements are close except for the LINQ-to-SQL.  This is not a scientifically accurate test and I would recommend downloading my examples and run your own tests on your own hardware.

 

LINQ and Entity Framework … sigh

Our company has a ton of legacy code using VB and simple text based queries that are submitted to the database.  There are several disadvantages to this method versus new techniques like LINQ and EF.  First, syntax errors in the query itself can only be tested in run-time.  Second, syntax highlighting, automatic formatting and intellisense are not supported.

One of the improvements started earlier this year was the use of LINQ in place of string queries.  Immediately a flaw was discovered in the interface for setting up tables.  That flaw is the missing capability to synchronize changes made in tables in the corresponding database.  The developer is forced to delete the table and re-add the table in order to reflect any field changes.  This is ok, if there are no stored procedures that return a result set for that table.  Then you have to go to each stored procedure and switch to something else, replace the table, then switch the stored procedures back.  There are third-party add-ons that can synch the tables, but they seem to of the bloat-ware variety and are not very effective (some of them don’t work right).  A second “feature” of LINQ is that it creates a settings.settings file in the project to add the ability to change database settings from an interface.  This seems like a great feature until one realizes that it over writes the apps.config file when changes are saved.  The side effect is that comments in apps.config are removed.  Our company uses comments to switch between databases located on our local PC, staging server and the production server.  Powershell scripts comment and uncomment the sections for each of the connector strings to enable the correct database connector.  This goes out the window when the settings.settings control panel wipes out any commented connection strings (usually the staging server and production server settings). 

So we began looking at Entity Framework.  This was a dream.  It has a synchronize capability making it much easier to use.  I jumped in with both feet and started replacing LINQ setups with EF (they are compatible with each other, just keep the table names the same).  My plan was to create a device context of table definitions in each object directory.  This would create an organization where the tables that were used by the current object were in the same directory and any abandoned tables would not be used by some far off code not related to the object that might be refactored in the future.  Then I ran into a serious problem.  EF doesn’t recognize name spaces.  Yikes!  So I changed plans quick.  I placed the device context in a central directory under the project and decided we would just use one device context for all objects.  I further discovered that only one database can be used per device context.  While that hasn’t been an issue yet, it will be if we decide to use this for more of our larger objects (that query spanning two or more databases).  I theorized that we might be able to use nested “using” statements, but I haven’t tried it yet.  This issue, coupled with the namespace issue has made me put on the breaks until I can do further analysis.  I have also heard rumors that EF is not as efficient at queries as LINQ. 

Other issues that discovered is that the objects created for the tables that are dragged into the context may have an “s” appended to them.  This causes all kinds of confusion about the table names.  It’s also a headache to have to trace back to the device context to get the real table name to find it in the database (if you are troubleshooting code you are unfamiliar with).  I would prefer an ORM* that would create object names that matched the tables exactly, followed the standard namespace convention and had a visual interface that didn’t stomp on my app settings and was flexible enough to synchronize changes with the database (because we make database changes).  Am I asking for too much?

So now what’s my plan?  At this point, I’m going to put some time and effort into NHibernate (I have posted on that subject before).  Stay tuned for my results on this ORM.



*LINQ and EF are also known as ORMs (Object Relational Mappers).