Why there may be a place for Direct SQL

October 17, 2007 at 11:26 am 2 comments

Over at Service Oriented Shenanigans Graham’s written a good, provocative piece titled Why Direct SQL is a very stupid thing to do. I’m happy to be baited… 😉 I started to respond there, but it turned into something worth a full post. Hopefully trackback pulls the whole conversation together like it’s supposed to…

Graham argues that because Siebel implements referential integrity through the repository rather than through database rules, it’s idiotic bypass all these rules and run SQL directly against the database. I’d make a few points:

  • If you’re using Siebel Remote, then direct SQL could be additionally stupid. Any changes made directly to the database are not be propagated to the transaction log, so won’t be routed to the local databases, so your server will be out of sync with your remote clients… Nasty!
  • If, however, you’re NOT using Siebel Remote, or you’re re-extracting all your local databases anyway, then I’d argue that there is a place for direct SQL. A limited place, sure, requiring utmost care, diligence etc etc, but at the end of the day it’s just another tool to only be used when appropriate. Siebel Expert Services will happily run direct SQL when the alternatives are ridiculously cumbersome. The alternatives (e.g. EIM, EAI) are also quite capable of significantly damaging your database and also require an appropriate level diligence: developers shouldn’t lapse into thinking that just because things are being done with the ‘proper’ methods they can’t do harm (EIM Delete in the wrong mode, anyone?).
  • If you use direct SQL to insert a record, then you won’t get a Siebel Row Id. The procedures to generate a Row Id are in the database and could be called directly, but this really is idiotic. (I actually worked on an implementation where Siebel Expert Services had done this – hmmm…)

So don’t use direct SQL to insert records, and don’t break your referential integrity, but do remember that SQL is just another tool, with pros and cons to be weighed against the alternatives. And don’t come crying to me if you break your database and your architect justifiably calls you an idiot.

Advertisements

Entry filed under: Siebel, SQL.

Debugging SQL with Oracle Cost Based Optimizer More on Script Assist

2 Comments

  • 1. PH  |  October 17, 2007 at 6:30 pm

    Actually, there are situations where inserting records via SQL is okay, but it is DEFINITELY the exception.

    Also, direct SQL is often touted as a solution by supportweb. Data Deduplication tables is the first one that I can think of, and there are some examples in Life Sciences as well.

    The NO-SQL mantra has dropped away from the Siebel support mindset quite a bit, and rightly so.

  • 2. Graham  |  October 17, 2007 at 6:51 pm

    PH,

    I’d still say it was very risky… it’s the old problem of when someone’s actual competence is nowhere near the level that they think it is! You know, and I know… and most battle-scarred Siebel veterans know what’s a good idea and what isn’t… there’s a lot of people in the Siebel universe nowadays that this doesn’t apply to.

    The other area where direct SQL is “a good idea” is for clearing S_AUDIT_ITEM. After all there are no referential constraints on that bucket 😉


Feeds


%d bloggers like this: