August 22, 2013 at 1:18 pm
below86 (8/22/2013)
Eric M Russell (8/22/2013)
Chrissy321 (8/22/2013)
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Break out the procedure into lettered or numbered sections. Use TRY/CATCH and insert any errors generated by CATCH into a log along with the section of the procedure.
When writing a stored procedures, I typically have something like a @Exec_Status int output parameter which I increment backward based on line number at various points in the code (-1, -22, -138, etc.). If the procedure runs to completion, then it returns with status >= 0, but if it falls into error hander, then it returns with < 0 value.
Eric,
So do you have to continually adjust your numbers if you have to add or remove code? Sorry, I'm just having a hard time visualizing this concept right now. Could be just getting tired at the end of the day.
Chrissy,
So I would need to put the 'Begin CATCH...' after each section?
Where does the results from the 'SELECT * FROM [dbo].[#Logs]' go?
Thanks,
No, I don't adjust them, it makes no difference what the number is, so long as they're unique. If the current line I'm wanting to comment happens to be 242, then I'll set @Exec_Status = -242. I'll set @Exec_Status = 0 just before the RETURN statement.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 2:54 pm
So I would need to put the 'Begin CATCH...' after each section?
Where does the results from the 'SELECT * FROM [dbo].[#Logs]' go?
You would have to put the TRY/CATCH after each section. Generally I'll run through the code at the end of the project and add.
Most of my development is what I would call data processing, usually scheduled via SQL Server agent. My agent job might have two steps 1) the actual processing 2) An email notification process which will query the log for any failures/alerts/warnings. If step one bombs, step two will run if you configure your job steps to do just that.
I guess if you were into triggers you could generate a notification when an alert is inserted.
August 22, 2013 at 2:58 pm
Chrissy321 wrote:
I guess if you were into triggers you could generate a notification when an alert is inserted.
What would be the advantages/disadvantages of using a trigger for the notifications, please?
Triggers seem to be one of those subjects which provokes a good deal of debate, but a trigger in this case would appear to be an innocent enough usage.
Thanks.
August 22, 2013 at 2:58 pm
I get it now, it was throwing me off when you said 'line', I was thinking the actual line number.
Maybe this is just me not understanding stored procedures but let's say your code failed right after you 'set @Exec_status = -242'. How do you see the '-242'? If it failed then you don't have that variable to check for it's value. Does this value get returned from the stored procedure? I guess I should do some researching on stored procedures instead of asking a million questions.:-)
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 22, 2013 at 3:15 pm
below86 (8/22/2013)
I get it now, it was throwing me off when you said 'line', I was thinking the actual line number.Maybe this is just me not understanding stored procedures but let's say your code failed right after you 'set @Exec_status = -242'. How do you see the '-242'? If it failed then you don't have that variable to check for it's value. Does this value get returned from the stored procedure? I guess I should do some researching on stored procedures instead of asking a million questions.:-)
Thanks,
I implement @exec_status as an output parameter, and the return value can be captured by the calling application. You don't pass anything into @exec_status, just let it default to -1. Using this method, you only need one error handler, and you set value for @exec_status at various points in code to indicate where a failure if any occurred. I typically set @exec_status before each insert/update/delete or before within a conditional code block. It's more useful for complex procedure that are hundreds of lines long with many braching points.
create procedure dbo.mytestproc
(
@exec_status int = -1 output
)
as
begin try;
select @exec_status = -6;
print 1 / 0;
select @exec_status = 0;
end try
begin catch
print ERROR_MESSAGE();
end catch;
GO
Divide by zero error encountered.
-6
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 3:15 pm
What would be the advantages/disadvantages of using a trigger for the notifications, please?
The advantages would be quasi-instantaneous notification and you would eliminate the possibility that the error would prevent some type of subsequent or secondary notification process from being well 'triggered'. If for example you had a notification section at the end of your procedure the error may prevent that section from ever being called.
Depending on the frequency of insets in your log table performance may be an issue. I have only used a trigger once to fix a bad design so it was sort of a hack in a bad way.
Even for the trigger-averse an insert trigger on a logging table that calls an asynchronous email notification could be perfectly acceptable. So your point is well-taken and I will reconsider. Perhaps I have taken my anti-trigger zealotry a bit to far.
August 22, 2013 at 3:29 pm
Perhaps I have taken my anti-trigger zealotry a bit to far.
There is a role for triggers, but an anti-trigger bias isn't a bad thing.
August 22, 2013 at 4:27 pm
@ronkyle wrote:
There is a role for triggers, but an anti-trigger bias isn't a bad thing.
I often encounter an 'anti-trigger' bias amongst DBAs, but that's usually because they've had to deal with poorly-designed applications chock-full of triggers, and the triggers' sequences left undocumented.
Can't say I blame them, when triggers are used to McGyver application processing.
A trigger is just a specialised tool - a specific means to a specific end - and as the old saying goes, we should 'use the right tool for the right job'. Triggers seem to be one of those things that get misused/overused. Judiciously used, I have no aversion to them.
August 22, 2013 at 7:05 pm
Craig-315134 (8/22/2013)
A trigger is just a specialised tool - a specific means to a specific end - and as the old saying goes, we should 'use the right tool for the right job'. Triggers seem to be one of those things that get misused/overused. Judiciously used, I have no aversion to them.
Well, yes, they are a specialised tool, and they do have appropriate uses. But I've debugged far more triggers than I've written, and in more than half the cases the solution was to do what needed doing somewhere else, not in a trigger.
Of course working with views that are not automatically updateable can mean you have to write triggers if you want to update through the views, so that's one place where they can be essential.
As a general rule though, I advise people to avoid triggers if they can - because they tend to be used for things they aren't really appropriate for, and people tend to get them wrong. For example people often write trigger code that assumes only one row is affected, when in fact many rows may be affected; they write triggers to implement domain constraints which should be implemented as check constraints; I've even seen a trigger written to enforce something that could have been a unique constraint, when there was also a non-clustered index on the columns involved; they write triggers that cascade deletes or updates instead of letting the key constraint do it; and so on. It gets quite amusing when one of the inappropriate uses is combined with the "always just one row" error.
Maybe things have improved in the last few years, when I haven't been looking at other people's code much (except at code provided by some of the real experts who write articles here) - although looking at some of the queries in the forums here I rather think they may have gone the other way.
Tom
August 22, 2013 at 8:33 pm
RonKyle (8/21/2013)
I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic
I think I can help with wondering why it's not in wider practice--this is not a good idea. The database is a chokepoint, and you want it processing as few business rules as possible. The enforcement of business rules really belongs in the middle tier, whatever that might be for the application. Let the code in the intermediate levels do their thing and then send the result to the database.
I definitely have to say "It Depends" here. The examples here are a bit extreme but have also been true more often than not at various places that I've worked in the past.
One example is that reporting was done at the presentation layer... ALL of it including the aggregation of millions of rows of data. CPU, I/O, and the load on the pipe went nuts because someone suggested that all business logic should be kept out of the database. Of course, they blamed the database for this process being slow until I showed them how to properly pre-aggregate the data and then aggregate again to "swing" the data into it's proper position essentially putting both business layer and presentation layer functionality into the data layer.
Another example is an app that was setup to build 250,000 fairly wide rows of "default" data for a new project and send it to the database. They did it in the front end to avoid having business logic in the database. The job was a huge performance problem as it would saturate the pipe and drive physical I/O through the roof. I rewrote "the system" to pass a stored procedure 6 parameters and generated the required 250,000 rows in less than a second by putting the business logic into the database.
One final example (just to make 3) was an app that was constantly hitting a stored procedure with several joins about 12,000 times in an 8 hour period. The stored procedure only returned about 256 rows out of 14,000 and it was almost always the same 256 or so rows for sometimes weeks on end. It truly was a very slow changing dimension. It was also causing nearly 22 Trillion bytes of memory I/O every 8 hours and outstripped the combined resource usage of the 4 largest batch runs (one of which took four hours to run) for CPU, logical reads, and duration . We fixed that problem by moving some database functionality, that of storing data, to the presentation layer in the form of cache. We'd refresh the cache by running the proc just once a day.
Perhaps I think differently than most but, to me, there's almost no such thing as separate presentation, business, and data layers. For me, it's all about safety of data and performance as perceived by the servers themselves and the end user experience. For me, the database is anything but a choke point because I don't lock myself down into thinking such things as not mixing layers. Instead, I use the whole system to do what I need to get done, done. Oddly enough, I usually get the best performance improvements by moving business requirements to the data layer. Except for formatting things like dates, currency, and other numeric data which is better left to the front end, my play on an old saw is "Just because you can do something in T-SQL doesn't mean you shouldn't.":-D
That goes along with a wonderfully short argument I had with one of may favorite front end developers. He got mad at me one day and said "Jeff, when are you going to learn that you don't have to do everything in the database?" In return, I calmly replied "About the same time that you learn that not everything has be done outside the database." :hehe:
Like I said, "It Depends". 😛
--Jeff Moden
August 23, 2013 at 7:07 am
RonKyle (8/22/2013)
There are business entity relationships, which are modelled by the normal forms, and there are business rules, which have no impact on database design beyond maybe the addition of certain columns. As anyone who has worked with me will tell you, I am a stickler for the implementation of a design according to the normal forms. Only once have have denormalized because it signicantly enhanced performance.An office may have many jobsites. So a parent child table relationship. A discount for an order which needs to be apportioned across all the purchased items in a particular fashion is a business rule that needs to be dealt with in code, either at the database level or somewhere else. I prefer the somewhere else. To the greatest extent possible, I only want the database reading and writing.
Does that answer the question?
Not really. If I take what you have said literally I can derive some consequences which I am pretty sure you don't intend.
You don't want the database rejecting attempts to violate constraints, whether domain constraints, primary key constraints, uniqueness constraints, or foreign key constraints because that is actively doing something other than reading and writing. So your normalisation can do very little for you, because without constraint enforcement the normalisation will not prevent any of the anomalies it is intended to prevent.
Presumably (although you say you want to normalise) you won't have any tables in any normal form, because that would requires primary key constraints that the database will enforce, which again is not reading and writing.
Functions like +, -, substring, charindex are not reading or writing, so you want all arithmetic and string manipulation to be done in the application, not in the database.
A where clause that compares two columns or compares a column and a constant is doing a comparison, which is more than just reading, so you want to interpret any such where clause in the application layer, not in the database.
You want something that just reads and writes - presumably you tell it exactly where to write since storage allocation is not just reading and writing and therefor has to be done in the application layer.
sorting is more than reading and writing, so any order by clause in a query should be left uninterpreted by the database and interpreted by the application; this would mean that implementation of TOP would usually have to be in the application layer, but that doesn't matter TOP since is already relegated to the application by the rule that the database must not count, it must only read or write.
You have chosen to take some business logic which you've noticed really needs to be in the database and in order to preserve your dogma arbitrarily declared that this business logic isn't business logic but something else called "business entity relationships". I would love to understand how a primary key constraint can be called a business entity relationship, or how a domain constraint like "int not null" or "varchar(12) null" can be. Clearly domain constraints that involve a check constraint are business logic that must be in the application layer: for example "int not null check(? between -5 and 47)" is a perfectly good domain constraint, but the fact that this domain contains only integers from -5 to 47 inclusive is a fact of business logic and not an entity relationship. So it's not permitted to hold relational data in the database, because you can't be relational if can't have domain constraints.
As I said, I don't for one moment believe that you would apply any of those rules, although they are clearly implied by your statements; so my conclusion is that you are somewhat confused as to what having no business logic in the database implies.
There is also a principle that people who advocate a rigid and over-broad limitations on the business logic that can be performed in the database don't seem to understand; when designing and building a system the data layer/app layer/presentation layer distinction is useful as a guideline as to what functionality goes where, but arbitrary nonsense like "the database should only read and write" destroys any usefulness that that distinction has. There is an overriding principle: the system has to work, it has to deliver adequate throughput and satisfactory response times at an economical price and at the agreed delivery date, and it has to be capable of any growth in throughput anticipated and be sufficiently flexible to permit easy maintenance and upgrade as and when required. I have seen plenty of cases where that overriding principle has forced me to move bits of logic that I initially assigned to the app layer or the presentation layer into the database and that has convinced me that some business logic beyond what is mandatorily in the database, like domain constraints and other constraints that support data integrity, will often need to be in the database.
Tom
August 23, 2013 at 8:00 am
Not really. If I take what you have said literally I can derive some consequences which I am pretty sure you don't intend.
It's not my intention to write a book to explain every detail and nuance. The forum is for an exchange of general ideas. Almost anything anyone says can be taken to an extreme and made to look like it doesn't make sense. I could take your preference to have the business rules in the stored procedures and create thousand line stored procedures, but I assume you're not advocating that. (You're not, right?) There's a latin phrase for that which escapes me. However, I will look at your details over the weekend.
August 23, 2013 at 9:34 am
@L'EI wrote:
As a general rule though, I advise people to avoid triggers if they can - because they tend to be used for things they aren't really appropriate for, and people tend to get them wrong.
I think our experiences and observations have been similar regarding the (mis)use of triggers, but where I would very respectfully differ with you is in what @ronkyle called an 'anti-trigger bias'.
The solution to the problem of misusing a specialised tool is not avoidance. The solution is in education and training. Developers, as many of us have witnessed, often 'MacGyver' application design through the incorrect and injudicous use of triggers. Very well, then, let us educate them.
(They are educable, are they not? 😉 )
August 23, 2013 at 9:42 am
@jeff Moden wrote:
Perhaps I think differently than most but, to me, there's almost no such thing as separate presentation, business, and data layers. For me, it's all about safety of data and performance ...
Spoken like a true DBA! 😀
Seriously, though, Jeff, I don't think 'data safety and performance' and application layer abstraction must be mutually exclusive goals; as an application development manager who also keeps his hand in the database side of the shop, I have to concern myself with all these aspects of application design.
Trust me: there very much is such a thing as application layer abstraction, and correctly done it contributes not only to application maintainability and code reuse, but to data security and database performance as well.
August 23, 2013 at 11:17 am
Craig-315134 (8/23/2013)
@Jeff Moden wrote:Perhaps I think differently than most but, to me, there's almost no such thing as separate presentation, business, and data layers. For me, it's all about safety of data and performance ...
Spoken like a true DBA! 😀
Seriously, though, Jeff, I don't think 'data safety and performance' and application layer abstraction must be mutually exclusive goals; as an application development manager who also keeps his hand in the database side of the shop, I have to concern myself with all these aspects of application design.
Trust me: there very much is such a thing as application layer abstraction, and correctly done it contributes not only to application maintainability and code reuse, but to data security and database performance as well.
I agree with application layer abstraction. I just don't agree that there's a solid line between any of the layers. I certainly don't agree with the idea that only the Business Layer should contain business rules nor do I agree that processing or even some presentation rules should be limited explicitly to the "application layer". To many people get to most databases without using the application. For example, having a NOT NULL constraint or an FK IS actually a business rule.
--Jeff Moden
Viewing 15 posts - 61 through 75 (of 90 total)
You must be logged in to reply to this topic. Login to reply