I made a fairly large social network type website and used nothing but inline SQL Statements to access the database (I was new to the language so back off!)
Are there any performance issues when doing it this way as opposed to using a massive XSD DataSet file to handle all the queries? Or is this just bad design?
Thanks!
I think the maintenance issue/cost will have much more impact, will be much greater then the performance impact (if there's any performance impact at all).
If it is SQL Server changing your Inline SQL Statements to be parametrised calls to sp_ExecuteSQL should yield a very significant performance improvement - and would probably be easier to refactor than moving to, say, Stored Procedures instead of inline code.
IME ultimately, Stored procedures that return multiple recordsets (i.e. do several pieces of work / logic, rather than just replace single in-line queries) would yield more performance improvements.
Strictly speaking the SQL performance would be better if you use inline sql statements rather than an XSD Dataset. This is assuming that at a minimum you use parameterized queries (although SQL Server 2005 even optimizes for non-parameterized queries).
The statement that inline sql cannot be optimized by the database engine isn't true. Stored procedures are optimized identically to inline sql with SQL Server.
But whether it makes sense from a design perspective is another story entirely. And whether you are over-optimizing too early is another question.
Stay away from generated typed datasets. The performance isn't that good. If you need the type of functionality, look at LINQ; otherwise just grab the Enterprise Library and go direct.
When you reach real DB performance issues it won't really matter (performance wise) whether you're using stored procedures or direct SQL statements.
Your best bet in that situation is to avoid DB in the first place. In other words, it would be better to plan and architect a good caching mechanism because that will make all the difference when it really comes to serious traffic.
Stored procedures or inline code... again, performance wise (i'm not talking about maintainability, security, ...) simple doesn't matter that much anymore.
When having large amount of data a DB is better in ways of: maintenance and performance. Having stored procedures for example makes it possible to delegate the work and query-optimizations to a professional DBA, no recompiles are needed. When having XSD/XML data files, you have to regenerate your code for each change.
Also look at concurrency: more users, more web-request, more concurrency... you can end up in locks in the data file. You could implement advanced caching mechanisms to gain more performance but that's also extra maintenance and complexity.