I
Ian C
Please help speed up my query.
I have a simple query called "qry_worksheet" that is used to set the
criteria for a more complex query:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT t_worksheet.*
FROM t_worksheet
WHERE (((t_worksheet.job_week_no)=10));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"job_week_no" is indexed.
The more complex query links together a number of tables to provide
presentable meaningful data to the user:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT qry_worksheet.*, t_list_job_types.description AS
job_type_description, t_customer.business_name AS supplier_name,
t_customer.business_short_name AS supplier_nameSN, t_customer.account_number
AS supplier_ac_no, t_customer_1.business_name AS processor_name,
t_customer_1.business_short_name AS processor_nameSN,
t_customer_1.account_number AS processor_ac_no, t_customer_1.nominal_account
AS processor_nominal_ac, t_customer_2.business_name AS haulier_name,
t_customer_2.business_short_name AS haulier_nameSN,
t_customer_2.account_number AS haulier_ac_no,
t_list_grade_type.grade_type_description,
t_list_grade_type.grade_type_full_name, t_users.user_initials,
t_users.user_name, t_customer_locations.location_short_name AS
collection_point_short, t_customer_locations_1.location_short_name AS
delivery_point_short, t_list_transport_type.transport_type_description,
t_list_price_basis_1.price_basis AS transport_price_basis_desc,
t_list_price_basis.price_basis AS sell_price_basis_description,
t_list_price_basis_2.price_basis AS buy_price_basis_description,
t_list_processing_type.processing_type_description, t_customer.business_name,
t_list_grade_type.annex_Basel, t_list_grade_type.annex_EC,
t_list_grade_type.annex_National, t_list_currency.currency_symbol AS
buy_price_currency_symbol, t_list_currency_1.currency_symbol AS
sell_price_currency_symbol, t_list_currency_2.currency_symbol AS
transport_price_currency_symbol, t_customer_1.VAT_rate AS processor_VAT_rate,
t_customer_1.nominal_account AS processor_nominal_account,
t_list_status.status_description, t_list_reject_types.reject_description,
t_list_status.include_in_documentation, t_list_status.Include_in_invoicing,
t_list_status.include_in_worksheet, t_list_status.include_in_stock,
t_list_status.include_in_alloocation, t_users_1.user_initials AS
manager_initials
FROM ((((((((((((((((((qry_worksheet LEFT JOIN t_users ON
qry_worksheet.job_owner = t_users.ref_no) LEFT JOIN t_list_grade_type ON
qry_worksheet.processing_grade = t_list_grade_type.grade_type_id) LEFT JOIN
t_customer_locations ON qry_worksheet.collection_point =
t_customer_locations.location_id) LEFT JOIN t_customer_locations AS
t_customer_locations_1 ON qry_worksheet.delivery_point =
t_customer_locations_1.location_id) LEFT JOIN t_list_transport_type ON
qry_worksheet.transport_type = t_list_transport_type.transport_type_id) LEFT
JOIN t_list_price_basis ON qry_worksheet.sell_price_basis =
t_list_price_basis.price_basis_id) LEFT JOIN t_list_processing_type ON
qry_worksheet.processing_type = t_list_processing_type.processing_type_id)
LEFT JOIN t_list_price_basis AS t_list_price_basis_1 ON
qry_worksheet.transport_price_basis = t_list_price_basis_1.price_basis_id)
LEFT JOIN t_list_currency ON qry_worksheet.buy_price_currency =
t_list_currency.currency_id) LEFT JOIN t_list_currency AS t_list_currency_1
ON qry_worksheet.sell_price_currency = t_list_currency_1.currency_id) LEFT
JOIN t_list_currency AS t_list_currency_2 ON
qry_worksheet.transport_price_currency = t_list_currency_2.currency_id) LEFT
JOIN t_list_price_basis AS t_list_price_basis_2 ON
qry_worksheet.buy_price_basis = t_list_price_basis_2.price_basis_id) LEFT
JOIN t_list_status ON qry_worksheet.status = t_list_status.status_id) LEFT
JOIN t_list_reject_types ON qry_worksheet.reject_reason =
t_list_reject_types.reject_id) LEFT JOIN t_list_job_types ON
qry_worksheet.job_type = t_list_job_types.job_type_ID) LEFT JOIN t_users AS
t_users_1 ON qry_worksheet.job_owner_manager = t_users_1.ref_no) LEFT JOIN
t_customer ON qry_worksheet.supplier_customer_ref = t_customer.ref_no) LEFT
JOIN t_customer AS t_customer_1 ON qry_worksheet.processor_customer_ref =
t_customer_1.ref_no) LEFT JOIN t_customer AS t_customer_2 ON
qry_worksheet.haulier_customer_ref = t_customer_2.ref_no;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All joins are indexed. When I run the more complex query using the criteria
contained in the simple query above it returns 250 records within 1 second,
which is roughly the same as running the simple query "qry_worksheet" by
iself.
However when I change the criteria in the simple query "qry_worksheet" to:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT t_worksheet.*
FROM t_worksheet
WHERE (((t_worksheet.job_owner)=443));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"job_owner" is indexed
the simple query runs in about the same time and returns 2 records but the
more complex query takes about 15 to 20 minutes to run.
Can anybody help ......Please
I have a simple query called "qry_worksheet" that is used to set the
criteria for a more complex query:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT t_worksheet.*
FROM t_worksheet
WHERE (((t_worksheet.job_week_no)=10));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"job_week_no" is indexed.
The more complex query links together a number of tables to provide
presentable meaningful data to the user:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT qry_worksheet.*, t_list_job_types.description AS
job_type_description, t_customer.business_name AS supplier_name,
t_customer.business_short_name AS supplier_nameSN, t_customer.account_number
AS supplier_ac_no, t_customer_1.business_name AS processor_name,
t_customer_1.business_short_name AS processor_nameSN,
t_customer_1.account_number AS processor_ac_no, t_customer_1.nominal_account
AS processor_nominal_ac, t_customer_2.business_name AS haulier_name,
t_customer_2.business_short_name AS haulier_nameSN,
t_customer_2.account_number AS haulier_ac_no,
t_list_grade_type.grade_type_description,
t_list_grade_type.grade_type_full_name, t_users.user_initials,
t_users.user_name, t_customer_locations.location_short_name AS
collection_point_short, t_customer_locations_1.location_short_name AS
delivery_point_short, t_list_transport_type.transport_type_description,
t_list_price_basis_1.price_basis AS transport_price_basis_desc,
t_list_price_basis.price_basis AS sell_price_basis_description,
t_list_price_basis_2.price_basis AS buy_price_basis_description,
t_list_processing_type.processing_type_description, t_customer.business_name,
t_list_grade_type.annex_Basel, t_list_grade_type.annex_EC,
t_list_grade_type.annex_National, t_list_currency.currency_symbol AS
buy_price_currency_symbol, t_list_currency_1.currency_symbol AS
sell_price_currency_symbol, t_list_currency_2.currency_symbol AS
transport_price_currency_symbol, t_customer_1.VAT_rate AS processor_VAT_rate,
t_customer_1.nominal_account AS processor_nominal_account,
t_list_status.status_description, t_list_reject_types.reject_description,
t_list_status.include_in_documentation, t_list_status.Include_in_invoicing,
t_list_status.include_in_worksheet, t_list_status.include_in_stock,
t_list_status.include_in_alloocation, t_users_1.user_initials AS
manager_initials
FROM ((((((((((((((((((qry_worksheet LEFT JOIN t_users ON
qry_worksheet.job_owner = t_users.ref_no) LEFT JOIN t_list_grade_type ON
qry_worksheet.processing_grade = t_list_grade_type.grade_type_id) LEFT JOIN
t_customer_locations ON qry_worksheet.collection_point =
t_customer_locations.location_id) LEFT JOIN t_customer_locations AS
t_customer_locations_1 ON qry_worksheet.delivery_point =
t_customer_locations_1.location_id) LEFT JOIN t_list_transport_type ON
qry_worksheet.transport_type = t_list_transport_type.transport_type_id) LEFT
JOIN t_list_price_basis ON qry_worksheet.sell_price_basis =
t_list_price_basis.price_basis_id) LEFT JOIN t_list_processing_type ON
qry_worksheet.processing_type = t_list_processing_type.processing_type_id)
LEFT JOIN t_list_price_basis AS t_list_price_basis_1 ON
qry_worksheet.transport_price_basis = t_list_price_basis_1.price_basis_id)
LEFT JOIN t_list_currency ON qry_worksheet.buy_price_currency =
t_list_currency.currency_id) LEFT JOIN t_list_currency AS t_list_currency_1
ON qry_worksheet.sell_price_currency = t_list_currency_1.currency_id) LEFT
JOIN t_list_currency AS t_list_currency_2 ON
qry_worksheet.transport_price_currency = t_list_currency_2.currency_id) LEFT
JOIN t_list_price_basis AS t_list_price_basis_2 ON
qry_worksheet.buy_price_basis = t_list_price_basis_2.price_basis_id) LEFT
JOIN t_list_status ON qry_worksheet.status = t_list_status.status_id) LEFT
JOIN t_list_reject_types ON qry_worksheet.reject_reason =
t_list_reject_types.reject_id) LEFT JOIN t_list_job_types ON
qry_worksheet.job_type = t_list_job_types.job_type_ID) LEFT JOIN t_users AS
t_users_1 ON qry_worksheet.job_owner_manager = t_users_1.ref_no) LEFT JOIN
t_customer ON qry_worksheet.supplier_customer_ref = t_customer.ref_no) LEFT
JOIN t_customer AS t_customer_1 ON qry_worksheet.processor_customer_ref =
t_customer_1.ref_no) LEFT JOIN t_customer AS t_customer_2 ON
qry_worksheet.haulier_customer_ref = t_customer_2.ref_no;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All joins are indexed. When I run the more complex query using the criteria
contained in the simple query above it returns 250 records within 1 second,
which is roughly the same as running the simple query "qry_worksheet" by
iself.
However when I change the criteria in the simple query "qry_worksheet" to:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT t_worksheet.*
FROM t_worksheet
WHERE (((t_worksheet.job_owner)=443));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"job_owner" is indexed
the simple query runs in about the same time and returns 2 records but the
more complex query takes about 15 to 20 minutes to run.
Can anybody help ......Please