RE: Very slow metadata extraction?

Re select: 
o Command started spewing data back in maybe 3 seconds.
o Total rows returned: 49447
o Execution time on the client host: 20.5 minutes. This used a command-line
SQL processor that dumps results to System.out. I redirected the output to
file to avoid the time on the client side required to scroll the command
window. CPU on the client side was around 50%. CPU on the database host was
loafing - under 10%.
o Execution time on the DB host: 12 seconds. This used the Mckoi JDBC query
tool. It returned the same number of rows, just a LOT faster.

BTW, I've rewritten the code that cause the original message. I now do one
call to getColumns() and filter out the desired tables in the code, rather
than doing a getColumns() call for each of the desired tables. Total
execution time went down from 5 hours (est) to 3 minutes (measured).

-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
Downer
Sent: Sunday, November 28, 2004 2:02 AM
To: [email protected]
Subject: Re: Very slow metadata extraction?

I will look into this ASAP.  It looks likely that a bottleneck is being 
reached with the meta data procedure.  About how many columns in total 
are in the database?  Mckoi needs to dynamically construct the columns 
table in memory when a call to 'getColumns' is made, and this may be 
causing the poor performance.

Does the query 'select * from SYS_INFO.sUSRTableColumns' also perform 
poorly in your database?

Toby.

R.W. Shore wrote:
> I have a database configured in server (not embedded) mode. Mckoi starts 
> as a service. I haven't messed with the db.config file for this 
> database, except to point the db and log paths to where I want the files.
> 
> The particular schema I'm using contains about 1,000 tables, 3,000 
> procedures, and 3,000 triggers (one insert trigger per table, one update 
> trigger/table, one delete trigger/table). The Mckoi server is running 
> under Java 5.0 on a Win XP Pro (SP2) host; hardware is an AMD 64 
> processor. When the schema had only 500 tables and no triggers or 
> procedures, I could extract metadata for the tables at about 3 
> minutes/100 tables. Now, however, it's taking about 60 minutes/100 
> tables. The CPU on the host machine is pegged, with the Mckoi service 
> taking all available CPU cycles. There are no other users accessing the 
> database. I'm doing a metadata.getColumns() call and a 
> metadata.getPrimaryKeys() call for each table individually. (Yes, I 
> understand that I can and maybe should do a single getColumns() call.) 
> The service definition permits Mckoi to use as much as 512M of RAM. The 
> status display shows that the service process that's eating up the CPU 
> has less than 200M of either physical or virtual memory, so I don't 
> think I'm in a GC thrash.
> 
> Could this time increase be due to the lack of an index on the system 
> tables that Mckoi uses to build the result set for getColumns() or 
> getPrimaryKeys()? If not, any other suggestions on what's going on and 
> perhaps what to do to get a reasonable response?


---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]




---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]