Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am writing an application that will use many tables and I've been told that I must not use stored procedures in this project because it will be too slow.

It has been suggested that I use TSQL. I have only used stored procedures until now.

Is using TSQL different? Is this the way to go for faster data access or are there other methods?

share|improve this question
    
If you want to avoid using 'Stored Procedures' then fine, but whoever told you that stored procedures will be too slow clearly doesn't know what they are talking about. I get the feeling that whoever told you this is the type of person who prefers 'SQL Injection' style syntax because its 'quicker to develop'. –  Dal Aug 31 '11 at 9:55

3 Answers 3

You can access data in the following ways (assuming you are using .NET) - amongst others:

(1) using ADO.NET (and LINQ)

(2) using ODBC (not recommended if you can use other options)

(3) using T-SQL via ADO.NET

(3A) using T-SQL via LINQ

(4) using SQL Server CLR (C# code on the server side)

The debate between using (1) and (3) is an ongoing debate, however, if you don't have intentions to change your database to another one, it is fairly common to program the database layer in T-SQL. T-SQL is mostly used to do basic Create, Read, Update and Delete operations as well as triggers processing. Unlike plain ADO.NET, T-SQL may be more secure since it keeps your code SQL free.

Most ORM tools generate code for either (1) or (4). So your tool may affect your decision.

If you have complex logic and intensive calculation or if your require several calls to the database before your unit of work is completed, you could then use (4).

You may be interested to know more about this in the following link: http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures

share|improve this answer

I have to say I'm a bit puzzled by the initial question. Presumably when you wrote the stored procedures you used T-SQL ? Also I'm not convinced that stored procedures will actually be especially slow, or slower than other data access methods. Working with stored procedures does have its issues, but in my experience speed is not normally one of them.

If your client doesn't want to use stored procedures, probably there is nothing you can do about it. At the end of the day it is all about keeping the punters happy.

share|improve this answer

Speed probably shouldn't be your only consideration. In terms of productivity and maintainability T-SQL can have problems because it isn't a full, general purpose programming language. It doesn't support object oriented or functional programming, and even passing data from one stored procedure to another can a bit funny in some cases. There are far fewer code libraries available. Debugging support is not as good. Other than some of the raw SQL statements, it will probably be very hard to port. The only environment the T-SQL can run in is one that you have to pay for (Express Edition withstanding).

You can now use the SQL Server CLR to get around some of these issues, but there are a lot of limitations imposed so that Microsoft could integrate it with SQL Server and ensure badly written code in the CLR doesn't trash the whole database.

On the other hand, if enough of your business logic is in the database you don't need to worry about object-relational mapping, and it will probably run faster than even a hand-coded object oriented data access layer.

Most people decide to put most or all of their business logic outside the database, but it is possible to put most of it in one.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.