April 3, 2014 at 8:55 am
I have a new developer that recently started.
He is using LINQ and he approached me on some performance\connectivity issues which turned out to not be related to SQL Server.
I goggled LINQ and I did not like what I saw.
I discouraged him from using it and wondering if banning the use of LINQ would be warranted?
Your input would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2014 at 9:13 am
at our shop, we use a lot of LINQ; generally, the developers write whatever they want, and if they hit a performance block, then that call gets modified to use a stored procedure; i'll write the stored procedure for them, and add/modify any indexes that might be appropriate to support it.
that resolves the performance issue for them.
I'm usually the one who identifies the slow queries and bring them to the developers attention;
i find out they are slow because they show up in an extended events trace for slow queries i capture on the production server.
generally, i'd say i do a couple of conversions like that a month.
I treat it as a support issue; i certainly would not think about drawing a line as the DBA Nazi and say "No LINQ For You!'
Lowell
April 3, 2014 at 9:18 am
ok, I appreciate the feedback.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2014 at 9:27 am
I agree with Lowell 10000%. There is no reason to dictate that the development is not allowed to use a certain technology because the DBA mandates it. I like your approach Lowell. Allow them to do what they want and then work to fix issues as they arise. This has a great sense of teamwork which is so often lost these days.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2014 at 9:48 am
I regret the post.
I do everything to help my users.
I check the execution plans, add missing Indexes, rewrite the code, etc.
I would never do anything to hamper development or progress.
I ask for him to send me his code but he did respond to my request.
I tried capturing the code but I do not see any connections to the Server.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2014 at 10:08 am
Welsh to capture the code, for example, i decided the dmv's were not enough; not everything exists in the cache to check for performance issues.
Instead i started with a rollover DML trace, capturing pretty much all events on a specific database;
then i created a job that would query that trace and insert results into a permanent Audit Table, like every 15 minutes, looking for any thing WHERE DATEDIFF(SECOND, StartTime, EndTime) > 10
that gave me all queries that took longer than 10 seconds.... a good starting point for me. i could review the TextData, and identify which items are "ok" to run long, and which were not.
the ones that should not run long(think big batch jobs) , i'd address just as you described...indexing, examining execution plans etc.
once you see an example oir two of the linq queries, you can see an offending query and say "i KNOW LINQ created that"
if you cannot tweak it with indexes, it's simple to create a tuned procedure that would return that same data, and you just get with the dev team to change the db layer int he app to use the new alternative.
i later changed my trace to extended events, mostly because Grant Fitchey beat the drum enough about them that it sunk in; i dragged my feet for too long because i could script a trace backwards and forwards, and it was familiar, vs extended events, which were alien to me.
Lowell
April 3, 2014 at 10:18 am
Welsh Corgi (4/3/2014)
I regret the post.I do everything to help my users.
I check the execution plans, add missing Indexes, rewrite the code, etc.
I would never do anything to hamper development or progress.
I ask for him to send me his code but he did respond to my request.
I tried capturing the code but I do not see any connections to the Server.
Thank you.
No need to regret the post. I certainly hope you did not think I meant in any way to indicate that you do not try to help your users or hamper development. That was not my intention in any way shape of form. I was merely complimenting Lowell on the way he handles it. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2014 at 10:50 am
Thanks for the good advice.
I should have done more research before asking the question.
Upon my request for information the developer gave me the connection string and it was the wrong server and database. I'm standing by to assist him as needed.
I monitor the server for issues constantly and I occasionally see processes that he is running but no issues so far.
My tail is behind my legs. :blush:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2014 at 4:08 pm
Lowell (4/3/2014)
Welsh to capture the code, for example, i decided the dmv's were not enough; not everything exists in the cache to check for performance issues.Instead i started with a rollover DML trace, capturing pretty much all events on a specific database;
then i created a job that would query that trace and insert results into a permanent Audit Table, like every 15 minutes, looking for any thing WHERE DATEDIFF(SECOND, StartTime, EndTime) > 10
that gave me all queries that took longer than 10 seconds.... a good starting point for me. i could review the TextData, and identify which items are "ok" to run long, and which were not.
the ones that should not run long(think big batch jobs) , i'd address just as you described...indexing, examining execution plans etc.
once you see an example oir two of the linq queries, you can see an offending query and say "i KNOW LINQ created that"
if you cannot tweak it with indexes, it's simple to create a tuned procedure that would return that same data, and you just get with the dev team to change the db layer int he app to use the new alternative.
i later changed my trace to extended events, mostly because Grant Fitchey beat the drum enough about them that it sunk in; i dragged my feet for too long because i could script a trace backwards and forwards, and it was familiar, vs extended events, which were alien to me.
Lowell,
Thanks. I have created Database Triggers to capture and store information.
Thanks again for the tip.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 3, 2014 at 5:53 pm
Oh, I just don't like triggers for that kind of thing, I'd even prefer a server side trace or Xe's EE's once I learn to use thamproperly (depending on version) and tere are various alerts which can be configured, depending upon what you want to track. But while I don't like "me too" answers I agree with te guys. One thing I would say though is if its a non-trivial app, design first, not code first. Other than Mom 'n' Pop's Corner Shop websites, or two screen apps - the parable of the Three Little Pig still applies.
If it's a 5 simple screens and 4k rows, it's not worth being the Relational Taliban over, lash the backend together, let them loose.
Also, I have a fairly well practiced description of how SQL Server uses memory, CPU cache, buffer memory and stolen pages. Slightly different for Dev's and network Guys, but good ones can get "Don't throw shit passthrough against a bad design" and "Yes, I know it was running at 95% memory usage, this is not a reason to reboot because the server's "Running out of memory", that's how I set it up, I'm very unhappy, here's why ... ". Good Devs hate writing inefficient code, let them know why XYZ is crap in a 10 minute chunk. Hell, things change - you will learn shit when they push back on occasion.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 4, 2014 at 7:04 am
I do not like triggers either. Since it only fired when an abject was created, Dropped or modified it was not firing off that much. But now I know better.
I had an issue with a consulting firm dropping objects and blaming it us.
When they cried wolf I showed them that they were doing the dropping.
They are gone now and no more issues.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 4, 2014 at 7:36 am
This is my take on LINQ to SQL. I have found that it does not handle large data sets well. It can be slow. And, because you do not have the SQL code, you can't tell what is causing the problem. I use LINQ on small datasets. If I need to traverse large datasets, I may still use LINQ, but, I will use a stored procedure that has been proven to be efficient. By large I mean about 100,000 records or more in a single query. Where I work, most dataset are well below that. I have one project I'm completing where I am not using LINQ to SQL at all.
LINQ should not be banned, but, experience tells me that it has its place. I now recognize whent to use it and when to not use it at my shop. Of course, you may have different results.
Tom
It's fir day today... :w00t:
April 4, 2014 at 7:48 am
OCTom (4/4/2014)
This is my take on LINQ to SQL. I have found that it does not handle large data sets well. It can be slow. And, because you do not have the SQL code, you can't tell what is causing the problem. I use LINQ on small datasets. If I need to traverse large datasets, I may still use LINQ, but, I will use a stored procedure that has been proven to be efficient. By large I mean about 100,000 records or more in a single query. Where I work, most dataset are well below that. I have one project I'm completing where I am not using LINQ to SQL at all.LINQ should not be banned, but, experience tells me that it has its place. I now recognize whent to use it and when to not use it at my shop. Of course, you may have different results.
Tom
It's fir day today... :w00t:
One of the other developers here knew that I am pretty savvy with sql so he asked me to help him debug a query that was created with EntityFrameworks. OK, I know the topic here is LINQ but it kind of the same thing. Anyway, he sends me the query and it was over 10k lines in SSMS and had somewhere on the order of 100 or so subselects in it. He didn't quite understand why I told him I couldn't debug it without some frame of reference. He wasn't really sure what the query was supposed to do. Needless to say I handed it back to him untouched and told him we could look at when he could tell me what it does. He is no longer here and I never heard or seen that monstrosity again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2014 at 5:14 pm
Sean Lange (4/4/2014)
OCTom (4/4/2014)
This is my take on LINQ to SQL. I have found that it does not handle large data sets well. It can be slow. And, because you do not have the SQL code, you can't tell what is causing the problem. I use LINQ on small datasets. If I need to traverse large datasets, I may still use LINQ, but, I will use a stored procedure that has been proven to be efficient. By large I mean about 100,000 records or more in a single query. Where I work, most dataset are well below that. I have one project I'm completing where I am not using LINQ to SQL at all.LINQ should not be banned, but, experience tells me that it has its place. I now recognize whent to use it and when to not use it at my shop. Of course, you may have different results.
Tom
It's fir day today... :w00t:
One of the other developers here knew that I am pretty savvy with sql so he asked me to help him debug a query that was created with EntityFrameworks. OK, I know the topic here is LINQ but it kind of the same thing. Anyway, he sends me the query and it was over 10k lines in SSMS and had somewhere on the order of 100 or so subselects in it. He didn't quite understand why I told him I couldn't debug it without some frame of reference. He wasn't really sure what the query was supposed to do. Needless to say I handed it back to him untouched and told him we could look at when he could tell me what it does. He is no longer here and I never heard or seen that monstrosity again.
"If you don't know what you're coding, keep your hands off the f*ing keyboard" is still good advice. That is still ignored far too often. I often have to remind myself, to be fair
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 4, 2014 at 5:46 pm
Lowell (4/3/2014)
Welsh to capture the code, for example, i decided the dmv's were not enough; not everything exists in the cache to check for performance issues.Instead i started with a rollover DML trace, capturing pretty much all events on a specific database;
then i created a job that would query that trace and insert results into a permanent Audit Table, like every 15 minutes, looking for any thing WHERE DATEDIFF(SECOND, StartTime, EndTime) > 10
that gave me all queries that took longer than 10 seconds.... a good starting point for me. i could review the TextData, and identify which items are "ok" to run long, and which were not.
the ones that should not run long(think big batch jobs) , i'd address just as you described...indexing, examining execution plans etc.
once you see an example oir two of the linq queries, you can see an offending query and say "i KNOW LINQ created that"
if you cannot tweak it with indexes, it's simple to create a tuned procedure that would return that same data, and you just get with the dev team to change the db layer int he app to use the new alternative.
i later changed my trace to extended events, mostly because Grant Fitchey beat the drum enough about them that it sunk in; i dragged my feet for too long because i could script a trace backwards and forwards, and it was familiar, vs extended events, which were alien to me.
Thanks for the great advice!:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply