{"action":"created","issue":{"url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/75","repository_url":"https://api.github.com/repos/CartoDB/odbc_fdw","labels_url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/75/labels{/name}","comments_url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/75/comments","events_url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/75/events","html_url":"https://github.com/CartoDB/odbc_fdw/issues/75","id":300820646,"number":75,"title":"Retrieving one row takes almost half minute","user":{"login":"jequiroga","id":5423858,"avatar_url":"https://avatars3.githubusercontent.com/u/5423858?v=4","gravatar_id":"","url":"https://api.github.com/users/jequiroga","html_url":"https://github.com/jequiroga","followers_url":"https://api.github.com/users/jequiroga/followers","following_url":"https://api.github.com/users/jequiroga/following{/other_user}","gists_url":"https://api.github.com/users/jequiroga/gists{/gist_id}","starred_url":"https://api.github.com/users/jequiroga/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jequiroga/subscriptions","organizations_url":"https://api.github.com/users/jequiroga/orgs","repos_url":"https://api.github.com/users/jequiroga/repos","events_url":"https://api.github.com/users/jequiroga/events{/privacy}","received_events_url":"https://api.github.com/users/jequiroga/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2018-02-27T22:39:07Z","updated_at":"2018-02-28T18:08:53Z","closed_at":null,"author_association":"NONE","body":"I have in production a Postgres 10 database up to date and odbc_fdw library compiled with your current master branch (@ Feb, 27th, 2018). I used your previous version and since middle of January queries from postgres to SQL Server 2005 are taking at least 300x more time than before just retrieving one row (The query is always returning one row).\r\n\r\n### Steps to Reproduce\r\n\r\n1. Install CentOs 7 and exclude from CentosRepo-Base postgres\r\n2. Add The postgres official repository for CentOS 7 and install postgres 10\r\n3. Install odbc_fdw but at the end uninstall all dev packages (gcc, postgresql-10-dev, inixODBC-dev, etc.)\r\n4. Configure UNIX-ODBC to reach MSSQL-Server 2005 database\r\n5. Install ODBC Driver for MS-SQL Server (msodbc /freetds) \r\n6. Install odbc_fdw extension in postgres\r\n7. Create a Foreign server using the odbc_fdw extension, and a query using the foreign server \r\n8. Create a query that contains some views or tables to SQL-Server\r\n\r\n### Current Result\r\nI'm actually using just two queries all of them retrieve just one record. One do it using three seconds, the critical one use 27 secs. In MSSQL-Server those exact queries takes less than a second, in fact in MSSQL Server Manager there is 00:00 secs doing one record and less than a second retrieving all rows.\r\n\r\nHave you ever experienced such bad performance during your tests?. I have in development (Windows machine Postgres 9.6, same MSSQL-Server database as production postgres server) using ogr extension and the retrieval took less than a second (from 800ms to 1 sec). Using the same query your extension takes 25 secs. I added the analyze result in production.\r\n\r\n\"Foreign Scan on ra.clients_csgor_view  (cost=25.00..17918.00 rows=17893 width=378) (actual time=7259.219..24879.050 rows=1 loops=1)\"\r\n\"  Output: client_id, client_sin, first_name, last_name, last_app_id, app_program_year_id, app_program_year, app_award_limit, app_status_id, current_program_year_id, current_program_year, pd_established\"\r\n\"  Filter: ((clients_csgor_view.client_sin)::text = '739533537'::text)\"\r\n\"  Rows Removed by Filter: 17892\"\r\n\"  Foreign Table Size: 17893\"\r\n\"Planning time: 201.383 ms\"\r\n\"Execution time: 25078.335 ms\"\r\n\r\nThis is the result using a foreign server with FreeTDS, not Microsoft MS-MSQL Server ODBC driver for Unix, to eliminate the ODBC Driver variable from the issue.\r\n\r\n\"Foreign Scan on ra.clients_csgor_view  (cost=25.00..17918.00 rows=17893 width=1346) (actual time=7122.452..24665.566 rows=1 loops=1)\"\r\n\"  Output: client_id, client_sin, first_name, last_name, last_app_id, app_program_year_id, app_program_year, app_award_limit, app_status_id, current_program_year_id, current_program_year, pd_established\"\r\n\"  Filter: ((clients_csgor_view.client_sin)::text = '739533537'::text)\"\r\n\"  Rows Removed by Filter: 17892\"\r\n\"  Foreign Table Size: 17893\"\r\n\"Planning time: 217.162 ms\"\r\n\"Execution time: 24822.832 ms\"\r\n\r\nThe exact same MSSQL-Server database using ogr extension (only for Windows, that is why I had to use yours) in a postgres 9.6 with ogr_fdw.\r\n\r\n\"Foreign Scan on ra.clients_csgor_view  (cost=25.00..17918.00 rows=17893 width=378) (actual time=51.760..235.947 rows=1 loops=1)\"\r\n\"  Output: client_id, client_sin, first_name, last_name, last_app_id, app_program_year_id, app_program_year, app_award_limit, app_status_id, current_program_year_id, current_program_year, pd_established\"\r\n\"  Filter: ((clients_csgor_view.client_sin)::text = '721362009'::text)\"\r\n\"  Rows Removed by Filter: 17892\"\r\n\"  Foreign Table Size: 17893\"\r\n\"Planning time: 409.593 ms\"\r\n\"Execution time: 491.747 ms\"\r\n\r\nI Read that using the option \"use_remote_estimate\" the performance can be improved, but your extension seems that does not allow it; not in the foreign server options. in the table I added, but no effect whatsoever.\r\n\r\n### Expected result\r\n\r\nTo have a performance that takes 1 sec at most (is just one record!)\r\n\r\n### System trying to connect to and version\r\nProduction Server:\r\n- CentOS 7 minimal (updated up to date)\r\n- Postgresql 10 (updated up to date)\r\n- odbc_fdw (compiled using sourced @ Feb 27th, 2018)\r\n- unix-ODBC (latest available on CentOS repos)\r\n- Microsoft Unix ODBC (msodbcsql-13.1.9.1-1.x86_64.rpm and FreeTDS 7.6)\r\n\r\nMSSQL Server 2005 (all service packs applied)\r\n- Windows 7 (Up to date)\r\n- CPU Intel core i5 8GBytes of RAM\r\n\r\nDevelopment\r\n- Windows 10 (up to date)\r\n- Postgres 9.6\r\n- ogr_fdw \r\n- ODBC 64 bits (SQL Server native Client 11.0).\r\n\r\nIf you need anything else, please let me know. I'm not an expert on Postgres, so I could ask more detailed directions."},"comment":{"url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/comments/369329529","html_url":"https://github.com/CartoDB/odbc_fdw/issues/75#issuecomment-369329529","issue_url":"https://api.github.com/repos/CartoDB/odbc_fdw/issues/75","id":369329529,"user":{"login":"jequiroga","id":5423858,"avatar_url":"https://avatars3.githubusercontent.com/u/5423858?v=4","gravatar_id":"","url":"https://api.github.com/users/jequiroga","html_url":"https://github.com/jequiroga","followers_url":"https://api.github.com/users/jequiroga/followers","following_url":"https://api.github.com/users/jequiroga/following{/other_user}","gists_url":"https://api.github.com/users/jequiroga/gists{/gist_id}","starred_url":"https://api.github.com/users/jequiroga/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jequiroga/subscriptions","organizations_url":"https://api.github.com/users/jequiroga/orgs","repos_url":"https://api.github.com/users/jequiroga/repos","events_url":"https://api.github.com/users/jequiroga/events{/privacy}","received_events_url":"https://api.github.com/users/jequiroga/received_events","type":"User","site_admin":false},"created_at":"2018-02-28T18:08:53Z","updated_at":"2018-02-28T18:08:53Z","author_association":"NONE","body":"Seemed that there is a problem with \"SET client_min_messages TO DEBUG1;\", in PgAdmin or in psql the connection get lost, and this is all I could get from psql. I tried twice and both terminate the connection abnormally.\r\n\r\nEXPLAIN (ANALYZE, VERBOSE) SELECT * FROM ra.clients_csgor_view WHERE client_sin = '721362009';\r\nDEBUG:  odbcGetForeignRelSize\r\nDEBUG:  odbcGetTableOptions\r\nDEBUG:  odbcGetOptions\r\nDEBUG:  extract_odbcFdwOptions\r\nDEBUG:  CONN STR: database=ATBC;DSN=ATWIS_Test;UID=<ms_sql_user>;PWD=<mssql_user_user_passwd>;DSN=ATWIS_Test;UID=<ms_sql_user>;PWD=<mssql_user_user_passwd>\r\nDEBUG:  Successful result: Connecting to driver\r\nDEBUG:  getQuoteChar\r\nDEBUG:  getNameQualifierChar\r\nDEBUG:  Count query: SELECT COUNT(*) FROM (SELECT ac.intAdult_Id AS \"client_id\",\r\n      ISNULL(ac.chrSIN_No, '') AS \"client_sin\",\r\n      REPLACE(REPLACE(ISNULL(ac.chvFst_Name, ''), CHAR(146), ''''), CHAR(200), 'È') AS \"first_name\",\r\n      REPLACE(ISNULL(ac.chvLst_Name, ''), CHAR(146), '''') AS \"last_name\",\r\n      ISNULL(ap.intRfral_Mstr_Id, 0) AS \"last_app_id\",\r\n      ISNULL(rm.intCSG_Program_Year_Id, 0) AS \"app_program_year_id\",\r\n      ISNULL(rm.chvDesc, '') AS \"app_program_year\",\r\n      rm.mnyAward_Limit AS \"app_award_limit\",\r\n      rm.intRfral_Status_Id AS \"app_status_id\",\r\n      cpy.intCSG_Program_Year_Id AS \"current_program_year_id\",\r\n      cpy.chvDesc AS \"current_program_year\",\r\n      ISNULL(vcp.isPD_Established, 0) AS \"pd_established\"\r\n    FROM dbo.tbl_Adult_Client ac\r\n      INNER JOIN tbl_Adult_Program ap ON ap.intAdult_Id = ac.intAdult_Id\r\n        AND ap.intProgram_Id = 2\r\n      INNER JOIN vt_Referral rm ON rm.intRfral_Mstr_Id = ap.intRfral_Mstr_Id\r\n      LEFT JOIN vPD_Client_Program vcp ON vcp.intAdult_Id = ac.intAdult_Id\r\n        AND vcp.intProgram_Id = 2\r\n      LEFT JOIN tbl_CSG_Program_Year cpy ON cpy.bitIs_Default = 1\r\n    WHERE ac.intAdult_Id > 0 AND ISNULL(LTRIM(RTRIM(ac.chrSIN_No)), '') <> '') AS _odbc_fwd_count_wrapped\r\nDEBUG:  Successful result: Executing ODBC query\r\nserver closed the connection unexpectedly\r\n        This probably means the server terminated abnormally\r\n        before or while processing the request.\r\nThe connection to the server was lost. Attempting reset: Failed.\r\n\r\nIf I don't set the debug level, the result is exactly the same as in my first post. Any ideas?"}}
{"actor":{"display_login":"jequiroga"}}