1

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

  • This looks like a partitioned view, which makes me wonder if you have the appropriate check constraints on your primary key in each table. Also, stop using NOLOCK. – Randolph West Mar 10 '18 at 6:57
  • Can you do sp_refreshview and run the query ? – Kin Shah Mar 10 '18 at 19:00
  • sp_refreshview didn't work, not to mention the view is rebuild after each newly added database. Why can't I use NOLOCK? woudn't it work faster & have less problems with the replications on it? Are you telling me move from a view with a dynamic INSTANCE_NO to a table like this? With a different default INSTANCE_NO CHECK? CREATE TABLE [TA_DUMMY] ( [V1] INT NOT NULL, [DESC_1] VARCHAR(255) NULL, [INSTANCE_NO] INT DEFAULT(1) CHECK([INSTANCE_NO] = 1) ) – Nils Coussement Mar 12 '18 at 13:45
1

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
0

Anwser :

Go to each database it's properties --> Options --> Automatic.

Set 'Auto Close' to false

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.