SQL Server Performance Forum – Threads Archive

Performance Suggestions Desperately Needed

I have a query that I’m running to produce a global summary for an invoice for the telco I work for. The problem is that its taking from 30 minutes to 1 hour to execute this SQL statement each time it runs, which is about 20 minutes longer than is remotely acceptable. I’m relatively new to SQL and T-SQL so I may not be using good ‘programming practices’ in this statement, and I’m desperately hoping some of you can give me suggestions on increasing the performance of the following query: Select bil_pk, bil_billcycle, bil_billmonth,
(select sum(lon_amount)
from LongDistance
where lon_is800 = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Instate_Amount,
(select sum(lon_duration)
from LongDistance
where lon_is800 = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Instate_duration,
(select count(lon_pk)
from LongDistance
where lon_is800 = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Instate_Count,
(select sum(lon_amount)
from LongDistance
where lon_is800 = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Outstate_Amount,
(select sum(lon_duration)
from LongDistance
where lon_is800 = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Outstate_duration,
(select count(lon_pk)
from LongDistance
where lon_is800 = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as t800_Outstate_Count,
(select sum(lon_amount)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Instate_amount,
(select sum(lon_duration)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Instate_duration,
(select count(DISTINCT lon_800number)
from LongDistance
Where lon_is800 = ‘1’
and lon_covfk in (select cov_pk from cover where cover.cov_bilfk = bil_pk)
)
as Distinct_800_Count,
(select count(DISTINCT lon_calledfrom)
from LongDistance
Where lon_isANI = ‘1’
and lon_covfk in (select cov_pk from cover where cover.cov_bilfk = bil_pk)
)
as Distinct_ANI_Count,
(select count(lon_pk)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isinstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Instate_Count,
(select sum(lon_amount)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Outstate_amount,
(select sum(lon_duration)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Outstate_duration,
(select count(lon_pk)
from LongDistance
where lon_isoutbound = ‘1’
and lon_isoutstate = ‘1’
and lon_isinternational = ‘0’
and lon_isdirectoryassistance = ‘0’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Outbound_Outstate_Count,
(select sum(lon_amount)
from LongDistance
where lon_isdirectoryassistance = ‘1’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as LD_DA_amount,
(select sum(lon_duration)
from LongDistance
where lon_isdirectoryassistance = ‘1’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as LD_DA_duration,
(select sum(lon_amount)
from LongDistance
where lon_isinternational = ‘1’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as International_Amount,
(select sum(lon_duration)
from LongDistance
where lon_isinternational = ‘1’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as International_Duration,
(select count(lon_pk)
from LongDistance
where lon_isinternational = ‘1’
and lon_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as International_Count,
(select sum(lsc_monthlyservice)
from LocalServiceCharges
where lsc_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Recurring_Charges,
(select sum(lsc_partialmonthlycharges)
from LocalServiceCharges
where lsc_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Pro_Rates,
(select sum(lsc_installcharges)
from LocalServiceCharges
where lsc_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Setup_Fees,
(select sum(fea_amount)
from FeatureActivations
where fea_activatedfeature = ‘DIR ASST’ and
fea_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Local_DA_Amount,
(select count(fea_amount)
from FeatureActivations
where fea_activatedfeature = ‘DIR ASST’ and
fea_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Local_DA_Count,
(select sum(fea_amount)
from FeatureActivations
where fea_activatedfeature != ‘DIR ASST’ and
fea_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Feature_Activations,
(select count(fea_amount)
from FeatureActivations
where fea_activatedfeature != ‘DIR ASST’ and
fea_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Feature_Activations_Count,
(Select sum(cov_beginningbalance)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Previous_Balance,
(Select sum(cov_payments)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Payments,
(Select sum(cov_otheradjustments)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as adjustments,
(Select sum(cov_financecharges)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Finance_Charges,
(Select sum(cov_pastdue)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Past_Due,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘Federal Tax’
) as Federal_Tax,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘State Tax’
) as State_Tax,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘School Tax’
) as School_Tax,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘KY Equal Access’
) as KY_Equal_Access,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘USF’
) as USF,
(Select sum(tax_amount)
from TaxesSurcharges
where tax_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and
tax_description = ‘PICC’
) as PICC,
(Select sum(cov_longdistanceothercharges)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Switch_Access,
(Select sum(net_amount)
from Internet
where net_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Internet_Monthly_Amount,
(Select count(net_pk)
from Internet
where net_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
and (net_amount > 0 or net_prorate != 0)
)
as Internet_Monthly_Count,
(Select sum(net_prorate)
from Internet
where net_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Internet_Prorates,
(Select sum(web_amount)
from Web
where web_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Web_Monthly_Amount,
(Select count(web_pk)
from Web
where web_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Web_Monthly_Count,
(Select sum(cov_networkservicesmonthlycharges)
from cover
where cov_bilfk = BillMaster.bil_pk
)
as Network_Services_Monthly_Amount,
(Select count(nws_pk)
from NetworkServices
where nws_covfk in (Select cov_pk from cover where cov_bilfk = bil_pk)
)
as Network_Services_Monthly_Count,
(select count(DISTINCT lsc_phonenumber)
from LocalServiceCharges
Where lsc_covfk in (select cov_pk from cover where cover.cov_bilfk = bil_pk)
)
as Distinct_LOCAL_Line_Count from BillMaster
where bil_billmonth = ‘200303’ and bil_billcycle = ‘4’ One area I can readily see that might help is by pre-loading (somehow) the following resultset in the query "(Select cov_pk from cover where cov_bilfk = bil_pk)" the question is, is this possible and if so how? Please note that the query WORKS and produces valid results, so this isn’t a request to debug it, I’m simply hopeful that some of you can provide suggestions on increasing the performance of this beast. As it stands now, it is simply a query, it will be turned into a stored procedure that accepts the variables in the final where condition, and run weekly. Thanks for your time, hopefully I’ll be able to make contributions to this forum in other areas…at least once I’m ‘up to speed’.

---

I would suggest using some views. Combine all of your subqueries that use the same where clause into 1 query, joined to the BillMaster table, and put that query into a view. When you get all of your subqueries combined into views, then in your final select statement join all the views together.<br /><br />Here is an example of the first query to be put into a view:<br /><br />CREATE VIEW LongDistanceInStateCallSummary AS<br />SELECT c.bil_billmonth, c.bil_billcycle, SUM(a.lon_amount) AS t800_Instate_Amount, SUM(a.lon_duration) AS t800_Instate_duration<br />FROM LongDistance a<br />INNER JOIN cover b<br />ON (a.lon_covfk = b.cov_pk)<br />INNER JOIN BillMaster c<br />ON (b.cov_bilfk = bil_pk)<br />WHERE a.lon_is800 = ‘1’ <br />AND a.lon_isinstate = ‘1’<br />AND a.lon_isinternational = ‘0’<br />GROUP BY c.bil_billmonth, c.bil_billcycle<br />GO<br /><br />In the final query you would simply join the views together with the BillMaster table to get the desired results.<br /><br />SELECT a.bil_pk, a.bil_billcycle, a.bil_billmonth, b.t800_Instate_Amount, b.t800_Instate_duration<br />FROM BillMaster a<br />LEFT OUTER JOIN LongDistanceInStateCallSummary b<br />ON (a.bil_billmonth = b.bil_billmonth<br />AND a.bil_billcycle = b.bil_billcycle)<br />etc.<br /><br />I don’t know how much this will help performance by itself, but it should help with segmenting the code to ease debugging at the very least. The reason I used a left outer join instead of an inner join was becuase I don’t know if your data will always have every month in every view of the data. If you are unsure, and you don’t want NULL to come up in your result set, you can use the COALESCE function to return a 0 instead. For instance, in the above query, we would do this:<br /><br />SELECT a.bil_pk, a.bil_billcycle, a.bil_billmonth, COALESCE(b.t800_Instate_Amount, 0) AS t800_Instate_Amount, COALESCE(b.t800_Instate_duration, 0) AS t800_Instate_duration<br /><br />If you have questions about the COALESCE function chech books online, it is really handy for a lot of purposes.<br /><br />If this is a high transaction database and you don’t care about reading uncommited transactions (plus or minus a few on a count doesn’t matter much), then you may want to consider adding the join hint WITH (NOLOCK) after the table names. This in itself could help performance tremendously if there is a heavy load on the DB.<br /><br />Of course I may be totally confused and in that case just ignore this post <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Valid tip by Negative (positively)and when it comes to gain the performance then think about using PROFILER & PERFMON. Using PROFILER you can fetch trace and use the same to get recommendation on INDEXES from Index Tunning Wizard and from PERFMON it would help for H/w & SQL counters about stressing physical resorces on the machine. HTH _________
Satya SKJ

---

How do I use those? If you don’t mind me asking. (New to SQL Server – I’ve only been using it for ‘the basics’ now its time for me to delve further into its capabilities) Also, thanks, Negative, for the suggestions, I’ll try that out. Another questions which arose from this query in my first post. Due to that one statement mentioned in the first post (where in I have to link back to the original Billmaster table through another table just to get valid results) I am considering adding redundant data (particularly billcycle and bill month) fields to the referenced tables to avoid this ‘kloogy’ operation. Do you think the performance gain on this query would be enough to justify the additional cost of adding this redundant data?

---

The SQL Server profiler can be found with the SQL Server tools, it should have been installed with the Query Analyzer and Enterprise Manager. The Performance Monitor is a windows system tool, and you should be able to find it under Administrative Tools in Win2k and XP. For help on how to use these tools search through the articles on this site, there is a lot of helpful information here. As far as the table design goes, I would not denormalize your data for the sake of this query. You generally risk too much with data integrity to justify denormalization. While it may save you some time fixing this query, you will pay for that time tenfold in the future.

---

]]>