.net Slow SqlDataReader

Question

I am working with a WPF app which executes a stored procedure in SQL Server. The method Search queries a table and produces a result query that has more than 1000 rows and of an object type of Cliente.

The query execution time is 19 seconds and the creation of Cliente takes several seconds.

The creation of object come in chunks of 54,59, 58, 59, 65

I was expecting the query would take time, but not for create objects:

public IEnumerable<Cliente> Search(Filtro filtro)
{
        System.Diagnostics.Stopwatch stop = new System.Diagnostics.Stopwatch();
        stop.Start();

        System.Diagnostics.Debug.WriteLine(stop.Elapsed);

        using (var q = SqlHelper.ExecuteReader("MyConection", "MySP", params))
        {
            System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            List<Cliente> clientes = new List<Cliente>();

            while (q.Read())
            {
                clientes.Add(new Cliente()
                {
                    Id = q.GetInt32(q.GetOrdinal("ID")),
                    Field1= q.GetString(q.GetOrdinal("Field1")),
                    Field2= q.GetString(q.GetOrdinal("Field2")),
                    Field3= q.GetString(q.GetOrdinal("Field3")),
                    Field4= q.GetInt32(q.GetOrdinal("Field4")),
                    Field5= q.GetString(q.GetOrdinal("Field5")),
                    Field6= q.GetString(q.GetOrdinal("Field6")),
                    Field7= q.GetString(q.GetOrdinal("Field7")),
                    Field8= q.GetDateTime(q.GetOrdinal("Field8")),
                    Field9= q.GetInt32(q.GetOrdinal("Field9")),
                    Field10= q.GetString(q.GetOrdinal("Field10")),
                    Field11= q.GetString(q.GetOrdinal("Field11")),
                    Field12= q.GetString(q.GetOrdinal("Field12")),
                    Field13= q.GetDateTime(q.GetOrdinal("Field13")),
                    Field14= q.GetString(q.GetOrdinal("Field14")),
                });

                System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            }

            System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            stop.Stop();

            return clientes;
        }
}

the SqlHelper come from the nugetpackage Microsoft.ApplicationBlocks.Data (https://www.nuget.org/packages/Microsoft.ApplicationBlocks.Data/) i post the code from the method

public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if( connection == null ) throw new ArgumentNullException( "connection" );
        if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0)) 
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            AssignParameterValues(commandParameters, parameterValues);

            return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else 
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(connection, CommandType.StoredProcedure, spName);
        }
    }

I am using System.Diagnostics.Stopwatch to log times.

00:00:00.0000024 << start method
00:00:19.0196951 << 19 seconds later query ends
00:00:19.0216880 << start create object Cliente
00:00:19.0226029
......
00:00:19.0430857
00:00:19.0443222
00:00:19.0447338 << here .net stop
00:00:37.6781235 << 18 seconds later start again to create object Client
00:00:37.6809429
00:00:37.6830919
...
00:00:37.7094000
00:00:37.7097978
00:00:37.7102040 << here .net framework stop
00:00:56.0205271 << 19 seconds later start again create object Client
00:00:56.0238062
00:00:56.0266515
....
00:00:56.0702602
00:00:56.0706580
00:00:56.0710598 << here .net framework stop
00:01:14.8534896 << 18 seconds later start again create object Client
00:01:14.8566631
00:01:14.8595772
....
00:01:14.8945812
00:01:14.8958162 << here .net framework stop
00:01:33.2787788 << 19 seconds later start again create object Client
00:01:33.2801869
....
00:01:34.6647856 << end

Why does the object create in chunks and then stop?

How can I optimize the code or am I missing something about SqlDataReader?

if you need more details let me know

update

I did some change to discard a problem with Microsoft.ApplicationBlocks.Data and some ajustment and didnt work still creating object in chunks and takes 1:34 minutes to complete

00:01:34.1193157

