November 14, 2008 at 11:55 am
I have a case where 10 or so sprocs with multiple CTE's in several of the sprocs have been created to provide source data for a report in SSRS. The deal is the "customer" now wants an nth of that data for one report and I am trying to reduce the number of sprocs down to match the simplified request. Here's the situation. I need to pull all open balances for each customer for a specific month then determine which of those customer's balances are overdue then define the amount that's overdue by 7 days, then the amount that's overdue by 90 days and calculate the percent current. Customer, account number, amount open, amount due and age exists for all accounts in a detailed format. This data needs to be aggregated by customer then by the other criteria listed. Oh and separated by Top number based on past due balance and all others. The report will have a chart and a gauge as well. The data is contained in three tables which isn't a big deal. My desire is to pull over the data, group it, and perform aggregations and dump it all into one table in case it becomes an ongoing request.
November 14, 2008 at 12:19 pm
Okay, I understand what you're trying to do... what's your question?
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 14, 2008 at 12:32 pm
The question is how do I reduce what I have now from multiple sprocs to maybe one? There has to be a simple way and I think I get caught up too much in the details of the existing code to see my way clear.
November 14, 2008 at 12:52 pm
Without understanding your database, I can only give you generalities.
- Take the process one step at a time. Filter your data as much as possible, and eliminate any unnecessary steps.
- Parameterize whenever possible (i.e. Select Top (@batchSize)). This will possibly reduce your amount of work in the future.
- Consider replacing the CTE's with temporary or variable tables. CTE's are great if you don't need to re-use the data or when you need recursion; however, if you can re-use the data, then a temporary table may do the job better.
- Allow the presentation layer (i.e. SSRS) to perform the sorting for you; try to avoid doing the sort in the actual stored procedure.
HTH.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply