I'd like to execute this query:

select * from Invoices
where CustomerID in
(select CustomerID from Customers where Name = 'Muller');

Execution time 4.5 sec (too long, I think).

When I split the query in:

1. select CustomerID from Customer where Name = 'Muller'
2. select * from invoices where CustomerID in (5,6,1575,2329,3631,4011,4070,5206,5615)

the execution times are 0.6 and 0.01 seconds.

Why does the first query need so long? Is there a way to improve that?

Indexes are set properly, 4500 records in customers, 9500 records in invoices.

Thanks. Uli

Uli,

I don't know the reason for the lack of speed, but couldn't you use:

select invoices.* from invoices
join customers on invoice.customerID = customers.customerID
where customers.name = 'Muller'

Regards, Harry

"Uli Becker" <test@test.com> schreef in bericht
news:A2211372-B2BB-4A9A-800E-FC849746783F@news.elevatesoft.com...
> I'd like to execute this query:
>
> select * from Invoices
> where CustomerID in
> (select CustomerID from Customers where Name = 'Muller');
>
> Execution time 4.5 sec (too long, I think).
>
> When I split the query in:
>
> 1. select CustomerID from Customer where Name = 'Muller'
> 2. select * from invoices where CustomerID in
(5,6,1575,2329,3631,4011,4070,5206,5615)
>
> the execution times are 0.6 and 0.01 seconds.
>
> Why does the first query need so long? Is there a way to improve that?
>
> Indexes are set properly, 4500 records in customers, 9500 records in
invoices.
>
> Thanks. Uli
>

Harry,

<<
 select invoices.* from invoices
 join customers on invoice.customerID = customers.customerID
 where customers.name = 'Muller'
>>

Wow, that only needs 0.5 seconds. Thanks a lot; I'll have to remember this way to query.

Regards Uli

Uli,

<< I'd like to execute this query:

select * from Invoices
where CustomerID in
(select CustomerID from Customers where Name = 'Muller');

Execution time 4.5 sec (too long, I think). >>

Is this the same as the previous issue that you reported ?  If so, then I'm
still waiting on the password for the table(s).

--
Tim Young
Elevate Software
www.elevatesoft.com

<<
Is this the same as the previous issue that you reported ?  If so, then I'm
still waiting on the password for the table(s).
>>

Yes, it is one of the tables. Sorry, I forgot the table is encrypted (did you send me a
message?).
The password is <toskana> for the DBISAM table. I migrated the table and didn't change the
password, so it should be <elevatesoft> now.

Thanks. Uli.

Addendum:

<<
Is this the same as the previous issue that you reported ?  If so, then I'm
still waiting on the password for the table(s).
>>

After removing the encryption from both tables the execution time is now 0.8 sec. (Before
4.5 sec).
So it seems like the encryption slows down queries a lot.

Regards Uli

Uli,

<< Yes, it is one of the tables. Sorry, I forgot the table is encrypted (did
you send me a message?). The password is <toskana> for the DBISAM table. I
migrated the table and didn't change the password, so it should be
<elevatesoft> now. >>

In the future, I wouldn't post passwords here, just to be safe.

Here's the issues with your speed problems:

1) You're comparing apples and oranges.  DBISAM 3.x's encryption was not
strong encryption, whereas EDB's (and DBISAM 4's) is.  Therefore, you're
going to have to either not using encryption or be more careful about how
your queries are structured so that they use available indexes for
optimizations.

2) Your query is not optimized.  You've got a case-insensitive index
defined, but your query is not using it because the name column is defined
with a case-sensitive collation (DEU, with no _CI suffix):

select * from MyTable where Name = 'Young'

The query should be:

select * from MyTable where Name COLLATE DEU_CI = 'Young';

which will force the collation to be DEU_CI (German, case-insensitive) for
the purposes of the query and the index selection in the optimizer.

3) Query plans are very useful, so use them as much as possible.  For
example, the query plan for your original query clearly stated that EDB was
using a row scan to execute the query, resulting in every row needing to be
read.  This information is very valuable in determining where there are
areas that need improvement in terms of optimizations.

4) Read this section of the manual, which covers how the index selection
occurs for optimization:

http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=edb1sql&category=0&topic=16

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< In the future, I wouldn't post passwords here, just to be safe. >>

Sorry about that. I only thought of my own unimportant pw.

<< 2) Your query is not optimized.  You've got a case-insensitive index
defined, but your query is not using it because the name column is defined
with a case-sensitive collation (DEU, with no _CI suffix)
>>

Interesting: I simply didn't know that I had to use COLLATE DEU_CI in the query.

<< 3) Query plans are very useful, so use them as much as possible. >>

OK, I'll do so.

Thank you very much for your detailed explanation.

After all these improvements making my application really fast, I have to tell you that
(after a painful week of learning by doing) I really like ElevateDB and am looking forward
to use it in all my apps.

Appreciate Uli

Tim


>select * from MyTable where Name COLLATE DEU_CI = 'Young';

Sigh - I thought I'd finished work on my query generator and now I have another check and bit to add - sigh

Roy Lambert

ps is the section in the manual on optimisations new?

Uli,

<< Sorry about that. I only thought of my own unimportant pw. >>

It's no problem.  Just trying to keep everyone's data safe.

<< Interesting: I simply didn't know that I had to use COLLATE DEU_CI in the
query. >>

In this case, yes.  The reason is that the column is defined with the
collation of just DEU.  If you defined the column with a collation of
DEU_CI, then:

1) You wouldn't need to specify the collation manually in the WHERE clause
2) No matter how the column was searched, via an index or brute-force via a
row scan, it would also be done as a case-insensitive search.

<< After all these improvements making my application really fast, I have to
tell you that (after a painful week of learning by doing) I really like
ElevateDB and am looking forward to use it in all my apps. >>

That's great to hear.

--
Tim Young
Elevate Software
www.elevatesoft.com