All 3 entries tagged Spring

View all 45 entries tagged Spring on Warwick Blogs | View entries tagged Spring at Technorati | There are no images tagged Spring on this blog

October 06, 2005

Bulk deleting bad data

I had to clear up some old bad data that was left over from a bit of bad code. Unfortunately the bad data didn't rear its ugly head until recently and a lot of data had built up. It was also very hard to detect the bad data because of many places it could be referenced from…only if it had no references to it from any of 7 places would it need to be deleted.

This means doing a really horrible query either like this:

select id from atable where
id not in (select id from anothertable)
and
id not in (select id from yetanothertable)
and
id not in (select id from moretables)
.....
.....

This is very, very, very slow.

A more efficient way of doing this is this:

select id from atable a where
not exists (select id from anothertable b where a.id = b.id)
and
not exists (select id from yetanothertable c where a.id = c.id)
and
not exists (select id from moretables d where a.id = d.id)
.....
.....

However, when you are dealing with potentially 100,000's of rows it is still quite slow…but it does get there. The next problem is actually deleting the data once you've managed to select it. As a little test I thought I'd try and delete the whole lot, but that just didn't work…too slow. Even if I did have the patience to leave it running for hours, I couldn't let it lock up the database like that for that long.

So, the only solution was to do it in batches. I wrote a quick java program that would iterate through an do the deletes in small batches of a 100 or so at a time. My first mistake was trying to reuse some Spring/Hibernate code I already had instead of going straight to old school and using JDBC.

Although in theory you can get a Connection object from the hibernate Session, via session.connection(), it really is NOT the same as just getting a good old fashioned JDBC connection. The deletes were taking absolutely ages, so I profiled it and noticed that hibernate was still trying to do some of its funky stuff in the background, really slowing things down.

Plan B (or is it D by now?). Spring comes with a handy little JdbcTemplate which lets you do real JDBC but without a lot of the exception and connection/statement/resultset closing pains. Finally…it worked.

So, lesson of the day:

  • not exists type queries are faster than not in queries
  • Bulk deletes can be verrrrrrry slow
  • Batching deletes is better, but with real JDBC not hibernate SQL calls

October 04, 2005

Hibernate Statistics and Spring JMX intregration

Writing about Spring JMX integration from Secret Plans and Clever Tricks

Chris wrote about this a while back and I dutifully investigated and started using this beautifully simple bit of configuration to get hibernate statistics showing up in the JMX console for a couple of web applications. This all seemed fine and well for a while and I never really looked at it further.

However, we recently had some performance problems with BlogBuilder and I was left scratching my head. After a bit of investigation, it looked like the hibernate statistics were turned on when I thought they were off. This shouldn't really matter, but it turns out it was logging and keeping every single SQL statement that hibernate had run since the last application restart! It shows all this SQL in the JMX console, but my browser blew up trying to render the page because it was trying to dump out 100,000's of lines of SQL to the page :(

If it really was keeping a log like this it could well be the cause of a performance problem, so I turned it off and I think so far the performance has been better…I'll know for sure tonight.

So…word of warning.

Only turn on the hibernate statistics if you really need them


September 02, 2005

External Spring configuration

I recently had to generalise some configuration I had for Single Sign On (SSO). Some configuration which I didn't think was going to change on a per server/deploy basis, does indeed need to change. Generally we don't like to do a different build per different server so we try to put settings that will change on a per server basis into external configuration.

Traditionally this is done via system properties (System.setProperty("sso.someproperty","true");) which we set in the properties-service.xml file in the JBoss deploy directory. These are really handy as they are dynamically reloaded every time you change them so you can quickly change something at run time. We use this for hot changing where web services point at for instance in case one breaks.

However, there are sometimes bigger configuration changes that you might want to make which are not really just single strings. Not only that, the system properties don't really fit in very well with a Spring way of thinking.

Spring offers two possibilities. From their documentation they talk about the PropertyPlaceholderConfigurer but that just puts individual string properties in an external properties file again.

I decided just to split out the parts of my XML bean configuration that are per server specific into separate file. You can then just dump a smaller configuration file somewhere in your classpath (such as the JBoss conf directory) that is per server specific. In your web.xml you can reference your configuration like this:

<context-param>
   <param-name>contextConfigLocation</param-name>
   <param-value>/WEB-INF/main-spring-beans.xml,classpath:deploy-specific-beans.xml</param-value>
</context-param>

And there you go, you have a nice Spring XML configuration to put your server specific configuration in, without a separate build.


November 2022

Mo Tu We Th Fr Sa Su
Oct |  Today  |
   1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30            

Tags

Search this blog

Most recent comments

  • One thing that was glossed over is that if you use Spring, there is a filter you can put in your XML… by Mathew Mannion on this entry
  • You are my hero. by Mathew Mannion on this entry
  • And may all your chickens come home to roost – in a nice fluffy organic, non–supermarket farmed kind… by Julie Moreton on this entry
  • Good luck I hope that you enjoy the new job! by on this entry
  • Good luck Kieran. :) by on this entry

Galleries

Not signed in
Sign in

Powered by BlogBuilder
© MMXXII