Quick tip about SqlCmd.exe 13


Some days ago, I’ve installed Visual Studio 2017 and today, I’ve try to execute a simple SQL query with SQLCMD.EXE.

What’s the problem ? No really one… except that I’ve received an “ODBC Driver 13 for SQL Server” error and “Login timeout expired“.
After some hours to find why this error occurred (on a script already used before) and how to solve that… And I’ve found that it’s working with the previous version of SQLCMD but not with the new version. I don’t know why.

So, my workaround is to add a new item in the “PATH System Environment Variables” (follow this link to explain how to), and to move up this item before SQL Server 130 (my SQL Server 2016)… see screenshots below.

PS: you can check the version of SqlCmd.exe, using the /? argument.

Posted in SQL Server, Tips and others

Tip to optimize SQL Queries


Identification of the problem.

Some days ago, a customer call me to try to solve a performance problem into an existing application connected to SQL Server. Using SQL Server Profile, we found some queries with unexpected times. For example, to find one item in a large table, the SQL query use many seconds (between 4 and 10). Next, after multiple checks, we see that this C# code (using Parameters.AddWithValue) is executed like that by SQL Server and is responsible of this extra time in the application.

using (var cmd = new SqlDatabaseCommand(connection))
{
    cmd.CommandText.AppendLine(" SELECT TOP 1 MyString "):
    cmd.CommandText.AppendLine("   FROM MyTable ");
    cmd.CommandText.AppendLine("  WHERE MyString = @MyValue ");
    cmd.Parameters.AddWithValue("@MyValue", "abc");
    var data = cmd.ExecuteTable();
}
exec sp_executesql N' SELECT TOP 1 MyString 
                        FROM MyTable 
                       WHERE MyString = @MyValue 
',N'@MyValue nvarchar(3)',@MyValue=N'abc'

My first reaction is “that’s correct and I don’t see how to optimize this query.”… But the question “Why this query is so slow” is always there !

The solution.

Many searches and checks later, we found this command CONVERT in SQL Server Profiler.

sqlprofileconvert

Eureka… The problem come from the AddWithValue method where we set a C# String parameter value… So the Unicode value is converted later by SQL Server to @MyValue nvarchar(3)’,@MyValue=N‘abc’.

The query can be optimized by setting the correct SqlType (VarChar and not NVarChar) when we define the parameter (or you can change the database structure using NVarchar, NChar and NText… but your database size will be increase).

var param = new SqlParameter()
{
    ParameterName = "@MyValue",
    SqlDbType = SqlDbType.VarChar,
    Value = "def"
};
cmd.Parameters.Add(param);

And without other changes, my application (on this query) is 3 times more fast.

Try your self.

If you want to try your self.

1. First, create a new table in a sample database.

CREATE TABLE MyTable (
  ID INT,
  MyString VARCHAR(80)
)

2. Execute this script to generate 1 million of rows.

DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
WHILE @row < 1000000 BEGIN SET @row = @row + 1; -- Build the random string SET @length = ROUND(80*RAND(),0); SET @string = ''; WHILE @length > 0 BEGIN
      SET @length = @length - 1;
      SET @code = ROUND(32*RAND(),0) - 6;
      IF @code BETWEEN 1 AND 26 
         SET @string = @string + CHAR(ASCII('a')+@code-1);
      ELSE
         SET @string = @string + ' ';
   END 

   -- Ready for the record
   SET NOCOUNT ON;
   INSERT INTO MyTable VALUES (@row, @string)
END

3. Create a C# Console project and use this code to execute a query with NVarchar (Unicode) parameter and with Varchar parameter.

const string CONNECTION_STRING = "Server=(localdb)\\ProjectsV12;Database=SCOTT;Trusted_Connection=True;";
const decimal NB_REQUESTS = 100;

var watcher = Stopwatch.StartNew();
Console.WriteLine(" Starting first request... using NVarChar.");
for (int i = 0; i < NB_REQUESTS; i++)
{
    using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
    {
        cmd.CommandText.AppendLine(" SELECT TOP 1 MyString FROM MyTable WHERE MyString = @MyValue ");
        cmd.Parameters.AddWithValue("@MyValue", "abc");
        var data = cmd.ExecuteTable();
    }
}
Console.WriteLine($"{watcher.ElapsedMilliseconds / NB_REQUESTS} ms by request.");

