1

On hand is a requirement for a report that needs to perform a substring operation and grouping on the chopped strings in a column. For example, consider my over-simplified scenario:

Among others, I have a column called FileName, which may have values like this

  NWSTMT201308201230_STMTA
  NWSTMT201308201230_STMTB
  NWSTMT201308201230_STMTC

etc.

The report I'm working on should do the grouping on the values before the _ sign.

Assuming the volume of data is large, where is the best place to do the substring & grouping - in the Stored procedure or return the raw data and do all the work in SSRS?The expectation is to have good performance and maintainability.

FMFF
  • 1,652
  • 4
  • 32
  • 62
  • Do it in the database possibly using an indexed view or add a computed column to the table but can just compute as part of query. – T I Aug 21 '13 at 13:24
  • If it's a large volume and you're only interested in the aggretated results, do it in the database if at all possible... SSRS can be a terrible memory hog if it's dealing with large amounts of rows. – Ian Preston Aug 21 '13 at 14:25

1 Answers1

1

As you mention, there are a few different possibilities. There's no correct answer for this, but certainly each method has advantages and disadvantages.

My take on the options:

  1. On the SQL server: as a computed column in a view.
    • Pro: Easy to reuse if the query will be used by multiple reports or other queries.
    • Con: Very poor language for string manipulation.
  2. On the SQL Server: Query embedded in the report, calculation still in query. Similar to 1, but now you lose the advantage of reuse.
    • Pro: report is very portable: changes can be tested against production data without disturbing current production reports.
    • Con: Same as 1, string manipulation in SQL is no fun. Less centralized, so possibly harder to maintain.
  3. In the report, in formulas where required. Many disadvantages to this method, but one advantage:
    • Pro: It's easy to write.
    • Con: Maintenance is very difficult; finding all occurences of a formula can be a pain. Limited to VB Script-like commands. Editor in SSRS Authoring environment is no fun, and lacks many basic code editing features.
  4. In the report, in the centralized code for the report.
    • Pro: VB.NET syntax, global variables, easy maintenance, with centralized code per report.
    • Con: VB.NET Syntax (I greatly prefer C#.) Editor is no better than the formula windows. You'll probably still end up writing this in another window and cutting and pasting to its destination.
  5. Custom .NET assembly: compiled as a .dll, and called from the report.'
    • Pro: Use any .NET language, full Visual Studio editor support, along with easy source control and centralization of code.
    • Con: More finicky to get set up, report deployment will require a .dll deployed to the SSRS Server.

So my decision process for this is something like:

  • Is this just a one time, easy formula? Use method 3.
  • Is this cleanly expressed in SQL and only used in one report? Method 2.
  • Cleanly expressed in SQL and used in multiple reports or queries? Method 1.
  • Better expressed in Visual Basic than SQL? Method 4.
  • Significant development effort going into this with multiple developers? Method 5.

Too often I'll start following method 3, and then realize I've used the formula too many places and I should have centralized earlier. Also, our team is pretty familiar with SQL, so that pushes towards the first two options more than some shops might be.

I'd put performance concerns second unless you know that you have a problem. Putting this code in SQL can sometimes pay off, but if you aren't careful, you can end up calling things excessively on results that are ultimately filtered out.

Jamie F
  • 23,189
  • 5
  • 61
  • 77