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.

Phil


Can you post the query plan.

Roy Lambert

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
================================================================================

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

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

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.

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