No more stored procedures
-
John Cardinal wrote:
Stored procedures do *not* reduce network traffice, well designed queries do.
And having a query manipulate data inside a stored procedure then send back the final answer is much better than having lots of data being sent back-and-forth across the network so some client app can do what could be done in a stored procedure.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
Curious, since there is a chance howbeit slight, that a GUID is already used, don't you have to add code to make sure that never happens, such as checking if the GUID is already used? Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int? :: This reply was suppose to be to Marc.. also don't like having the DB generate the ID for me. I use GUID's for primary key values and I hook the new row event and populate the ID before the DB even sees the transaction. That way, I can update the primary table and foreign tables [edit]independent of the DB even getting involved[/edit] then send all the transactions at once (and rollback also if necessary). CP really needs to get this bug fixed.. ::
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
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
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 -
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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 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
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 SmithMarc 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...
-
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...
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
-
Miszou wrote:
Any thoughts? I have mine, but I'd be interested in hearing from others...
Run away!!!
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
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
-
Joe Woodbury wrote:
Stored procedures improve performance
To who? Not the end user, that's been proven time and again so often that I'm stunned to see some people here still contributing to that fallacy.
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.
-
John Cardinal wrote:
Yeah, I'm assuming for the purposes of this discussion that goes without saying
What scares me is the number of people who aren't aware of this. If they were, then Colin would be out of a job:-D
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.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
-
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...
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
-
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
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. -
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
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?
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Well, Miszou...it seems that your supervisor needs to read the SQL Server Books Online...there is also a good article on this in the "SQL Server Standard" magazine (the March/April 2005 issue)...so...ask him/her to spend some time learning (more) SQL (and perhaps .NET?)...The stored procs are supported by any serious RDBMS - I've been told that even recent MySQL supports them... By the way, I do not understand why does your company still uses classic ASP? -- modified at 6:36 Thursday 4th January, 2007
-
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 wrote:
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.
That was exactly my first thought too. Marc
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 -
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...
Rocky Moore wrote:
Yeah, I sure hope mission critical apps think about it
I love how "mission critical apps" is the ultimate "ooh, now I'm scared" buzzword to drop on people. Consider: While each generated GUID is not guaranteed to be unique, the total number of unique keys (2128 or 3.40282366×1038) is so large that the probability of the same number being generated twice is very small. Now your basic int, a 32 bit value, (4 bytes), is going to rollover much sooner than a GUID is going to collide. What does your DB do when the autonumbering rolls over? I actually tested that once. Now consider some other numbers. Even at 2^31 for a signed int, thats 2 trillion records. Hmmm. I don't know a lot of mission critical apps that will hit 2 trillion. Ever. I can think of a couple, like cataloging stars or grains of sand. More numbers. At 4 bytes for an int just to store the ID, that's 8GB if you were to have a record using every possible integer ID. Let's say, ooh, on average, another 1000 bytes for data per row? So that's another 2^31 * 1000, or 2 terrabytes of data. That terrabyte star catalog is the only thing I can think of that requires that much space. So, let's be real when we talk about mission critical apps. 2 trillion records? 2 terrabytes of disk space? And those numbers are using an integer ID and based on the range of values that it is capable of.
Rocky Moore wrote:
In a large application, they would have to be unique to maybe 20-100 tables.
No. Only the primary key needs to be unique. Not the FK's! And identical keys in separate tables isn't an issue either!
Rocky Moore wrote:
Not to mention that have no order so you end up using another field to force order.
Ummm...PK's should be abstracted anyways. You'd never order on a PK!
Rocky Moore wrote:
Little things like this bug me so bad
Well, we pick our battles. If you actually stop and think about the issue, I think there's very little to be bugged about, and actually a case more for being bugged about your mission critical apps using an integer ID. Marc
People are just notoriously impossible. --DavidCrow
There's