Below is code example to demonstrate that, SQL queries can be directly executed from AX 2012 to improve performance in some cases.
str sqlStatement = strFmt(@"
IF NOT EXISTS (SELECT 1 FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME =
'ModelElement')
BEGIN
CREATE TABLE [dbo].[ModelElement](
[ElementType] [int] NOT NULL
DEFAULT (0),
[RootHandle] [int] NOT NULL DEFAULT
(0),
[ParentHandle] [int] NOT NULL
DEFAULT (0),
[ElementHandle] [int] IDENTITY(1,1)
NOT NULL,
[Name] [nvarchar](255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
[AxId] [int] NOT NULL DEFAULT (0),
[ParentId] [int] NULL DEFAULT (0),
[Origin] [uniqueidentifier] NULL,
[PartOfInheritance] [INT] NOT NULL
DEFAULT (0),
CONSTRAINT
[PK_ModelElement_ElementHandle] PRIMARY KEY CLUSTERED
(
[ElementHandle] ASC
),
CONSTRAINT
[I_ModelElement_ElementId] UNIQUE NONCLUSTERED
(
[ElementType] ASC,
[AxId] ASC,
[ParentHandle] ASC,
[Name] ASC
),
CONSTRAINT
[I_ModelElement_ElementName] UNIQUE NONCLUSTERED
(
[ElementType] ASC,
[Name] ASC,
[ParentHandle] ASC
)
)
CREATE UNIQUE INDEX I_ModelElement_NameOrderList
ON [dbo].ModelElement( ParentHandle, ElementType, Name )
INCLUDE ( AxId )
--
CREATE INDEX I_ModelElement_HandleOrderList ON ModelElement(
ParentHandle, ElementType, AxId )
CREATE UNIQUE INDEX
I_ModelElement_ParentHandle ON [dbo].ModelElement(ParentHandle, ElementHandle)
CREATE NONCLUSTERED INDEX
[I_ModelElementRecursions] ON [dbo].[ModelElement]
(
[ParentHandle] ASC,
[ElementHandle] ASC,
[ElementType] ASC
)
INCLUDE
(
[AxId],
[Name]
)
CREATE NONCLUSTERED INDEX
[I_ReadHeaderExt] ON [dbo].[ModelElement]
(
[ElementType] ASC,
[ParentHandle] ASC,
[AxId] ASC
)
INCLUDE (
[ElementHandle],
[Name],
[Origin]
)
CREATE NONCLUSTERED INDEX
[I_RootHandle] ON [dbo].[ModelElement]
(
[RootHandle]
)
CREATE UNIQUE INDEX [I_ModelElement_Id1Id2]
ON [dbo].[ModelElement] (ElementType, ParentId, AxId)
WHERE (ParentId <> 0 AND AxId
<> 0)
END
ELSE
BEGIN
DELETE FROM ModelElement;
END");
Connection
sessionConn = new
Connection();
Statement
statement = sessionConn.createStatement();
new
SqlStatementExecutePermission(sqlStatement).assert();
statement.executeUpdate(sqlStatement);
CodeAccessPermission::revertAssert();
