Re: Sybase::CTlib vs DBD::Sybase

[sundialsvc4]

In my experience, the “overhead” if any that might be introduced by a database-API layer is negligible in all cases.   What matters is how you go about doing what you are doing.   (And here, the API-specific options do vary, as not all database implementations are the same.)

The biggest and most important rules of thumb that I can immediately think of are these:

• If you have a “bulk load” facility, use it, and use it properly.   Do it at the right time of day night.   It is usually but not always appropriate to drop or to disable indexes, then rebuild them afterward.
• Prepare an SQL statement once, then use it repeatedly.   Always use placeholders.   Explicitly close result-sets and cursors, to expressly indicate to the remote system when results are no longer needed.
• Know how to set the size of the returned row-set cache and set it appropriately.   Pay attention to movements along the TCP/IP wire.
• Do work inside of reasonably sized transactions, to achieve I/O avoidance but without allowing a rollback-log to become too large.   Understand how your DBMS implements its transaction facility.   Fully understand transaction isolation levels and use them appropriately.
• If you are doing a complex query, EXPLAIN it before you install it into your software.   “There’s more than one way to do it.”   Find the best way to say it.
• Understand how your DBMS implements stored procedures and temporary tables (if any), and consider carefully how to use them to your best advantage.   Some implementations are well-done, with languages that are rich and powerful; other implementations frankly suck.
• Do not let Crystal Reports® do anything other than print-out the results that are prepared for it.   Do not let Excel® do database-driven lookups and calculations.   (At best, they will run thousands of queries; at worst, they will push millions of records down a little-bitty wire, only to throw 99.9% of those records away.)   If you find anyone in your shop doing any such thing, forsooth, beat them most severely about the toenails with a Camel-hair brush.   But I digress...