Is this a well documented pattern?

I am trying to find if the below is a well documented pattern (or anti-pattern for that matter) for reducing application latency. I have tried this technique and on face this looks to be saving me some 20% in latency. I would like to know if there are any side affects that I should be aware of

Context:

You have got a method/function/procedure which makes multiple SELECT calls to database and you need to optimize it.

lets say the flow of your method is :

  getDBConnection()  
  execute("Select a,b from tableA");  
  bind a with varA 
  bind b with varB  
  ---SOME Business Logic-----  
  execute("Select c,d from tableB");  
  bind c with varC  
  bind d with varD   
  ---SOME more Business Logic-----  
  execute("Select e,f from tableC");  
  bind e with varE  
  bind f with varF  
  ---SOME more Business Logic-----  
  releaseConnection()

Sol开发者_如何学编程ution : Use Union ALL to make a single call to Database

 getDBConnection()
 execute("Select a,b,'sqlA' from tableA"+  
 " UNION ALL "+  
 " Select c,d,'sqlB' from tableB"+  
 " UNION ALL "+
 "Select e,f,'sqlC' from tableC");  
 bind a,b where records have "sqlA"   
 bind c,d where records have "sqlB"
 bind e,f where records have "sqlC"  
 releaseConnection()  
 --------Do all Business Logic here-----

The use of union limits the "shape" of your queries. They basically have to all return the same number and (compatible) types of columns, in the same order.

A better approach would be to use multiple queries in a single command and then deal with multiple result sets:

execute("Select a,b from tableA;"+
  "Select c,d from tableB;"+
  "Select e,f from tableC");

Or maybe create a dedicated stored procedure that runs these queries.

Apart from this, this optimization technique can lump together unrelated operations, which will limit the reusability of individual operations later on. You might want to consider a design that better separates these operations and uses some kind of QueryManager to first collect them and later run them all together.

Pushing everything together may mask the real problem: Do you know where the latency is coming from?

If these queries are called many times you may be spending a lot of time on the compilation phase. Using prepared statements might help if the tables don't change too significantly during your application's lifetime:

conn = connect_to_db()
pstmt = conn.prepare('select ...')
...
pstmt.bind(parameters) // if necessary
pstmt.execute()

If the latency isn't from compilation it may be execution - the queries you gave are simple selects, but anything more complex might warrant an examination of explain plans.

If your dbms and your tables' structure allow it, some restructuring might also help cut back on how much querying needs to be done: Can you combine the select statements with a join instead of a union? Can you merge the tables with partitioning?

That's all a bunch of general ideas. To answer your actual question, I haven't seen that approach used before but I wouldn't let notoriety be the only deciding factor. As the previous poster pointed out you might sacrifice code reusability. Finally, as the number of tables grows this approach won't scale very well: you'll still have to look up which rows have "sqlA", have "sqlB", etc. in the application code.