How much of this task can/should be done in SQL?

Answer 1

I have no idea what step 3 means, but all the other steps can be done in SQL, also, they probably should be done in SQL where its a lot more efficient.

I assume from your tags that you're using a MySQL database (it would have been helpful to know).

My initial thots would be somethnig along the lines of:

 SELECT outdata.Pos,
     outdata.OutData,
     outdata.Warn,
     MIN(outdata.Mismatch),    
     outdata.Producer,
     outdata.Depl,
     outdata.DeplOffMax,        
     outdata.DeplOffMin,
     outdata.Axis
     outdata.Connection
 FROM outdata, indata
 WHERE
   (//some nasty type juggling here
     outdata.depl='*'
     OR outdata.deploffmin='*'
     OR indata.depl='*'
     OR (
        outdata.depl<>'*'
        AND outdata.deploffmin<>'*'
        AND indata.depl<>'*'
        AND outdata.depl-outdata.deploffmin>indata.depl
     )
   )  
 AND ( outdata.outdata=indata.outdata
   OR outdata.outdata='*'
   OR indata.outdata='*' )
 AND ( outdata.connection=indata.connection
   OR outdata.connection = '*'
   OR indata.connection='*' )
 AND (outdata.axis=indata.axis
   OR outdata.axis='*' 
   OR indata.axis='*' )
 ....repeat for all the fields you want to match       
 GROUP BY outdata.Pos,
     outdata.OutData,
     outdata.Warn,    
     outdata.Producer,
     outdata.Depl,
     outdata.DeplOffMax,        
     outdata.DeplOffMin,
     outdata.Axis
     outdata.Connection
 ORDER BY 4 ASC;

Simples.

C.

Answer 2

First, the question is too long.

Second, the rules (#1, #1b, #2, #3, and #4) are confusing and need clarification.

However, in spite of that, this is my advice.

If it's not obvious how to do it in SQL, don't.

For something like this, with "rules" for matching ("star is matching anything", "select the row with lowest Mismatch when duplicates exist", etc.) you have two choices.

1. Simplify the processing steps so that they can be implemented in SQL. This is generally a good thing. Your current statement is confused and hard to follow. Time spent simplifying would be time well spent.

2. Use an ordinary programming language. When you reach things which are hard to do in SQL, don't "force" them. Get the data from SQL and process it in PHP.

SQL -- generally -- is slow. It's best used when you have vast volumes of data that won't fit in memory and you have transactional updates going on. 1000 rows and 30 fields is a trivial amount of data that easily fits in memory. If you're given a batch of data to analyze (with no updates going on) SQL's not helping much.

In a very busy website -- using PHP -- you may have problems with lots of concurrent users. In which case, get it out of PHP so it's not running under Apache. Until such time as Apache runs out of memory, just do it in PHP.

Answer 3

One thing I've learned. Anything that can be calculated should not be saved in the database

Answer 4

I don't fully understand the business logic, but for such a tiny amount of data I would not bother going through the pain of trying to implement in SQL. It is likely to be faster just pulling all the data over and using PHP to apply your filters.