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]