August 30, 2016 at 7:06 am
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.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
August 30, 2016 at 7:12 am
It seems to me as if the modern development method is the problem.
Get your user-stories implemented as soon as possible.
The database is «persistence layer». It might as well as be a collection of Excel-files.
You old people don't understand how things are done nowadays.
It is not our problem.
August 30, 2016 at 7:42 am
Sean Redmond (8/30/2016)
It seems to me as if the modern development method is the problem.Get your user-stories implemented as soon as possible.
The database is «persistence layer». It might as well as be a collection of Excel-files.
You
old peopleworkshy youngsters don't understand how thingsare done nowadayswork any more.It is
not oura major problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2016 at 7:55 am
Here is (if I remember correctly) a parametric search that took 15 seconds to execute. It took me considerably longer to make sense of this SQL. This is what is meant by gobbledigook. (I've replaced all of the user-defined DB-object names with letters)
I am so sorry, did not mean to offend but the last time I heard the word gobbledigook it was meant in an entire different way. By the way I am not a DBA, I am a developer trying to do some DBA in between because I had no other option.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
August 30, 2016 at 7:56 am
I think the argument against store procedure sounds a lot like someone saying we should not use screws because their preferred tool is a hammer.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 30, 2016 at 8:04 am
We're perhaps just kicking a dead horse. I still freshly remember this topic when it was originally published four years ago, and it has come up again numerous times long before that and numerous times since. At least within my personal universe of past experience, ORM was always present to one degree or another. But it was mostly only for niche applications. For a CRM or case management type application, the developer who coded the prototype or initial CRUD screens may heavily utilize something like LINQ or Dapper, but as the application development progressed to more complicated things like: dashboards queries, business logic, and reports; stored procedures would be used.
Personally, I dislike coding CRUD procedures, because it turns into busy work and becomes a distraction with all the numerous tasks that have to be tracked and coordination with QA. If the app developers and QA want to take care of the CRUD screens and leave the heavy data access tasks to those of us who specialize in database development and T-SQL performance optimization, then that's great. I'm not going to impose my will where it's not really needed. It seems to me that ORM is the database development equivalent of sheet rock walls in building construction; it definately has it's proper place and can greatly reduce the cost and completion time for a project... when it's limits are understood.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 30, 2016 at 8:26 am
We started an application many years ago with .NET/C# and ORM technology ( and no DBA ). Over the years it continued to perform largely due to hardware enhancements.
We're now at 64-core servers with 512GB memory and looking to convert from spinning disk Netapp devices to enterprise flash drives. What I'm now trying to introduce, among other things, is what I call a "hurdle" in QA. Since none of our QA environments has anywhere near a production-like load, database access code must run blazingly fast in QA, or it doesn't go to prod, period. We'll see how far I get with this.
Another example of the problem is a 4GB plan cache with 2,500 single-use plans out of a total of about 12,000 plans. The ORM sets parameter character length ( e.g. varchar(31) ) to the actual string length, rather than to the database column length. But this is probably only a portion of the reason for single-use plan bloat.
August 30, 2016 at 8:47 am
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 🙂
August 30, 2016 at 9:21 am
Sean Redmond (8/30/2016)
Have you ever tried to read the SQL generated by a 6-join LINQ statement? It took me 30 minutes just to tease it apart.Here is (if I remember correctly) a parametric search that took 15 seconds to execute. It took me considerably longer to make sense of this SQL. This is what is meant by gobbledigook. (I've replaced all of the user-defined DB-object names with letters)
...where ((@p__linq__0 IS NULL) OR ... ... ... @p__linq__53)
Wow, epic. I wonder about a query with 53 mostly? optional parameters. There's nothing too wrong about that
@param = column OR @param is null
pattern, but what does that GUI look like? Users are crazy if they insist on that kind of flexibility. There's (53+52+51...+1) = ~ 1456 combinations that I'll bet 99.99% have never been used. It would be cool if the queries executed logged the param values actually used, you could then potentially eliminate some of those joins, or make some parameters not optional.
August 30, 2016 at 9:41 am
Indianrock (8/30/2016)
Another example of the problem is a 4GB plan cache with 2,500 single-use plans out of a total of about 12,000 plans. The ORM sets parameter character length ( e.g. varchar(31) ) to the actual string length, rather than to the database column length. But this is probably only a portion of the reason for single-use plan bloat.
It's probably a bigger contributor than you'd think. If each VARCHAR field can hold lengths varying by 30 characters then that is 30x30 variations for a table with two varchars in it.
August 30, 2016 at 9:44 am
Aaron N. Cutshall (8/30/2016)
One of the issues I've found with ORM frameworks is that it trains one into a particular way of thinking. For example, I worked on a project that was very database-centric yet most of the stored procedures were written by .NET developers using a CRUD mentality. This would have been fine had the application been a UI oriented toward single record inserts, updates, and deletes but it wasn't. Therefore, stored procedures were written in a very procedural fashion with conditional logic and loops along with CRUD calls to stored procedures that turned around and called yet another stored procedure which called yet another to perform a single insert, update, or delete with only parameters.Hello people! T-SQL is NOT the same as .NET!! Added to that, for some reason they used GUIDs as their primary key on all the tables! Then they wonder why performance sucked outside of development! >Doh!!<
Conditional logic can potentially be good in a stored proc, but yeah I hear you. I've seen that GUID thing done too. Then every nonclustered index also gets a copy of that super wide GUID 🙂 Not to mention the sorting implications. You can generate sorted GUIDs... but you wouldn't!
I get why app devs like them. Sequences aren't easily made asynchronous, and in Dev environments sometimes multiple devs want to create IDs against a shared backend, and GUIDs make that trivial. But then, using a database side sequence with locking is not such a bad thing.
I had an argument with a software architect about just such a thing. In the end I convinced him that the stored proc would hand the application back an (int) ID for them to use (which was then mapped to an object big int key and stored in the nHibernate ORM... D'oh!) At least that was better than the application passing a GUID to the database for use as an ID. Weird system, data warehouse, orm behind the application, plus in between oltp database for user defined sets of customers to be used in Data warehouse queries. I didn't really object to the ORM setup in that case, because the application stored a lot of small application state data and user preferences stuff that was pretty small data, and not a huge concern. It was only those customer sets that had to be joined to the data warehouse that were concerning. Don't want to be joining a DW to an ORM! Equally don't want transactions in the DW, so third database was logical.
I think sometimes people just want to control all the logic in their domain, no matter how inappropriate.
August 30, 2016 at 9:57 am
David.Poole (8/30/2016)
Indianrock (8/30/2016)
Another example of the problem is a 4GB plan cache with 2,500 single-use plans out of a total of about 12,000 plans. The ORM sets parameter character length ( e.g. varchar(31) ) to the actual string length, rather than to the database column length. But this is probably only a portion of the reason for single-use plan bloat.
It's probably a bigger contributor than you'd think. If each VARCHAR field can hold lengths varying by 30 characters then that is 30x30 variations for a table with two varchars in it.
And it might even be a good thing that the ORM query plans are not being reused if they are anything like that epic long 6 table join query with 53 optional parameter (sniffing).
But hopefully that char(30) param = varchar(31) column comparison is not an implicit conversion that is non-SArgable. It's not right? If it is then wow ORM, not good.
August 30, 2016 at 10:55 am
It can definitely become a "hot mess." The main database has now grown to 5TB and combines OLTP with OLAP-reporting style queries. I shouldn't blame everything on the ORM ( which is now mainly entity framework with some use of LINQ ), because I really don't know how much of the sql is coming directly from C# code. I just looked at the table-valued-parameter article mentioned here earlier -- ET and LINQ don't support it 🙂
The "business-side" doesn't help since they've allow customers to get used to running double-wildcard LIKE queries via the browser application. Then add trying to capture too much session-state data in an aspstate database. Once you give the customer ( or even your own internal Operations staff ) the green light to input only 5 characters of a 17-character vehicle vin number ( for example ), it's hard to take that away. And I'm suspecting that more often than not they have an electronic document that would allow them to copy and paste the full VIN into the app. So you're left "suggesting" that performance will be better if you put all or most of the VIN in there. I think many customers still think they have their own database.
August 30, 2016 at 4:15 pm
Erland's article may not be current. In discussing TVP's and the ability to use them with Entity Framework, one of our developer's pointed out the article below. However, we make almost no use of stored procedures so that would have to be worked around.
Erland Sommarskog, SQL Server MVP. Latest revision: 2016-08-21.
August 30, 2016 at 5:00 pm
Indianrock (8/30/2016)
Erland's article may not be current. In discussing TVP's and the ability to use them with Entity Framework, one of our developer's pointed out the article below. However, we make almost no use of stored procedures so that would have to be worked around.Erland Sommarskog, SQL Server MVP. Latest revision: 2016-08-21.
http://www.c-sharpcorner.com/UploadFile/78607b/using-table-valued-parameters-in-entity-framework/
Viewing 15 posts - 91 through 105 (of 142 total)
You must be logged in to reply to this topic. Login to reply