sqlobject-discuss

[SQLObject] Enduring long queries outside SQLObject

[Frank B. <fb...@fo...>]

Hallo,

I have to feed my database via a CSV-file from the outside world.
Loading the CSV via Mysql's "load data infile..." query takes a rather
long time and, maybe more importantly, invalidates several SQLObject
instances. This leads to errors in the Webware application I'm
running, where SQLObjects are called, until the Webware AppServer
eventually hangs.  The outside query has a duration of about 10
seconds for 55,000 rows.

Now I'm contemplating strategies against this. I'm not quite sure
where the problems lie, though. The only real "solution" I have is
restarting the Webware AppServer, but I wonder, if I could just as
well "restart" my SQLObjects somehow. 

(And, slightly off-topic here, but I know, many here use Webware: How
do I restart Webware's Appserver in an elegant way from inside
Webware?)

Another solution might be to load the csv into a temporary table and
then run a SQLObject controlled "select into real_table..."

Thanks for any helping insight!

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Enduring long queries outside SQLObject

[Ian B. <ia...@co...>]

On Nov 6, 2003, at 12:44 PM, Frank Barknecht wrote:
> I have to feed my database via a CSV-file from the outside world.
> Loading the CSV via Mysql's "load data infile..." query takes a rather
> long time and, maybe more importantly, invalidates several SQLObject
> instances. This leads to errors in the Webware application I'm
> running, where SQLObjects are called, until the Webware AppServer
> eventually hangs.  The outside query has a duration of about 10
> seconds for 55,000 rows.

Well, you could try locking SQLObject instance access while you're 
loading the CSV file, if there's a concurrency issue.  I think you 
could do this in MySQLConnection, maybe in the _query method (i.e., 
check for a lock and wait until it is released).

> Now I'm contemplating strategies against this. I'm not quite sure
> where the problems lie, though. The only real "solution" I have is
> restarting the Webware AppServer, but I wonder, if I could just as
> well "restart" my SQLObjects somehow.

Using 0.5 you should be able to .expire() the instances.  
DBConnection.Transaction.rollback does this, and you could probably 
separate that code out, maybe make an expireAll method on DBConnection 
instances.  Ultimately that method should also find all sub-connections 
(i.e., transactions), each of which have their own cache and set of 
instances, but that's probably not an issue for you using MySQL.

> (And, slightly off-topic here, but I know, many here use Webware: How
> do I restart Webware's Appserver in an elegant way from inside
> Webware?)

Look at the auto-reloader.  I can't remember off the top of my head how 
it works, but I think it just sets a flag in the Application which 
causes it to restart.  But if you have stalled threads this won't work, 
the only way to restart then is to kill -9.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] Enduring long queries outside SQLObject

[Frank B. <fb...@fo...>]

Hallo,

Ian Bicking hat gesagt: // Ian Bicking wrote:
> Using 0.5 you should be able to .expire() the instances.  
> DBConnection.Transaction.rollback does this, and you could probably 
> separate that code out, maybe make an expireAll method on DBConnection 
> instances.  Ultimately that method should also find all sub-connections 
> (i.e., transactions), each of which have their own cache and set of 
> instances, but that's probably not an issue for you using MySQL.

Thank you for that tip. You're thinking about something as this: 

#####
def expireAll(Caches):
	# subCaches = [(sub, sub.allIDs()) for sub in self.cache.allSubCaches()]
	subCaches = [(sub, sub.allIDs()) for sub in Caches]
	for subCache, ids in subCaches:
		for id in ids:
			inst = subCache.tryGet(id)
			if inst is not None:
				print " -- Expiring %s\n%s" % (inst.id, inst)
				inst.expire()

x = Product(45)
y = Product(103724)
c = Product._connection.cache.allSubCaches()
expireAll(c)
######

I will try that.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Enduring long queries outside SQLObject

[Randall R. <ra...@ra...>]

On Thursday, November 6, 2003, at 01:44 PM, Frank Barknecht wrote:

> Hallo,
> (And, slightly off-topic here, but I know, many here use Webware: How
> do I restart Webware's Appserver in an elegant way from inside
> Webware?)

 From a servlet (or your SitePage):
self.application().server().shouldRestart()

will tell the AppServer to restart.

--
Randall Randall
ra...@ra...