In this article, Part 2 of the JDBC Peformance Series, John Goodson shows us how to optimize system performance by retrieving data efficiently. He looks at the costs of retrieving long data, how to reduce the size of the data retrieved, choosing the right data types when designing your schema, and gives tips on working with Result Sets.

Read Performance Tips for the Data Tier (JDBC) Part II - Retrieving Only Required Data

That was 2 pages of trivialities... Is TheServerSide.com becoming *the* newbie site?

http://www.javaperformancetuning.com/tips/index.shtml

has more meat and less fluff, IMHO (see JDBC section).

P.S.: I'm not affiliated with that site in any way.

I found this article enlightening and interesting. I, along with the other developers on my team had no idea that performing full table scans on multi-million row tables using keyset cursors might be impacting performance negatively, I mean who would have thunk it?

It will be difficult for us though to change our field data types from all BLOBS. Computers deal in binary all the time so its a real eye opener to find out that other data types may actually yield higher performance.

The tips in the article may also help some of the issues we were having with our UI, its not easy to show three million results in a listbox to a user you know. Hopefully with more refined searching we will now only have to show them one million instead.

Apparently there is also something called indexing which we read about in the truly visionary "Learn how to be a DB Guru in 24 min's" and are interested in learning more about, its supposed to make selects faster or something, any chance we can have an article on that?

... my team had no idea that performing full table scans on multi-million row tables using keyset cursors might be impacting performance negatively, I mean who would have thunk it?

I read that as pure irony and I'm laughing my ass off ;-)

... my team had no idea that performing full table scans on multi-million row tables using keyset cursors might be impacting performance negatively, I mean who would have thunk it?

Then you should consider taking some basic programing courses.

It was sarcasm. Apparently the start sarcasm and end sarcasm tags were omitted.

Laugh all you want, some are completely clueless when it comes to databases. My team actually had to rewrite a system because those simple tips had not been followed by the original developers. Some entities had names that were localized (English and French), and the only solution they found was to store those names in a serialized HashMap within a BLOB (key=Locale, value=String). A maintenance nightmare for DBAs.

Any enterprise developer worth his or her salt should be experienced in data modeling, relational DBs, and SQL.

Laugh all you want, some are completely clueless when it comes to databases. My team actually had to rewrite a system because those simple tips had not been followed by the original developers.

You see, the above article was like telling a carpenter how to use a hammer. So your team isn't very experienced, it seems, because the article contains REALLY basic stuff.

I don't want TheServerSide to become an newbie level forum. I want it to be a slashdot'ish site for the J2EE community. Hardcore stuff. Interesting news.

That's what TheServerSide has been all about. Keep it that way.

I totally agree with you. I prefer articles that go much deeper. Still, the original developers showed themselves as professional and experienced, even if they were clueless in this case.

I must admit that we did laugh a bit at the old system we had been given. I just hope the original developers got better experience now. I wouldn't want to clean up their mess again. :)

The client that had to pay for both the original system and the rewrite found it less funny of course. But that's another story.

Laugh all you want, some are completely clueless when it comes to databases. My team actually had to rewrite a system because those simple tips had not been followed by the original developers.

You see, the above article was like telling a carpenter how to use a hammer. So your team isn't very experienced, it seems, because the article contains REALLY basic stuff.I don't want TheServerSide to become an newbie level forum. I want it to be a slashdot'ish site for the J2EE community. Hardcore stuff. Interesting news. That's what TheServerSide has been all about. Keep it that way.

There are many organizations where the DB knowledge is separated from the coders. This is most unfortunate yet a reality. So if you don't like it or need it - Ignore it. I would have like to have seen it labeled newbee or refresher. But oh well.

How much interesting/hardcore news has there been lately? I think these articles are more of a result of a slowdown in that area.

If there is anybody that needs a crash course in SQL then it is the EJB/OR guys from the "Big Java Application Servers" domain that for the most part does not have a clue.

Besides, if I were them, I would thread very carefully, and lie low for 2 and 3 years, after the of enourmous overuse of Java EJB.

The only thing that match the stupitidy of "well-meaning impractical theoreticans" is their arrogance. :)

REgards
Rolf Tollerud

If there is anybody that needs a crash course in SQL then it is the EJB/OR guys from the "Big Java Application Servers" domain that for the most part does not have a clue.Besides, if I were them, I would thread very carefully, and lie low for 2 and 3 years, after the of enourmous overuse of Java EJB.The only thing that match the stupitidy of "well-meaning impractical theoreticans" is their arrogance.

Rolf, in your last contribution to a thread on data access, you argued in favor of storing date and time information in VARCHAR columns. If possible, would you please take this opportunity to now share some more of your great data access tips with we arrogant, stupid and impractical EJB/OR guys who are in such need of a crash course in SQL :-)

Yes I remember that discussion. I was just meekly pointing out that if you have an application that are going to use different databases, some unknown, it may be a good idea to use a string format for dates, ie YYYY-MM-DD (ISO).

In the resulting discussion some argued that then it was not possible to do date manipulations in the database! (=putting application logic into the backend :).

Sigh. Why do I bother?

Regards
Rolf Tollerud

In the resulting discussion some argued that then it was not possible to do date manipulations in the database! (=putting application logic into the backend :).Sigh.

Oh, I remember that. It was me. I was just wondering how to do a "SELECT * FROM ATABLE WHERE ADATE > '10/10/2002' ORDER BY ADATE", using a VARCHAR2 ADATE field, nothing more. I wouldn't call that application logic, unless you consider outer joins and result ordering/filtering application logic too.

Why do I bother? Regards Rolf Tollerud

Lots of people have asked that too.

Regards,
Henrique Steckelberg

Henrique,

How to do,

"SELECT * FROM ATABLE WHERE ADATE > '10/10/2002' ORDER BY ADATE"

this way,

"SELECT * FROM ATABLE WHERE ADATE > '2002-10-10' ORDER BY ADATE"

Not too difficult, do you think?

Regards
Rolf Tollerud

Henrique,How to do,"SELECT * FROM ATABLE WHERE ADATE > '10/10/2002' ORDER BY ADATE"this way, "SELECT * FROM ATABLE WHERE ADATE > '2002-10-10' ORDER BY ADATE"Not too difficult, do you think?RegardsRolf Tollerud

In the example you gave using distributed databases, this simple solution could lead to problems, like character sets differences, blank-padded or non-padded comparison semantics differences, and so on. Much easier and safer to rely on the JDBC driver and prepared statements.

Regards,
Henrique Steckelberg

I was just meekly pointing out that...blah blah blah

Rolf? meek?
This I have to see.
Could you post a link to this discussion? (lol)

You are completly unfair. If I choose a nickname it could aptly be
"modesto".

<grin>

It must be better to use some native format than to break data types,
but there is no porability problems with data formats in practice too.
Ask coworker or google before to break data, all data access problems are very old and have not broken solution.

Yes I remember that discussion. I was just meekly pointing out that if you have an application that are going to use different databases, some unknown, it may be a good idea to use a string format for dates, ie YYYY-MM-DD (ISO).In the resulting discussion some argued that then it was not possible to do date manipulations in the database! (=putting application logic into the backend :).Sigh. Why do I bother?RegardsRolf Tollerud

Sigh. Why do I bother?

I have absolutely no idea Rolf. Perhaps you should stop?

If there is anybody that needs a crash course in SQL then it is the EJB/OR guys from the "Big Java Application Servers" domain that for the most part does not have a clue.Besides, if I were them, I would thread very carefully, and lie low for 2 and 3 years, after the of enourmous overuse of Java EJB.The only thing that match the stupitidy of "well-meaning impractical theoreticans" is their arrogance.

Rolf, in your last contribution to a thread on data access, you argued in favor of storing date and time information in VARCHAR columns. If possible, would you please take this opportunity to now share some more of your great data access tips with we arrogant, stupid and impractical EJB/OR guys who are in such need of a crash course in SQL :-)

I think I read once(very fast reading) in your blog that you don't really know the difference between a hash join and a nested loop. Is it true? Because then Rolf might have a point.

It is not any slight performance gain that is the question, but how to avoid all grief and sorrow with all the different date formats in the world. Not to speak of all the bugs there are in the tools in the date department, including Java.

But maybe people in US don't see this as a problem because they do not have that many international sites?

Regards
Rolf Tollerud

But maybe people in US don't see this as a problem because they do not have that many international sites?

Hmm--I work for a Hong Kong-based company with clients from all around Asia (including China, Japan, Korea, Thailand), Europe, Africa and South America as well as the US. Altogether we have clients in dozens of countries, and support about two dozen languages. We work with Sybase, Oracle and SQL Server.

I have not once seen a bug related to native database date/time types, or the related JDBC classes. I've certainly never seen a need to use text types for date formats.

Guess I've just been lucky--after all, Rolf couldn't be talking out of his ass, could he?

