SPEC-India

There are many advatages of stored procedures. some of them are:

1. Procedures are on the server so messages don’t need to go back and forth to the client during the time the procedure is executed.
2. Procedures are parsed once, and the result of the parsing is stored persistently, so there’s no need to reparse for every execution.
3. Procedures are in the catalog so they are retrievable, and procedures are subject to security provisions, in the same way as other SQL data.
4. Procedures are in one place so code sharing is easy, and when changes happen there’s no need to send code changes to clients.

 When you use a stored procedure, you are depending on cache (which is limited) and access to the procedure (which may be serialized). Therefore it is a mistake to use stored procedures too frequently .

Some of the important points we should remember while creating the store procedure are.

1. Don’t declare parameters to be “input/output” when they’re merely “input” parameters.
2. Don’t write stored procedures as if the precompiler has the built-in skills of a compiler. Take things out of loops, fold, put declarations together in one statement, and avoid testing assumptions on your own—don’t expect the DBMS to do these things for you.
3. Shift logic from WHERE clauses to IF clauses whenever possible.
4. If a stored procedure contains an IF statement and is inside a trigger, take the condition out of the IF and put it in the trigger’s WHEN clause.
5. avoid putting COMMIT or ROLLBACK in the stored procedure. The transaction-end statements are especially bad if you’re using distributed transactions and TP monitors

Advantages of XML

1)  It supports Unicode, allowing almost any information in any written human language to be communicated.
2)  It can represent common computer science data structures: records, lists and trees.
3)  Its self-documenting format describes structure and field names as well as specific values.
4)  The strict syntax and parsing requirements make the necessary parsing algorithms extremely simple, efficient, and consistent.
5)  XML is heavily used as a format for document storage and processing, both online and offline.
6)  It is based on international standards.
7)  It can be updated incrementally.
8)  It allows validation using schema languages such as XSD and Schematron, which makes effective unit-testing, firewalls, acceptance testing, contractual specification and software construction easier.
9)  The hierarchical structure is suitable for most (but not all) types of documents.
10)  It is platform-independent, thus relatively immune to changes in technology.
11)  Forward and backward compatibility are relatively easy to maintain despite changes in DTD or Schema.

Disadvantages of XML

1)  XML syntax is redundant or large relative to binary representations of similar data especially with tabular data.
2)  The redundancy may affect application efficiency through higher storage, transmission and processing costs
3)  XML syntax is verbose, especially for human readers, relative to other alternative ‘text-based’ data transmission formats.
4)  The hierarchical model for representation is limited in comparison to an object oriented graph
5)  Expressing overlapping (non-hierarchical) node relationships requires extra effort.
6)  XML namespaces are problematic to use and namespace support can be difficult to correctly implement in an XML parser.
7)  XML is commonly depicted as “self-documenting” but this depiction ignores critical ambiguities.
8)  The distinction between content and attributes in XML seems unnatural to some and makes designing XML data structures harder.
9)  Transformations, even identity transforms, result in changes to format (whitespace, attribute ordering, attribute quoting, whitespace around attributes, newlines). These problems can make diff-ing the XML source very difficult.
10)  Encourages non-relational data structures (data non-normalized)
11)XML is very concrete and highly non-canonical. It introduces a very strong coupling between the actual representation chosen and the processing program (unlike relational storage and SQL)