R. M. Menon

As far as I know HTML5 games have come a long way in terms of performance. What are your thoughts on building mobile games using HTML5 and JS. My thinking is that we are still far from ideal but it is a matter of time.

I have a simple question. How effective or ineffective XP would be if pair programming is removed from the practice. Having practised it myself, having attended Kent Beck's seminar and also having read his book, I like it but I also see other people's opinion and understand the objections. So my question is:
Can we eliminate or have a revised version of pair programming when practising XP?

Originally posted by Scott Selikoff:
I do have a unique id (just not one generated at the time of the update). I try to avoid PLSQL solutions when I can, I was hoping for something in pure SQL.

Ironically, adding the ORDER BY caused actual deadlocks instead of "deadlock exceptions" from oracle since they were all grabbing the first record. Oh well.


I decided to solve the problem using java method synchronization on the 2 sql queries since the environment is such that its a single jvm with dozen of threads. It still uses manuel committing for database protection, but so far seems to be more reliable than what I had before.

Any reason to avoid PL/SQL? You are missing out on many features - including this one...

Also, realized in this case, you will "block" (not deadlock) by putting order by - sorry about that (I assumed sessions commit immediately after the update - which was stupid of me, of course)

can you give me pseudo code for the case where you were using "select for update" that caused deadlock 5% of time?

Also, I am trying to understand why one would want to update a "random" row in a session. (Using rownum gives you a random row to update.)
Can you explain why?

Originally posted by Scott Selikoff:
Is it psosible to perform an update in JDBC (preferably via Oracle) such that you can perform an update and read the results in a single SQL statement?

Other restrictions are that the row does not have any generated keys. I'd like to do the equivalent of the following "Fetch me a single open record, change a column value to be closed, and tell me which record I updated"

I can do this with 2 statements using SELECT FOR UPDATE then performing the UPDATE but in high traffic this throws deadlock exceptions in oracle about 5% of the time.

The query that performs the update is:

UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id
FROM myTable
WHERE status = 'Open' AND ROWNUM = 1)

This will perform the update I want but it won't tell me which record was updated especially if there are multiple threads executing at the same time with this query.

[ March 23, 2006: Message edited by: Scott Selikoff ]

Use sequence for generating unique values, if required - you have to have a way of uniquely identifying a row in a table (primary key)

You can do something like this in PL/QSL:

UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id
FROM myTable
WHERE status = 'Open' AND ROWNUM = 1)
RETURNING <your primary key> INTO l_uniq_id;

where l_uniq_id is a local variable in a PL/SQL procedure...

Also to avoid the deadlock you should order the rows (by primary key) that you are selecting from the table within the update so threads dont step on each other. Something like:


UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id fROM (SELECT id
FROM myTable
WHERE status = 'Open' ORDER BY <primary_key> AND ROWNUM = 1)
RETURNING <primary_key> INTO l_uniq_id;

Originally posted by Ryan Johnson:
We have a background process that runs every few minutes. It opens up several sessions and each performs works. When we take the system down for maintenance we have a korn shell script that kill -15 the java threads.

Problem is that even though autocommit is off insert,update,delete that have been made are being committed when the session shuts down and we would like it to roll back.

Writing code to handle a graceful shutdown and have it roll back would be a significant effort. Is there a jdbc flag that will tell oracle 'if this session dies on you rollback and don't commit unless we specifically say so'.

When you take the system down for maintenance, why don't you wait for the threads to finish the jobs? When you shutdown Oracle normally, it should do that. If you want to "force kill" then you should get an SQLException which you should be catching and doing a rollback in your JDBC client code anyways? Also, do you have "intermittent" commits in your transaction that may be causing this issue?

Originally posted by jaikiran pai:


Well, thats exactly, what i wanted. Suppose it had 10000 rows and i set a fetch size of 500. One first invocation, i need to get the 1-500 rows then on second invocation 501-1000 rows and so on, which i think is not possible with the approach mentioned by "Stu Derby".

That is what fetch size does. It fetches in chunks that you specify. i don't see where the problem is.

Originally posted by jaikiran pai:
Hi,
We have a J2EE application deployed on Jboss. We have a requirement wherein, we have to execute a query to retrieve a huge ResultSet. This can run into millions of records. Naturally, we have already seen the OutOfMemory error. Is there any way(API) through which i can retrieve the ResultSet in chunks?

Which DB? In Oracle, you could set the fetch size and it won't run out of memory (assuming you use the Oracle supplied JDBC drivers.)

Originally posted by George Stoianov:
Hi Menon,

Can you please eloborate on why count(*) would not return the correct results in Oracle or point to a place to find some explanation.

Thinking about the record count I think it is kind of interesting how one has to approach this, are there examples of this in your book?

In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.

I will give an example so you can comment on that. Let us say I make a method that accepts a connection and a table name in the method I issue a count(*) and retrieve the number of rows which the method returns to my calling method. This in my mind is good OO and reusable functionality that fits well in any program. How would you rework that taking into account the imperfect results from count(*)?

I do a lot of web development and usually a record count is printed when displaying a table yes I can keep a counter and append the count to the string holding the table in a place where it appears on top of the output or bottom whatever, but that means I have to duplicate this code for every loop for every table, that is not good OO and leads to problems. If I later find a better way I then need to go and change my code in all of those places and possibly introduce new problems.

Thanks in advance,
George

My reply is relevant only for Oracle.

In Oracle, when you do a select - the results are fixed as of the point of time (roughly speaking) your select started(in the default transaction isolation level). Even if you insert and commit records during the select run, it would still show the same result as was true at the point it ran (in the default transaction isolation level). I cover these concepts in detail in my book in a separate chapter on Transactions.

So if you executed one query Q1 and then immediately executed another query, to get the count, it is possible that the count is wrong since other transactions may have happenned in between. But that is not even important. The fact is that count(*) entails going through all records in the query again and that work if done in the database is unnecessary = especially if you can do it easily outside using a counter.

And reuusabuluty of code is not OO specific. In procedural code also you can and should write reusable code - though OO is a more powerful paradigm in this respect.

If it is a genuine requirement, you could write a generic procedure to count from a given table but that does not solve the original problem which was for any given query results (remember you can have joins.)

I mentioned my suggested approaches in this case in the eariler response.

Originally posted by Jeanne Boyarsky:

Unfortunately, "Menon" doesn't follow our naming policy. A first name (or initials) and last name is required. Can you change it back?

It is ok to sign your posts with Menon as you have been doing, it's just the display name that needs it.

Done. Now I know why I did not have it as "Menon" in the first place. It is a strange requirement though, if you ask me..

Originally posted by stu derby:


Yes, agreed. However, all the JDBC forums I've ever looked at have people clamoring for the best way to do just that.

A very common problem is where they have a web system and want to display a "page" of results; maybe rows 31 through 40, and they have a business requirement to display "Page n of m" along with the data.

R. M. Menon, how would you do it, generally?

"R. M. Menon" sounds very formal - Menon is good enough I go by my last name everywhere. I just changed the display name in my profile - so hopefully for future posts, it would just be Menon.

It depends how costly it is to do a count(*) from the table. Consider the case where it is very costly to count the rows (since number of rows returned is higher). In this case, I would convince the business users that you can do with just a "Page n" information. In Oracle (which is what I am familiar with most) it is very easy to get the nth page rows efficiently by rexecuting a query. The users typically just need to sort the data and see chunks of it in pages - they dont go beyond even 5 to 10 pages. Even google shows you an approximation when it says pages n of m found, btw.

See a very good discussion on this at asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:2468032473603624973::NO::F4950_P8_DISPLAYID,F4950_P8_B:127412348064,Y

If that is not an alternative, you can get an approximation of the count of rows in Oracle (if you use CBO - cost based optimizer, you can look at the approximate count Oracle thinks for this query in v$sql_plan).


Lastly, - with the caveat that this would still give you approximate count - AND would be slow - at least the first time (i.e., I dont like this solution at all) - you can do a count(*) in the very first "page" and then cache that info. For each page you would always reexecute the query.
PS: I have a whole section on this topic in my book
PS 2: Before you think of scrollable result sets - I would say, a big NO. Not in Oracle, at least. Easist to reexecute the query and get the correct set of rows using the correct order by clause each time the user presses "Next" in Oracle. I cover scrollable result sets and why they are not much practical use when coding in Oracle, in general, in my book.

Originally posted by Bill Johnston:
I wonder if Menon might have something on that in his new book?

I am afraid not This is kind of specific requirement. The only way I can think of is to use JMS like Mark suggested. The trigger would result in a JMS message to the application. (I am no JMS expert, btw)

May I ask what the real business requirement is in this case?

Originally posted by George Stoianov:
As there is no method... that I know of in java to return the record count and looping through all recs to get a count is not very efficient.

You may create a method that executes a
SELECT COUNT(*) FROM <table_name>;
and returns the result as a java long in java that way you can get counts for any table they will run in the db making them faster and improving the performance of your application.

Please note that in some databases it is advisable to put the primary key in the count function.

Hope this helps,
george

Why would the above improve performance?

In many cases, getting counts of records can be avoided altogether - That is why I asked why the questioner wanted to do it in the first place. Also, the above solution will give you incorrect resuls (at least in case of Oracle.)

The simplest way, IMHO, (if this is a real requirement) is to simply maintain a counter while looping through the result set - assuming you are looping through it anyways (which typically you do.)

Originally posted by kwame Iwegbue:
thanks guys. but thats so odd that there's no clear method for doing this like getColumnCount() ?!

It is not odd at all since without executing the query, even the database has no clue how many rows a query would bring - think about it. By the way, may I ask why you need to know the total number of rows?

Also, make sure that your con variable is local and that you always close each Connection. Do not leave Connection objects hanging around like this.

And close the connection in a "finally" clause once you are done.

Originally posted by Ilja Preuss:
Well, as an "Agilist", I was actually talking about the automated kind of tests that you run by the press of a button after every little change to the system.

Sure, You can still write such tests. In fact putting code in Pl/sql (or Transact SQL) would make it easier - you can write separate tests for stored procedures and Java layers.

Originally posted by Ilja Preuss:
Anyway, all I wanted to say is that "you payed for the db, so you should use it's feature to the maximum" sounded overly simplistic to me - even harmful if taken literally. If after careful consideration of *all* the influences on a project you decide to put more code into the DB, more power to you.

"over simplistic" - is in the eyes of the beholder. To me doing things that DB can handle best within DB sounds like common sense. To exploit and fully use the capabilities of a very powerful tool seems the right thing to do. Especially if it makes your application that much more functional and scalable.