Hope to add the NativeDB driver support for Sybase SQL Anywhere

Flashcqxg · 2020-04-13 03:05:31

Hello:
NativeDB is a special drive for Sybase SQL Anywhere and extremely fast,NativeDB see http://www.nativedb.com/.
I want to use it for TSQLDBServerHttpApi,although now using TSQLDBFireDACConnectionProperties can connect to Sybase SQL Anywhere, but it's speed is relatively slow, far less than NativeDB.
Hope to add the NativeDB driver support for Sybase SQL Anywhere.
Thank a lot!

mpv · 2020-04-13 05:48:47

You can try SynDBOleDB, SynDBODBC or SynDBZeos. Hope all of them can connect to Sybase.

Flashcqxg · 2020-04-14 00:39:23

mpv wrote:

You can try SynDBOleDB, SynDBODBC or SynDBZeos. Hope all of them can connect to Sybase.

Thank you,they are too slow.

mpv · 2020-04-14 08:38:18

AFAIK MS SQL and Sybase use the same TDS protocol for communication. So I don't understand why, for example, OleDB provider and SynDBOleDb implementation should be slow on Sybase while it fast enough on MSSQL. And ZEOS even support TDS directly. Did you have some measures for comparison between NativeDB and Syn* classes?

ab · 2020-04-14 08:52:19

Did you check what https://www.freetds.org/userguide/seemtooslow.htm says ?
Especially about DNS resolution.

And please define what "slow" means for you.
If all ways of accessing the DB, including FireDAC, is "slow" for you, it means that either your performance expectations are wrong (e.g. you expect HUGE resultsets to return quickly whereas all data is retrieved so it takes time), or you have something broken in your configuration.
Only if one provider is noticeably slower (3-10 times) than another way of communication, then there is a problem in the driver.

Flashcqxg · 2020-04-14 11:36:01

"Slow" is relative, mainly because NativeDB is too fast. I have tested, access SQL Anywhere on local machine, take out 40,000 records, NativeDB only needs 6-11ms, and FireDAC needs 700-900ms. As the server side, this speed experience is very obvious. Therefore, I hope to increase the NativeDB driver, thanks.

Last edited by Flashcqxg (2020-04-14 11:37:01)

ab · 2020-04-14 11:44:03

Are you sure that the 40,000 records are actually fetched, and not only the first dozen, just enough to be displayed?
We don't use open cursors (which tend to put any DB server on its knees when too much cursors are opened at once), but fetch all records as JSON and close the statement ASAP.
If it is too big/slow, reduce the number of records fetched by using paging.

I am not convinced that NativeDB is faster than FireDAC or Zeos. Usually, Zeos is faster than anything else on Delphi.
For instance, ZDBC or our SynDB classes are like NativeDB in concept: it bypasses the DB.pas unit and TDataSet.
I still suppose you are not fetching all rows, just the first ones.

Flashcqxg · 2020-04-14 12:02:34

Zeos can connect to the database, but it prompts an error when querying and failed.
The NativeDB query statement is the same as FireDAC, and the total amount of data to be obtained in the end is similar to the following code:

query.sql.add ('select * from test');
query.open();
query.fecthall();//for firedac

memo.lines.add (query.recordcont.tostring);

Last edited by Flashcqxg (2020-04-14 12:16:34)

EgonHugeist · 2020-04-15 09:16:24

Hi, Zeos has a native acess driver for Sybase SQL Anywhere. But it's not using FreeTDS lib.

The protocol name is 'ASA'. To be honest the access api used in Zeos is very old, still supported by Sybase but there is no documentation available anymore. I've a "new" driver started two years ago but i never had the time to finalize it. The current "DBLIB" implementation is quiet slow becsause a teammember added a precaching technic for the obsolate DBLIB drivers ['mssql','sybase']. The reason is/was the DBLIB api has many limitations. The types a undefined just as binary/text returned(signed/unsigned ordinals are quiet uncleare too). Jan did force a full load of data and ask metainformations afterwards(just a nice technic for TDataSet users). That's why is don't use it anymore.

