DOM VS. SAX For XML Parsing

DOM (Document Object Model)

• DOM Parser for Java is in JAXP (Java API for XML Parsing) package.
• It represent an XML Document into tree format.
• DOM Parser creates an In Memory tree representation of XML file and then parses it, so it requires more memory

SAX ( Simple API for XML Parsing)

• It’s recommended to use SAX XML parser for parsing large xml files in Java because it doesn't require to load whole XML file in Java and it can read a big XML file in small parts.
• SAX is an event based XML Parsing and it parse XML file step by step so much suitable for large XML Files.
• SAX XML Parser fires event when it encountered opening tag, element or attribute and the parsing works accordingly. 

DOM VS. SAX 

• DOM and SAX parser are extensively used to read and parse XML file in java
• I recommend use DOM parser over SAX parser if XML file is small enough and go with SAX parser if you don’t know size of xml files to be processed or they are large.
• SAX is an event based parser and raise and event, while DOM is not . 
• At DOM Traverse in any direction.
• At SAX Top to bottom traversing.
• SAX is read only , DOM is read and write both. 

Read more:
http://javarevisited.blogspot.com/2011/12/difference-between-dom-and-sax-parsers.html#ixzz2WfmBqdl9

SQL - Parameter Sniffing

If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'.

This plan is created the first time ( whenever SQL Server is forced  to compile or recompile  ) a stored procedure is executed and is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems.

Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.

How to Deal With Parameter Sniffing ISSUES:

Option 1: With Recompile

The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan.  To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed. 

 This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure

The drawback of this option is the store procedure is recompiled with every execution.

Option 2: Disabling Parameter Sniffing

change the way the parameter values were used within the stored procedure.  By creating two different local variables  inside my procedure, setting those variables to the passed parameters, 

Option 3: Creating Multiple Stored Procedures

This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values. 

To clear procedure cache (execution plan)
For SQLServer:
DBCC FREEPROCCACHE

For Oracle
alter system flush shared_pool;

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

SQL - Delete vs Drop vs Truncate

1. DELETE operation remove some or all rows and need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. will cause all DELETE triggers on the table to fire.
2. TRUNCATE removes all rows  from a table and no DML triggers will be fired.
3. DROP removes a table from the database, indexes and privileges, No DML triggers will be fired.

• DROP and TRUNCATE are DDL commands, DELETE is a DML command.
  Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
• TRUCATE is faster and doesn't use as much undo space as a DELETE.

• Specify CASCADE CONSTRAINTS to drop all referential integrity constraints(at the detail table) that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.

           Example
                drop table Dept cascade constraints 

http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands