In most cases, simply creating an Oracle SEQUENCE with all defaults is good enough: CREATE SEQUENCE my_sequence; This sequence can then be used immediately in triggers when inserting new records in a table: CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON my_table FOR EACH ROW -- Optionally restrict this trigger to -- fire only when … Continue reading Don’t Forget to Set the SEQUENCE CACHE Size
Tag: performance
The Index You’ve Added is Useless. Why?
Recently, at the office: Bob: I've looked into that slow query you've told me about yesterday, Alice. I've added the indexes you wanted. Everything should be fine now Alice: Thanks Bob. I'll quickly check ... Nope Bob, still slow, it didn't seem to work Bob: You're right Alice! It looks like Oracle isn't picking up … Continue reading The Index You’ve Added is Useless. Why?
NoSQL? No, SQL! – How to Calculate Running Totals
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don't know SQL. And it isn't even necessarily our fault. We're just not exposed to SQL nowadays. But consider this: We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, … Continue reading NoSQL? No, SQL! – How to Calculate Running Totals
jOOQ Newsletter: February 26, 2014
Subscribe to the newsletter here Tweet of the Day Our followers, users and customers are shouting their love for jOOQ to the world. Here are: Ben Hood who is constantly discovering new useful features in jOOQ. https://twitter.com/0x6e6562/status/436753836540452864 Antoine Comte who is skipping MyBatis to jump directly to jOOQ (in French) https://twitter.com/comte_a/status/433699690069385216 Both guys are absolutely right, of … Continue reading jOOQ Newsletter: February 26, 2014
What you Didn’t Know About JDBC Batch
In our previous blog post "10 Common Mistakes Java Developers Make When Writing SQL", we have made a point about batching being important when inserting large data sets. In most databases and with most JDBC drivers, you can get a significant performance improvement when running a single prepared statement in batch mode as such: PreparedStatement … Continue reading What you Didn’t Know About JDBC Batch
MongoDB “Lightning Fast Aggregation” Challenged with Oracle
What does "Scale" even mean in the context of databases? When talking about scaling, people have jumped to the vendor-induced conclusion that: SQL doesn't scaleNoSQL scales It is very obvious that NoSQL vendors make such claims. It has also been interesting that many NoSQL consumers made such claims, even if they probably confused SQL in … Continue reading MongoDB “Lightning Fast Aggregation” Challenged with Oracle
Faster SQL Pagination with Keysets, Continued
A while ago, I have blogged about how to perform keyset pagination (some also call this the "seek method"). Keyset pagination is a very powerful technique to perform constant-time pagination also on very large result sets, where "classic" OFFSET pagination will inevitably get slow on large page numbers. Keyset pagination is most useful for lazy … Continue reading Faster SQL Pagination with Keysets, Continued
ID Lists Aren’t the Best Solution for the N+1 Problem
In their eternal attempts to circumvent the N+1 problem, Hibernate users often resort to IN predicates with ID lists. In this post, we'll see how those users might just be replacing a horrible thing with a bad one, which is better but not yet good. Here's why: The N+1 Problem The N+1 problem is a … Continue reading ID Lists Aren’t the Best Solution for the N+1 Problem
Faster SQL Pagination with jOOQ Using the Seek Method
Last week, I have blogged about why it is important to stay in control of your SQL, as writing good SQL helps keeping your operations costs down. This is true in many ways and today, we're going to look into another way to write good, high-performing SQL: Using the "Seek Method". Slow OFFSET In order … Continue reading Faster SQL Pagination with jOOQ Using the Seek Method
jOOQ Newsletter September 17, 2013
Subscribe to this newsletter here. SQL for calculations SQL can be used for heavy calculations. This doesn't mean that it has to, of course. Many Java-oriented software architects are reluctant to allow for business logic entering their database. DBA tend to disagree and promote complex logic in database views or stored procedures. The pros and … Continue reading jOOQ Newsletter September 17, 2013