July 17, 2015 at 9:29 am
Anyone experience an issue where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column. Any help would be appreciated.
July 17, 2015 at 12:19 pm
What is the data type of the column you are excluding? If it is a LOB type (XML, NVARCHAR(MAX), etc...) including/excluding the column can have a large impact on performance like you are seeing.
Also what are the indexes on the table? What are the estimated execution plans for the 2 queries?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2015 at 12:55 pm
There are no indexes on the table and the two columns are nvarchar(256).
In the actual query plan when the INSERT into the staging table is not included the remote query is taking 100% of the plan. When the INSERT into the staging table is included the query plan shows the INSERT into the table 95% of the plan and the remote query 5%. It's interesting that the actual number of rows (893) varies from the estimated number of rows (74072) in the "table insert" part of the actual query plan.
July 17, 2015 at 1:03 pm
Can you post the query or a generic representation of it?
Cross server queries, whether using Linked Servers or openrowset are notorious for performance issues depending on how you code them. Also it depends on the permissions the user connecting to the remote server has, it may not be able to get the proper statistics and be pulling ALL the data across from the remote server when adding the additional column.
Can you post the execution plans? If you are concerned about disclosing database information you can use SQLSentry PlanExplorer (free version available) to anonymize the execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply