Skip to content
+1-888-319-3663

COMMUNITY FORUM

Maps and Query Editor

Joe Powers asked 6 years ago
When clicking MODIFY in a map Datasource window SmartConnect times out for 30 seconds and returns a timeout error. I searched the KB and found this:
This week we have an article from our service delivery manager at eOne. He has discovered a tip to improve sql performance when using the query builder windows in SmartConnect:
We have had a few people that have had timeout issues when creating queries in our SmartConnect Query Builder window accessed by hitting the modify button when you have a SQL ODBC datasource. For most people the list of tables is quickly populated from whatever database that was specified. Others will see it sit there for a while and ultimately get a SQL timeout. What is actually running behind the scenes is a standard SQL Server procedure called sp_tables. Again, on most systems we see results returned very quickly if we run the following statement in SQL Server Management Studio:
  EXEC sp_tables
For people that are having a timeout issue like described above we can simply restart the SQL Server service and SQL Server will run that query very quickly and you shouldn’t have timeout issues anymore. Most likely this occurs because of the SQL Server running for a long period of time and many processes (ie Creating databases, tables, views, restoring databases, etc) have fragmented the internal index.
I hope this helps anyone who has experienced any timeouts in SmartConnect or even someone potentially using the sp_tables procedure for other purposes outside of SmartConnect.
Chris Dew
 
Except that by using the SQL Profiler I can see that sp_tables runs in under 2 seconds, the query that causes the timeout is this which SmartConnect issues after sp_tables:
 
select pf.TABLE_CATALOG as PK_TABLE_DATABASE,  pf.TABLE_SCHEMA as PK_SCHEMA_NAME,  pf.TABLE_NAME as PK_TABLE_NAME,  pf.COLUMN_NAME as PK_FIELD_NAME,  ff.TABLE_CATALOG as FK_TABLE_DATABASE,  ff.TABLE_SCHEMA as FK_SCHEMA_NAME,  ff.TABLE_NAME as FK_TABLE_NAME,  ff.COLUMN_NAME as FK_FIELD_NAME,  pf.ORDINAL_POSITION as ORDINAL
 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc,  INFORMATION_SCHEMA.KEY_COLUMN_USAGE ff,  INFORMATION_SCHEMA.KEY_COLUMN_USAGE pf
 where (rc.CONSTRAINT_NAME=ff.CONSTRAINT_NAME) and  (rc.CONSTRAINT_SCHEMA=ff.CONSTRAINT_SCHEMA) and  (rc.CONSTRAINT_CATALOG=ff.CONSTRAINT_CATALOG) and  (rc.UNIQUE_CONSTRAINT_NAME=pf.CONSTRAINT_NAME) and  (rc.UNIQUE_CONSTRAINT_SCHEMA=pf.CONSTRAINT_SCHEMA) and  (rc.UNIQUE_CONSTRAINT_CATALOG=pf.CONSTRAINT_CATALOG) and  (ff.ORDINAL_POSITION=pf.ORDINAL_POSITION)
order by pf.TABLE_SCHEMA, pf.TABLE_NAME, ff.TABLE_SCHEMA, ff.TABLE_NAME, pf.ORDINAL_POSITION
 
Running this directly in SSMS show that this query takes about 2.5 minutes, hence the timeout.
 
How can this be fixed ????
 
 
Answers
Patrick Roth Staff answered 6 years ago
Joe,
You don’t note your SQL version however I know that you are on SQL 2014 as we have one other person running into this issue.
From your research (noted offline) and a bit I’ve done on the previous case, the underlying issue is a change to the cardinality estimator (CE) in SQL 2014 and how it is handling this query.  IN your case (and my other partners) it isn’t handling it well and runs too long easily overwhelming our 30 second timeout for the operation.
What is interesting is that on 3 separate SQL 2014 instances here, we don’t have this issue.  On my SQL 2014 machine (using compat level 120/SQL 2014 so apples to apples), the query above runs in 0-1 seconds.
More interesting, when I apply the QUERYTRACEON 9481 to the query above it runs consistently in 4-6 seconds. 
I would maybe suspect that it has something to do with how many tables/indexes that you have in the DB and you appear to have Wennsoft loaded (in the offline results) which adds in a large amount of tables to the company db.  But that is just a guess of course.
At this time since it is a change that we’d have to make on the query itself, the only thing you can do on your end is enable the global 9481 traceflag on your SQL or change your SQL Compatibility level to 2012.
We’re looking at this for a future service pack.
 
Joe Powers answered 6 years ago
Wennsoft ??, Not sure what you are referring to here, and I wouldn’t want you chasing down a false lead.
Regarding setting the dB to 2012 Level, The Microsoft GP Documentation states:
4. Database Compatibility:  If you are moving to a new SQL Server in addition to the upgrade to Microsoft Dynamics GP 2015, you can restore your databases to the new SQL Server and start your upgrade.  Please refer to KnowledgeBase Article 878449 for the steps to move to a new SQL Server.  Once the databases are restored to the new SQL Server, you must change the database compatibility. 
a. In the SQL Server Management Studio. right-click your database and click Properties. 
b. Under Select a Page on the left, click Options.
c. Change the Compatibility Level to match the version of SQL Server you are running SQL 2012 = 110, SQL 2014 = 120.
I noted the MUST. Therefore my workaround of the Global SQL Startup parameter for Trace 9481
 
Patrick Roth Staff answered 6 years ago
Just wanted to follow up on this thread and summarize the issue and the resolution.
Due to the change of the CE on SQL 2014, the above query that SC runs on the SQL Change map:
select pf.TABLE_CATALOG as PK_TABLE_DATABASE…
will time out when using SmartConnect 2014/R2.
The reason is the timeout value of the query performed by SmartConnect is hard coded to 30 seconds (if I recall) and it is a query timeout and not a connection timeout (which is why changing the SC connectiontimeout value has no effect).
For SmartConnect 2014, the only solution that we can come up with (since we can’t change the query that SC is using) is to enable the SQL Trace Flag 9481 or to change the SQL Compatibility level to SQL 2012.
A better solution would be to update to SmartConnect 20.15.0.51.  The method/query was changed so that it works fine under SQL 2014 as well as previous version.

If you would like to submit an answer or comment, please sign in to the eOne portal.