WCF Service Query time vs Direct Query to DB 6x slower

Question

First question here :)

I am working with a WCF service that gets queries from a WinForms application, converts it to XML, sends it to a SQL Server DB, returns the query data as XML, converts it to a DataTable and sends it back to the WinForms application.

I tested the speed difference between this and a direct DB query, and it appears that using my service slows it by 6x.

What I need to do is to reduce the time the service takes to do the query, but I don't know how it works by heart. Do you have any general idea of where the problem could be? Is there any way I could improve the performance of the service? Would using JSON (instead of XML) help solve this issue?

Thank you so much for reading this and looking forward to great answers!

Here is the code that I used to test both connections:

namespace TestServiceVSDatabaseSpeed
{
    class Program
    {
        static void Main(string[] args)
        {
            // Initialization
            DataTable dtService = new DataTable(), dtDirect = new DataTable();
            Console.Write("~Direct Connection VS Service Connection~\n\nSELECT * FROM COILS\n");
            Stopwatch sw1 = new Stopwatch(), sw2 = new Stopwatch();

            // Direct connection test
            sw1.Start();
            dtDirect = GetSqlDataDirectConnection();
            Single directTime = sw1.ElapsedMilliseconds;
            Console.WriteLine("Direct query time: {0} ms ({1} rows)", directTime, dtDirect.Rows.Count);
            sw1.Stop();

            // Service connection test
            sw2.Start();
            dtService = GetSqlDataServiceConnection();
            Single serviceTime = sw2.ElapsedMilliseconds;
            Console.WriteLine("Query via service time: {0} ms ({1} rows)", serviceTime, dtService.Rows.Count);
            sw2.Stop();

            // Conclusion
            Console.WriteLine("\nDirect Connection faster than Service Connection by {0} ms!", serviceTime - directTime);
            Console.WriteLine("That's {0} times faster!", serviceTime / directTime);
            Console.ReadLine();
        }

        /// <summary>
        /// Does a direct query to the database
        /// </summary>
        /// <returns>A DataTable.</returns>
        private static DataTable GetSqlDataDirectConnection()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = "Persist Security Info=False;User ID=MyId;Password=MyPassword;Initial Catalog=The_Catalogue;Server=ThisServer";

                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }

                using (SqlCommand command = new SqlCommand("SELECT * FROM Coils", conn))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {

                        DataTable coilTable = new DataTable("Coils");
                        adapter.Fill(coilTable);

                        return coilTable;
                    }
                }
            }
        }

        /// <summary>
        /// Does a query to the database via the IOM Service.
        /// </summary>
        /// <returns>A DataTable.</returns>
        private static DataTable GetSqlDataServiceConnection()
        {
            DataContractClient contract = new DataContractClient();
            contract.Endpoint.Address = new EndpointAddress("net.tcp://localhost:8888/MyService/DataContract");
            NetTcpContextBinding netTcpContextBinding = new NetTcpContextBinding(SecurityMode.None) { MaxReceivedMessageSize = 2147483647 };
            contract.Endpoint.Binding = netTcpContextBinding;
            contract.Endpoint.Contract = ContractDescription.GetContract(typeof(IDataContract));

            return contract.ExecuteQueryNoParam("SELECT * FROM Coils", Catalog.SqlCatalog).Tables[0];
        }
    }
}

Here are the results:

~Direct Connection VS Service Connection~

SELECT * FROM COILS
Direct query time: 436 ms (24596 rows)
Query via service time: 2748 ms (24596 rows)

Direct Connection faster than Service Connection by 2312 ms!
That's 6.302752 times faster!

Apart from the overhead in transforming the data, here's a fair amount of [inherent overhead in DataTables](https://stackoverflow.com/questions/424598/what-is-the-memory-overhead-of-storing-data-in-a-net-datatable) which will all have to come back down the wire.. — stuartd, Jun 19 '17 at 15:22
Lots of reasons...for one - might consider binary encoding for net.tcp. Check first call vs. subsequent call perf...as there may be some initialization cost at the service. Service instancing could play a part - especially if your service is slow to start. In the service case, the datatable is filled, then serialized, transmitted, and then deserialized...all costly. Bet the overhead goes down (as a percent) as the size goes up. — Clay, Jun 19 '17 at 15:57
Makes my skin crawl to see a sql statement sent over a service connection, btw. Scary - and too tightly coupled...but then again, using datatable is too tightly coupled. Consider a datacontract-decorated POCO - which will probably serialize/deserialize faster. — Clay, Jun 19 '17 at 15:59

score 0 · Answer 1 · answered Jun 19 '17 at 15:29

If I look at it, it seems we need to see the service converting the call. To answers your questions :

In general, it's normal that a direct call is faster, converting to XML can take some time.
Can't say without seeing the service itself.
I don't think changing to JSON would make a big difference, you are still processing 24596 rows.

You could process with the results after the call. But all in all, it's normal you're having a big performance hit, make sure to use indexes, if you're not using it you may encounter even bigger problem with the query time if you select only some of the items.

WCF Service Query time vs Direct Query to DB 6x slower

1 Answers1