SQL Server query

[acidman1]

Whenever i see an SP i feel like ive been raped. This one SP i ran across had 4,000 lines in it.

Lol I have come across sp's like that too, that is more bad design and coding and could apply to any programming environment. I have seen .Net code that has thousands of lines to perform simple case statements.

 

[Fuma]

I hate inline SQL when developing desktop (WinForms/WPF) applications.

 

[semaphore]

Lol I have come across sp's like that too, that is more bad design and coding and could apply to any programming environment. I have seen .Net code that has thousands of lines to perform simple case statements.

Yes agreed it can be applied to any environment. There will always be the people who love sprocs because thats how they learnt, afraid of change its understandable. But in the realm of high scalability and sharding/federation. Yeah no thanks. And no amount of arguing will ever change my view and thankfully my team are in full agreement.

 

[semaphore]

I hate inline SQL when developing desktop (WinForms/WPF) applications.

Ye inline can also become mangled.

I use dapper on some of my projects.

https://code.google.com/p/dapper-dot-net/

 

[|3ash3r]

Funny. How one of the biggest systems in SA runs perfectly fine without them.

What is the name of this big system you speak of?

 

[Bar0n]

Its that flawed logic that lets stored procedures proliferate by the thousands.

So why does the rest of the world still bother with it? Have you enlightened them and spread your anti-SP gospel? Are you telling me something like Facebook does not make use of stored procedures?

 

[semaphore]

So why does the rest of the world still bother with it? Have you enlightened them and spread your anti-SP gospel? Are you telling me something like Facebook does not make use of stored procedures?

http://highscalability.com/blog/201...tored-procedures-to-update-social-graphs.html

Our numbers are growing by the day, we will soon start an organised religion and preach our sermon to the world.

 

[Bar0n]

http://highscalability.com/blog/201...tored-procedures-to-update-social-graphs.html

So what? That doesn't say they don't use stored procedures. That means they've developed an alternative to the stored procedure. They haven't done away with SPs. And this is just in one single case.

a multiple statement query, which acts like a run-time stored procedure

All the logic runs on the server side, with no intermediate network hops.

All the logic is still done on the DB server side.

 

[BobsLawnService]

Semaphore, so you've written off stored procedures beecause you have had to deal with one badly written one? That is crazy.

Do you have any real reasons for thinking that they are evil?

 

[semaphore]

So what? That doesn't say they don't use stored procedures. That means they've developed an alternative to the stored procedure. They haven't done away with SPs. And this is just in one single case.




All the logic is still done on the DB server side.

Yes it runs on the DB server but the actual logic does not SIT on the db or did you miss that part entirely? The call carriers the code through. My argument is on mixing business logic into database design. If people want to utilize the DB to process everything then go ahead (but i wont).

 

[semaphore]

Semaphore, so you've written off stored procedures beecause you have had to deal with one badly written one? That is crazy.

Do you have any real reasons for thinking that they are evil?

I've written off stored procedures because i have incorporated better methods of doing real time transactions, high data volume processing and better data layer abstraction.

 

[Bar0n]

Yes it runs on the DB server but the actual logic does not SIT on the db or did you miss that part entirely? The call carriers the code through. My argument is on mixing business logic into database design. If people want to utilize the DB to process everything then go ahead (but i wont).

For performance reasons, when a stored procedure is run > 600 times per second. In other situations, stored procedures work fine for them.

I'm certain your app does not need that kind of performance.

 

[semaphore]

For performance reasons, when a stored procedure is run > 600 times per second. In other situations, stored procedures work fine for them.

I'm certain your app does not need that kind of performance.

Show me some stats of an sproc executing 600 times in 1 second. I would love to see that

 

[Bar0n]

Show me some stats of an sproc executing 600 times in 1 second. I would love to see that

It's in that link you quoted?

 

[semaphore]

It's in that link you quoted?

You're reading what you want to see. Hes stating using that normal insert process it would be limited to 600. Then stored procedures would increase the rate but then cause problems with the workflow.

Its cool dude you love sprocs i dont, its like im not religious at all but millions are. I wont convince you and you will never convince me.

And btw we are currently inserting in at 245/sec so far from 600 but still a decent speed.

 

[Bar0n]

You're reading what you want to see. Hes stating using that normal insert process it would be limited to 600. Then stored procedures would increase the rate but then cause problems with the workflow.

Its cool dude you love sprocs i dont, its like im not religious at all but millions are. I wont convince you and you will never convince me.

The only thing I'm convinced of is that you have an irrational phobia of sprocs. They are useful. Using the power of the DB server to handle some of your program's logic is never a bad idea.

 

[semaphore]

The only thing I'm convinced of is that you have an irrational phobia of sprocs. They are useful. Using the power of the DB server to handle some of your program's logic is never a bad idea.

Well that's where architecturally we differ i do not believe a programs logic should reside on a server/device used for storage.

And if you even want to start getting more technical the logic of an application should not reside in the client either. It should reside in its own enclosed application space al la SOA.

 

[jxharding]

Prob depends on situation. For us it works.

E.g. you have a SQL Transcation which affected 10 tables,
Now you would then need to
go from Backend --> SQL 10 times putting locks on a ****load of places while moving between backend and sql, instead of having it in one stored proc thereby limiting the amount of posts (and potentially lag) between app and database .
If there is a problem between backend and SQL - everything rolls back - user has to enter everything again - probably even not being informed of error, etc etc etc,
where with an SP you'd have the process complete irrespective of whether it will ever hit backend code again - i.e. if the user logs on again after connection issues he has a higher likelihood that the transaction completed and he wont have to do the transaction again


OR
build this baby up in Dynamic SQL? Holy smoke no. I rather avoid dynamic SQL than SP's even though both have uses.


but at least we get the chance to rethink our methods and see the opinion of others, so this thread has actual value.

On the 2004 article
1) very debatable
2) i debug SP's every day
3) i get plenty feedback from SP's - IDs that were created, rollback info, info on exactly what went wrong
4) pass objects to SP's every day - SQL 2008
5) If the DBA wont allow you access, well then yeah. All IDEs give you access to view source even if only read only

but yeah, to each his own, some places it will prob work, other places prob not.

 

[Ancalagon]

Having business logic in your database API is a bit of an outdated practice these days. They used to do it because the possibility of "easily" changing your front end/middleware to something completely different was enticing. But then, people realized that in all of the projects they had ever worked on, nobody had ever swapped their middle ware or front end for something else. And, stored procs are a pain to debug. Its much easier to keep all of your logic in the same language that your application is written in, whether that be C# or Java.

Also, the proliferation of ORMs such as Entity Framework kinda made them even more obsolete by making integration that much easier.

The only case where I can think of that a SP might be useful would be a performance critical piece of code, since ORMS generally have a penalty with them. However, I suppose it depends on what exactly is causing this piece of code to run slowly - is it disk access or CPU time? If its CPU time, then a stored proc is no help at all. Only in the case of a disk access bound scenario could I possibly see a stored proc as being useful, and then I would treat it the same as inline assembler in C++ - to be avoided unless necessary.

 

[Batista]

Well that's where architecturally we differ i do not believe a programs logic should reside on a server/device used for storage.

And if you even want to start getting more technical the logic of an application should not reside in the client either. It should reside
in its own enclosed application space al la SOA.

I agree,a programs logic shouldn't exist in the db.I like to separate my programs into GUI/Data/logic,but what are you using to query your db then?Something like MS entity or a doodad?You cant write SP off for good, they have their place.