·
1 min read

Performance – memo/ntext fields

Performance issues in AX can be some of the most difficult to troubleshoot. Particularly as there’s so many different areas which can potentially be the cause/solution to the issue. This post will just talk about one of the potential causes of bad performance, no doubt we’ll cover some more performance issues in later posts.

One of the common causes of bad performance is memo (in the database – ntext) fields. Unecessarily selecting memos can cause performance degredation. The business users often insist that they need the field to be so large that memo type is required, but in reality the length is rarely used. So I’ve created a bit of SQL which checks through a database, finds all tables with memo columns and details the length of the largest value in that field and also the total number of records in the table. This can give you the data you need to decide whether the memo field is a problem and whether is can be changed to a string (in the database nvarchar) of up to 1000 characters instead.

declare
@SQL varchar(255),
@ColumnName varchar(255),
@TableName varchar(255)

set quoted_identifier off

create table #foo
(
tableName varchar(100),
columnName varchar(100),
maxLength int,
maxRecords int
)

declare tableCursor CURSOR for

select tablename = t.name, columnname = c.name
from sys.columns c join sys.tables t
on c.object_id = t.object_id
where c.system_type_id = 99
order by t.name

OPEN tableCursor

FETCH TableCursor into @TableName, @ColumnName
WHILE @@Fetch_Status = 0
BEGIN
set @SQL = ‘insert into #foo select “‘ + @TableName + ‘”, “‘+@ColumnName+'”,
maxlength = isnull(max(datalength(‘ + @ColumnName + ‘)),0)/2, maxRecords = isnull(count(*),0) from ‘

+ @TableName
exec(@SQL)
FETCH TableCursor into @TableName, @ColumnName
END
select * from #foo
drop table #foo
CLOSE TableCursor
DEALLOCATE TableCursor
RETURN
EMEADAXSupport

Related posts