{"action":"created","issue":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/1338","repository_url":"https://api.github.com/repos/npgsql/npgsql","labels_url":"https://api.github.com/repos/npgsql/npgsql/issues/1338/labels{/name}","comments_url":"https://api.github.com/repos/npgsql/npgsql/issues/1338/comments","events_url":"https://api.github.com/repos/npgsql/npgsql/issues/1338/events","html_url":"https://github.com/npgsql/npgsql/issues/1338","id":185854106,"number":1338,"title":"Why is using a new connection so slow?","user":{"login":"DaveVdE","id":650760,"avatar_url":"https://avatars.githubusercontent.com/u/650760?v=3","gravatar_id":"","url":"https://api.github.com/users/DaveVdE","html_url":"https://github.com/DaveVdE","followers_url":"https://api.github.com/users/DaveVdE/followers","following_url":"https://api.github.com/users/DaveVdE/following{/other_user}","gists_url":"https://api.github.com/users/DaveVdE/gists{/gist_id}","starred_url":"https://api.github.com/users/DaveVdE/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DaveVdE/subscriptions","organizations_url":"https://api.github.com/users/DaveVdE/orgs","repos_url":"https://api.github.com/users/DaveVdE/repos","events_url":"https://api.github.com/users/DaveVdE/events{/privacy}","received_events_url":"https://api.github.com/users/DaveVdE/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":10,"created_at":"2016-10-28T07:13:27Z","updated_at":"2016-10-28T09:20:53Z","closed_at":null,"body":"Please consider my trivial example:\r\n\r\n```\r\nusing System;\r\nusing System.Diagnostics;\r\nusing Npgsql;\r\nusing Npgsql.Logging;\r\nusing NpgsqlTypes;\r\n\r\nnamespace ConsoleApp1\r\n{\r\n    public class Program\r\n    {\r\n        private static readonly string ConnectionString = CreateConnectionString();\r\n\r\n        private static string CreateConnectionString()\r\n        {\r\n            var builder = new NpgsqlConnectionStringBuilder\r\n            {\r\n                Host = \"redacted\",\r\n                Username = \"redacted\",\r\n                Password = \"redacted\",\r\n                Database = \"redacted\"\r\n            };\r\n\r\n            return builder.ToString();\r\n        }\r\n\r\n        private static void Clear()\r\n        {\r\n            using (var conn = new NpgsqlConnection(ConnectionString))\r\n            using (var cmd = new NpgsqlCommand(\"delete from testtable\"))\r\n            {\r\n                conn.Open();\r\n                cmd.Connection = conn;\r\n                cmd.ExecuteNonQuery();\r\n            }\r\n        }\r\n\r\n        private static void Insert(NpgsqlConnection conn, long id)\r\n        {\r\n            using (var cmd = new NpgsqlCommand(\"insert into testtable (id, data) values (@id, @value)\"))\r\n            {\r\n                cmd.Connection = conn;\r\n                cmd.Parameters.AddWithValue(\"@id\", id);\r\n                cmd.Parameters.AddWithValue(\"@value\", NpgsqlDbType.Jsonb, @\"{\"\"key\"\":\"\"value\"\"}\");\r\n                cmd.ExecuteNonQuery();\r\n            }\r\n        }\r\n\r\n        private static void Test1()\r\n        {\r\n            var stopwatch = Stopwatch.StartNew();\r\n\r\n            var id = 0L;\r\n\r\n            Clear();\r\n            Console.WriteLine($\"Elapsed: {stopwatch.ElapsedMilliseconds}ms\");\r\n\r\n            for (int count = 10; count > 0; --count)\r\n            {\r\n                using (var conn = new NpgsqlConnection(ConnectionString))\r\n                {\r\n                    conn.Open();\r\n                    stopwatch.Reset();\r\n                    stopwatch.Start();\r\n                    Insert(conn, ++id);\r\n                    Console.WriteLine($\"Elapsed: {stopwatch.ElapsedMilliseconds}ms\");\r\n                }\r\n            }\r\n        }\r\n\r\n        private static void Test2()\r\n        {\r\n            var stopwatch = Stopwatch.StartNew();\r\n\r\n            var id = 0L;\r\n\r\n            Clear();\r\n            Console.WriteLine($\"Elapsed: {stopwatch.ElapsedMilliseconds}ms\");\r\n\r\n            using (var conn = new NpgsqlConnection(ConnectionString))\r\n            {\r\n                conn.Open();\r\n\r\n                for (int count = 10; count > 0; --count)\r\n                {\r\n                    stopwatch.Reset();\r\n                    stopwatch.Start();\r\n                    Insert(conn, ++id);\r\n                    Console.WriteLine($\"Elapsed: {stopwatch.ElapsedMilliseconds}ms\");\r\n                }\r\n            }\r\n        }\r\n\r\n        public static void Main(string[] args)\r\n        {\r\n            //NpgsqlLogManager.Provider = new ConsoleLoggingProvider(NpgsqlLogLevel.Debug);\r\n            Test1();\r\n            Test2();\r\n        }\r\n    }\r\n}\r\n```\r\n\r\nIn `Test1`, I'm disposing the connection after every insert. Think of it as a unit-of-work pattern. This is what is best practice in ADO.NET, as pooling should make this fast enough to be negligible. \r\n\r\nExcept on my test environment, each insert takes about 50ms.\r\n\r\nIn `Test2`, I'm not disposing the connection, and each insert takes <1ms.\r\n\r\nI've been looking at the source for NpgsqlConnection and NpgsqlConnector, trying to think of why this would be so slow, but I can't see it. What is going wrong here?\r\n\r\nAnd what can I do to resolve this? \r\n"},"comment":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/comments/256875143","html_url":"https://github.com/npgsql/npgsql/issues/1338#issuecomment-256875143","issue_url":"https://api.github.com/repos/npgsql/npgsql/issues/1338","id":256875143,"user":{"login":"roji","id":1862641,"avatar_url":"https://avatars.githubusercontent.com/u/1862641?v=3","gravatar_id":"","url":"https://api.github.com/users/roji","html_url":"https://github.com/roji","followers_url":"https://api.github.com/users/roji/followers","following_url":"https://api.github.com/users/roji/following{/other_user}","gists_url":"https://api.github.com/users/roji/gists{/gist_id}","starred_url":"https://api.github.com/users/roji/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roji/subscriptions","organizations_url":"https://api.github.com/users/roji/orgs","repos_url":"https://api.github.com/users/roji/repos","events_url":"https://api.github.com/users/roji/events{/privacy}","received_events_url":"https://api.github.com/users/roji/received_events","type":"User","site_admin":false},"created_at":"2016-10-28T09:20:53Z","updated_at":"2016-10-28T09:20:53Z","body":"Well, if I understand correctly, you can't reproduce the problem when running the test program on a mac... So I suggest trying to run it on another Windows machine, different from the original Windows 10 VM where you've seen the problem, just to make sure there isn't some weird machine-specific issue going on. I'd even prefer a non-VM just to exclude that as well.\r\n\r\nOr did I misunderstand?"}}
{"actor":{"display_login":"roji"}}