Re: SQLGetData performance

From: Dmitri Ivanov (di2va4nov_at_0_digits_for_spammers_0.aha.ru)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 23:11:08 +0300

Hello Vito,
"Vito" <wiwaniec@accesswave.SPAMFILTER_AT_DOT.com> wrote:

V> Hi
V>
V> I need an ODBC-expert opinion regarding SQLGetData.
V> I have a win32 application that connects to SQL Server through ODBC.
V> The application runs on a Windows 2000 workstation with the latest
V> SP and patches and connects to SQL Server 2000 (with latest patches)
V> on Windows 2000 server (latest patches). The application uses ODBC
V> API functions to get the data and loads the data into controls in
V> the application.
V>
V> A query that used a particular view took a few minutes to load the
V> data. I copied the query from the application log file and run in
V> Query Analyzer - it returned in reasonable time around 10 seconds.
V> I added extra debugging into my code and found out that the query
V> itself run reasonably fast but loading the data took the few minutes
V> time. In particular the SQLGetData() took long time to load data
V> from a column after a row was fetched but only occasionally. For
V> example SQLGetData from row 370, column 6 returned after 1 min 3
V> sec, row 879 col 2 - returned after 1 min 25 sec. The query returned
V> 975 rows and there were few more columns that caused similar
V> problems. The behavior was consistent - I run the application with
V> the same query a few times and the "hangup" was always in the same
V> row and column. The columns that took long time were not large -
V> varchar(10), varchar(120), varchar(255).
V>
V> I tuned the view and it solved the problem - SQLGetData() returns
V> the data quickly from each row and column.
V>
V> I would like to know what could be the reason for the behavior. I
V> can see that the SQLExecDirect() can take long time if the query is
V> badly written. I think SQLFetch() can also take some time once in a
V> while if there is an issue with paging. But don't see what could
V> cause SQLGetData() take a long time afte a row had been fetched.
V>
V> Any thoughts or directions to find an explanation?

I can not explain the point exactly but I hope you do not have these columns
bound. IMHO SQLGetData() should be used for long columns only, e.g.
longvarchar. For varchar(255), binding the column would definitely perform
better. 

--
Sincerely,
Dmitri Ivanov
Common Lisp ODBC interface - www.ystok.ru

Relevant Pages

  • Re: [PHP] ODBC and long text fields
    ... We've a PHP app that uses ODBC to talk to a MS SQL server. ... The internal code for a query is ...
    (php.general)
  • RE: Timeout Expired
    ... Try creating the query you are running via ODBC as a stored procedure in the ... This will allow SQL Server to create and store an optimized execution ...
    (microsoft.public.sqlserver.odbc)
  • SQLGetData performance
    ... I need an ODBC-expert opinion regarding SQLGetData. ... I have a win32 application that connects to SQL Server through ODBC. ... patches and connects to SQL Server 2000 on Windows ... A query that used a particular view took a few minutes to load the data. ...
    (microsoft.public.data.odbc)
  • ODBC and long text fields
    ... We've a PHP app that uses ODBC to talk to a MS SQL server. ... "Invalid Descriptor Index" on SQLGetData as well. ...
    (php.general)
  • Timeout Expired
    ... I have a table in SQL Server 2000 with 3.5 million rows in it, ... I am querying this table via ODBC using the microsoft driver. ... I have a non-unique index ... the query takes approximately 1.5 minutes to complete when run through Query ...
    (microsoft.public.sqlserver.odbc)
Loading