SQL Server – Slow Query on View Crossing Multiple Databases

execution-planperformancesql serversql server 2014view

Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.

Please note that each database can be split using the INSTANCE_NO
Also, each table has only about 100 rows.

ALTER VIEW [dbo].[VI_MULTI_DUMMY]  AS
SELECT 1 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM [HUB_1].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 2 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_2].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 4 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_4].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

I have a huge performance problem for a query that only accesses one database:

--> 0 sec
SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] , ... [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

--> 5-7 sec
select T.* from VI_MULTI_DUMMY AS T  WITH(NOLOCK) where  INSTANCE_NO = 3 OPTION(RECOMPILE)

I've used RECOMPILE, NOLOCK, … and I'm out of idea's, who can help me?

EDIT : SOLUTION
A solution as stupid as the problem :
Go to each database it's properties–> Options–> Automatic.
Set 'Auto Close' to false

Best Answer

Never mind,

I just went with sp_executesql

Performance issue are gone, readability gone

SET @myInst = '[db2Name]'
SET @sqlReplaced = REPLACE('myQry','{@myInst}',@myInst)

EXECUTE sp_executesql
@statement                  = @sqlReplaced,
@ParmDefinition             = @ParmDefinition,

@clientNo                   = @clientNo,
@lastTransactionDate                = @lastTransactionDate,
@msInstanceNo               = @msInstanceNo, 
@dateFrom                   = @dateFrom , 
@dateUntil                  = @dateUntil

Related Solutions

Postgresql – Optimizing query using view on EAV structure

This is one (of many) downsides of EAV designs.

You can't really improve the JOIN: because of the necessary complexity, a cost based optimiser won't get to the perfect plan. It finds "good enough"

Suggestions:

don't use a view: use aggregate type queries (eg COUNT(*) = 2 if I match both height and weight)
use a trigger to maintain a real (or sparse) table and query that

The first option scales better becauses a few indexes on the main EAV fact table can cover all queries nicely.

SQL Server Linked Server – Query Performance Tips

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

Best Answer

Related Solutions

Postgresql – Optimizing query using view on EAV structure

SQL Server Linked Server – Query Performance Tips

Related Question