Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Crow King
Starting Member
2 Posts |
 Posted - 2005-04-02 : 18:47:45
|
| I have been looking up and down for any literature (or even anecdotes) on using SQL to solve optimization problems. I am attempting to tackle an application where I have to assign sales calls to salesman and would like to avoid record at a time processing.Does anyone here have any recommendations (books, websites, anything)? Thanks.ck |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-02 : 22:16:00
|
| Books Online is always the best place to start for learning SQL Server. But the problem you've described is pretty vague, to talk about optimizing it before you're even defined it is premature and likely to cause you problems down the road. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-03 : 13:19:44
|
| You can read a LOT on this forum about set based processing. Ken Henderson also has some good TSQL books if you just want to buy a book. Search the forum for "set based", "loop", etc and begin reading up. You also might want to pick up on how the internals of this thing are working by reading "Inside SQL Server 2000" by Kalen Delaney. If you have a good understanding of the language and engine, you will be able to make much better set-based code. It's a very specific way of thinking.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-03 : 15:36:16
|
| When you say "solve optimization problems", do you mean using SQL to optimize the assignment of sales calls to salemen, or optimization of SQL code that already does this?If you mean the first, this sounds like a Routing problem. Without knowing the algorithim that you want to use to solve the problem, it is really hard to say if SQL is a good choice for this application. Looking back at an old Operations Management textbook, I see that there are a number of mathmatical models for solutions to these types of problems.If you want help with optimizing a particular piece of code or seeing how to do it with set based queries, you should post a good description of the problem you want help with, along with the code, including DDL for the tables, sample data, and procedures.CODO ERGO SUM |
|
|
|
Crow King
Starting Member
2 Posts |
Posted - 2005-04-03 : 20:35:56
|
| Thanks to everyone for posting.Yes, the problem I am attacking is the classic NP complete "routing" salesperson problem. Since it is so common, I was hoping someone could point me to a SQL based approach. It sounds like solutions for this type of problem aren't usually done in SQL. I have an existing heuristic record at a time solution which delivers good results but I would like to reduce the amount of time between defining the business rules and producing the results. I thought SQL might help, as the record at a time thing is killing me. I haven't touched linear programming since college and I don't want to have to get back to it unless absolutely necessary. lol - looking for a shortcut, as always!Anyway, I will search on "routing" and "loop" and if anyone knows of any references, please let me know. Thanks for you help.ck |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-04 : 00:40:34
|
| I would say that applications of this kind are not a real main stream application for SQL Server, since it is mostly about mathematical methods.If you are looking for methods of solving the problem, I would do Google searches on the term "Vehicle Routing Problem". I believe that the Traveling Salesman Problem is considered a subset or type of Vehicle Routing Problem. This site seems to have a lot of good links:http://osiris.tuwien.ac.at/~wgarn/VehicleRouting/vehicle_routing.htmlIt may be that some of the approaches to solving these problems can be done with SQL, but there seem to be a lot of heuristic methods available. I know I have never done one in SQL or worked with anyone who told me they had done one in SQL.I worked on a system years ago where I developed a data entry front end for a similar application of picking the most optimal assignment of shipments to different trucking companies to reduce freight costs. The system produced good results, but it failed because it just could not produce the results fast enough. I believe that it the biggest challenge for all systems of this type. No one needs answers that tell them how they should have allocated yesterdays shipments.CODO ERGO SUM |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2005-04-04 : 11:28:39
|
| Rockmoose posted a puzzle to solve the TSP problem in SQL. Take a look here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39535Or another similar problem I posted here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39736 These are path-finding problems. The solutions to the first used brute force and the second used a few optimizations. The problem is similar to the NP hard bin-packing problem. Googleing for it may turn up something. I know I have seen it discussed on some other forums.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
|
|
|
|
|
Using SQL to solve optimization problems