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 Friday, April 4, 2014 7:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1558484
Posted Friday, April 4, 2014 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 2,553, Visits: 3,800
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...
Post #1558498
Posted Friday, April 4, 2014 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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...


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 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 #1558511
Posted Friday, April 4, 2014 5:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 338, Visits: 3,289
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...


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.
Post #1558681
Posted Friday, April 4, 2014 5:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 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.



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/

Post #1558686
Posted Friday, April 4, 2014 6:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264
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...


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.


Hmmm interesting.


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 #1558688
Posted Saturday, April 5, 2014 11:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
One of the biggest problems I've had with Linq to SQL (and other ORMs, as well) is the data-type mismatching it sometimes does. Most of our character based columns are VARCHAR but Linq to SQL sometimes fails to realize that and passes NVARCHAR literals to be found. That makes the code Non-SARGable (basically, can never do an Index Seek) because, due to data-type precedence, it causes the entire table to be scanned so it can implicitly convert the entire column to NVARCHAR to do a lookup.

Once I told the Developers about the problem, they've taken steps to use the correct data-types. I just don't know what those steps are but I'm sure that my assistant DBA (Google) could find the steps for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558751
Posted Sunday, April 6, 2014 3:35 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 338, Visits: 3,289
[quote]Welsh Corgi (4/4/2014)
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.[/quote
That's the problem with Abject Oriented Programming (I'll get my coat)


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1558867
Posted Sunday, April 6, 2014 6:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:43 PM
Points: 4, Visits: 83
Hello all,
I moved into data warehouse after being a hard core c# developer for multiple years. My take on LinQ to SQL is as below.

1. Nothing should be banned. Everything is there for a reason and adds heaps value if used appropriately.
2. LinQ to SQL is great for small data sets.
3. It is not advisable for complex sql joins as it generates sql every time and executes raw sql statements on db server there by re-generating execution plan every time, etc.
4. It is very hard to keep track of what has been changed by developers in the code layer which might affect db performance.
5. It makes further enhancements around that area/section hard as now things would require changes to code and sql both. Any changes to code layer would require, release which is normally more time consuming and tricky compared to releasing sql server objects.

There could be many more reasons to avoid it if it is possible. I kind of like to keep data specific codes encapsulated within stored procedures for maintenance reasons.

Hope it helps.

Aditya Daruka
Post #1558877
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse