0

Sorry for the bad title. Couldn't really think of a good name without an explanation.

In our system (source is inside Oracle PL/SQL packages) we have quite a lot large SQL queries with a sh..load of variables, dozens of joins to multiple tables and a lot of conditions. Some of those tables and variables are even configurable by the user.

Example would be a warehouse. The user has some orders/positions and items in different boxes. He can see the order needs item_x and he can see that there is a box with item_x in it. When he tries to perform some action he get's an error that item_x for his order is not available.

Most of the time the problem is some configuration or special case that prevents the system for using that box the user has in mind (he isn't telling the system which boxes, the system is searching for a valid box with a lot of ordering etc.).

At the moment a problem like this ends up in a procedure which looks like this:

  • Client calls and says action_x isn't working all though he has the item_x that is needed by the order in box 1234
  • We connect to the database and check that what he saying is true
  • We copy the freakin huge SQL into a worksheet.
  • We debug into the procedure to copy all the variables and constants being used in the operation he is performing and replace them (by that time probably 20 minutes have passed).
  • Now we can comment out the WHERE-clause or change some JOINs to LEFT JOINs to find the row that was filtered out and the reason why.

Those things can happen after updates because some bug sneaked into the conditions, it can be some configuration problem caused by the user or it is just right that those item wasn't seen but the user can't keep track on all the reasons why, nether can we.

What would be good practices to change this problem. Of course it might be possible to remove the conditions, change everything to LEFT JOINs and validate everything after, while looping over the data, so it is possible to print out a neat message. All though it has to be more like returning a freakin huge result set with reasons behind every row why that wasn't a match. And of course this would lead to really, really bad performance and his application would probably freeze for minutes.

Another solution might be having two queries. One with conditions and INNER JOINs and another without and with LEFT JOINs which only gets executed if the first doesn't return any valid rows. This wouldn't impact performance for working cases but would probably end up in bugs etc. because of redundancy and at the end displaying that for the user would be pretty hard.

And of course we could speed up the search/replace part with some aggressive logging of the variables but at the end that would just speed up the debugging. Are there good solutions for problems like that which might also benefit the user?

1 Answer 1

1

You might want to pull the join logic into an inner query, and then have your "prod" and "debug" filtering applied to the same inner. E.g. starting from

CREATE PROCEDURE Procedure
    @param1 Type,   
    @param2 Type   
AS   
     Select A.id, B.name
     From A
     Join B On A.id = B.a_id
     Where A.prop = @param1 and B.other = @param2
GO  

You first create

CREATE FUNCTION Function
    @param1 Type,   
    @param2 Type   
RETURNS TABLE  
     RETURN Select A.id, B.name, A.id is not Null As [A exists], B.a_id is not Null As [B exists], A.prop = @param1 As [A param1], B.other = @param2 As [B param2]
     From A
     Outer Join B
GO

Then the inital query becomes

CREATE PROCEDURE Procedure
    @param1 Type,   
    @param2 Type   
AS   
     Select id, name
     From Function(@param1, @param2)
     Where [A exists] and [B exists] and [A param1] and [B param2]
GO  

And when you want to debug why you didn't find any rows matching A.prop = "Specific Value"

Select Count(prop)
From A
Where prop = "Specific Value" 

// some rows returned

Select * 
From Function("Specific Value", "Other value")
Where [A param1]

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.