Hi,
I'm currently in the process of optimising our BI reports due to poor refresh speeds and the load they place on the SQL server. We are using an on-premise data gateway.
I've noticied that there are 3 SQL queries which are causing the high load, all 3 are being called by the Mashup Engine. Could anyone give me a bit more information about these? They are called several times per refresh over the current 20 minute period to complete a refresh and each take multiple seconds to complete, compounding to multiple minutes in total.
The where clause of the queries have been shortened.
Query 1
select
s.name TABLE_SCHEMA,
o.name TABLE_NAME,
i.name INDEX_NAME,
cc.name COLUMN_NAME,
cast ( ic.key_ordinal as bigint ) ORDINAL_POSITION,
i.is_primary_key PRIMARY_KEY
from sys.objects o
join sys.schemas s on s.schema_id = o.schema_id
join sys.indexes as i on i.object_id = o.object_id
join sys.index_columns as ic on ic.object_id = i.object_id and ic.index_id = i.index_id
join sys.columns as cc on ic.column_id = cc.column_id and ic.object_id = cc.object_id
where ( i.is_primary_key = ? or i.is_unique_constraint = ? or i.is_unique = ? ) and o.type in ( ? ) and ic.key_ordinal <> ? and ( ( s.name = N ? and o.name = N ? ) or ( s.name = N ? and o.name = N ? ) or ( s.name = N ? and o.name = N ? ) or (...)
order by i.name, s.name, o.name
Query 2
select
convert ( nvarchar, fk.object_id ) FK_NAME,
cast ( f.constraint_column_id as bigint ) ORDINAL,
s1.name TABLE_SCHEMA_1,
o1.name TABLE_NAME_1,
c1.name PK_COLUMN_NAME_1,
s2.name TABLE_SCHEMA_2,
o2.name TABLE_NAME_2,
c2.name PK_COLUMN_NAME_2,
f.constraint_object_id,
f.constraint_column_id
from sys.foreign_key_columns f
join sys.foreign_keys fk on f.constraint_object_id = fk.object_id
join sys.objects o1 on o1.object_id = f.parent_object_id
join sys.schemas s1 on s1.schema_id = o1.schema_id
join sys.objects o2 on o2.object_id = f.referenced_object_id
join sys.schemas s2 on s2.schema_id = o2.schema_id
join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = f.parent_column_id
join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = f.referenced_column_id
where ( ( s1.name = N ? and o1.name = N ? ) or ( ...))
union select convert ( nvarchar, fk.object_id ) FK_NAME, cast ( f.constraint_column_id as bigint ) ORDINAL, s1.name TABLE_SCHEMA_1, o1.name TABLE_NAME_1, c1.name PK_COLUMN_NAME_1, s2.name TABLE_SCHEMA_2, o2.name TABLE_NAME_2, c2.name PK_COLUMN_NAME_2, f.constraint_object_id, f.constraint_column_id
from sys.foreign_key_columns f
join sys.foreign_keys fk on f.constraint_object_id = fk.object_id
join sys.objects o1 on o1.object_id = f.parent_object_id
join sys.schemas s1 on s1.schema_id = o1.schema_id
join sys.objects o2 on o2.object_id = f.referenced_object_id
join sys.schemas s2 on s2.schema_id = o2.schema_id
join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = f.parent_column_id
join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = f.referenced_column_id
where ( ( s2.name = N ? and o2.name = N ? ) or (...))
order by f.constraint_object_id, f.constraint_column_id
Query 3
select
s.name TABLE_SCHEMA,
o.name TABLE_NAME,
c.name COLUMN_NAME,
cast ( c.column_id as bigint ) ORDINAL_POSITION,
c.is_nullable IS_NULLABLE,
case when ( t.is_user_defined = ? and t.name is not ? ) then t.name when ( c.system_type_id = ? or t.name is ? ) then ? else t_system.name end DATA_TYPE,
case when ( c.system_type_id in ( ? ) ) then ? when ( c.system_type_id in ( ? ) ) then ? else ? end NUMERIC_PRECISION_RADIX,
c.precision NUMERIC_PRECISION,
case when ( c.system_type_id in ( ? ) ) then ? else c.scale end NUMERIC_SCALE,
case when ( c.system_type_id in ( ? ) ) then c.scale else ? end DATETIME_PRECISION,
case when ( c.system_type_id in ( ? ) ) then floor ( c.max_length / ? ) when ( c.system_type_id in ( ? ) ) then c.max_length else ? end CHARACTER_MAXIMUM_LENGTH,
cast ( e.value as nvarchar ( max ) ) DESCRIPTION,
d.definition COLUMN_DEFAULT,
cc.definition COLUMN_EXPRESSION,
case when c.is_identity = ? or c.is_computed = ? or t.system_type_id = ? or c.generated_always_type > ? then ? else ? end IS_WRITABLE, ? FIELD_CAPTION
from sys.objects o
join sys.schemas s on s.schema_id = o.schema_id
join sys.columns c on o.object_id = c.object_id
left join sys.types t on c.user_type_id = t.user_type_id
left join sys.types t_system on t.system_type_id = t_system.user_type_id
left join sys.default_constraints d on d.object_id = c.default_object_id
left join sys.computed_columns cc on c.object_id = cc.object_id and c.column_id = cc.column_id
left join sys.extended_properties e on o.object_id = e.major_id and c.column_id = e.minor_id and e.class = ? and e.name = ?
where ( ( s.name = N ? and o.name = N ? ) or (...)
