No more stored procedures

[Rocky Moore]

Now there is an answer! :)

Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...

[Rocky Moore]

Throwing the baby out with the bath water is not a good thing. At this point it appears the product is already optimized for SQL Server and doing all this extra work just negates all the work that was already done along with possibly harming performance. Not to mention most of the SQL is problably not compatible between the databases anyway. If they want to move to support other servers, it is far better to build in a provider model and build out a diffrent provider for each DB. That way you can optimize for each database using all the power of each instead of a the lowest common denominator. This would also allow you to use CLR on SQL Server in the future if you have the need which can even add more abilities. I would go to the boss and push for a simple plug-in type architecture instead of hard coding SQL into the applciation. Every time you need to change one thing in your SQL (even for simply optimizing), you have to release yet another version of your application causing even more work.

Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...

[Gary R Wheeler]

<Aerosmith> Livin' on the edge... </Aerosmith>

---

Software Zen: delete this;

Fold With Us![^]

[Member 96]

I agree if that's the way someone did it. None of what you say (in any of your replies) is anything I've witnessed personally, but like any technique it's open for poor implementation.

[Marc Clifton]

Rocky Moore wrote:

don't you have to add code to make sure that never happens, such as checking if the GUID is already used?

I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.

Rocky Moore wrote:

Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int?

There's a lot of debate[^] on the subject. (And a lot of links you can read up on). Someone published some code somewhere that helps with clustering, but I'd rather not touch it. The thing I like about GUID's is it makes it easy to merge offline changes. And I have to deal with that occasionally. Marc

Thyme In The Country

People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

[Dave Kreskowiak]

That's probably the most asinine thing I've heard. I've only read about half the thread here, and the focus appears to be on the performance benefits, or lack there-of, of stored procedures. I'll give you good reason to USE stored procedures -- data consistancy. The database applications I write all use stored procedures to keep the data consistant, not for performance reasons. The database maintains itself without any outside C# or C++ or VB.NET code. If you make a change in a table, triggers, functions, stored procs, and whatnot maintain the integrity of the data. Data can be validated and massaged before the changes are commited. The database takes care of itself without any outside influence. The database can pretty much run without a coded user interface. If there is something that needs to change in the database, like a correction to some data, you can do it directly using SQL and the database still maintains itself.

Dave Kreskowiak Microsoft MVP - Visual Basic

[Ennis Ray Lynch Jr]

You could write an abstraction layer for data access and have the UI or Business layer use the abstraction layer. The abstraction layer would provide an interface and your actual implementation would use stored procedures. Why still use stored procedures? I have worked with Oracle, MySql, Access (Jet), and Sql Server and all of them have a different SQL syntax. BTW, @@identity is SQL Server only.

---

On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage

[Rocky Moore]

Marc Clifton wrote:

I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.

Yeah, I sure hope mission critical apps think about it ;) Of course, it is not just "that" table as most databases have some form of relationship where they are used as keys. In a large application, they would have to be unique to maybe 20-100 tables. If used in an enterprise, that could be combined over different branch offices to thousands of tables. Not to mention that have no order so you end up using another field to force order. Little things like this bug me so bad, I would have to make extra queries just in case :) Thanks for the thoughts!

Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...

[Andy Brummer]

Rocky Moore wrote:

Little things like this bug me so bad, I would have to make extra queries just in case

If you consider the bug rate of even an exceptional developer working under an exceptional development process, the probability of the extra queries causing errors is probably more likely then getting a guid collision. :laugh:

---

I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon

[Andy Brummer]

The best advice in this whole thread. :-D

---

I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon

[Stan Klimoff]

I agree. SP improve performance only in terms of round-trips; there's no speed difference between a call of an SP or a plain-text SQL execution -- of cause, you can invent a special case when a common repeating action is accomplished by a-completely-dynamic-sql-with-a-unique-execution-plan. My experience is limited to SQL Server though. I guess there are DMBS that respect the difference. And, IMO, every major DBMS has an SQL only in respect to the fact that the DBMS itself is a standalone product. It would be weird to sell a DBMS that needs a set of programming tools to make something-not-so-trivial. Also, SPs make the DBMS interaction language Turing-complete.