Instead it's woth it adding a CTLIB api but FreeTDS is not ready supporting it.(the Sybse Adaptive server already did deprecate DataBase-LIB and suggest to us C-Type-LIB API instead)

However iirc, the user can't access the protocol because of the mORMot URI limitations thought.
Regards Michael

Last edited by EgonHugeist (2020-04-15 09:17:31)

Flashcqxg · 2020-04-15 11:33:19

So,currently,NativeDB is the best solution to access SAP SQL Anywhere.

mpv · 2020-04-15 12:14:57

@Flashcqxg - did you try with SynDBOleDB and SynDBODBC. Is they also slow?

Last edited by mpv (2020-04-15 12:15:19)

EgonHugeist · 2020-04-20 15:09:01

@Flashcqxg
if updated the zeos api of the "ASA" protocol for SQLAnywhere17.

I've to revert my words about "old" API. The api still works perfectly and is continued to be supported by SAP. It's just the documentation of Sybase/SAP which is missing(some purposes of parameted are unknown and we send dummy values). See http://codeverge.com/sybase.sqlanywhere … ocs/841759 others did run into same issue.
So Zeos !HAS! a native driver for SQLAnywhere. The Zeos protocol is named "ASA".

EgonHugeist wrote:

However iirc, the user can't access the protocol because of the mORMot URI limitations thought.

means you need to patch SynDBZeos because the enumeration AB uses is just a limitation in this area.

I've moved my old Sybase API files to 7.3-testing. It's not complete and optimized, compilation might fail, so it's not officaly added to the zeos packages. It's a lookup nothing else. Other DB-vendors have higher priority for me. I've a pending native access drivers for DB2,Informix,FB3up in queue. Means you have a driver to work with which propably is faster than FireDAC or UniDAC or something else which is using the TDataSet's.

Regards, Michael

Last edited by EgonHugeist (2020-04-20 15:14:03)

Flashcqxg · 2020-04-27 00:51:45

ab wrote:

Are you sure that the 40,000 records are actually fetched, and not only the first dozen, just enough to be displayed?
We don't use open cursors (which tend to put any DB server on its knees when too much cursors are opened at once), but fetch all records as JSON and close the statement ASAP.
If it is too big/slow, reduce the number of records fetched by using paging.
I am not convinced that NativeDB is faster than FireDAC or Zeos. Usually, Zeos is faster than anything else on Delphi.
For instance, ZDBC or our SynDB classes are like NativeDB in concept: it bypasses the DB.pas unit and TDataSet.
I still suppose you are not fetching all rows, just the first ones.

For SQL Anywhere 17, I believe NativeDB is the fastest, this can be verified by testing.

EgonHugeist · 2020-05-29 15:42:30

Just a note from me: I've updated the Native ASA driver of zeos for V17. Also did i add a new driver with protocol name "asa_capi" for the recommended sybase api.

Flashcqxg · 2020-06-01 14:35:09

Hi:
EgonHugeist,Can you send a copy of the updated the Native ASA driver of zeos for V17 to me?My Email:51536348@qq.com.
Thanks!

EgonHugeist wrote:

Just a note from me: I've updated the Native ASA driver of zeos for V17. Also did i add a new driver with protocol name "asa_capi" for the recommended sybase api.

Last edited by Flashcqxg (2020-06-01 14:36:03)

mORMot Open Source

#1 2020-04-13 03:05:31

Hope to add the NativeDB driver support for Sybase SQL Anywhere

#2 2020-04-13 05:48:47

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#3 2020-04-14 00:39:23

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#4 2020-04-14 08:38:18

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#5 2020-04-14 08:52:19

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#6 2020-04-14 11:36:01

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#7 2020-04-14 11:44:03

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#8 2020-04-14 12:02:34

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#9 2020-04-15 09:16:24

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#10 2020-04-15 11:33:19

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#11 2020-04-15 12:14:57

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#12 2020-04-20 15:09:01

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#13 2020-04-27 00:51:45

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#14 2020-05-29 15:42:30

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

#15 2020-06-01 14:35:09

Re: Hope to add the NativeDB driver support for Sybase SQL Anywhere

Board footer