Aaron N. Cutshall (8/30/2016)
Ralph Hightower (8/30/2016)
Switching the patient lookup to a stored procedure increased the performance of that phase of the application by 25%.
Ralph, it's all in how the stored procedure was written and how the data is accessed. If it's done properly using a set-based methodology then performance could improve (of course, assuming indexes are appropriate, etc.). T-SQL is very much set oriented and in fact suffers from procedural processes as a result. So when T-SQL is written in a procedural fashion performance will not be optimal.
Agreed, but I think there is something to be said for reducing queries sent to the database, and responses back to the application. The best query is the one not run. Sure, a query is not much data over the wire, and connection pooling can help to minimize handshaking, but it all adds up, and there is overhead in the application waiting for a response before potentially sending that second optional query. There's also potential for network packets to be dropped or otherwise needing to be resent. Ralph's two original queries, combined into a stored proc, even if they are RBAR loop extravaganzas would still benefit from that reduced network IO. Maybe they are already highly optimised and the two tables have appropriate indexes on them. In that case the performance improvement will likely be directly dependant on the ratio of how often that second query is actually needed, because as Ralph pointed out, its optional. Maybe that accounts for why it's 25%
On another note, there might be potential to improve the application by adding functionality to look up multiple people at once. If you find the application is rapid fire calling that stored proc to look up single persons at a time, then you have exactly that opportunity. Or if in the application, the user is allowed to select a list of people and check they exist, and your application code is going thought that list and calling the stored proc once for each person in the list, then, well, don't do that. Pop them into a table valued parameter and pass that to a stored proc, then in the sproc, join that table param to the target table. There's other ways to do that too, like passing XML as a string parameter. Or JSON if you have SQL 2016. I like the table valued param way. There's good ways to handle them in C# too. Not really in other languages though unfortunately. This is the authoritative article on the subject http://www.sommarskog.se/arrays-in-sql-2008.html and Erland Sommarskog has been updating it too I notice 🙂