| Author |
Topic |
|
Nexzus
Starting Member
14 Posts |
 Posted - 2009-10-02 : 16:39:40
|
| Hi,Just looking for a little advice regarding some app design questions.I'm of the belief that you should offload as much as possible to the database system. Limiting rows, performing calculations, etc - let the database do what its best at on its data. Basically, ask for and return only what data you need, and then send that data off to the presentation. At the same time, SQL can be rather limiting in the kind of data that can be returned.I also know that a call to a database is relatively expensive, CPU wise, network overhead-wise, etc.As a very example, I'm developing a mutual fund tracker site. I have the mutuals funds in one table, and their daily prices in another, both appropriately normalized.If I wanted to grab the name, and corresponding price data for a particular mutual fund identifier, I could either:1) Hit the database twice, once to get the Fund Name via ID, and once to get the price data, via ID.or2) Hit the database once, but bring back more data (in this case, each row with price would also have the fund name.Yes, it's a basic example but hopefully the idea is clear. I guess the question is, is there a point where one method (many hits to grab exact data) is better or worse than another method (single query return possibly extraneous data).Thanks for reading.Adam |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-10-03 : 16:46:25
|
| There is also a third way: make one call to the database and return two result sets.CODO ERGO SUM |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-10-06 : 01:40:29
|
| What's wrong with 2? It gives you what you want in one hit doesn't it? How can you avoid bringing back 'more data' if that is what you need? Just select the rows you want to use.Anyway - always minimise round-trips, so 2 is right. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-06 : 03:56:16
|
| I'd say it depends but 99.9% of the time #2 is the way to go. The performance impact of doing two db-server round trips instead of a simple join is HUGE.- Lumbagohttp://xkcd.com/327/ |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2009-10-07 : 17:50:31
|
| I'm with MVJ: if your presentation layer can handle two result sets returned from one DB call, that's best of breed as it limits IO, CPU and network bandwidth to the absolute minimum. I know it's possible to do this in ASP classic and .NET, not sure what others. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-10-08 : 08:07:29
|
quote:
Originally posted by lazerath
I'm with MVJ: if your presentation layer can handle two result sets returned from one DB call, that's best of breed as it limits IO, CPU and network bandwidth to the absolute minimum. I know it's possible to do this in ASP classic and .NET, not sure what others.
If you are advocating doing a join at the client rather than the DB then you are dead wrong. |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2009-10-08 : 13:10:12
|
| Why would you need to join these record sets at the client at all? Unless I misinterpreted, Adam only needs the name of mutual fund as a single, separate record set and not per each row of its daily price. Here's a description of how to do this in ASP from 4GuysFromRolla:http://www.4guysfromrolla.com/webtech/083101-1.shtml |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-10-08 : 19:18:15
|
quote:
Originally posted by lazerath
Why would you need to join these record sets at the client at all? Unless I misinterpreted, Adam only needs the name of mutual fund as a single, separate record set and not per each row of its daily price. Here's a description of how to do this in ASP from 4GuysFromRolla:http://www.4guysfromrolla.com/webtech/083101-1.shtml
Sure - that's my point. You would join at the database. I thought you were advocating 2 recordsets, one for the fund and one for the price, which would be wrong.The correct way isselect f.name, p.price from fund finner join price p on f.fundid=p.fundIDwhere ......What you are suggesting is fine (in fact prefereable) when the data is unrelated.If it's related then relate it in your query. |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2009-10-08 : 20:43:05
|
| We are not on the same page here. I am indeed advocating using 2 record sets for this purpose. I see a database joined query as returning [N-1 * LEN(Fund.Name)] more data than is necessary, where N is the number of rows in the result set. If you are only in need of a single value, why return it on every row? Simpler code at the front end? It's certainly not for speed and a desire to minimize system resources. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-09 : 02:54:30
|
I also agree that 2 recordsets in a single shot is the best for this purpose. I'm not really sure why I proposed a join but I know I used both methods in my days as a webdeveloper. I also actually used a 4th option which was kind of nasty but did the trick:SELECT FundName, NULL, NULL, NULL FROM fund_names WHERE ID = @FundIDUNION ALLSELECT Col1, Col2, Col3, Col4 FROM fund_details WHERE ID = @FundIDI wouldn't recommend this to anyone though  - Lumbagohttp://xkcd.com/327/ |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-10-12 : 07:31:47
|
| Ok, if there are multiple rows per fund then 2 statements might be quicker. I thought it was a table with funds and a table with a single daily price so the useful data per fund comes back on one row.As usual, it's always best do both and measure them. |
|
|
|
|
Best Practices Advice Needed