public IEnumerable<Cliente> Search(Filtro filtro)
{
        System.Diagnostics.Stopwatch stop = new System.Diagnostics.Stopwatch();
        stop.Start();
System.Diagnostics.Debug.WriteLine(stop.Elapsed);
using (var con = new SqlConnection("MyConnection")){
var command = con.CreateCommand();
                command.CommandText = "MyCommand";
                command.CommandType = CommandType.StoredProcedure;
                var param = GetParams(filtro);
                foreach (var item in param)
                {
                    command.Parameters.Add(new SqlParameter(item.Key, item.Value));
                }
                con.Open();
        using (var q = SqlHelper.ExecuteReader("MyConection", "MySP", params))
        {
            System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            List<Cliente> clientes = new List<Cliente>();

            while (q.Read())
            {
                clientes.Add(new Cliente()
                {
                    Id = q.GetInt32(q.GetOrdinal("ID")),
                    Field1= q.GetString(q.GetOrdinal("Field1")),
                    Field2= q.GetString(q.GetOrdinal("Field2")),
                    Field3= q.GetString(q.GetOrdinal("Field3")),
                    Field4= q.GetInt32(q.GetOrdinal("Field4")),
                    Field5= q.GetString(q.GetOrdinal("Field5")),
                    Field6= q.GetString(q.GetOrdinal("Field6")),
                    Field7= q.GetString(q.GetOrdinal("Field7")),
                    Field8= q.GetDateTime(q.GetOrdinal("Field8")),
                    Field9= q.GetInt32(q.GetOrdinal("Field9")),
                    Field10= q.GetString(q.GetOrdinal("Field10")),
                    Field11= q.GetString(q.GetOrdinal("Field11")),
                    Field12= q.GetString(q.GetOrdinal("Field12")),
                    Field13= q.GetDateTime(q.GetOrdinal("Field13")),
                    Field14= q.GetString(q.GetOrdinal("Field14")),
                });

                System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            }

            System.Diagnostics.Debug.WriteLine(stop.Elapsed);
            stop.Stop();

            return clientes;
        }
}

}

update i create a test proyect and took only 00:00:02.6977991 seconds agains the 1:30 minutes in the WPF Proyect i dont understend what is the diferent is the same method just i am calling now from the test proyect

[TestMethod]
    public void BuscarClienteAudPorAsignar()
    {

        var service = new MyServices();
        Task.Run(async() =>
        {
            var clientes = await service.Search(filtro);

            foreach (var item in clientes)
            {
                System.Diagnostics.Debug.WriteLine(item);
                Assert.IsTrue(true);
            }
        }).Wait();
        //took only 00:00:02.6977991 seconds to complete
    }

sorry bad english

One thing that leaps to mind--GetOrdinal is very expensive. If you could call that just on the first iteration (store it in variables) and then use the variables on subsequent iterations it would move much faster. — Tom Regan, Nov 15 '19 at 21:04
The most likely explanation is simply that `MySP` is very slow in producing rows, taking 19 seconds to cough up each individual batch. Investigate the performance of the query that's supposed to yield these results. Another, less likely candidate is network performance; try adding `Packet Size=32767` to your connection string. — Jeroen Mostert, Nov 15 '19 at 21:06
are any of the columns perhaps huge? large xml or json, perhaps? if so: bandwidth sounds the likely cause; how long does `MySP` take when executed in SSMS? note that SSMS limits the width of text columns (quite aggressively, by default), so it can't really tell us everything we want - but we can at least identify whether the problem is the actual query instead of bandwidth — Marc Gravell, Nov 15 '19 at 21:24
also: is there anything unusual in the `Cliente()` constructor or in any of the property setters? i.e. something *there* that might explain the time? — Marc Gravell, Nov 15 '19 at 21:26
Why don't you use `SqlDataAdapter` to fill a `DataSet` then read from `DataSet`and map to `Cliente`. — Shleemypants, Nov 15 '19 at 22:18
As mentioned, show us the Cliente code. The constructor or those properties might be doing something. — LarsTech, Nov 15 '19 at 22:27
last update didnt work i will investigate the "Packet Size 32767" — juan carlos peña cabrera, Nov 15 '19 at 23:25
Didnt work with the option "Packet Size 3267" takes 00:01:37.3911167 minutes. I also config the connection "Packet Size 8060" and takes 00:01:36.6102189 minutes — juan carlos peña cabrera, Nov 15 '19 at 23:47
The last results you show makes we wonder about something else in your WPF code that might slow things behind. Do you have a timer or something that do work elsewhere? — Larry, Nov 16 '19 at 23:31

score 0 · Answer 1 · answered Nov 15 '19 at 21:57

What MAY be killing you is the call to GetOrdinal every time on every field, every record. You will probably get a boost by using the reader, get the schema and getting the ordinal position ONCE in a list, then just use that value when adding. Something like below. The Reader will have the ordinal results.

using (var q = SqlHelper.ExecuteReader("MyConection", "MySP", params))
{
    System.Diagnostics.Debug.WriteLine(stop.Elapsed);
    List<Cliente> clientes = new List<Cliente>();


    // Build your own list in the same order you are reading them in
    List<int> myOrdinal = new List<int>();
    myOrdinal.Add(q.GetOrdinal("ID"));
    myOrdinal.Add(q.GetOrdinal("Field1"));
    myOrdinal.Add(q.GetOrdinal("Field2"));
    myOrdinal.Add(q.GetOrdinal("Field3"));
    myOrdinal.Add(q.GetOrdinal("Field4"));
    myOrdinal.Add(q.GetOrdinal("Field5"));
    myOrdinal.Add(q.GetOrdinal("Field6"));
    myOrdinal.Add(q.GetOrdinal("Field7"));
    myOrdinal.Add(q.GetOrdinal("Field8"));
    myOrdinal.Add(q.GetOrdinal("Field9"));
    myOrdinal.Add(q.GetOrdinal("Field10"));
    myOrdinal.Add(q.GetOrdinal("Field11"));
    myOrdinal.Add(q.GetOrdinal("Field12"));
    myOrdinal.Add(q.GetOrdinal("Field13"));
    myOrdinal.Add(q.GetOrdinal("Field14"));

    while (q.Read())
    {
        clientes.Add(new Cliente()
        {
            // then use your list of ordinal values directly
            Id = q.GetInt32(myOrdinal[0]),
            Field1 = q.GetString(myOrdinal[1]),
            Field2 = q.GetString(myOrdinal[2]),
            Field3 = q.GetString(myOrdinal[3]),
            Field4 = q.GetInt32(myOrdinal[4]),
            Field5 = q.GetString(myOrdinal[5]),
            Field6 = q.GetString(myOrdinal[6]),
            Field7 = q.GetString(myOrdinal[7]),
            Field8 = q.GetDateTime(myOrdinal[8]),
            Field9 = q.GetInt32(myOrdinal[9]),
            Field10 = q.GetString(myOrdinal[10]),
            Field11 = q.GetString(myOrdinal[11]),
            Field12 = q.GetString(myOrdinal[12]),
            Field13 = q.GetDateTime(myOrdinal[13]),
            Field14 = q.GetString(myOrdinal[14]),

        });
        System.Diagnostics.Debug.WriteLine(stop.Elapsed);
    }
    System.Diagnostics.Debug.WriteLine(stop.Elapsed);
    stop.Stop();
    return clientes;
}

if this does work, it would be interesting to know what your performance improvement results are.

I highly doubt that the performance issue is related to the getordinal calls. Sure, they are slow, but not anywhere near that magnitude. And if streaming the results aren't an option (you are returning an IEnumerable), then I'd suggest just using Dapper to do all the mapping. — Robert McKee, Nov 15 '19 at 22:00
@RobertMcKee, I was just trying to offer a solution without having to change everything else the user was working with. — DRapp, Nov 15 '19 at 22:04

score -2 · Answer 2 · answered Nov 15 '19 at 21:28

-2

For reading and posting sql query into Database, you can use Dapper ORM. Using dapper is a faster and easy way to do CRUD operation into the database.

answered Nov 15 '19 at 21:28

Anjum Matin

1
2

.net Slow SqlDataReader

2 Answers2