tsqlquery extrem slow with ado

[719]

hi,

i use zeos 7.2.6.1 with lazarus 2.0.6 (32 bit) under win10 (64bit) and connect to an ms-sql server 2012. my problem is, that querys run with TZQuery are extremly slow and i can´t figured out why. i have a dbgrid, connected to a datasource and the datasource.dataset is connected to a tzquery.

the code simplyfied:

con:=TZConnection.Create(nil);
qry:=TZQuery.Create(nil);
qry.Connection:=con;

con.Protocol:='ado';
con.Database:='DRIVER=SQL Server;Trusted_Connection=Yes;SERVER=xxx;DATABASE=yyy;
con.Connected:=true;

qry.SQL.text:='SELECT TOP 500 * FROM dm.viewBatchOverview WHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 ORDER BY Batch_ID DESC;';
qry.open;
(this 1=1 are paceholder for additional conditions that can be replaced in the sql without the need to complet regeneration of the entire statement)

this query takes 30 seconds.
in ms-sql management studio it takes 1 second
with lazarus native sqldb+odbc connection it takes 5 seconds.

what can i do to fix this? second question: what is the optimal connection method to an ms-sql-server without the needing of external .dll´s or drivers? i mean a simple connection string that works without any os-specifig adjustments like system dsn stuff...

thanks in advance.

[marsupilami]

hello 719,

I was told that ADO is quite slow in general. You might want to test Zeos 7.3 with the new OLEDB driver.

When it comes to connecting to a Microsoft SQL Server, you have two options, I think:

• Use the Microsoft driver with ADO / OLEDB / ODBC. All three should allow to use some kind of connection string. You are dependent on the Microsoft driver in that case. But it usually is installed with the SQL server. Also this is the only way to access the windows internal database.
• Use freetds. This is a library you can use to access Microsoft SQL server. In Zeos 7.2 use one of the FreeTDS-Protocols. This library requires TCP/IP for working correctly, so you will have to enable TCP/IP on the server side. In most cases it will be dependent on some microsoft C++ runtime libraries.

Best regards,

Jan

[719]

thanky you for the answer but i have found the solution by accident:

the problem was the "DRIVER=SQL Server" in the connection-string. i have it changed to "Provider=SQLOLEDB.1" and now the query
takes only 1 second !

[719]

hello marsupilami,

you sayed, i should use freetds. I've wanted that for a long time but the problem is:
i can´t find any windows-dll on the net to download. have searched for hours -nothing. only linux github stuff, you have to compile...
the ftp-link ( ftp://ftp.freepascal.org/fpc/contrib/windows/ )
from lazarus-website also doesn´t work/ is dead.

i know this webiste:
https://sourceforge.net/projects/freetdswindows/
but this 24mb archiv contains only .a libs -no .dll.

if anyone can give me the windows-dll for the actual freetds version 1.124 I would be very grateful.

[marsupilami]

Hello 719,

the problem was the "DRIVER=SQL Server" in the connection-string. i have it changed to "Provider=SQLOLEDB.1" and now the query
takes only 1 second !

Nice - it is good to know, that this can be a driver issue.

hello marsupilami,

you sayed, i should use freetds. I've wanted that for a long time but the problem is:
i can´t find any windows-dll on the net to download. have searched for hours -nothing. only linux github stuff, you have to compile...
the ftp-link ( ftp://ftp.freepascal.org/fpc/contrib/windows/ )
from lazarus-website also doesn´t work/ is dead.

i know this webiste:
https://sourceforge.net/projects/freetdswindows/
but this 24mb archiv contains only .a libs -no .dll.

if anyone can give me the windows-dll for the actual freetds version 1.124 I would be very grateful.

There is a FreeTDS build in our files section: https://sourceforge.net/projects/zeoslib/files/3rd party/FreeTDS/

Also I added a wiki page on how to build FreeTDS on Windows to our Wiki some time ago: Building FreeTDS with libiconv on Windows 32 Bits.
Please note: FreeTDS can be built without libiconv. libiconv is only needed if you need a character set other than UTF8. Also using FreeTDS has drawbacks - like not being able to use Windows Internal Database. As Always there are pros and cons to all these solutions.

Best regards,

Jan

[719]

hello marsupilami,

thank you for the actual .dll files! that helped me a lot.

best regards

sven