Re: Very slow

[Jack Klebanoff <kl...@sbcglobal.net>]

Sunitha Kambhampati wrote:

> Edson Carlos Ericksson Richter wrote:
>
>> I've run with derby.language.logQueryPlan=true and result is that I 
>> have table scan for only one table, that has actually one record.
>
>
> I am curious as to why a table scan is being picked.  Does this table 
> have indexes that can be used  for the query in question ?  Also what 
> isolation level is this query running at.
>
> Sunitha.
>
If the table only has one row then a table scan is faster than an index 
scan.

Jack

Re: Very slow

[Jack Klebanoff <kl...@sbcglobal.net>]

Edson Carlos Ericksson Richter wrote:

> Thanks for fast response.
>
> I think the reason Derby didn't used any index  is because since this 
> table, for my tests purposes has only one record, I've not created any 
> index at all.
> I suspect that one record table will be in cache for a long time 
> (since it's a comment making queries over this table in my code). At 
> all, this one record is having "cost 13", so it's not the problem.
>
> My problem appear be in the sub-select. Each sub-select make queries 
> over a 33000 records table. I think I forgot to make a better index 
> for it. After indexing, performance got better (not perfect, but it's 
> usable - I think the large amout of log being generated is the reason 
> for the little delay in responses). The cost for sub-select query 
> dropped down from near 13000 to 19,85.
>
> I other databases (like MaxDB and MS SQL, this table was kept in 
> memory, because he is considered "small"). That's the reason I never 
> made any indexing over it.
>
> I'll try links sent at other mails, so I could discover if Tomcat 
> integration has it's secrets too.
>
> Thank you all for comments,
>
> Edson Richter
>
You might also try increasing the size of the Derby page cache. The 
default page cache size is 1000 pages at 4K bytes per page. The default 
page cache may not be large enough to hold the 33000 row table in 
addition to indexes on your other tables. You can set the page cache 
size using the derby.storage.pageCacheSize property. Normally you set it 
in a file named derby.properties located in the derby home directory. 
For example, to set the page cache size to10000 pages (40M bytes) add 
the line:

derby.storage.pageCacheSize=10000

to your derby.properties file.

See http://incubator.apache.org/derby/docs/tuning/ for more details 
about setting this and other Derby properties and for a larger 
discussion of Derby tuning.

Jack

Re: Very slow

[Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>]

Thanks for fast response.

I think the reason Derby didn't used any index  is because since this 
table, for my tests purposes has only one record, I've not created any 
index at all.
I suspect that one record table will be in cache for a long time (since 
it's a comment making queries over this table in my code). At all, this 
one record is having "cost 13", so it's not the problem.

My problem appear be in the sub-select. Each sub-select make queries 
over a 33000 records table. I think I forgot to make a better index for 
it. After indexing, performance got better (not perfect, but it's usable 
- I think the large amout of log being generated is the reason for the 
little delay in responses). The cost for sub-select query dropped down 
from near 13000 to 19,85.

I other databases (like MaxDB and MS SQL, this table was kept in memory, 
because he is considered "small"). That's the reason I never made any 
indexing over it.

I'll try links sent at other mails, so I could discover if Tomcat 
integration has it's secrets too.

Thank you all for comments,

Edson Richter

Re: Very slow

[Mike Matrigali <mi...@sbcglobal.net>]

Also, at least for serializable isolation level table scan will result
in a table level lock, where an index scan will only get a row lock.

Sunitha Kambhampati wrote:
> Jack Klebanoff wrote:
> 
>> Sunitha Kambhampati wrote:
>>
>>> Edson Carlos Ericksson Richter wrote:
>>>
>>>> I've run with derby.language.logQueryPlan=true and result is that I 
>>>> have table scan for only one table, that has actually one record.
>>>
>>>
>>>
>>>
>>> I am curious as to why a table scan is being picked.  Does this table 
>>> have indexes that can be used  for the query in question ?  Also what 
>>> isolation level is this query running at.
>>>
>>> Sunitha.
>>>
>> If the table only has one row then a table scan is faster than an 
>> index scan.
>>
>>
> True for one row case.
> 
> But lets say the table is modified to have more rows and then it 
> probably makes sense to have an index scan ,  but you will be stuck with 
> a tablescan for this query till the query gets recompiled as  a result 
> of a stale plan check. (right ?).  So it still seems it would be better 
> for the optimizer to pick an index scan when it can instead of table 
> scan even for a single row.
> 
> Sunitha.
> 
> 

Re: Very slow

[Sunitha Kambhampati <ks...@gmail.com>]

Jack Klebanoff wrote:

> Sunitha Kambhampati wrote:
>
>> Edson Carlos Ericksson Richter wrote:
>>
>>> I've run with derby.language.logQueryPlan=true and result is that I 
>>> have table scan for only one table, that has actually one record.
>>
>>
>>
>> I am curious as to why a table scan is being picked.  Does this table 
>> have indexes that can be used  for the query in question ?  Also what 
>> isolation level is this query running at.
>>
>> Sunitha.
>>
> If the table only has one row then a table scan is faster than an 
> index scan.
>
>
True for one row case.

But lets say the table is modified to have more rows and then it 
probably makes sense to have an index scan ,  but you will be stuck with 
a tablescan for this query till the query gets recompiled as  a result 
of a stale plan check. (right ?).  So it still seems it would be better 
for the optimizer to pick an index scan when it can instead of table 
scan even for a single row.

Sunitha.