And don't forget about sp's providing better performance on the database.

Post #415,782 by mmoffitt 12/1/16 1:37:54 PM 12/1/16 1:37:54 PM Reply	And don't forget about sp's providing better performance on the database. bcnu, Mikem I think religion should be treated with ridicule, hatred and contempt. And I claim that right. Christopher Hitchens.
Post #415,793 by malraux 12/1/16 5:29:11 PM 12/1/16 5:29:11 PM Reply	That depends On architecture, caching, data usage patterns, scaling, and a myriad other concerns too. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,817 by mmoffitt 12/2/16 11:07:32 AM 12/2/16 11:07:32 AM Reply	For OLTP applications, I disagree, with MS Sql Server anyway. Sure, you can optimize the server for ad hoc, force parameterization, etc., and vastly improve ad hoc performance, but overall (including network traffic) stored procedures on MS Sql Server for an OLTP application will outperform ad hoc. Unless you can convince me that this guy ( http://www.sqlbits.com/sessions/event14/Stored_Procedures_vs_Ad-Hoc_SQL_Performance_Showdown ) is completely wrong, I'm sticking with OLTP apps benefit performance-wise over ad hoc. If I'm wrong about that, what have I missed? (and yes, I am genuinely asking). bcnu, Mikem I think religion should be treated with ridicule, hatred and contempt. And I claim that right. Christopher Hitchens.
Post #415,824 by drook 12/2/16 2:39:34 PM 12/2/16 2:39:34 PM Reply	Performance is the least of my concerns at this point IMO queries should be written first for correctness, second for clarity, third for performance. (Just like all code.) Correctness includes returning the right results and maintaining data integrity. You have to do both as an absolute baseline before anything else matters. After that, clarity/readability is far more valuable than performance 90% of the time. -- Drew
Post #415,852 by malraux 12/4/16 10:59:35 AM 12/4/16 10:59:35 AM Reply	Until it isn't any more. :-) But generally I agree. Don't prematurely optimize. Stored procedures are dangerously seductive for that. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,850 by malraux 12/4/16 10:58:19 AM 12/4/16 10:58:19 AM Reply	Performance != throughput Which is why I said it depends. A straight through speed test, even a comprehensive one, tells you little about how a system performs under full load. The database is the hardest piece of architecture to scale simply. When you reach the point where too much is going on for a single server, you start to have problems. If large portions of your logic are in the database, you really have problems. This isn't theoretical, either. I've seen Oracle databases running on the largest box Sun made at the time moved to RAC and having horrific problems due to intense stored procedure usage. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,854 by drook 12/4/16 11:07:14 AM 12/4/16 11:07:14 AM Reply	Interesting Since all the logic in a proc has to happen somewhere, I would assume doing it outside the DB would take longer on head-to-head, no-load comparisons. As long as the logic happening in the proc has to be atomic, wouldn't that continue to get worse as things scale? In fairness, I'm not a DBA so I'm assuming there's something in DB world that's like the multiple small compute instances you can do with app servers. I've never built out DB infrastructure at this scale though, so I'm coming from the standpoint that I can't be the only one trying to do this so someone must have solved this. -- Drew
Post #415,874 by static 12/5/16 4:05:45 AM 12/5/16 4:05:45 AM Reply	I've looked at DB scale out, though not recently. In a previous job, we ran into questions about how to tackle that kind of problem. Read-scaling can be done with simple replication, if you don't need transactions. We did that, and that was reliable. It helped I was also one of the web programmers and could build the database abstraction layer to load-balance SQL Selects. Another method is sharding, which seems to have matured in the last few decades. We would've had to do it ourselves when we looked at the concept and I wasn't sure I was up to doing that! The Wikipedia article has more information. Wade.
Post #415,884 by malraux 12/5/16 9:15:44 AM 12/5/16 9:15:44 AM Reply	Eventually the amount of work exceeds the capacity of a single machine to do it At which point you have a distributed system whether you want it or not, and a distributed database is one of the hardest things to get right. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,860 by boxley 12/4/16 5:15:50 PM 12/4/16 5:15:50 PM Reply	sure, lets take the speed out of the cpu and replace it in multiple small connected units, :-) always look out for number one and don't step in number two

Welcome to IWETHEY!