Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Sybase::CTlib vs DBD::Sybase

by perl@1983 (Sexton)
on Aug 03, 2012 at 12:34 UTC ( #985225=perlquestion: print w/ replies, xml ) Need Help??
perl@1983 has asked for the wisdom of the Perl Monks concerning the following question:

Can someone please help to clarify which Sybase library performs faster: Sybase::CTlib or DBD::Sybase? I noticed that the BCP operation with Sybase::CTlib is way faster than DBD::Sybase. DBD::Sybase anyways has an "Experimental Bulk Load" functionality, which seems to be doing normal Insert only. So, the questions are: - is BCP better in Sybase::CTlib? - Do people use DBD::Sybase only because of portability? Any specific reason for using it instead of Sybase::DBlib? Many Thanks.

Comment on Sybase::CTlib vs DBD::Sybase
Re: Sybase::CTlib vs DBD::Sybase
by marto (Chancellor) on Aug 03, 2012 at 13:08 UTC
Re: Sybase::CTlib vs DBD::Sybase
by sundialsvc4 (Monsignor) on Aug 03, 2012 at 13:23 UTC

    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...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://985225]
Approved by moritz
Front-paged by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2014-08-21 05:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (127 votes), past polls