{"action":"created","issue":{"url":"https://api.github.com/repos/Teradata/PyTd/issues/29","repository_url":"https://api.github.com/repos/Teradata/PyTd","labels_url":"https://api.github.com/repos/Teradata/PyTd/issues/29/labels{/name}","comments_url":"https://api.github.com/repos/Teradata/PyTd/issues/29/comments","events_url":"https://api.github.com/repos/Teradata/PyTd/issues/29/events","html_url":"https://github.com/Teradata/PyTd/issues/29","id":129045155,"number":29,"title":"Extremely slow when fetching large volume of data compared with pyodbc/jdbc","user":{"login":"yinhao","id":2814486,"avatar_url":"https://avatars.githubusercontent.com/u/2814486?v=3","gravatar_id":"","url":"https://api.github.com/users/yinhao","html_url":"https://github.com/yinhao","followers_url":"https://api.github.com/users/yinhao/followers","following_url":"https://api.github.com/users/yinhao/following{/other_user}","gists_url":"https://api.github.com/users/yinhao/gists{/gist_id}","starred_url":"https://api.github.com/users/yinhao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yinhao/subscriptions","organizations_url":"https://api.github.com/users/yinhao/orgs","repos_url":"https://api.github.com/users/yinhao/repos","events_url":"https://api.github.com/users/yinhao/events{/privacy}","received_events_url":"https://api.github.com/users/yinhao/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/Teradata/PyTd/labels/bug","name":"bug","color":"fc2929"}],"state":"closed","locked":false,"assignee":{"login":"escheie","id":1410311,"avatar_url":"https://avatars.githubusercontent.com/u/1410311?v=3","gravatar_id":"","url":"https://api.github.com/users/escheie","html_url":"https://github.com/escheie","followers_url":"https://api.github.com/users/escheie/followers","following_url":"https://api.github.com/users/escheie/following{/other_user}","gists_url":"https://api.github.com/users/escheie/gists{/gist_id}","starred_url":"https://api.github.com/users/escheie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/escheie/subscriptions","organizations_url":"https://api.github.com/users/escheie/orgs","repos_url":"https://api.github.com/users/escheie/repos","events_url":"https://api.github.com/users/escheie/events{/privacy}","received_events_url":"https://api.github.com/users/escheie/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/Teradata/PyTd/milestones/13","html_url":"https://github.com/Teradata/PyTd/milestones/15.10.00.13","labels_url":"https://api.github.com/repos/Teradata/PyTd/milestones/13/labels","id":1495788,"number":13,"title":"15.10.00.13","description":"","creator":{"login":"escheie","id":1410311,"avatar_url":"https://avatars.githubusercontent.com/u/1410311?v=3","gravatar_id":"","url":"https://api.github.com/users/escheie","html_url":"https://github.com/escheie","followers_url":"https://api.github.com/users/escheie/followers","following_url":"https://api.github.com/users/escheie/following{/other_user}","gists_url":"https://api.github.com/users/escheie/gists{/gist_id}","starred_url":"https://api.github.com/users/escheie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/escheie/subscriptions","organizations_url":"https://api.github.com/users/escheie/orgs","repos_url":"https://api.github.com/users/escheie/repos","events_url":"https://api.github.com/users/escheie/events{/privacy}","received_events_url":"https://api.github.com/users/escheie/received_events","type":"User","site_admin":false},"open_issues":0,"closed_issues":1,"state":"open","created_at":"2016-01-10T22:42:31Z","updated_at":"2016-02-06T01:00:30Z","due_on":null,"closed_at":null},"comments":5,"created_at":"2016-01-27T06:22:37Z","updated_at":"2016-02-06T01:28:27Z","closed_at":"2016-02-06T01:00:30Z","body":"Hi,\r\n\r\nI compare the operating time when fetching large amount of dataset among teradata python module, ODBC(with pyodbc), and JDBC(with jaydebeapi).\r\n\r\nIt seems that it takes too long when fetching large amount of dataset, I used pandas read_sql to get the result, but the performance is same when using session.fetchall() directly.\r\n\r\nHere is the result comparision for sample size = 10000, 50000, 100000, 500000.  The ODBC driver performs awesome, however it cannot fetch DECIMAL data properly.\r\n\r\n-------time of sample size - 10000 --------\r\nODBC: 0:00:01.216346\r\nPyTD: 0:00:09.844205\r\nJDBC: 0:00:03.215781\r\n\r\n-------time of sample size - 50000 --------\r\nODBC: 0:00:01.270462\r\nPyTD: 0:00:35.796656\r\nJDBC: 0:00:08.558847\r\n\r\n-------time of sample size - 100000 --------\r\nODBC: 0:00:02.567906\r\nPyTD: 0:01:09.826736\r\nJDBC: 0:00:18.884113\r\n\r\n-------time of sample size - 500000 --------\r\nODBC: 0:00:08.691584\r\nPyTD: 0:05:34.788833\r\nJDBC: 0:01:27.788149\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/Teradata/PyTd/issues/comments/180646544","html_url":"https://github.com/Teradata/PyTd/issues/29#issuecomment-180646544","issue_url":"https://api.github.com/repos/Teradata/PyTd/issues/29","id":180646544,"user":{"login":"escheie","id":1410311,"avatar_url":"https://avatars.githubusercontent.com/u/1410311?v=3","gravatar_id":"","url":"https://api.github.com/users/escheie","html_url":"https://github.com/escheie","followers_url":"https://api.github.com/users/escheie/followers","following_url":"https://api.github.com/users/escheie/following{/other_user}","gists_url":"https://api.github.com/users/escheie/gists{/gist_id}","starred_url":"https://api.github.com/users/escheie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/escheie/subscriptions","organizations_url":"https://api.github.com/users/escheie/orgs","repos_url":"https://api.github.com/users/escheie/repos","events_url":"https://api.github.com/users/escheie/events{/privacy}","received_events_url":"https://api.github.com/users/escheie/received_events","type":"User","site_admin":false},"created_at":"2016-02-06T01:28:26Z","updated_at":"2016-02-06T01:28:26Z","body":"A fix has been committed to drastically improve performance.  There are also several settings that may help to improve fetch performance.\r\n\r\n1.   Set cursor.arraysize to a value greater than 1.   This attribute as documented in the Python Database specification controls how many rows are fetched at one time.  Increasing this value can increase throughput at the cost of higher memory consumption.   Note that this value is not used when LOB data is being returned with a ResultSet.  \r\n2.  Use charset=UTF8 on Unix/Linux and charset=UTF16 on Windows/Mac.   These encodings are what is expected by Python, so if these charsets are also the session charset, the ODBC driver doesn't have to spend time performing character conversions.\r\n\r\nThanks,\r\n-Eric"}}