6

As part of a work assignment I've long dealt with a giant, dynamic SQL-creating stored procedure that's used for retrieving a bunch of inventory items. This stored procedure accepts a ton of parameters (maybe like 20+) which, in turn, turn on and off the dynamic SQL that's generated.

Eventually we exec all this SQL and generate a result.

Inventory, unsurprisingly, has special rules about who can and can't see certain items and how many items they can order, that kinda thing. As a result, a lot of this logic gets set in the stored procedure.

As you can imagine, with its reams of dynamic SQL the programmers on my team universally loathe needing to change this darn thing.

Given that we already treat the stored procedure as an 'endpoint' (we let the stored procedure handle everything so there's no inconsistency, letting a user see items they shouldn't, for example), I feel like this would be a great candidate for being made into a proper stand-alone service.

In my mind I kind of imagine doing away with the stored procedure and instead using a straight select into some sort of immutable list on the application side. We'd then need to generate a series of filters which would preform the same kinda filtering the various bits of dynamic SQL are doing now and getting a result out the other side.

What gives me pause, however are the following:

  • I guess I'm just naive about the best way to go about creating a 'filter chain' like this. We're predominately a Java shop so I've certainly dealt with applying a Predicate to a Collection and all, but I've generally done this with just a few filters. Is there a good example of the best way to sort of set up a 'filtering pipeline,' where you start with some large list and progressively winnow it down or is it really just a matter of having a giant list of filters?

  • When I think about the above, it strikes me that I'd just essentially be recreating SQL in a non-SQL environment and that seems bad. Our platform is SQL Server/TSQL and it seems like creating stored procedures/functions that accept tables as input is, if not frowned upon, not practiced widely.

Doing things entirely in SQL also makes them very difficult to unit test, from my experience.

As mentioned, we're predominately a Java shop but I'm open to suggestions from any language. SQL Server's a bit less malleable, as you can imagine.

Anyway, this problem's been bouncing around my head for a year or more and I'd love to hear your thoughts!

|improve this question
  • You could try something like Coollection. It's not Linq for Java, but it's close. – Robert Harvey♦ Aug 11 '16 at 21:14
  • I'm kind of curious, how does a stored procedure about retrieving inventory items have anything to do with how MANY somebody can order? Not seeing certain items I can understand, but that... – JDT Aug 12 '16 at 12:48
  • @JDT: Inventory can be restricted such that a person in a given group may only be allowed to order a certain number of items (usually over a sliding window, say 3 items every 30 days or something). As mentioned, this sproc's only ever gotten more complicated over time, hence my question. – benjamin Aug 12 '16 at 16:43
  • 1
    A dynamic SQL approach is not necessarily bad, if the creation code is well structured and maintainable. Why is the SQL creation done in a stored procedure, instead of a java function or class at the client side? Moreover, if you are going to move the place where the filtering occurs from the server to the client, your program will always need to transfer all inventory items over the network, for every query, and then might throw away lots of that data again. If that is the case, it might become a performance problem. – Doc Brown Aug 13 '16 at 12:01
  • 1
    ... moreover, if the SQL creation would a Java program, you could easily unit test it (without the database). Just write tests which compares the generated queries for different parameter sets with a list of expected queries. – Doc Brown Aug 13 '16 at 12:06
1

I think there are two issues at work here. The first issue is searching and filtering, which in this case is probably best solved using SQL. The second issue is the fact that you have intricate business logic in the stored procedure, which does not really belong there.

You could start by identifying what parts of your stored procedure belong on the SQL side and what parts are actually business logic. Filtering items that start with 'sheep' is SQL, not returning more items than can be ordered by a sliding widow is business logic. (Heck, I can't even think how you could have the two in a single SQL query and have it work right...) Make a list of all the things that are business logic and then pick one to work on.

Next, write a test. I'm assuming that you have code that at some point returns the list of items that were requested and that internally calls down to the stored procedure. You are going to expand that code by fitting in a new layer that takes care of your business logic. Your test should probably be on the code that sits just 'above' where you would introduce your filtering pipeline. Write your test on top of that code. Have the test (or tests if you need more) deal with the one specific piece of business logic you picked.

Next, refactor. Take the SQL code out of the stored procedure and write Java to replace it. A good way to do this would be to create a single class to fit the business logic you are currently working on (for example, a 'MaximumOrdersPerSlidingWindowLimiter'). Unittest that class and rely on the test written above to make sure you don't break anything. The class should take a list of items and return a list of items. Have this class extend an interface that is common for all these small classes that you will be creating. Something like this might suit you.

Keep tearing down the stored procedure until all your business logic is Java and all that remains is actual SQL querying code.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.