[Qt-interest] QODBC seems very slow connecting to MSSQL

[Christian Dähn]

Hi,

I used the TDS sql driver from Qt up to this year for a big MS SQL Server 2005 with many
connections / queries per second. The funny thing is: everybody claims TDS to be dead already,
but there's no alternative to this native client.

ODBC is and stays much slower than TDS and: I had to move to QODBC due to the dropped
support by Qt (I've patched the QTDS massively for SQL Server 2005 - so, if anybody needs
an up to date QTDS driver: ask me).

If you have slow QODBC connections/queries, check out this:
a) is the tracing / logging for ODBC enabled? then turn it off.
b) are your queries quite complex? do you have sufficient indexes?
c) did you set QSqlQuery::setForwardOnly(TRUE) before executing your query?

The setForwardOnly() should be set (by my experience) always to TRUE for all MS SQL Servers -
otherwise the query results are fetched as single rows from the database which slows down
your queries by factor 100 ore more.

Example:
I had a big client database and requested a result set of clients with only 100 entries.
Due to the setForwardOnly(false) (which is Qt's default), the QODBC asked the database
(a MS SQL Server 2008) exactly 100 times for the next result (row) - this took me up to 800 ms!
When I turned on the forward only mode, I got all 100 rows as one reply - in only 25 ms!

ciao,
Chris


Am Di 06.07.2010 11:13 schrieb Harry Sfougaris <hsfougaris at gmail.com>:

>>Unfortunatly TDS doesn't support latest versions of MSSQL server (and
>>it looks like project is dead or almost dead).
>Looking at the FreeTDS mailing list it doesn't look that dead as everyone makes it out to be.
>And of course it supports all version of MSSQL server; it just doesn't support some very fancy new options
>
>>And more sadly thing is that TDS is the only MSSQL driver for other platforms than windows...
>I think that is exactly the issue here. People claim TDS is dead so we should use ODBC, but all ODBC drivers (outside Windows) is based on TDS anyway....
>If TDS really dies, so will all the ODBC drivers. Meanwhile, we are stuck with inefficient access to one of the world's most popular DBMS (unfortunately)...
>
>Harry
>
>>
>>
>>2010/7/5 Harry Sfougaris <hsfougaris at gmail.com>:
>>>
>>>On 5 ???? 2010, at 20:42, "Scott Aron Bloom" <Scott.Bloom at onshorecs.com> wrote:
>>>
>>>>The TDS system is very old, and much slower than the ODBC connection...
>>>
>>>I think this statement is only accurate when running under Windows.
>>>All ODBC drivers I have seen under Mac and Linux are based on TDS.
>>>
>>>And I agree odbc is slower than tds even under windows.
>>>It was very sad for me to see Qt dropping support for it, thus leaving no decent support for MSSQL when not running under windows.
>>>
>>>Harry
>>>
>>>
>>>>
>>>>Read about its problems here.
>>>>
>>>>http://labs.trolltech.com/blogs/2010/02/09/tds-driver-deprecation/
>>>>
>>>>Scott
>>>>
>>>>-----Original Message-----
>>>>From: qt-interest-bounces at trolltech.com
>>>>[mailto:qt-interest-bounces at trolltech.com] On Behalf Of Stephen Jackson
>>>>Sent: Monday, July 05, 2010 10:15 AM
>>>>To: Qt-interest
>>>>Subject: Re: [Qt-interest] QODBC seems very slow connecting to MSSQL
>>>>
>>>>On 5 July 2010 12:53, Roland Tollenaar wrote:
>>>>>
>>>>>We seem to experience that using QODBC with QSqlQuery can be rather
>>>>slow
>>>>>when used with MSSQL.
>>>>>
>>>>>Is there a native plugin for mssql that is faster?
>>>>>
>>>>>We are using Qt 4.5.2.
>>>>>
>>>>>Thanks in advance,
>>>>>
>>>>
>>>>The native driver is TDS
>>>>
>>>>qt/src/sql/drivers/tds
>>>>
>>>>However, I don't know how it performs compared to ODBC.
>>>>
>>>>--
>>>>
>>>>Stephen Jackson
>>>>_______________________________________________
>>>>Qt-interest mailing list
>>>>Qt-interest at trolltech.com
>>>>http://lists.trolltech.com/mailman/listinfo/qt-interest
>>>>
>>>>_______________________________________________
>>>>Qt-interest mailing list
>>>>Qt-interest at trolltech.com
>>>>http://lists.trolltech.com/mailman/listinfo/qt-interest
>>>
>>>_______________________________________________
>>>Qt-interest mailing list
>>>Qt-interest at trolltech.com
>>>http://lists.trolltech.com/mailman/listinfo/qt-interest
>>>
>>
>>_______________________________________________
>>Qt-interest mailing list
>>Qt-interest at trolltech.com
>>http://lists.trolltech.com/mailman/listinfo/qt-interest
>
>
>_______________________________________________
>Qt-interest mailing list
>Qt-interest at trolltech.com
>http://lists.trolltech.com/mailman/listinfo/qt-interest


Mit freundlichen Grüssen,
Christian Dähn
Consultant

// ASinteg GmbH
// Hagenower Strasse 73, 19061 Schwerin
// Registergericht Schwerin HRB 9035, Geschäftsführer: Sandro Seltitz
// Telefon +49 (0)385 30 200 500, Fax +49 (0)385 30 200 509
// Internet http://www.asinteg.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20100713/bdf05fb4/attachment.html