Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Do you think this SQL Query be made faster? |
Mon, Aug 20 2007 10:28 PM | Permanent Link |
P. Read | Hi All,
I have tried everything to get this query to run faster. I know it's doing quite a complex process, but I thought there might be a way to make the query time faster. Basically the query produces a result which I produce my business by SUBURB report. SELECT UPPER(suburb) AS ReportField, '' AS Description, SUM(CAST(history.dep_time AS DATE) - CAST(history.ar_time AS DATE)) AS TotalNights, SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) - CAST(history.ar_time AS DATE)) AS total_acc_rev, SUM(journal_history.trans_total) AS TransTotal FROM history, journal_history INNER JOIN department ON journal_history.trans_department = department.department_code WHERE CAST(journal_history.trans_date AS DATE) >= '2006-03-09' AND CAST(journal_history.trans_date AS DATE) <= '2006-03-12' AND journal_history.trans_total > 0 AND history.res_num = journal_history.res_num AND department.accom = True GROUP BY ReportField ORDER BY ReportField Thanks, Phil. |
Tue, Aug 21 2007 2:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
Can you post the query plan. Roy Lambert |
Tue, Aug 21 2007 6:51 AM | Permanent Link |
P.Read | Hi Roy,
Thanks for the reply... Here's the PLAN ================================================================================ SQL statement (Executed with 4.25 Build 5) ================================================================================ SELECT UPPER(suburb) AS ReportField, '' AS Description, SUM(CAST(history.dep_time AS DATE) - CAST(history.ar_time AS DATE)) AS TotalNights, SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) - CAST(history.ar_time AS DATE)) AS total_acc_rev, SUM(journal_history.trans_total) AS TransTotal FROM history, journal_history INNER JOIN department ON journal_history.trans_department = department.department_code WHERE CAST(journal_history.trans_date AS DATE) >= '2007-03-09' AND CAST(journal_history.trans_date AS DATE) <= '2007-06-12' AND journal_history.trans_total > 0 AND history.res_num = journal_history.res_num AND department.accom = True GROUP BY ReportField ORDER BY ReportField Tables Involved --------------- history (history) table opened shared, has 2156 rows journal_history (journal_history) table opened shared, has 11556 rows department (department) table opened shared, has 49 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: ReportField Result set will be ordered by the following column(s) using a case-sensitive temporary index: ReportField ASC WHERE Clause Execution ---------------------- Join Ordering ------------- The driver table is the history table (history) The history table (history) is joined to the journal_history table (journal_history) with the INNER JOIN expression: history.res_num = journal_history.res_num The journal_history table (journal_history) is joined to the department table (department) with the INNER JOIN expression: journal_history.trans_department = department.department_code Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order Optimized Join Ordering ----------------------- The driver table is the department table (department) The department table (department) is joined to the journal_history table (journal_history) with the INNER JOIN expression: department.department_code = journal_history.trans_department The journal_history table (journal_history) is joined to the history table (history) with the INNER JOIN expression: journal_history.res_num = history.res_num The expression: CAST(journal_history.trans_date,DATE) >= '2007-03-09' AND CAST(journal_history.trans_date,DATE) <= '2007-06-12' AND journal_history.trans_total > 0 has been rewritten and is UN-OPTIMIZED, covers 11556 rows or index keys, costs 7210944 bytes, and will be applied to the journal_history table (journal_history) before any joins The expression: department.accom = True is UN-OPTIMIZED, covers 49 rows or index keys, costs 25480 bytes, and will be applied to the department table (department) before any joins Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: department.department_code = journal_history.trans_department is UN-OPTIMIZED The expression: journal_history.res_num = history.res_num is UN-OPTIMIZED ================================================================================ >>>>> 285 rows affected in 12.297 seconds ================================================================================ |
Tue, Aug 21 2007 8:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | P.Read
Unless I'm misreading it you're missing indices. The recommendation is that all columns that are used in WHERE, JOIN or ORDER BY clauses should be indexed. This The expression: department.department_code = journal_history.trans_department is UN-OPTIMIZED The expression: journal_history.res_num = history.res_num is UN-OPTIMIZED Say's they aren't. You might also find that translating AND history.res_num = journal_history.res_num into a JOIN would help speed things up. I'm not an sql guru and operate by trial and error so without your tables that's the best I can do. Roy Lambert |
Tue, Aug 21 2007 3:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Phil,
<< Thanks for the reply... Here's the PLAN >> Roy is correct - you need, at the very least, indexes to optimize the joins. The WHERE clause is optional, but we usually recommend at least one of the conditions be optimized also. Also, you should definitely consider moving the: history.res_num = journal_history.res_num condition into the FROM clause as a JOIN. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Aug 22 2007 6:43 AM | Permanent Link |
P.Read | Thanks Roy & Tim for your replies...
Those suggestions really helped. Went from 12.5 seconds to 0.12 seconds. I apologise for my lack of knowledge on this but can I ask a quick question about the INDEXES which speeded up the process so much in the query... What kind of rule should I when assigning indexes? Should I crete them at the time of creating the tables say in the 'Database System Utility' OR temporarily in a query. I'm also wondering why RecordID or RecordHash wouldn't have sufficed as the index. Also when I create a table and say make a field custID and make it the index, should I remove the RecordID / RecordHash indexes OR leave them in, what the rule there? Thanks once again for your valuable time guys, Phil. |
Wed, Aug 22 2007 9:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
>Those suggestions really helped. Went from 12.5 seconds to 0.12 seconds. Neat >I apologise for my lack of knowledge on this but can I ask a quick question about the >INDEXES which speeded up the process so much in the query... > >What kind of rule should I when assigning indexes? Should I crete them at the time of >creating the tables say in the 'Database System Utility' OR temporarily in a query. I'm >also wondering why RecordID or RecordHash wouldn't have sufficed as the index. > >Also when I create a table and say make a field custID and make it the index, should I >remove the RecordID / RecordHash indexes OR leave them in, what the rule there? Ideally don't use RecordID or RecordHash for indices. 1. Create a primary index based on data in the table which uniquely identifies the row in the table 2. Create an index for ANY column you want to a) use in a JOIN or WHERE statement b) use in a .Locate or .FindKey c) use in an ORDER BY or d) to affect the display sequence of a table e) used in a filter (I think that's all inclusive) 3. Make sure you have case sensitive where needed 4. Create extra indices where you want both ascending and descending ORDER / sorts 5. Remember where you have a compound index ONLY the first field is used for JOIN's WHERE's and filters but ALL columns are used in an ORDER BY (as long as its the same as the compound index) Creating the indices and leaving them there makes it easier when running queries or filters but takes a bit longer to update a record. I'd recommend create and leave unless you run out of disk space Roy Lambert |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |