With so many DBMS products out there, what makes SQL Anywhere stand out?

Are there any exclusive features that SQL Anywhere has to offer?

asked 11 Nov '09, 16:05

Ben%20S's gravatar image

Ben S
23316
accept rate: 50%

edited 15 Mar '13, 18:48

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273


Another major feature of SQL Anywhere is the fact so many features are built-in to the database engine, with DDL to match, rather than implemented as external applications. External applications have to go through an interface to get at database data, and by definition the sum total will be fatter and slower: fatter because the external application must include interface logic that is not required if the feature was implemented inside the engine, and slower because no matter how fast an interface is, it is slower than no interface at all.

For example:

  • High-performance UNLOAD SELECT, missing from Microsoft SQL Server AFAIK, and crudely implemented in Oracle (SPOOL)... no, wait, SPOOL is a SQL*Plus thing, and SQL*Plus is an external application. And I'm thinking SPOOL might not be called "high performance" when millions of rows are involved, I don't know, doesn't matter in this context, it is not built in to the engine.

  • High-performance LOAD TABLE, crudely implemented in MSS as BULK INSERT, which may just be a shout-out to that funky external application bcp.exe, and missing from Oracle (don't say SQL*Loader, that's another external application).

Perhaps the most extreme example: SQL Anywhere has a built-in HTTP server. Does it work? You bet! iAnywhere Solutions eats its own dog food... iAnywhere Solutions built the SQL Anywhere Monitor using the engine's built-in HTTP server. And the documentation website uses the built-in HTTP server: DocCommentXchange 2.0 Rocks!

This is not just an abstract benefit: Because these features are built-in, you can get at them easily from code you write that also runs inside the engine: your own stored procedures, triggers, events and web services. With other DBMS software, if you want to write a stored procedure that uses a feature implemented externally, you really face an uphill battle... shell out to the OS, anyone?

And now on to another feature: Watcom SQL syntax, the favored dialect inside SQL Anywhere, is cleaner than, say Transact SQL [barf!]. Oracle's PL/SQL has its fans, it's certainly powerful, but AFAIK it is unique in all the world.

Don't get me started on IBM DB2... it has the most feeble stored procedure SQL syntax on the planet. True story: I worked on a huge conversion from SQL Anywhere to DB2, and the folks on the DB2 side had enormous difficulties making things work (like nested triggers). When the DB2 code finally compiled, it just would not run, no matter how many gigabytes of RAM you threw at the engine. IBM Labs got involved, finally said "not yet available". The project was a failure: DB2 was abandoned, SQL Anywhere lived on (smells like success, but that's just my bias talking).

I've looked at MySQL, really tried, failed... must be stupid... the sum total of my effort was this blog posting: Unpublished MySQL FAQ. Sadly, that is the most popular thing I've ever written... I certainly could make more money as a MySQL consultant, or Oracle, or MSS, or [kak] DB2, anything but SQL Anywhere. That way lies poverty.

permanent link

answered 12 Nov '09, 07:18

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

edited 12 Nov '09, 11:26

I did self-censor this answer: I removed mention of ANSI compatibility. I regularly offend important people, like respected ANSI committee members, by ranting about ANSI idiocies. Plus, I don't know nor do I care what is and what isn't compatible... SO, I will let other people talk about SQL Anywhere versus Other Stuff with respect to ANSI compatibility. To me, it is mostly an RFC checkpoint.

(12 Nov '09, 11:40) Breck Carter

Ooops, that should be "RFP checkpoint" or "RFQ checkpoint" as in Request For Proposal/Quotation: the pointy-haired boss reads about something in a magazine and then insists it is a "must-have feature" when evaluating vendor proposals, even though Dilbert, Wally, Alice and Asok all know it is ridiculous.

(12 Nov '09, 11:49) Breck Carter

Excellent support has to be a major reason.

On the rare occasions that we have had serious issues, we have been able to get a lot of good quality support quickly, and new builds of the software etc. This is terribly important when you are the jam in the sandwich between the customer and the RDMS vendor.

We are a very small customer of iAnywhere's and I really don't feel we would get support like that from the alternative suppliers.

permanent link

answered 13 Nov '09, 12:35

Justin%20Willey's gravatar image

Justin Willey
6.8k110142212
accept rate: 20%

One of the important reasons that SQL Anywhere works so well as an embedded database with an application is our focus on self-managing features, that permit the server to function near-optimally with very little human or programmatic intervention. Here are a few examples of the self-managing database technology that is included in the server:

  • Buffer pool sizing automatically altered based on database workload and memory availability
  • Automatic run-time adjustment of query execution strategies to respond to unexpected result sizes or resource constraints
  • Significant, cost-based query rewrite optimizations to enable the optimizer to generate an efficient execution strategy, to minimize the impact of different syntactic representations of the same SQL request
  • Automatic prefetching adjustments in response to application behaviour
  • Database statistics maintained automatically via run-time sampling and feedback
  • Active database support: procedures, triggers, events triggered automatically when `significant’ events occur
  • Online backup, table and index reorganization
  • Database stored as a single file in the OS file system; backup, image copy is trivial

Two short papers [1,2] available on my blog explain these features in detail.

  1. Ivan T. Bowman, G. N. Paulley, et al. (September 2007). SQL Anywhere: An Embeddable DBMS. IEEE Data Engineering Bulletin 30(3), pp. 29-36.

  2. I. T. Bowman et al. (April 2007). SQL Anywhere: A Holistic Approach to Database Self-Management. In Proceedings, 2nd International IEEE Workshop on Self-Managing Database Systems, Istanbul, Turkey.

permanent link

answered 13 Nov '09, 16:52

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Many of the features Ivan and Glenn speak of are not obvious at all, to the average SQL Anywhere administrator or developer, unless they have experience with other database software... and then it's "wow, this is so easy, how can it possible be this easy?" The closest analogy I can think of is the introduction of cellular phones; new customers did not fully appreciate the revolution in their hands, and would complain about spotty coverage etc. Former mobile phone users, however, thought cellular was the answer to every dream: ease of use, reliability, flexibility, robustness and feature-set.

(13 Nov '09, 17:09) Breck Carter

I should have said "Former mobile radio telephone users", and anyone under 30 probably still doesn't know what I'm talking about unless they watch old movies. See en.wikipedia.org/wiki/Mobile_radio_telephone

(13 Nov '09, 17:26) Breck Carter

SQL Anywhere is designed to "just work" for many types of systems. In particular it is ideal for embedding inside other software products, for servers that are deployed outside the traditional data center, or in mobile applications. All of these uses are in places where the user doesn't typically know that there is a database running.

Also, the SQL Anywhere product is designed with developers in mind, providing a very rich feature set with intelligent defaults.

permanent link

answered 11 Nov '09, 20:17

Chris%20Kleisath's gravatar image

Chris Kleisath
3.2k92836
accept rate: 38%

Another way to say "intelligent defaults" is "iAnywhere Solutions does things the way they should be done." In fact, the default settings are so good, in so many applications, that only the exceptions stand out in my memory: it took a long time for the default page size to get changed to 4K, and for some reason optimization_goal wasn't always 'all-rows'.

(12 Nov '09, 06:03) Breck Carter

I have to agree with Justin. It's definitely the support. When issues come up we can deal directly with people that know. Which means we can support our customers better.

permanent link

answered 13 Nov '09, 13:23

Jon%20Greisz's gravatar image

Jon Greisz
571979
accept rate: 0%

This one's going to sound strange but the fact that, for all intents and purposes, NOBODY has heard of SQL Anywhere gives you a competitive advantage if you use it. It is marketed by stealth, on purpose... if you don't believe me, do this Google search: stealth kleisath

All sorts of companies, large and small and REALLY LARGE, keep the fact they use SQL Anywhere secret; I can think of companies like F-d-x, C-s-o, I-tu-t and so on. Some of them keep the secret for competitive reasons ("if I use SQL Anywhere, and the other guy uses Oracle, he'll be spending more money and going to market late").

Some people keep it secret because they don't want to stand out, like someone at a cocktail party in San Francisco saying they voted Republican... if they say they voted for SQL Server or MySQL or Obama, they're more likely to fit in.

I realize this doesn't answer your question, but I'm sure that will come shortly.

permanent link

answered 11 Nov '09, 16:33

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

1

I am so tempted to self-censor, remove the joke about Republicans and Obama... but I am leaving it in as an example of protected free speech. That's not to say that discussions devoted to politics or religion are encouraged, they are not... no Rush-Limbaugh-versus-Daily-Kos arguments, please!

(12 Nov '09, 11:35) Breck Carter

Unique to SQL Anywhere, at least as far as I know: The database file is binary-compatible across machine architectures and operating systems; e.g., Windows to Linux to Solaris to Windows Mobile, etc., little-endian to big-endian and back.

So you want to scale up or down? Stop engine on machine/OS #1, copy the file, start engine on machine/OS #2.

permanent link

answered 13 Nov '09, 17:18

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

I would simply state "The product continues to expand in the right direction."

This is a really subjective experience:

In my years of database development with SQL Anywhere from V 5.5 onwards, there have been several situations where I thought "I would/could really need this feature", and it came up just in time. Not in all cases, of course, but often enough to get the feeling that there's a really senseful interaction between Product Management and Development.

And in cases I would like any enhancements, I feel ease to post them to the "product futures" news group. Though we're no big customer we know our requirements get attention, and in several cases have led to product enhancements (well, at least that's my strong belief...).

A further advantage is the NNTP community - it's really a pleasure (and a honour) to get in contact with the folks who build the product.

I would not expect anything comparable from the big database companies. I guess I would not even try to expect:)

permanent link

answered 13 Nov '09, 20:58

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

  1. I use SQLCentral, define Remote Server on Oracle and SQL Server2008 & Azure SQL Server Oracle have linkage but if I connect to other Database, have to pay, not cheap. SQL Server also, if you want to connect to Oracle, have to pay a lot. So I use SQLAnywhere as Trading company from Oracle to Azure SQL Server I can use Watcom SQL dialect to Oracle, SQL Server

  2. Whenever I use Database, I use Stored Procedure and test it whether I can call it select parts,location,qty from stock_take('SHORT') select parts,location,qty from stock_take('EXCESS') select parts,qty from stock_take('MATCH')

only SQLAnywhere, stored procedure behave as TABLE so that integrate with other table Am other database support it ? (Oracle, MS SQLSrv, MySQL, PostGreSQL tested)

permanent link

answered 20 Nov '13, 08:34

Ukraine's gravatar image

Ukraine
76336
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×8
×3

question asked: 11 Nov '09, 16:05

question was seen: 4,021 times

last updated: 20 Nov '13, 08:34