ORM (Object Relational Mapper) - what is available

Once you get familiar with ORM's you will "Love or Hate" the idea.

For a new project I write I decided to use once more an ORM Tool. There are so many ORM's outside there and because I develop it only in .Net C# and I love the idea for open source I decided I only go for an open source object relational mapper.

Microsoft comes out with some stuff called "Linq to SQL" and "Entity Framework". I could find lot of information about it but in the end I could NOT find a really working solution which is based on a n-tier architecture. Since my point of view is that applications are also applications which are based on different tiers or models I'm not satisfied with this "0815 / drag & drop" Microsoft solutions which are done in 5 minutes.

An application has to be divided into various tiers. Lets say it simple:
- Data Access Layer
- Business Object Layer
- Presentation Layer

if you need more well done but I prefer a clean architecture instead of one Website Project which contains a mix of anything. Can be that for other people it is good enough. Anyway, enough about my personal point of view.

Following criteria are important to me:

• Perspective on Performance
• Perspective on Data-source independence - access various data-sources SQL Server, OBDC etc.
• Perspective on Lazy Load of parent entities
• Perspective on Pluggable Creation Mechanism
• Perspective on Separation Of Tiers And Layering Preferences
• Perspective on Transactions Control
• Perspective on Mapping (1:1, 1:N, N:M)
• Perspective on Query Facilities

After many hours of researching, downloads, testings and clarifications I come up with the following list which could be interesting. Please note the notes beside are my personal point of views and does not need to match anything other opinion.

• http://www.ornetmapper.com/contact.aspx - Problems to generate ASP.Net Membership Tables needed to adapt code for creating first samples.
• http://www.codeplex.com/crudcodegen - Console application and doesn't display errors
• http://www.codeplex.com/IrisGenerator/Wiki/View.aspx?title=Quick%20Start&referringTitle=User%20Guide - Very nice UI but it throws an exception upon creation, after downloading the solution from codeplex I was not able to compile the solution.
• http://www.codeplex.com/ZFCGDAL - It's a Mygeneration Software Template. I loved to work in past with MyGen but the template Ver 2 does not work for some reasons.
• http://www.codeplex.com/affinityorm - It's only an attempt, currently it display tables and data but not more then that.
• http://www.codeplex.com/mitadata/ - Only contains a test project, not usable
• http://www.codeplex.com/LazyFramework/SourceControl/DownloadSourceCode.aspx?changeSetId=15856 - VB.Net - doesn't match my requirements.
  http://www.codeplex.com/netopenfwk - Don't get what this should do with ORM.
  http://www.codeplex.com/DbEntry - A huge framework and sample applications are available but how do I generate code I don't know
• http://www.codeplex.com/Sculpture - Amazing Model Driven approach - you have to watch screen casts!!!!!
• http://www.codeplex.com/jmr - not active since aug. 2007
• http://www.codeplex.com/autodbupdater - Its not an ORM Mapper (DbUpdater keeps a simple record of all scripts that have previously been run against a database and which 'version' of the database each script was run against)
• http://www.codeplex.com/normal/SourceControl/ListDownloadableCommits.aspx - not active since dec. 2007 - download is not compilable
  http://www.codeplex.com/worm - Web ORM - a mix of C# and VB.Net - no clue where to start.
• http://www.codeplex.com/nanomapper - Very nice and simple but it would need extensions e.g. save xml schema and selection of tables which have to be generated.
• http://www.codeplex.com/SimpleDAL - started project need to check in some months agian

Notice: if you have an actual ORM mapper which is not listed above post them please (Only Open Source Projects please and no shareware - comments with links to shareware application will not be approved!!).

How to find readable time difference bettween Oracle date types?

It is not crucial, but it is cool. This way you can find time date/time difference nicely. its very cool :-)

SELECT
lpad(EXTRACT(HOUR FROM(dend_date - dstart_date) DAY TO SECOND),2,’0′) ‘ hour ‘ lpad(EXTRACT(MINUTE FROM(dend_date - dstart_date) DAY TO SECOND),2,’0′) ‘ minute ‘ lpad(EXTRACT(SECOND FROM(dend_date - dstart_date) DAY TO SECOND),2,’0′) ‘ second ‘ “Interval”FROM process_logorder by dstart_date desc;

ref::Oracle® Database SQL Reference10g Release 2 (10.2)B14200-02

thanks to: http://inanme.wordpress.com/2007/01/29/how-to-find-readable-time-difference-bettween-oracle-date-types/

Oracle Error: ORA-00054 - resource busy and acquire with NOWAIT

Today I tried to create an INDEX over 2 columns where one of them were already indexed. After receiving the error ORA-00054 - resource busy and acquire with NOWAIT I needed some time that this is because of previous used INDEX.

Previously used index:
CREATE INDEX index_name ON table_name
(
column_name ASC
)
/

wanted to change it to the following:
CREATE INDEX index_name ON table_name
(
column_name1,column_name2
)
/

all I needed to do were to delete previous INDEX and then it run fine - maybe this is not at any scenario correctly to do but for my case it worked out.

SQL Server SELECT TOP x equivalent in ORACLE

No commet !

SQL Server:
SELECT TOP 10 * FROM myTable

ORACLE:
SELECT * FROM myTable WHERE ROWNUM <= 10

MySQL:
SELECT * FROM myTable LIMIT 10

Oracle Performance - Part 3

Enterprise Manager (and / or GridView)

Pro:

• Great tool for developers and dba's
• very easy to use to prevent of hint-sytaxt failers
• makes easy usage of init.ora tuning parameters

Contra:

• within the execution plan only expected rows are displayed

Oracle Performance - Part 2

Data-Dictonary / Tables / Views and more

• v$ - Views are like "Dynamic Performance Views"
• Not all of them are relevant for tuning
• they are a part of the SYS

Checkout the Oracle ref. books, within the Database Performance Guide and reference book, they describe the most important views

Measuring:

Usually you measure CPU, Elapsed Time, I/O und access at the SGA

Possibilities for analyses:

• statspack
• dv-review / tuning package
• enterprise manager

1. Instance information are retrived from: SELECT * FROM V$SYSSTAT
2. Session information are retrived from: SELECT * FROM V$SESSTAT
3. My sessions information is retrived from: SELECT * FROM V$MYSTAT
4. Events are retrieved from : SELECT * FROM V$EVENT_NAME

Examples:

1. SELECT n.name statsitic, st.VALUE, se.username from v$sesstat st, v$statname n, v$session se WHERE st.statistic#=n.statistic# and st.sid = se.sid and se.username=''your_username'
2. Wait - Statistics -> SELECT * FROM v$waitstat;
3. Show the wait of your actuall session: SELECT sid, event, p1text, p1, p2text, p2, seconds_in_wait sek , state from v$session_wait where sid=YOUR_ID

Possibles values for the value state:

• WAITING - session is waiting
• WAITED UNKNOWN TIME - duration of waiting is unknown (TIMED_STATISTICS)
• WAITED SHORT TIME - last wait was lower then 0.01 sec.
• WAITED KNOWN TIME - duration of last wait

extension for the V$SESSION_WAIT -> V$ACTIVE_SESSION_HISTORY

it display the history of your session, and some new col. are displayed which are making life easier to identify BLOCKING_SESSION, BLOKING_SESSION_SERIAL and some more specific data.

the above described views are a part of the diagnostic pack. before you gone use them, please check your license pack there are some hints! oracle is getting warster then MS, they even deliver everything and install it directly but you're not allowed to use it... so check your lic. package.

Oracle Performance

Performance != Performance

Parameter tuning is one of the more important issues - inti.ora / spfile.ora but dont forget its just a part of it and should not be weight to heavy. Instance tuning is mostly just between 10% - 20% of it.

do not forget about all the other parts:

• Datamodel
• correct indexing
• sql optimizing
• application optimizing - which is in 90% of the cases the issue
• operating system / hard ware optimizing (CPU's, RAM, DISC's, Network)
• usage of actuall versions

how to start:

one of the most important issues do it slowly and step by step. Many times it easier to identify if you change one parameter at the time instead of several together. The worst case, you never know which change have done the work in the end.

Define yourself a clear defined target, its 50% of the work when you can reach your targets. Measuring is not less important, otherwise you have no idea how to compare, right?

Make yourself cycles:

1. tune business rules
2. tune data design
3. tune application design
4. tune logical structure
5. tune database operations
6. tune access path
7. tune memory allocations
8. tune I/O and physically structure
9. tune resource constention
10. tune underlyding platforms

Many times its not a single job, you will have to contact developers / DBA / System Managers / Management / Network People etc., all of them can be a decision maker within changes you need to make - create yourself a plan and check it toghether with all different departments.

how to: prevent concurrent updates on Oracle 10 Database and up

some time ago I had to verify on an Oracle Database 10 that concurrent updates are not possible. The answare to fix this issue were very simple: just use a new functionlity of Oracle 10 (SQL Server support this since years): ORA_ROWSCN

here a small example:

CREATE OR REPLACE TRIGGER triggerName
BEFORE INSERT ON TableName
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
:NEW.ORA_ROWSCN := '1';
End triggerName;

Notice: in Oracle this is a hidden field.