Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Opinion of use of LINQ Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1558031
Posted Thursday, April 3, 2014 9:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1558040
Posted Thursday, April 3, 2014 9:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1558043
Posted Thursday, April 3, 2014 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,270, Visits: 12,100
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1558051
Posted Thursday, April 3, 2014 9:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1558065
Posted Thursday, April 3, 2014 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1558077
Posted Thursday, April 3, 2014 10:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,270, Visits: 12,100
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1558087
Posted Thursday, April 3, 2014 10:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 4,186, Visits: 4,264
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.



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/

Post #1558109
Posted Thursday, April 3, 2014 4:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1558289
Posted Thursday, April 3, 2014 5:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 338, Visits: 3,277
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.
Post #1558323
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse