issue with performace - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with performace

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-02 : 17:25:31

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-02 : 19:06:20

please suggest me a method to follow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-02 : 19:40:22

case when exists (select * from ...) then
would be better.
Suspect a left join to (select distinct emp_number from tbl_app_info) tbl_app_info would be even better.

select a.emp_number,
coalesce(a.associate_name,' ') as associate_name,
case when tbl_app_info.emp_number is not null then 1 else 0 end as app_count,
case when tbl_po_info.emp_number is not null then 1 else 0 end as po_count,
from tbl_emp_numbers a
left join (select distinct emp_number from tbl_app_info) tbl_app_info
on tbl_app_info.emp_number = a.emp_number
left join (select distinct emp_number from tbl_po_info) tbl_po_info
on tbl_po_info.emp_number = a.emp_number
where (associate_name is null or associate_name <> 'test')
and application_type = 'TY'
order by associate_name

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-02 : 22:07:43

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-03 : 11:57:27

I tried with the below code also..still the performance is an issue...Please somebody help me out...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources