1

There's a set of values stored in a ms sql db table. These values indicate,

a. serial number of the item

b. capacity of the item

c. availability of the item

I need to design a search function that has following capabilities and to find out an item or combined items to match/nearer a given capacity,

e.g. given capacity is 8. Available items are of capacity 10, 6, 4, 2. There are few items with above capacity. In the best case, result should return items with capacity 10 before deciding to approach the combining algo-part. In the event no single item is found to satisfy the given capacity, algo must proceed to combine, yet only if adequate number of items are available to combine...

  1. Rule of search : all combined items must be adjacent by serial number)
  2. Combined capacity should be an exact or a near match (greater than) given capacity
  3. Items must be available (Boolean value check)

How should I approach for this? The tough part could be finding total capacity of combined items while they are adjacent to each other. Currently I am thinking of each pulling data into C# and do the search or do entire search within SQL via a stored procedure. All ears to hear and try out any suggestions that could get better performance and satisfies the main criteria.

Improve this question
4
  • How do you define 'adjacent by serial number'? If they're the same value, this turns into a condensing-set query, which can be expensive. If they're not, it gets more expensive. So you probably want two actual queries, however you call them. Do you need to worry about concurrent access to the rows? Commented Jun 18, 2014 at 1:56
  • Great aspect @Clockwork-Muse well there aren't concurrent access at the moment. I am still very skeptical about "adjacency"... because adjacent is my rule to combine 2 or 3 (maximum) items. Is there anything better you can suggest? Commented Jun 18, 2014 at 3:23
  • Show some of your actual data and what you've got so far. How do you know you don't have concurrent access - is this a standalone process that runs with effective exclusive access over the rows? Otherwise, plan for things to go pear-shaped. "Adjacent" implies the serial numbers have been "incremented" somehow, which is actually a dangerous thing to deal with naively (and potentially expensive correctly) - did you mean the serial numbers must be the same, that is, they share it? Or do you have pre-defined groups? Commented Jun 18, 2014 at 8:00
  • Things have taken a totally different shape... The so called "approver" doesn't just seem to agree with any of the conceptual designs so far. Instead of being automated - I find it more and more manual... it's a pain in **** when changes come into db design. So I am in need for posting a question based on new db design and leaving this question aside for now. Commented Jun 21, 2014 at 4:26

1 Answer 1

Reset to default
1

In my opinion a stored procedure would be the best bet. You could set it up to write to a temp table or a table variable for the first part (a perfect, or possible match, ie > 8) then you could check to see the # of rows in the temp table, 0 == we didn't find it, so try to find a number of items that could satisfy the match.

Perhaps in the second part of the search you could look for items that have a count lower than 8 but sorted in descending order of capacity. Once you identify the one that has the highest, (lets call it @ok_but_not_complete_match_serialnum) then just look at the items below or above that one until you have enough capacity.

You could use a couple of temp tables and temp indexes to make this all run quite fast.

In your query you could use running totals of Capacity ordered by part number to easily identify the items that would match the request.

So assuming the serial numbers have a discernable order, for the second part something like this might work

Assuming that @ok_but_not_complete_match_serialnum is the serial num of the item you identified that has the highest (but not complete) match, 

select * from parts where serialnum 
between @ok_but_not_complete_match_serialnum - 10
and @ok_but_not_perfect_match_serialnum + 10

I picked 10 as the arbitrary range above and beyond the item, but maybe a smaller # is desireable. Even if querying the running totals doesn't get you the # you need, you could just keep checking the next item by serialnum until you've satisfied the need.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.