Why should I use SQL Anywhere for my next project? - SQLA

Why should I use SQL Anywhere for my next project?

2009-11-11T16:05:26Z

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

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

Answer by Breck Carter for Why should I use SQL Anywhere for my next project?

2009-11-11T16:33:18Z

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.

Answer by Chris Kleisath for Why should I use SQL Anywhere for my next project?

2009-11-11T20:17:18Z

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.

Answer by Breck Carter for Why should I use SQL Anywhere for my next project?

2009-11-12T07:18:23Z

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.

Answer by Justin Willey for Why should I use SQL Anywhere for my next project?

2009-11-13T12:35:51Z

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.

Answer by Jon Greisz for Why should I use SQL Anywhere for my next project?

2009-11-13T13:23:10Z

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.

Answer by Glenn Paulley for Why should I use SQL Anywhere for my next project?

2009-11-13T16:52:08Z

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.

Ivan T. Bowman, G. N. Paulley, et al. (September 2007). SQL Anywhere: An Embeddable DBMS. IEEE Data Engineering Bulletin 30(3), pp. 29-36.
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.

Answer by Breck Carter for Why should I use SQL Anywhere for my next project?

2009-11-13T17:18:49Z

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.

Answer by Volker Barth for Why should I use SQL Anywhere for my next project?

2009-11-13T20:58:50Z

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:)