- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Have you ever had the pleasure of getting a job where your primary task was supporting and updating a system that was designed 5+ years before by people who probably didn't understand databases as well as they should have? If not, then you are a lucky man.
Sometimes you can't just change the schema because you know it's wrong, because it would take months of work to rewrite thousands of lines of code and SQL. Sometimes this sort of thing actually has a big enough benefit that you can justify the cost, but usually it doesn't, so you learn to live with the quirks.
Remember, we're talking real life here, where time and money is actually a commodity. I'm all for fixing the design when possible but even I wouldn't think of rewriting an entire schema just because one stored procedure has to be written badly.
Admin
Please quit reading my fortune. It's bad enough knowing I'm headed this way sooner or later, I don't need others reminding me.
Admin
I very recently had to code something similar, because A) SP's were required B) The query was against an oracle linked server C) Depending on the parameters the schema for the tables queried was different
No matter how much i argued with my boss, or our dba's they insisted that it be a SP rather than coded in the application.
Admin
It's that sort of defeatists attitude which I am talking about.
I'm not just talking about re-writing the schema, I'm talking about introducing an interface/façade which hides the complexity of the schema, usually for shifting schema design this is in the form of programatically created views (such as used in Remedy).
Gandhi once said "Be the change you want to see in the world." - it also goes with the phenomena of broken windows.
Part of my current role remit is a cross-development team code debt review, essentially buying time from the business, across the company, to fix these broken windows, with the promise of quicker delivery of new functionality, quicker turn-around of bug fixes, and greater business content delivery options (MVVM/MVC used extensively to give the option of some nice WPF development work, which keeps the business happy because it looks nice, and keeps the developers happy because it's new-ish technology).
Time and money are indeed valuable but, long-term, doing this saves both - and in the present climate that can only be a good thing.
shrugs
This didn't mean to turn into a "all companies are shat, mine are great, aren't I wonderful" rant - but I was in the same position, as many of you are now, a few years ago - putting plasters on gaping wounds, sweeping stuff under a carpet - but I had an epiphany, a moment of clarity, and it was all so obvious what had to be done.
Out went BeyondCompare, and in came Nant and some build scripts. Out went dynamic sql and in came some programatically generated stored procs and views to deal with shifting DB schemas. (1,000 Stored procs to write against 1,000 tables? if they follow a structure they can be automated, and indeed they were).
End result? Two months work and the release process was reduced from 2 man weeks (each release took two developers one week working together) down to 5 mins (including compile time). Overnight data load process went from 7 hours (using dynamic sql) to 2 hours (using a series of code-generated stored procs).
shrugs again
:)
Admin
The alleged perf benefit hasn't existed to any legitimate extent for several versions of SQL Server.
SQL Server will: cache the query cache the execution plan
end result: sprocs and dynamic SQL pretty much differ only in time it takes to transmit a few extra characters across the wire.
Just used dynamic SQL when they allow you do, and make damn sure it's parameterized.
Admin
It'll even detect possible truncations and complain about them. Have you also seen the difference in paging support between SQL2k and 2k5? :)
LINQ to SQL doesn't so much optimize the queries for you as it's systematic about how it writes them. It will try to make efficient queries, and may detect some situations you may not normally see, but it's not doing anything you couldn't do by hand. Obviously -- it's using ADO.NET underneath.
Unfortunately, I wish we'd put in batch inserts/updates OOB in LINQ to SQL, as statics off of the data context, but time is time.
Admin
Notice that I replaced EXEC with sp_execute_sql which allows executing parametrized queries. The trivial parameters are passed safely this way. The one parameter that is concatenated in the string is escaped securely with QUOTENAME.
So there is no injection risk anymore, also each variation of the generated SQL strings' query plan is cached so no performance penalty. I reduced the number of variations to the minimum by using a parametrized query.
I recommend anyone reading this to take a look at this excellent overview of the subject: The Curse and Blessings of Dynamic SQL
Admin
Exec is handy when you have to construct SQL dynamically based on data extracted from tables (often due to someone else's poor database design).
Admin
So the real WTF is ASP.NET?
Fair enough :)
Admin
The wonderful world of Microsoft SQL Server is where you find only RTRIM and LTRIM with no TRIM in sight. Don't forget they didn't have DATE or TIME datatypes until SQL Server 2008, you had to make due with DATETIME...[/quote]
I always used YYYYMMDD formatted integers for dates in SQL Server, the DATETIME datatype completely sucked...
Admin
Admin
If this is SQL Server (and I think it is), stored procedures do not get a performance boost over SQL commands because of compile once, run many.
Since SQL Server 2000 (possibly SQL 7), EVERY sql statement (procedure or query) is compiled and cached without regard to its type.
The only performance benefit one will see between a sql query and a sproc with the same execution plan will come from the network because it takes less time to transfer 'exec mySproc' than it does 'select col1, col2, col3, col4, col5 from myTable where col1=@userid'
Admin
What are you talking about? Your applications should have at least three tiers, data access, business logic, UI. ASP.net app calls business functions(passing params), business functions call data functions(passing params), and the data functions call SP's in the actual database(passing params).
You're not coding everything in the ASP.Net page itself, are you?
Admin
Dynamic SQL, Exec() and xp_cmdshell are necessary for many DBA tasks, such as scripting index regeneration, table checks, etc. It's possible you've never managed a large DB or data warehouse before so you may not have the need. One example is nightly scripting of every object in every DB to text files. Other examples include rsyncing data on a schedule, or FTP'ing a file using a predefined script as a template.
Admin
Putting SQL code in an app is just bad form. You can't change it easily, and the DBA can't use it to determine what's affected in the case of a schema change.
Let me give you an example. My largest view is of many tables, each having between 215 million and 250 million rows, one table for each calendar year quarter. When I wanted to create a separate DB (and filegroup) just for this data, I modified TWO views. If someone had coded adds/deletes/selects in the applications, this wouldn't have been nearly as easy.
At any time I can find any word in any table name, field name, view name / content, sp name / content, or UDF name / content. In less than a second. You can't do that in your code editor. I can print / output every one of these so I can test query execution plans. You can't do that in your code editor either. And I can put this in a DBA-level revisioning archive, which you can't do because your code and the raw SQL it calls are hopelessly intertwined.
Admin
A stored procedure called many times, each with different parameters, will be treated as a single query and will be weighted more likely to be pinned in memory automatically. Those same statements sent as raw SQL will be stored as separate statements, all of them vying for the same memory, which greatly reduces their ability to be precompiled the next time they are used. This is rue for at least SQL Server 6.5 through 2005.
Dynamic SQL is fine in my book -- there are too many things you cannot do without it. There are also some statements which perform better in dynamic SQL than precompiled (because the execution plan is wrong for some combinations of optional parameters, or because inermediate processes within the sp are invoked depending on those optional parameters or parameter values).