I think I read once(very fast reading) in your blog that you don't really know the difference between a hash join and a nested loop. Is it true? Because then Rolf might have a point.

I'm quite confident of my understanding of the difference between the three types of joins. Thanks for your concern. (Perhaps you benefit by brushing up on your English comprehension skills.) Next :-)

I think I read once(very fast reading) in your blog that you don't really know the difference between a hash join and a nested loop. Is it true? Because then Rolf might have a point.

I'm quite confident of my understanding of the difference between the three types of joins. Thanks for your concern. (Perhaps you benefit by brushing up on your English comprehension skills.) Next :-)

Really?

Isn't this from your blog? And it seems pretty recent, April 2004. To me, at the moment it looked like you just brushed up your technical skills. Shouldn't I be concerned?
It's true that english is not my first language, but the phrase 'Perhaps you benefit...' doesn't sound right, maybe it misses a would or should or at least a 'd.
Anyway, it's good to know that we have a new Leonardo around:-)))

If you ever work with relational databases, you should go out and buy O'Reilly's SQL Tuning, by Dan Tow. The book is all about how to represent a SQL query in a graphical form and then, using some simple rules of thumb, determine an optimal execution plan for the query. Once you have found the optimal execution plan, you can add indexes, query hints, or use some other tricks to persuade your database to use this execution plan. Fantastic stuff. There is even sufficient introductory material for those of us (especially me) who know less than we should about the actual technical details of full table scans, index scans, nested loops joins, hash joins, etcetera to be able to start feeling confident reading and understanding execution plans. Unlike most database books out there, this book is not very platform-specific, though it does often refer specifically to Oracle, DB2 and SQL Server.

In English, "who know less than we should about the actual technical details" does not mean "do not know the difference between". This is basic reading comprehension skills. In that blog I am admitting to not being an expert DBA and not knowing so much about some details at the actual physical level of memory and disk access.

See, I'm much more interested in recommending a great - and very unique - book, than in boasting about my knowledge.

Now go away. Or at least have the balls to post with your real name.

Now go away. Or at least have the balls to post with your real name.

Wow Gavin, that brush off was a lot quicker than usual, even for you. :-)

Working for JBoss must be a bit more stressful then Andy O lets on for you to have this level of insecurity.

    -Mike

I found this article enlightening and interesting. I, along with the other developers on my team had no idea that performing full table scans on multi-million row tables using keyset cursors might be impacting performance negatively, I mean who would have thunk it? It will be difficult for us though to change our field data types from all BLOBS. Computers deal in binary all the time so its a real eye opener to find out that other data types may actually yield higher performance.The tips in the article may also help some of the issues we were having with our UI, its not easy to show three million results in a listbox to a user you know. Hopefully with more refined searching we will now only have to show them one million instead.Apparently there is also something called indexing which we read about in the truly visionary "Learn how to be a DB Guru in 24 min's" and are interested in learning more about, its supposed to make selects faster or something, any chance we can have an article on that?

You know, if only that didn't happen I would laugh. I've seen that happen more than once and hear developers ask "why should I index the table?" or "You should never use views, they are slow." Of course I end up send out a bunch of articles about database performance from several vendors. But by then, the damage is already done and takes 3x more effort to fix.

Sometimes reading the easy things refresh the important points...

Sometimes reading the easy things refresh the important points...

Good point. Though many of the developers on this site are experienced, I'm sure there are plenty of newbies reading the boards too. No need to be a technology elitist. If after reading the 1st few paragraphs, you find it targeted to beginners, stop reading. It's that simple.

Mike

That was 2 pages of trivialities... Is TheServerSide.com becoming *the* newbie site?

The first article in this serie really sucked. I was hoping that we were going to learn something with the next artciles but obviously I was wrog. What are we going to learn with the next article ? That we should close the connections ?

I doubt many knew the subtle differences in using the metadata calls shown in Part 1. Part 1 was certainly better. It was definitely illuminating, at least to me.

This is an excellent book, highly recommended:

http://www.amazon.com/exec/obidos/tg/detail/-/0596005733

Not to belittle the author's commendable efforts, but I really think this is not the right place to publish this article. I mean you have to be totally clueless in "Server Side" development if this article actually taught you anything.

I agree with most of the comments above. TSS should concentrate on the really meaty stuff and stick to its nitch. There are billions of newbie Java sites out there. Let's not become the billion-and-one site. We've already seen how JDJ is compromising subtance and moving to the toddler market. "Server Side" implies the readership is intermediate-to-advanced or advanced.