February 2, 2009 at 3:21 am
Hi All
One of the Queries is taking lot of time to execute on one of my prod servers. When at the same time i run the query on my second prod server ( with small db size ) it executes very quickly. Below is my Query :
to proceed on this.
February 2, 2009 at 3:46 am
Please can you post the structure of the view test_result_view?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 4:12 am
have you checked with ram size??
is sql getting req mem??
you can run performance monitor of windows and check whether at time Query get fired,is ram size greater than 500mb,
it should always be greater than 500 mb.
also keep AWE enabled.
you have to change from sp_configure.
February 2, 2009 at 4:34 am
Hi Chris
Below is the code for test_resultt_view :
February 2, 2009 at 4:35 am
sbv
February 2, 2009 at 4:45 am
I think that the problem is absolutely in the qty_tested item, try to run the query without that part, and I think it would run smoothly. If that's the case, try to eliminate the correlated subqueries there. Hopefully I can try later to rewrite your query if nobody else did so by then.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
February 2, 2009 at 4:58 am
Instead of sub queried , even if i reference the view name . The performance is still the same !!!!!
February 2, 2009 at 5:39 am
Thanks...also the view limit_set_view please.
Views of views can be performance hogs. Others have pointed out the correlated subqueries - they may not be much of a problem however combining the two into one may give a modest improvement in performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 6:18 am
Hi Chris : Below is code as required :
February 2, 2009 at 7:45 am
Resolving the tables out of the views results in this:
SELECT it.error_code, COUNT(*) as occurances,
SUM(d.qty_tested) AS qty_tested,
SUM(d.qty_failed) AS qty_failed
FROM [assembly] as a
INNER JOIN tester_configuration as tc ON a.assembly_id = tc.assembly_id
INNER JOIN uut_composite as uc ON tc.configuration_id = uc.tester_config_id
INNER JOIN (
SELECT assembly_number, tester_type, stop_date, tech_mode, log_to_daily_report,
SUM(1) AS qty_tested,
SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) AS qty_failed
FROM [assembly] as a
INNER JOIN tester_configuration as tc ON a.assembly_id = tc.assembly_id
INNER JOIN uut_composite as uc ON tc.configuration_id = uc.tester_config_id
GROUP BY assembly_number, tester_type, stop_date, tech_mode, log_to_daily_report
) d
ON d.assembly_number = a.assembly_number
AND d.tester_type = tc.tester_type
AND d.stop_date = uc.stop_date
AND d.tech_mode = uc.tech_mode
AND d.log_to_daily_report = uc.log_to_daily_report
INNER JOIN individual_test as it ON uc.result_id = it.result_id
LEFT JOIN limit_set as ls ON ls.limit_set_id = tc.limit_set_id AND ls.error_code = it.error_code
INNER JOIN test_limit as tl ON ls.limit_id = tl.limit_id
WHERE a.assembly_number = '57-875'
AND tc.tester_type = 11
AND uc.stop_date >= '1/12/2009 12:00 AM'
AND uc.stop_date <= '1/16/2009 11:59:59 PM'
AND uc.tech_mode = 0
AND uc.log_to_daily_report = 1
There are three tables in the derived table d, and these also appear in the main body of the FROM clause: how much does the result rowcount change as a result of adding in the three extra tables individual_test, limit_set and test_limit?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 8:52 am
Thanks a lot for ur help Chris !!!1
One error i am getting with your code :
Invalid column name 'error_code'.
February 3, 2009 at 2:33 am
nikhil.verma (2/2/2009)
Thanks a lot for ur help Chris !!!1One error i am getting with your code :
Invalid column name 'error_code'.
I'm sorry I can't go any further with this - the code has been edited out.
You need to check which table contains this column and ensure that it has been correctly aliased in joins and in the select list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2009 at 11:47 am
Nikhil,
Did you try viewing the Query Execution plan? Perhaps there is a scan or bookmark/key lookups happening on the indexes. Try to convert these to Index seeks by using covered indexes.
Also try setting the SET STATISTICS IO ON to figure out the logical reads, If the logical reads indicate a huge number it is usually due to fragmented indexes. Try rebuilding the Indexes on the tables involved in the Query to defrag the indexes.
Hope this helps.
Amol
Amol Naik
February 5, 2009 at 3:52 am
Hi Chris
With this i am not getting correct results for qty_tested , dty_failed. Counts are different.
February 5, 2009 at 4:08 am
nikhil.verma (2/5/2009)
Hi ChrisWith this i am not getting correct results for qty_tested , dty_failed. Counts are different.
Without seeing the code you are using now and the code you were using before, I can't offer any suggestions. I understand that you may not wish (or be permitted) to have live code on a public forum, but without the code to refer to, there's little that anyone can do to help other than refer you to possible causes after examining the plans.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply