Outdoc

  
Hi,

I am having problems with Outdoc.
When I try to select the espaces menu option I immediately get an error stating that the estimated time the query will take exceeds the configured threshold.

This is on our QA environment and I deleted all old espace versions, this made no difference.
Can anyone make a suggestion on how to get this working?
Hi Ronald,

Can you copy here the error you are getting?
If possible also the correspondent error log from Service Center.

Regards,
João Rosado
Hi João,

Here is the Error Message I get.

Error in advanced query GetEspaces in Preparation in eSpace_List in MainFlow in OutDoc (SELECT {Espace}.*, {Espace_Version}.[Description], {Espace_Version}.[Uploaded_Date], {UserMT}.*  FROM  {Espace}  INNER JOIN {Espace_Version} ON    (    COALESCE({Espace}.[Version_Id], 0) = 0    AND     {Espace_Version}.[Id] = (SELECT max(version2.Id) FROM {Espace_Version} version2 WHERE version2.eSpace_Id = {Espace}.[Id])    OR     {Espace}.[Version_Id] = {Espace_Version}.[Id]   )    LEFT JOIN {UserMT} ON COALESCE({Espace_Version}.[Uploaded_By], 0) = COALESCE({UserMT}.[Id], 0)  WHERE( Lower({Espace}.[Name]) LIKE (@eSpaceNamePattern )) AND {Espace}.[Is_Active] = 1 AND {Espace}.[Is_System] = 0  ORDER BY @OrderBy ): The query has been canceled because the estimated cost of this query (336) exceeds the configured threshold of 300. Contact the system administrator.
Hi Ronald,

That is indeed strange, that query doesn't look to have anything that could cause a estimated cost of 336 seconds.
I can make some changes to it and see if it solves the issue for you, but would like to understand first if it is really a query problem of if it's just the SQL Server calculating it wrong.

That limit that it complains is set at the database level (the default is 0, not limited). So would it be possible to ask your database administrator to increase it just a bit to 360?
I would like to know if the query without hitting the estimated limit really takes a long time (the estimated 336 seconds) or is fast.

Also are there maintenance plans being run frequently on your database?
In particular Rebuild Indexes and Update Statistics Plans.

Regards,
João Rosado
Hi João,

We do not have maintenance plans in place as yet. 
So from what you have said I imagine that is the reason for the problem?

Regards
Ronald
Solution
Hi,

Well, yes it can be a reason.
Basically you have a database setting (not the default and not set by the platform) that says that all queries with estimatives higher than 300seconds should be automatically canceled.

Having that kind of setting on without maintenance plans is dangerous, since it can be using wrong information to calculate the plans.
You may have problems in other parts of the platform as well.

I would still like to know if without the limitation the query takes a long time to execute or not.
I see a couple changes that could potentially be made in the query ..but in my system it is very fast so I can't measure any improvements.

Regards,
João Rosado
Solution