Friday, December 19, 2008

Arithmetic Abort Enabled

I have a stored procedure in SQL Server 2005 which takes a lot of a time to run from my .Net 2.0 Framework application and sometimes even gives a "Timeout expired".
Executing the same stored procedure in SQL Server Management Studio gives the result in less than 2 seconds! Why??

The answer lays in one single setting. When SQL Server Management Studio connects to the database it sets the "Arithmetic Abort Enabled" on. This option triggers the database to create new executions plans for the stored procedures.

Setting this option improved the performance of my .Net 2.0 Framework application!

Tuesday, August 05, 2008

Simple reflection form

Haven't you had some poco's (plain old CLR object) you just want to edit, but you had to make a form for each object?

The solution to this is dot net's reflection. The SimpleReflectionForm takes an object like the Customer below and shows this in a form.


public class Customer
{
public Guid Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public bool Married { get; set; }
}

Just call the form:


Customer c = new Customer
{
Id = Guid.NewGuid(),
Name = "Frank",
Age = 31,
Married = true
};

using (SimpleReflectionForm f
= new SimpleReflectionForm(c, "Id"))
{
f.ShowDialog();
}

The following form is shown:


As you can see the Id is ignored, by the optional parameter string[] propertiesToExclude.
The form supports all native .net types (int, string, datetime, guid etc). Guid and numeric types are verified when the Ok button is pressed.


You can download the source of SimpleReflectionForm
here.

Enjoy!

Monday, August 04, 2008

UNION with DISTINCT via ODBC to ORACLE

In a VBA application (MS Access XP) with linked tables to Oracle the following query completely crashes Access!

SELECT A1.V_ID, A1.PR_ID, A1.AREA_ID, A1.TYPE,
A1.AREA, A1.AREA_MAIN_ID, A1.ACTIVE
FROM AREAS AS A1
UNION SELECT DISTINCT A2.V_ID, A2.PR_ID,
-1 AS AREA_ID, A2.TYPE, '' AS AREA,
A2.AREA_MAIN_ID, -1 AS ACTIVE
FROM AREAS AS A2;


Solution: convert the distinct to a group by

SELECT A1.V_ID, A1.PR_ID, A1.AREA_ID, A1.TYPE,
A1.AREA, A1.AREA_MAIN_ID, A1.ACTIVE
FROM AREAS AS A1
UNION (
SELECT A2.V_ID, A2.PR_ID, -1 AS AREA_ID, A2.TYPE,
'' AS AREA, A2.AREA_MAIN_ID, -1 AS ACTIVE
FROM AREAS AS A2
GROUP BY A2.V_ID, A2.PR_ID, A2.TYPE, A2.AREA_MAIN_ID);

Thursday, July 24, 2008

Enable & disable foreign keys in Oracle


CREATE OR REPLACE PROCEDURE DIS_FK_CONSTR as
CURSOR c iS
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'ENABLED';

BEGIN
FOR i IN c LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.TABLE_NAME ||
' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
END LOOP;

END DIS_FK_CONSTR;
/


CREATE OR REPLACE PROCEDURE ENA_FK_CONSTR as
CURSOR c iS
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'DISABLED';

BEGIN
FOR i IN c LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.TABLE_NAME ||
' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
END LOOP;

END ENA_FK_CONSTR;
/