Archive for jdbc

Calling stored procedures from Mirth

Posted in Computación with tags , , , , , , on 27/October/2010 by Alex Pérez

Mirth JDBC reader/writer are well suited for simplest cases: only one SELECT and (optionally) a UPDATE for each row, but normally you need to use JDBC reader/writer with JS enabled.

Mirth JDBC API when using JS is very basic and does not allow easily deal with stored procedures: there are only executeCachedQuery() and executeUpdate(). If you’re lucky you can use executeUpdate() as long as your SP doesn’t use IN/OUT parameters or returns any resultset. Otherwise you should call the SPs in a java library and then invoke it from Mirth JS. Some time ago I documented how to call custom java classes from Mirth and this may be used to extend its database API. Continue reading

Advertisements

Indexing NULL column values

Posted in Computación with tags , , , , , , , , on 1/August/2010 by Alex Pérez

After reading some great articles from Burleson Consulting about the “is null/is not null” predicates I decided to make some additional tests. It’s a very common mistake to write SQL with this kind of predicates since the NULL values aren’t included in regular indexes (B*Tree, not function-based), so almost every “IS NULL” results in a TABLE FULL SCAN.

I want to test how much a index can improve a IS [NOT] NULL predicate measuring the #requests per minute that a legacy box can serve. In the old days this server was a mission-critical server, but nowadays is retired as test server and can be compared to a very low end box (P3-512M ram).
Well, I measure the time that the select statement takes to execute (using JMeter)  instead of cost because a 30% reduction cost does not mean a 30% time reduction, and usually the end-user speak in terms of time, and there are many “tricks” like moving a table to the KEEP_POOL that can speed-up SQL execution although the cost is the same.
Continue reading