Solved

Calculation speed using db or application

Posted on 2006-07-07
7
391 Views
Last Modified: 2011-09-20
Dear all,

A bit lazy to find this out by testing so here it goes

-> i have a sql db of 12000 items
-> each has 6 numeric properties
-> for period ranges up to 50000 records per item

now i need to calculate several statistics on the properties over the complete range per item

can do this on the sql db using stored procedures but was wondering if bringing over the data to .NET and do the calculations in there
and upload the completed dataset per item to the db would be faster maybe using multiple threads?

would appreciate some good arguments using this or that way

Thanks
Brian
0
Comment
Question by:bruintje
  • 3
  • 3
7 Comments
 
LVL 37

Assisted Solution

by:gregoryyoung
gregoryyoung earned 250 total points
ID: 17058548
I I understand you properly, it will be faster to run this on the db in 99% of cases simply because the data throughput you would need to the sqlserver. If you had an extremely heavy calculation (such as a non-linear regression of the datapoints) it might be worthwhile to farm it out to a few machines, i.e. schedule it out to say 4-8+ machines to utilize the multiple processors.

Generally speaking it takes a rather hefty calculation to make this worthwhile though.

Cheers,

Greg
0
 
LVL 5

Accepted Solution

by:
bastibartel earned 250 total points
ID: 17058643
Hi bruintje,

It is almost impossible to say without having an idea of how 'expensive' the computation is.
If you can imlement a fake algorithm (in the db) that performs a similiar number of (senseless) operations as your intended algorithm,
than you'll have an estimate for the computational expense.

If that compares to the time needed to pull the data from the db - never mind.
If calculation takes significantly longer, then you should consider writing a separate statistics module.

At that point, you have to ask yourself, what kind of operations your algorithm will perform.

Say, you have two huge tables, 1 column each  and need to mupltiply every value in the 1st table with every value in the 2nd table.
Then, a C/C++ programm will outperform the database by several orders of magnitude.

If you depend on random access via conditional statements (query-like access to rows in a set) than the db might be better.
We have had some experience with the first kind of algorithm: 10 sec in DB vs. 0.2 sec in C++.


I hope I could help a bit.

Cheers!
Sebastian
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 17058836
Sebastian .. I think you missed a part ..

"can do this on the sql db using stored procedures but was wondering if bringing over the data to .NET and do the calculations in there
and upload the completed dataset per item to the db would be faster maybe using multiple threads?"

inserting the data back to the db .. it takes a huge amount of calculation to overcome the network overhead associated with a large read combined with n writes over the network.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 44

Author Comment

by:bruintje
ID: 17059155
hmmm both of you confirmed my fears ;-)

saving the network traffic will be worthy the dread of writing stored procedures

thanks for the input and have a great weekend!
0
 
LVL 44

Author Comment

by:bruintje
ID: 17059176
screwed the acceptance, should've been just the other way around sorry
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 17059217
no worries .. I think most are here just to help:) if I traded in all my points .. I think I could get 30 tickets for the skeeball store (and we all know what 30 tickets gets you)

0
 
LVL 44

Author Comment

by:bruintje
ID: 17059252
:)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now