Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I need to perform large amounts of computations on data storerd in a busy Microsoft SQL server. Would it be faster if I retrieve the data from the server, perform the computations locally in C++, and possibly later update the tables on the server, all through an API?

Many thanks.

share|improve this question
    
Maybe... Or could you compile your library and run it on the server, and have a stored procedure that calls your library? That would save you the round-trip in your current scenario between the database and the client which does the calculations. Or could you write all of the calculation code in stored procedures and do away with the need for a separate library (have you done any testing to prove that the C++ library will be significantly faster)? –  FrustratedWithFormsDesigner Jul 11 '12 at 14:57
3  
Need more details. If the computation is trivial and the data is large, then keeping on the SQL server is probably best. If the computation is long/large/complex, then offloading is probably better. –  sdg Jul 11 '12 at 18:35

3 Answers 3

You need to determine (or share with us) busy how? Busy because the data has a lot of additions, updates, reads or all the above?

A table/database/server that has heavy transaction activity, can be periodically copied to a place that is going to be reported/read a lot. How up-to-date does it have to be? You tell us.

Is there any down-time, when these calculations/updates can be done or are you 24/7/365?

Many back-office operations can wait for their recalculated data. Maybe you just need to ask?

share|improve this answer
    
Right... Thanks! –  Ellen Jul 18 '12 at 13:52

It depends, firstly you do not want to hog the CPU processing the data if SQLServer wants all that CPU for itself - you'll be defeating the point, so migrating the data crunching task to a different server, even though this would be less efficient, would be faster overall. Typically DBs use a lot of CPU, so do some profiling whether you have the capacity.

If you have a mirrored server, that can be the best place to run a heavy computational task on the data, you'll just have to copy the results back to the original in most cases, but at least you'll have quick access to the original data.

Can you do the crunching during quiet times - overnight for example. If so, this is probably the best approach. If you need the data crunched in "real-time" then this obviously won't be sufficient.

share|improve this answer

There is no single answer for your question, and you have to weigh the costs for each stage to determine what is "best" for your environment.

  • First off, where is it easiest to establish and maintain a scheduler to fire this job? Where are admins going to be more comfortable with in having the scheduler located.
  • Next, what sort of network bandwidth is available between the DB server and the system performing the calculations in C++? There is a performance cost associated with transferring that data twice (once to the C++ machine, once to return the results).
  • What are the capabilities of the C++ calculation system? If they're not comparable or greater than the DB system, then it may take even longer to use the C++ calculation system due to data transfer and calculation time.
  • What is the nature of the calculations that need to be performed. Set manipulations are done much more easily within the DB server, but can require a fair amount of coding to replicate in C++. There is a cost associated with developing, validating, and maintaining the code (be it SQL or C++) that will perform the calculation. Some things are faster to write in SQL. Some are written faster in C++. What's your skill level in both cases?
  • Who is going to maintain this? What's their skill level in all of the related categories that have come up?
  • What sort of auditing and / or logging is required? Anything at all? Are custom notifications required from this when the calculations are complete? Long running jobs frequently require firing off some notice to indicate completion. What if the process errors out? How do you want to be notified? Which system gives you the best access to this information?
  • How clean is the data? What sort of errors are you likely to see within the data set? C++ can be better at error handling because you can customize it to your exact needs.
  • Have you considered a hybrid approach to optimize the problem for both environments? Pre-process on the DB server the stuff that SQL can do best, then ship a smaller data set to the C++ calculation system which performs the remainder of the tasks. Depending upon how large the data set and calculations are, this could be broken up into several iterations between the DB and calculation servers.
  • Are you able to break the data set into smaller pieces for processing? An incremental approach generally has less performance impact upon the host system simply because it's not doing as much at any one particular time.
share|improve this answer
    
Thanks! By trying to answer the questions you listed I'm able to come up with the best solution, after talking to the other users of the DB –  Ellen Jul 18 '12 at 13:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.