Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 13 total |
Spee problem in Query |
Sat, Mar 1 2008 11:47 AM | Permanent Link |
Uli Becker | 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 |
Sun, Mar 2 2008 6:05 AM | Permanent Link |
"Harry de Boer" | 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 > |
Sun, Mar 2 2008 6:47 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 3 2008 7:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 3 2008 8:06 AM | Permanent Link |
Uli Becker | <<
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. |
Mon, Mar 3 2008 9:46 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 3 2008 9:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 3 2008 10:30 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 3 2008 11:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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? |
Mon, Mar 3 2008 4:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |