issue with the performance 2

SqlHunter · Nov 2, 2004

Here I am facing a performance issue...I have around 15000 emp_numbers sitting in one table called
tbl_emp_numbers and I need to use a query for summary report.basically its for knowing how much entry
has been done in each table.The case I have written here is 3 but there are 10 more with similar situations
and each app_count,po_count etc wants basically a return saying whether this emp_number is captured in here
if captured return 1 else return 0.Is there any way to increase the performace..This is dead slow

select a.emp_number,
case
when a.associate_name is null then ' '
else a.associate_name
end as associate_name,
case
when (select count(*) from tbl_app_info where emp_number = a.emp_number) > 0 then 1
else 0
end as app_count,

case
when (select count(*) from tbl_po_info where emp_number = a.emp_number) > 0 then 1
else 0
end as po_count,

from tbl_emp_numbers a

where (associate_name is null or associate_name <> 'test')
and application_type = 'TY'
order by associate_name

donutman · Nov 2, 2004

It's always best to use derived tables rather than subqueries.

Code:

[Blue]SELECT[/Blue] a.emp_number[Gray],[/Gray]
       [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray]b.emp_cnt[Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]
       [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray]c.emp_cnt[Gray],[/Gray]0[Gray])[/Gray]
   [Blue]FROM[/Blue] tbl_emp_numbers a 
      [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue]
         [Gray]([/Gray][Blue]SELECT[/Blue] emp_number[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]emp_number[Gray])[/Gray] emp_cnt
          [Blue]FROM[/Blue] tbl_app_info [Blue]GROUP[/Blue] [Blue]BY[/Blue] emp_number[Gray])[/Gray] b
      [Blue]ON[/Blue] a.emp_number[Gray]=[/Gray]b.emp_number
      [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue]
         [Gray]([/Gray][Blue]SELECT[/Blue] emp_number[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]emp_number[Gray])[/Gray] emp_cnt
          [Blue]FROM[/Blue] tbl_po_info [Blue]GROUP[/Blue] [Blue]BY[/Blue] emp_number[Gray])[/Gray] c
      [Blue]ON[/Blue] a.emp_number[Gray]=[/Gray]c.emp_number
      [Blue]WHERE[/Blue] associate_name [Gray]<[/Gray][Gray]>[/Gray] [red]'test'[/red] 
         [Gray]AND[/Gray] application_type [Gray]=[/Gray] [red]'TY'[/red] 
      [Blue]ORDER[/Blue] [Blue]BY[/Blue] associate_name

I don't think you need the "associate_name is null or" but I could be wrong about that.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

SqlHunter · Nov 2, 2004

Hi tried the above..It was giving quick results for few of them..When i use this method for the complete list it was still giving issues.....Let me show the complete select

select a.emp_number,
case
when a.associate_name is null then ' '
else a.associate_name
end as associate_name,
case
when (select count(*) from tbl_app_info where emp_number = a.emp_number) > 0 then 1
else 0
end as app_count,

case
when (select count(*) from tbl_po_info where emp_number = a.emp_number) > 0 then 1
else 0
end as bpo_count,

case
when (select count(*) from tbl_til_info where emp_number = a.emp_number) > 0 then 1
else 0
end as ttl_count,
case
when (select count(*) from tbl_ck_info where emp_number = a.emp_number) > 0 then 1
else 0
end as c4k_count,

case
when (select count(*) from tbl_check_info where emp_number = a.emp_number) > 0 then 1
else 0
end as add_check_count,

case
when (select count(*) from tbl_nkt_info where emp_number = a.emp_number) > 0 then 1
else 0
end as check_req_count,

case
when (select count(*) from tbl_listing_info where emp_number = a.emp_number) > 0 then 1
else 0
end as list_let_count,

case
when (select count(*) from tbl_analysis where emp_number = a.emp_number
and (taxes_paid_flag is not null
or lien_issues is not null
or delinquent_amount is not null)
) > 0 then 1
else 0
end as prop_info_count,

case
when (select count(*) from tbl_listing_price_info
where analysis_id = (select analysis_id from tbl_analysis where emp_number = a.emp_number)
) > 0 then 1
else 0
end as list_info_count,

case
when (select count(*) from tbl_cost_info
where analysis_id = (select analysis_id from tbl_analysis where emp_number = a.emp_number)
and cost_subtype_id >= 1 and cost_subtype_id < 24
) > 0 then 1
else 0
end as prop_cost_count,

case
when (select count(*) from tbl_cost_info
where analysis_id = (select analysis_id from tbl_analysis where emp_number = a.emp_number)
and cost_subtype_id >= 24 and cost_subtype_id < 42
) > 0 then 1
else 0
end as sell_cost_count,

case
when (select count(*) from tbl_analysis_prop where emp_number = a.emp_number
and (prop_detail_comments is not null
or market_strat_comm is not null
or cost_of_funds is not null
or selected_period is not null )
) > 0 then 1
else 0
end as curr_act_count

from tbl_emp_numbers a

where (associate_name is null or associate_name <> 'test')
and application_type = 'TY'
order by associate_name

and the performance of this query is very bad..Its eating all the cpu time and resources.Please help me to get a work around this issue

SqlHunter · Nov 2, 2004

Please somebody help me with this performance

zhavic · Nov 2, 2004

Did you try something like this ?
( based on donutman's post )

Code:

SELECT  a.emp_number,
	    ISNULL( a.associate_name, ' ' ) AS associate_name,
		CASE WHEN c_app_info.emp_cnt > 0 THEN 1 ELSE 0 END AS app_count,
		CASE WHEN c_po_info.emp_cnt > 0 THEN 1 ELSE 0 END AS bpo_count,
		CASE WHEN c_til_info.emp_cnt > 0 THEN 1 ELSE 0 END AS ttl_count,
		CASE WHEN c_ck_info.emp_cnt > 0 THEN 1 ELSE 0 END AS c4k_count,
		CASE WHEN c_check_info.emp_cnt > 0 THEN 1 ELSE 0 END AS add_check_count,
		CASE WHEN c_nkt_info.emp_cnt > 0 THEN 1 ELSE 0 END AS check_req_count,
		CASE WHEN c_listing_info.emp_cnt > 0 THEN 1 ELSE 0 END AS list_let_count,
		CASE WHEN c_analysis_1.emp_cnt > 0 THEN 1 ELSE 0 END AS prop_info_count,
		CASE WHEN c_listing_price_info.emp_cnt > 0 THEN 1 ELSE 0 END AS list_info_count,
		CASE WHEN c_cost_info.emp_cnt_1_to_23 > 0 THEN 1 ELSE 0 END AS prop_cost_count,
		CASE WHEN c_cost_info.emp_cnt_24_to_41 > 0 THEN 1 ELSE 0 END AS sell_cost_count,
		CASE WHEN c_analysis_prop.emp_cnt > 0 THEN 1 ELSE 0 END AS curr_act_count
FROM tbl_emp_numbers a
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_app_info GROUP BY emp_number ) AS c_app_info ON c_app_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_po_info GROUP BY emp_number ) AS c_po_info ON c_po_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_til_info GROUP BY emp_number ) AS c_til_info ON c_til_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_ck_info GROUP BY emp_number ) AS c_ck_info ON c_ck_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_check_info GROUP BY emp_number ) AS c_check_info ON c_check_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_nkt_info GROUP BY emp_number ) AS c_nkt_info ON c_nkt_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_listing_info GROUP BY emp_number ) AS c_listing_info ON c_listing_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_analysis GROUP BY emp_number ) AS c_analysis ON c_analysis.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_analysis WHERE COALESCE( taxes_paid_flag, lien_issues, delinquent_amount ) IS NOT NULL GROUP BY emp_number ) AS c_analysis_1 ON c_analysis_1.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(x.emp_number) AS emp_cnt, x.emp_number
					FROM tbl_listing_price_info AS x
					INNER JOIN tbl_analysis AS y ON y.emp_number = x.emp_number 
					GROUP BY x.emp_number
			  ) AS c_listing_price_info ON c_listing_price_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT SUM( CASE WHEN x.cost_subtype_id BETWEEN 1 AND 23 THEN 1 ELSE 0 END ) AS emp_cnt_1_to_23,
					   SUM( CASE WHEN x.cost_subtype_id BETWEEN 24 AND 41 THEN 1 ELSE 0 END ) AS emp_cnt_24_to_41,
					   x.emp_number
					FROM tbl_cost_info AS x
					INNER JOIN tbl_analysis AS y ON y.emp_number = x.emp_number 
					GROUP BY x.emp_number
			  ) AS c_cost_info ON c_cost_info.emp_number = a.emp_number
	LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_analysis_prop WHERE COALESCE( prop_detail_comments, market_strat_comm, cost_of_funds, selected_period ) IS NOT NULL GROUP BY emp_number ) AS c_analysis_prop ON c_analysis_prop.emp_number = a.emp_number
WHERE ( associate_name IS NULL OR associate_name <> 'test' )
	AND application_type = 'TY'
ORDER BY associate_name

I've used COALESCE() function to find one not null value from given list of columns, but it may not use the right indexes when executing, so than you can use IS NOT NULL clauses as in your original query.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

donutman · Nov 3, 2004

If Zhavic's approach doesn't perform well, then I would try creating a temp table. Insert all of the emp_numbers into it and have a bit column for each of the above left joins. Then update the temp table with the appropriate result of each select query. You won't have to use a case statement to convert the positive numbers to 1, the bit column will do that.
Finally do a select of the temp table for your report. You may have to use a permanent table as the temp table in order to pass the results to your report program. Just be sure to use Truncate Table WorkTable at the beginning of your SP instead of Delete WorkTable to avoid filling up your Transaction Log.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

SqlHunter · Nov 3, 2004

Hi donutmand and zhavic ................
This is amazing............
The performance has increased so much..Thanks a lot...This is great hown u guys helped me....the only one issue
I have is with this left join

Here prop_detail_comments is varchar(3000),
market_strat_comm is varchar(3000),
cost_of_funds is decimal 5(7,2),
and selected_period is int

and it was giving error

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varchar to data type numeric.

LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_analysis_prop WHERE COALESCE( prop_detail_comments, market_strat_comm, cost_of_funds, selected_period ) IS NOT NULL GROUP BY emp_number ) AS c_analysis_prop ON c_analysis_prop.emp_number = a.emp_number

donutman · Nov 3, 2004

I don't see it in that left join? The only place I can see a potential conversion would be c_analysis_prop.emp_number.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

SqlHunter · Nov 3, 2004

SELECT a.emp_number,
CASE WHEN c_analysis_prop.emp_cnt > 0 THEN 1 ELSE 0 END AS curr_act_count
FROM tbl_emp_numbers a
LEFT JOIN ( SELECT COUNT(emp_number) AS emp_cnt, emp_number FROM tbl_analysis_prop WHERE COALESCE( prop_detail_comments, market_strat_comm, cost_of_funds, selected_period ) IS NOT NULL GROUP BY emp_number ) AS c_analysis_prop ON c_analysis_prop.emp_number = a.emp_number
WHERE ( associate_name IS NULL OR associate_name <> 'test' )
AND application_type = 'TY'
ORDER BY associate_name

I tried using like this to verify whether it works fine and it was giving the same error......

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varchar to data type numeric.

SqlHunter · Nov 3, 2004

Hi donutmand and zhavic...
When i used this it was giving the results......

(prop_detail_comments is not null
or market_strat_comm is not null
or cost_of_funds is not null
or selected_period is not null )

donutman · Nov 3, 2004

c_analysis_prop.emp_cnt > 0 is another possibility

SqlHunter · Nov 3, 2004

Its working fine with good performance..it takes only 1-2 seconds to execute...Thanks a lot for the post.....

Hi donutmand and zhavic....can u please explain how the use of derived tables rather than subqueries help in increasing the performance

donutman · Nov 3, 2004

Subqueries have to be run against each row of the table whereas a derived table's rowset is created once. BTW, what I was trying to do with my first post would have turned the derived table into a subquery. That's why it doesn't work. You can't reference something from outside the derived table while in the derived table query.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

SqlHunter · Nov 4, 2004

How does the derived table query makes the performance better...

donutman · Nov 4, 2004

By avoiding a subquery you improve the performance. The derived table alows you to do that and still get the results you want.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

issue with the performance 2

SqlHunter

Programmer

donutman

Programmer

SqlHunter

Programmer

SqlHunter

Programmer

zhavic

Programmer

donutman

Programmer

SqlHunter

Programmer

donutman

Programmer

SqlHunter

Programmer

SqlHunter

Programmer

donutman

Programmer

SqlHunter

Programmer

donutman

Programmer

SqlHunter

Programmer

donutman

Programmer

Similar threads

Part and Inventory Search

Sponsor