[Russell Jones]

last year I worked on a web based system for a company that was a market leader in its field. If you used the username "admin" and a password of "' or true--" you could easily get to a reporting page that allowed arbitrary sql to be executed. Most of the clients of the company had the database running as a domain admin account. Whichever way you chose to implement your DB access, please avoid building dynamic code that allows this kind of thing to happen. I include dynamic code inside an SP that uses the exec command. Russell

[Russell Jones]

it doesn't matter if you have identical values in 2 different keys. using identities you would start with Person_id = 1 and address_id = 1 and that would be fine. if you are worried about clashing keys wrap all your important calls in a transaction and only commit if there are no errors. Russell

[Pete OHanlon]

Hear hear.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[dazfuller]

That has got to be the dumbest thing I have ever heard, I know SQL2000 has some issues with stored procedures when it comes to using parameters (anyone wants to know what that is then let me know). If your boss is so concerned then surely the smartest thing to do is factor out all of the database calls into a Data Access Layer, then if you ever do sell the product to someone who uses a different database system then you only need to modify the DAL. Writing your SQL code directly into the application makes your application wholly dependent on the database you use and if any of your SQL objects or logic changes then so must your code. Also there is no guarantee that the SQL code you write will work in another database such as MySQL or Postgres (or even SQLite???). Using a DAL means that can all be sorted out in it's own DLL without the bulk of your code ever having to know what database system it's using. Then there's also the fact that it can be easier to manage security using views and procedures.

[Stewart Kingsley]

Its easier to convert a series of SPs, views etc to a new destination database than to have to find all the SQL in your code and convert that instead. Select @@identity will not work in Oracle (if memory serves) so SQL code conversion will HAVE to be done when moving to another backend DB. If you want the code to be entirely database independent then dont use SQL at all, if thats even possible. There is good software and there is bad software, and this is not one of them.

[MatthewTyler]

How does this help since for example @@identity doesn't exist in MySQL? So the code still won't run on a different DB. Writing code for different DBs is like writing DHTML for different browsers; easy in theory but unless you test against all the different DBs at implementation time porting a finished app is going to be a nightmare however you write the queries.

[beatle11]

From what I can gather, as it is just a small IT department I can't see there being an issue. Stored procedures shouldn't cause too many problems if you change databases as they are supported by all the major databases. It does seem unnecessary to change it but I guess either way works.

[reshi999]

I've worked as a SQL developer for 6 years and I can see where your boss is coming from - the theory is very elegant but in practical terms it will slow down processing speed Stored procedures are the key to SQL performance enhancements and I could not do without them - example: A legacy system calculating large scale posting costs was previously coded in C#, I recoded it into SQL: c# : Took 20-30 seconds to process a profile, plus had to code a custom garbage collection program over the top. sql : Takes 0.3 seconds to complete with no gc required, this was from a straight port of the code with a good indexing strat. The results speak for themselves really... But anyway I've had managers come up with similar rules (one insisted storing data as text files would be much faster than SQL tables, he got sacked soon after the benchmark figures came back) - Develop a test system doing the common operations for both using the different coding styles, benchmark them and show them the difference. One way to find a half way solution would be to code all your data access into a class, then explain that to move to another database simply requires one class to be recoded. hth.

[JHubSharp]

While not being a database expert (so I can't argue on the performance issue of SP's vs dynamic SQL), I've seen the kind of garbage dynamic SQL can create. I'm more curious as to the why of the argument. If you're selling to a client that supports MS SQL, you can easily have a script that builds what you need. If you're not, won't the syntax of your SQL potentially change anyway? Not to mention all the ways you interact with the database will have to be changed to using a different provider and such. Even using .NET and only programming to the data provider interfaces, I'd imagine a few things could still need to change. If work has to be done regardless, wouldn't you prefer to benefit from the performance (however slight) and abstraction of data logic that stored procedures provide?