Tuesday, January 22, 2008

Using Beyond Compare to compare 2 SSIS packages

Pretend you have two SSIS packages that you want to compare. Let's assume you don't care about cosmetics like position and width of squares, and that you are concerned about connections, queries, and everything else except GUIDs of objects.
When you save a SSIS package you get a .dtsx file. It is an escaped XML file for the most part. This means it is also text based. Beyond Compare does a great job of comparing text file. However, all the things we said we didn't want are also in this file.
Beyond Compare is powerful enough to allow us to tell it what things need to be ignored in the file. This is done by doing the following.
Open Beyond Compare | Tools | Pick Rules | New Rules...
NOTE: Later you can get back to it by choosing it in the menu first. Then after that you just have to go to the Beyond Compare | Tools | Edit Current Rules...
General Tab:
Name: SSIS Package
Associated with: *.dtsx
Whitespace Includes:
Tabs and spaces (8 character positions)
Line endings: checked
Importance Tab:
Unimportant Text checkboxes: check them all
Here is the important part.
Click the New button.
Category: Regular Expression
Regular Expression: (width|height|top|left|x|y)="-?\d{1,10}"
Click the New button.
Category: Regular Expression
Regular Expression: \{.{36}\}
Click the New button.
Category: Regular Expression
Regular Expression: ddsxmlobjectstreaminitwrapper binary=".{1,24}"
All other tabs and values are default values.
Now when you compare .dstx files these rules will automatically be applied, and the unwanted stuff will not be used as criteria when comparing the files.

Monday, January 21, 2008

Cost of Query in Oracle

One way to tell what is happening when a query is executed on Oracle is to open up SQL Plus and type the following before your SQL statement you want to analyze. Turning Autotrace on will give you performance cost in the way of an Execution Plan.
SET AUTOTRACE ON
When you are done, turn it off with
SET AUTOTRACE OFF

Timing SQL Plus query

To time a query in Oracle SQL Plus do the following.
SET TIMING ON
select * from mytable
.... Results here....
Elapsed: 00:00:00.15

Get definition for view in Oracle

set LONG 10000 select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';
Line one must be at least the size of the statement used to create the view. Either pick a really big number if you think the view is large or use the following to determine how long it is first. If it isn't, the text will be truncated
select text_length from all_views where view_name = 'MY_VIEW'
NOTE: name of the view is case sensitive as are most every thing in Oracle.
If you want to capture output to something other than a SQL Plus window such as a file, be sure to include do the following before you execute the select statement.
spool c:\v_person.sql
To close the file when you are done writing output to it, call the following:
spool off
To summarize:
select text_length from all_views where view_name = 'MY_VIEW'
10000
-- whatever value you get back, use on the next statement.
set LONG 10000
spool c:\v_person.sql
select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';
spool off