RE: <SPAM>Re: Very slow metadata extraction?

Specific, non-wildcarded strings for catalog, schema, and table. "%" for
column names -- that's what would have taken 5 hours or so.

Yes, the test indicates that it is much faster to minimize the number of
getColumns() calls and write code to compare the returns against the list of
desired tables, rather than to do individual getColumns() calls for each
(known) table with the column name (only) wildcarded. You'll recall that I
have something like 1500 tables defined in this database; I'm trying to get
the columns for about a third of them. So, the tradeoff here is between 500
getColumns() calls with specific catalog, schema, and table [5 hours] vs a
single getColumns() call with wildcarded catalog, schema, and table and with
custom code that pulls out the columns for the 500 or so tables of interest
[3 minutes].

-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
Downer
Sent: Monday, December 06, 2004 6:21 PM
To: [email protected]
Subject: <SPAM>Re: Very slow metadata extraction?

What were the filters you were using when it took the estimated 5 hours?

If I'm interpreting your test correctly, the SELECT query on 
sUSRTableColumns is very fast but retrieving the data is slow.  It is 
better to filter getColumns with custom code than to use the inbuilt 
filtering.

I will need to correct this, but please do tell me what the filters were 
that took a long time to process so I can create a test case.

Thanks,
Toby.


R.W. Shore wrote:

> I may have miscommunicated. The results below are from the 'select * from
> SYS_INFO.sUSRTableColumns' that you wanted me to run. I don't know why it
> took so long on the client - I'm using a wireless net that isn't THAT
slow. 
> 
> To answer your question directly: in the code that I've since rewritten,
the
> getColumns() specified a non-wildcarded catalog, schema, and table, and it
> specified "%" as the column patern.
> 
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
> Downer
> Sent: Monday, November 29, 2004 7:54 PM
> To: [email protected]
> Subject: Re: Very slow metadata extraction?
> 
> What were the filters you were applying to 'getColumns'?  It looks 
> obvious from this information that the filtering is the bottleneck.
> 
> Thanks,
> Toby.
> 
> R.W. Shore wrote:
> 
> 
>>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).



---------------------------------------------------------------
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]