hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....
here's my query:
SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))
UNION
SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
What indexes do you have on the underlying tables?
|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!my 2 cents
|||
A couple of comments on the first query: (NOTE: this may not make any difference...)
The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?
The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?
The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.
Code Snippet
FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)
Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:
Code Snippet
FROM vw_audit_detail_report dJOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
And then you do a UNION with the second query, so it seems that the
AND d.question_section_name <> b.question_section_name
and the entire second query, could also be eliminated ...
|||thanks so much.... that solved my problem...