watcher.Restart();
Console.WriteLine(" Starting second request... using VarChar.");
for (int i = 0; i < NB_REQUESTS; i++)
{
    using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
    {
        cmd.CommandText.AppendLine(" SELECT TOP 1 MyString FROM MyTable WHERE MyString = @MyValue ");
        var param = new SqlParameter()
        {
            ParameterName = "@MyValue",
            SqlDbType = SqlDbType.VarChar,
            Value = "def"
        };
        cmd.Parameters.Add(param);
        var data = cmd.ExecuteTable();
    }
}
Console.WriteLine($"{watcher.ElapsedMilliseconds / NB_REQUESTS} ms by request.");

And the result is… Amazing 😉

sqlprofileconvertresult

In a future version of SqlDatabaseCommand, I’ll add a global property to automatically convert NVarChar, NChar and NText to equivalent VarChar, Char and Text. Your queries will be optimized easily.

Posted in General, SQL Server, SqlDatabaseCommand, Tips and others

Développer avec un Simple Object Mapping Toolkit pour SQL Server


La majorité des applications actuelles ont besoin d’enregistrer des informations dans une base de données locale ou serveur. Plusieurs outils existent dont Entity Framework, le plus fréquemment proposés par Microsoft, ou ADO.NET, le plus performant mais le plus complexe à exploiter. Depuis plusieurs années, nous avons construit un ensemble d’outils simples afin de nous aider dans la fabrication de la DAL de nos projets, et plus particulièrement dans la recherche de données en les transformant facilement en objets .NET.

En septembre dernier, j’ai eu la chance de présenter ces toolkits dans le cadre des 24 Hours of PASS.

Le dernier toolkit présenté est SqlDatabaseCommand que j’ai déjà décrit dans un précédent article et que vous pouvez télécharger gratuitement via NuGet.

Posted in francais, SQL Server, SqlDatabaseCommand

Retour d’expérience ‘TFS Online dans une solution industrielle’


frenSome days ago, I had the chance to present a session in the Microsoft Experiences of Paris (in french) to explain how we’ve created a complete industrial application using VSTS (Visual Studio Team Services… or TFS Online).

msexperiences2016

In this presentation, I’ve explain how we’ve create a project for a aeronautic company:

  • The Workforce management web portal can plan, distribute, manage times, validate data and generate reports or send data to external systems.
  • We used the Agile Scrum methodology:
    • Definition of the Team (Product owner, Developers team,  Scrum master)
      Creation of this team in VSTS.
    • Creation of the Product Backlog using Features, User Stories and Tasks in VSTS.
    • Review and planning of Sprints.
    • Development with Visual Studio connected to VSTS.
    • Installation of Build and Deployment (Release Management) modules.
  • I’ve also presented some tools and frameworks used technically in this project:
    • To develop the BackEnd services
    • To develop the FrontEnd services
    • To tests all blocks

You can download the slides show on SlideShare.net.

Let me a comment if you need more details.

Posted in francais, General, Presentation, Team Foundation Server, Visual Studio

Configure a Visual Studio Online (VSO) Build Agent


When using Visual Studio Online (VSO) in your team or for your personal projects, it is very useful to integrate a compilation and a continuous deployment process. With MSDN subscriptions, you have about 240 minutes of hosted build. After this time, it’s very expensive and you can not install any tools of your choice on the server.

Microsoft allow to install freely the Build and Deployment agent on a on-premice server [note 2]… and very quickly and easily.
In this 5 minutes video, I am going to show how to configure a Build Agent linked to your VSO account.

In order to configure a Build Agents, you must install all tools to compile and to deploy your projects on the server:

Next, you must download and extract the VSO Build Agent included in the Admin portal of your Visual Studio Online account: https://[your_account].visualstudio.com/_admin/_AgentPool

And finally you need to run the ConfigureAgent.cmd program.

D:\agent>ConfigureAgent.cmd
Enter the name for this agent (default is Agent-AL423966) Agent-AL423966
Enter the URL for the Team Foundation Server (default is ) https://dvoituron.visualstudio.com
Configure this agent against which agent pool? (default pool name is ‘default’) default
Enter the path of the work folder for this agent (default is ‘D:\agent\_work’) D:\agent\_work
Would you like to install the agent as a Windows Service (Y/N) (default is N) Y
Enter the name of the user account to use for the service (default is NT AUTHORITY\NETWORK SERVICE) AL423966\BuildUser
Enter the password for user account AL423966\BuildUser: *********
Installing service vsoagent.dvoituron.Agent-AL423966…
Service vsoagent.dvoituron.Agent-AL423966 has been successfully installed.
Creating EventLog source vsoagent.dvoituron.Agent-AL423966 in log Application…
Configuration successful.

There are no special firewall rules to define, because when you start a build process from VSO, all requests are initiated from your Build Server (in https).

That’s all 😉

My next article will be “How to create a Build Definition, to compile and to deploy your SQL and Web projects in Azure

More information:

Posted in General, Team Foundation Server, Tips and others, Visual Studio

Simple Object Mapping Toolkit


A few days ago, I talked about an interesting topic when developing applications that require frequent access to databases (eg. SQL Server). Many frameworks are existing to connect your Business Layer to a Database : the best known is often Entity Framework.

After multiple projects using EF, we’ve decided to avoid this framework. Why? First for performance reasons and secondly to avoid maintenance problems (see my previous article about the migration of EF5 to EF6).

We prefer to use mapping tools where we need to create SQL queries and these tools convert data results to equivalents C# objects. For small projects, EF can be a solutions but when you need to develop a large application with a large database, you prefer manipulate, optimize queries to retrieve data quickly.

Dapper.NET is a toolkit, developed by the Stackoverflow team, to simplify querying and to convert SQL data to C# objects (see examples in my presentation).

But Dapper.NET will extend your IDbConnection interface (eg. SqlConnection). It’s easy to use, but to define guideline and for future evolution, I prefer to create an object with properties, methods and a live cycle. For these reasons, I’ve published the project SqlDatabaseCommand: you create an object SqlDatabaseCommand (eg. in a DataService), you define properties and you execute your command. Logging, Exceptions, Entities Generator are automatically managed by the toolkit.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine("SELECT * FROM EMP");
    var emps = cmd.ExecuteTable<Employee>();
}
Posted in General

Introduction to SqlDatabaseCommand


Since some years, I’ve tried to write some performing SQL Queries but also with easy way. In the .NET beginning, I’ve tried System.Data.SqlClient with DataAdapter and DataSets. Next, I’ve tried the Linq to SQL project. And next, I’ve tried Entity Framework. This last framework is the most used at this moment, but when I need some performing requests, it’s very difficult to optimize SQL requests generated by EF.

So, I decided to create a very light toolkit to use the full SQL features and to retrieve quickly all data: SqlDatabaseCommand. This class inherits from a base class called DatabaseCommandBase that contains all main features using only System.Data namespace (DbCommand, DbConnection, …). So, you can inherit this base class to extend the toolkit to other providers like Oracle, SqlLite, etc.

This project is Open Source and hosted on Github: https://github.com/Apps72/Dev.Data

Samples

For example, if you have already wrote a class (Employee) with all properties mapped to the EMP table. You can use this code and the ExecuteTable<T> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP ");
    var emps = cmd.ExecuteTable<Employee>();
}

The following code can be used to retrieve only the first data row, via the ExecuteRow<T> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE EMPNO = 7369 ");
    var emp = cmd.ExecuteRow<Employee>();
}

If you know that your request returns only one item, use the ExecuteScalar method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
    int count = cmd.ExecuteScalar<int>();
}

You can also add parameters in your queries and send parameters easily.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE HIREDATE = @HireDate ");
    cmd.Parameters.AddValues(new { HireDate = new DateTime(1980, 12, 17) });
    var emps = cmd.ExecuteTable<Employee>();
}

Find more features to manage Transactions, Logging, customization of your data results, extensions, data injection for Unit Tests and Best practices on https://github.com/Apps72/Dev.Data.

NuGet

To use this toolkit in your .NET projects, add a NuGet Reference to Apps72.Dev.Data.

  • First, search SqlDatabaseCommand in the NuGet Package Manager.
  • Next, select Apps72.Dev.Data (the second NuGet package is to include in a SQL Server CLR Project).

SqlDatabaseCommand-Nuget

Posted in General, SQL Server, SqlDatabaseCommand
Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 566 other followers

Who I am?