|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, February 23, 2009 8:19 AM
Points: 8,
Visits: 17
|
|
Thanks for that bit of feedback. I'm learning 'best practices' as I go along. I've inherited an environment setup by the previous guy and things were done so that they worked at the time. That kinda thing drives me nuts.
Somewhat on topic...what do you think of a scenario like this:
*ASP.NET website that pulls from SQL Server
*ASP.NET primarily calls sprocs when more than a simple SELECT is needed
*Views always used to handle multiple table joins where only a few columns are needed
Is this a 'good' or 'bad' way to go about things?
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, July 06, 2011 5:05 AM
Points: 582,
Visits: 1,059
|
|
Ryan
What is the usage scale for the website? Number of concurrent users, etc.
How mission critical is it?
--Shaun
Hiding under a desk from SSIS Implemenation Work 
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, February 23, 2009 8:19 AM
Points: 8,
Visits: 17
|
|
This is a highly important service that has two peak seasons. It displays property tax information for the county. A similar web app also displays public records. Both are mission critical functions.
During peak season for property taxes, there may be ~30 concurrent users. I've not seen more than that at one time.
By taking the complex SELECT with joins out of the ASP code and moving it to a sproc on SQL Server, I brought performance from ~35 seconds to 15. Adding an index to the table brought response to ~2 seconds :D
The property tax table contains nearly 9 million records and grows by hundreds of thousands when payments are being made.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, January 13, 2012 7:00 PM
Points: 15,
Visits: 46
|
|
why not add in diagrams and foreign key constraints? surely there's a perf penalty to consider when every fk is constantly yet needlessly checked by the dbms when the CUD (who cares about R) is done by stored procs and the db is secure against external changes.
reason I ponder this is my background is large mainframe that had no ref integrity capabilities and yet we managed to build, maintain and audit, massive and extermely high perf databases - probably most of your bank accounts, govt pension plans, and military inventory dbs.
cuz isn't that what dba's are supposed to do - steward the db's instead of letting the dbms do it for them?
jes wonderin ..
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, February 23, 2009 8:19 AM
Points: 8,
Visits: 17
|
|
I don't know
I'm a web programmer and I'm doing all I can to get up to speed on DBA stuff. What IS the correct architecture to use?
Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc?
Should a sproc handle the joins of 6 or 7 tables, or should a view be used if I only need 1 or 2 columns from each table?
These are the questions that burn in my mind...and I'm totally hijacking an editorial thread...
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Thursday, November 17, 2011 4:09 PM
Points: 9,359,
Visits: 8,864
|
|
Wow. This obtuse defense of cursors and RBAR has more hand-waving than a Michael Jackson concert. Practically every other post here has statements to the the effect that "sometimes cursors are the best solution", starting with the original article itself, and yet none of these claims has been backed up with an example, or a pointer of a cursor-based SQL procedure that is the best solution for SQL Server in its current release.
Not one.
Hmm, makes me think of one of my own maxims:
When everyone says that something is true, but no one can provide an example, then it probably isn't true.
And let me be clear about what I mean by cursors being bad. Technically, anything in SQL that serializes a data stream and can keep positional context is a cursor. However, when we say "cursors are bad" we do not mean client-side cursors, internal cursors, implicit cursors, etc. We mean explicit Transact-SQL server-side cursors. The kind that people explicitly write in procedures all the time (or try to hide in another stored procedure) and that use the CURSOR datatype.
Since the advent of the new features in SQL Server 2005 (particularly Varchar(MAX) and CTE's), I have not seen one instance where a cursor was the best solution for a problem. The best that can be said for it is that there are some (very few) cases where a cursor was no worse than some other solutions. But real-world problems addressing situations that reasonably belong in the database-tier where cursors are the clear best solution? Not one.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, July 06, 2011 5:05 AM
Points: 582,
Visits: 1,059
|
|
ryan.leuty (8/12/2008)
I don't know ...... Is it better [faster, more reliable, more secure] to send a complex statement to SQL via the web app, or use a sproc? Should a sproc handle the joins of 6 or 7 tables, or should a view be used if I only need 1 or 2 columns from each table? ...
It is more manageable, more reliable and more secure to use stored procs rather than SQL from the web app. (in my opinion)
view vs stored proc....you still need to wrap the view up in a stored proc in order use dynamic selection criteria.
Hiding under a desk from SSIS Implemenation Work
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, February 01, 2012 10:18 PM
Points: 353,
Visits: 374
|
|
gserdijn (8/12/2008)
But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...
Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it!
But why the heck are views in the list of no-no's? Forget the hand wringing about cursors; views are wonderful things especially when you have to present base data differently on the DB side to make something like Business Objects run well.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Thursday, November 17, 2011 4:09 PM
Points: 9,359,
Visits: 8,864
|
|
magarity kerns (8/12/2008)
gserdijn (8/12/2008)
But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...Whoa there! SQL would not be Turing complete without GOTO - don't be so quick to toss it!
This is NOT true. Both iteration and recursion also provide Turing-completeness.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, January 13, 2012 7:00 PM
Points: 15,
Visits: 46
|
|
| hey Ryan I know what u mean - stepping into dba shoes seems often like moving into the Dark Arts or something - tons of sage knowledge from all directions by persons smarter that me that totally defeat any attempt I attempt to move in any direction except lost. Now toss into that the tons of books these sages have penned in their valiant efforts to confuse me further (and possibly also to generate generous income) .. well daunting to say the least. Good luck, don't fix it if it ain't broke, and set yourself up a bunch of test databases and experiment like crazy on weekends and when the sages are otherwise asleep .. :)
|
|
|
|
