January 13, 2008 at 7:39 pm
Me and my teammate are brainstorming whether which data access method are we going to use. He's insisting on using DLINQ while I opt to still make use of stored procedures for scalability and maintenance.
I mean I know storedprocs is partially supported in DLINQ but what were talking here is that he wants to do everything in DLINQ (select, insert, update, delete).
I have checked how do DLINQ throw SQL statements in the database using SQL Profiler. It seems that it creates a dynamic sql and use sp_executesql to execute it. I dunno if this has any performance impact or anything.
My other reason for opposing the use of DLINQ is that maintenance will be a bit harder because everything will be on the application code. For example, in order to improve a performance of a certain query, you need to edit the codes and recompile again. I don't even now if it is possible to do such thing in DLINQ in the first place.
Anyone who has some opinion on this?
January 13, 2008 at 8:19 pm
The bigger issue I think is that you haven't provided any abstraction layer for performance tuning and you're not taking advantage of the power of SQL Server. Stored procedures can be optimized and changed around as needed to meet performance needs. If you need to make a change, then you're creating more work in the application. Instead if you use stored procedures, then the schema can be changed and the code tuned.
January 13, 2008 at 8:31 pm
Thanks! Point taken, I'll have it listed to prepare for rebuttal 😀
January 15, 2008 at 2:16 am
I've been through a similar evaluation process on a recent project and I think it's important to employ each of the tools/technologies you use in the role for which they are best suited. i.e. the RDBMS is specifically designed and optimised for data storage and retrieval. DLINQ I think is very clever but excels at ORM. DLINQ can still be achieved using sprocs (See ScottGu's blog) which I think is the best compromise.
Not only does this give you a better performing solution it means maintenance, tuning, updates etc are much easier as your application develops.
Just my two p'enneth.
January 15, 2008 at 2:17 pm
Stored procs, imho, are the better approach. Separate the database access code from the app. Initially it may seem like more work, but the CRUD stored procs don't take long to write and, if you need some side effects to occur upon data access or modification, you need only modify the procs rather than code such things into your application code.
You can also enforce security better on SQL Server as your application users only need access to the procedures rather than needing access directly to the tables.
January 15, 2008 at 6:20 pm
Thanks for sharing your opinion guys. It makes me more firm on my decision 🙂
As what Dan have said, each tool/technology is meant for one specialized function, like for RDBMS it is specifically designed and optimised for data storage and retrieval. DLINQ excels at ORM.
January 16, 2008 at 1:41 am
Stored procedures. DLinq is just more ad-hoc SQL code that I can't tweak to swell my proc cache.
From what little I know about Linq, I'd prefer to use stored procs to populate the entity framework and then the devs can write linq against the entity framework to their heart's content.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2008 at 4:44 am
To be fair, DLINQ, with it's dynamic SQL, is good for quickly creating prototypes. Also, if you decide to cache some data as XML, it saves having to know too much about XPATH/XQUERY.
Due to security issues, performance tuning etc, I would be inclined to refactor the data asscess layer so that DLINQ uses SPs before the code goes onto a production server.
I have not yet looked at PDLINQ (Parallel DLINQ) but suspect the same issues apply.
February 1, 2008 at 12:55 pm
LINQ in all its versions is (IMHO) nothing more than the Gentle.NET Framework which ceased development in 2006 http://sourceforge.net/projects/gopf with a few improvements.
I would continue to use stored procedures for all the reasons others mentioned above such as the ability to tune the actual SQL. LINQ is good if you have no idea how to write advanced SQL or know nothing beyond basic, insert, update and delete commands then LINQ is meant for you. However, I will concede that rapid prototyping should be marginally faster using LINQ. But do not be surprised when your application has performance issues or scales poorly if you carry this over to production.
What is most damming about LINQ for serious use is that the Query Optimizer Team at MS to the best of my knowledge and talks with some of them at conferences had little to no participation in its development. This is rather serious considering they are the subject matter experts on all things query related.
Run away from LINQ and bash those on the head who insist on using it in production with a reality check.
--Dave
March 3, 2008 at 2:44 pm
In a similar vein I have solutions architect that has come up with a 5 layer architecture
1. Data Access Layer
2. Entity Layer
3. Business Logic Layer
4. Binding Layer
5. Presentation Layer
I don't have problem with this but he says that these days the DAL should talk directly to the database an all database access control should take place in the DAL. In other words no stored procs.
This gives me the screaming heebie jeebies.
As far as I can tell his architecture is all about separating out the various layers and yet he seems to be saying the DAL should be lumped together.
The basic coding design is that the functionality is encapsulated and isolated within the layers.
His method obviously requires direct read/write access to tables which seems to me to be a direct contradiction of encapsulation. It seems to be saying "leave everything wide open". It also seems to be saying that although objects have private and public properties/methods etc the database layer should basically be nothing but public access.
I've got to be missing something fundamental here. Forgive my ignorance but surely the DAL should be split into two layers in its own right?
Data Access Code talks to Stored Procs talks to Data
There is enough code out there to auto-generate the CRUD procedures. I've seen one particular solution blast through a 2,500 table database providing all the CRUD procedures in under 10 minutes so I am not sure what the problem is with stored procs.
Also, sp_executesql has a limit of 4000 characters so there seems to be a limitation placed on what the DAL should be able to do.
The other thing I don't understand is at present a developer can pass a call to a proc called (for the sake of argument) "ListCurrentDeals" with perhaps 5 parameters. This means a few bytes down the wire to the server and quite a hefty chunk of data flying the other way.
If it was dynamic SQL then surely this would be a massive block of SQL going down the wire and a hefty chunk of data flying the other way.
As my ListCurrentDeals proc is called 20 times per second surely the proposed architecture with its dynamic SQL equivalent is going to cause network problems?
Don't flame me, I'm asking honest questions not trying to be defensive.
March 3, 2008 at 3:02 pm
I don't have problem with this but he says that these days the DAL should talk directly to the database an all database access control should take place in the DAL. In other words no stored procs.
I think LINQ and its ilk have a place, and having the DAL is a good thing, but to me talking directly to the database includes calling SP's since SP's are, for lack of a better word, objects in the database. My main reason for being a proponent of SP's is that I do not have to grant direct access to any tables if everything is done through SP's. I think performance is important too, but, based on a lot of what I have read, the performance impact of SP's vs. ad-hoc SQL is less with newer versions of SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 5:45 am
I think Store Procedure is the best Choice. I am also having the same problem either to use Store Procedure or DLINQ, because the other guy in my office insisting use to use LINQ.
So, what we have decided is to use Store Procedure and use LINQ to call these store procedure.
Right now we are using SQL Server, but for the same product we have requirements to migrate to ORACLE Database for other clients, so tht's was another problem for us in purely using LINQ, becasue it effects our application logic as well.
To cope with this solution, I have created another Layer on DLINQ, wrapper classes so when we'll migrate to another DB ... we can simply remove LINQ part and use wrapper classes for ODP.net.
any opinions in this reqards .....
May 12, 2008 at 11:13 pm
You're right- I'd use stored procs for data access and wrap them in a data access layer that returns scalar types or framework objects. I'd then wrap these in a data service layer that returns business objects to the application.
I wouldn't use LINQ since its a new technology and if youre unsure about it don't do it- the learning curve alone will cost you big time on the project.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply