Exclude/Include filter not working as expected, SLOW performance
we have 13000 objects in our database.
12800 of them are all in the dbo schema which contains the standard logic for our product.
The rest 200 are in the customer specific schema.
We are not interested in source controlling the standard logic, since we get it from our supplier.
We need to source control our customer specific changes. For that every change is made in another schema.
We can perfectly filter the objects out for source control with your tool.
(so they do not appear on the commit screen). However it is very very slow, that's why I suspected the tool not to filter the objects "server-side"
I started a profler session and my suspicion was confirmed: the tool is getting all objects and then filters the rows "client-side".
So first getting 13000 objects to not use 12800 of them. It is also using a lot of memory (after 2 hours we get memory exceptions).
Could you please change this? Now I'm trying to convince the team to use those tools (I used them on smaller projects).
But with this speed, they won't agree.
Here the queries I catched when opening commit tab with filter on:
SELECT so.name AS TableName,
ss.name AS TableOwner,
cc.name AS ConstraintName,
cc.objectid AS Constraintid,
cc.isnotforreplication AS NotForReplication,
cc.isnottrusted AS WithNoCheck,
cc.isdisabled AS Disabled,
sc.name AS ColumnName,
cc.parentcolumnid AS colid,
cc.definition AS Text,
CONVERT(bit, CASE cc.issystemnamed WHEN 1 THEN 1 ELSE 0 END) As issystemnamed
FROM sys.checkconstraints cc WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK) ON so.objectid=cc.parentobjectid
LEFT JOIN sys.schemas ss WITH (NOLOCK) ON ss.schemaid=so.schemaid
LEFT JOIN sys.columns sc WITH (NOLOCK) ON sc.columnid=cc.parentcolumnid AND sc.objectid=cc.parentobjectid
ORDER BY so.name, cc.name
SELECT s.name AS ProcedureName,
p.name AS ProcedureOwner,
ss.name AS SchemaName,
s.objectid AS id,
CONVERT(bit, ISNULL(sm.isschemabound,0)) AS SchemaBound,
CONVERT(bit, ISNULL(sm.usesansinulls, 0)) AS AnsiNulls,
CONVERT(bit, ISNULL(sm.usesquotedidentifier,0)) AS QuotedIdentifier,
s.type AS ProcedureType,
CASE WHEN am.nullonnullinput IS NULL THEN sm.nullonnullinput ELSE am.nullonnullinput END AS nullonnullinput,
COALESCE(p2.name,p3.name) AS executeas,
CASE WHEN am.executeasprincipalid = -2 THEN 1 ELSE 0 END,
CASE WHEN sm.executeasprincipalid = -2 THEN 1 ELSE 0 END)) AS executeasowner,
a.name AS assembly,
case when sm.definition is null THEN CONVERT(bit, 1) else CONVERT(bit, 0) END AS encrypted
FROM sys.objects s WITH (NOLOCK)
LEFT JOIN sys.databaseprincipals p WITH (NOLOCK) ON p.principalid=s.principalid
LEFT JOIN sys.schemas ss WITH (NOLOCK) ON ss.schemaid=s.schemaid
LEFT JOIN sys.sqlmodules sm WITH (NOLOCK) ON sm.objectid=s.objectid
LEFT JOIN sys.assemblymodules am WITH (NOLOCK) ON am.objectid=s.objectid
LEFT JOIN sys.assemblies a WITH (NOLOCK) ON a.assemblyid=am.assemblyid
LEFT JOIN sys.databaseprincipals p2 WITH (NOLOCK) ON p2.principalid=am.executeasprincipalid
LEFT JOIN sys.databaseprincipals p3 WITH (NOLOCK) ON p3.principalid=sm.executeasprincipalid
WHERE s.type IN (N'P', N'PC') AND
(CAST(CASE WHEN s.ismsshipped = 1 then 1
WHEN (SELECT majorid FROM sys.extendedproperties WHERE
majorid = s.objectid AND minorid = 0 AND class = 1 AND
name = N'microsoftdatabasetools_support') IS NOT NULL THEN 1
END AS bit)=0)
AdminJames Billings (Admin, Redgate) commented
The filter works *after* the comparison process across the whole database has finished. While I can see how this seems wrong at first glance, we need to do it this way to correctly work out all the dependencies in the database. It may well be that some of the objects that are filtered out are relied on by objects you want to change, and we need to be able to warn you of this, and it's required by our engine to understand the structure of the whole DB to function correctly - the same as SQL Compare.
If there's no dependency link between the vendor objects and the customer ones, could you split out the DB to just contain the objects you're frequently working on? That may help.
I agree! Please apply the filter serverside instead of clientside.