How to enforce SQL Server 2005 to use Worktable?

I have a problem in SQL Server 2005. In some cases SQL Server produces an execution plan of complex query (8 joins of views, some of views contains couple of joins) which does not contain a woktable creation in tempdb. As a result time of query execution increasion for about 5 seconds to about 4 minutes. All necessary indexes are created. It sims all data located in cache. Is there any way to enforce SQL Server to create worktable?

Query

SELECT [a0].[id],[a0].[Priority],[a0].[Heading],[a0].[DocumentDate],[a0].[LastName],[a0].[FirstName],[a2].[Name],[a1].[Position],[a4].[ContactTime],[a4].[Subject],[a0].[WorkPhone],[a0].[MobilePhone],[a0].[FaxNumber],[a0].[PrimaryEmail],[a5].[ContactTime],[a6].[Value],[a7].[id],[a7].[id_class],[a8].[id],[a8].[id_class],[a0].[FIO]
FROM [Bkc_EBM_Person_View] [a0]
LEFT JOIN [Bkc_EBM_ContactsInfo_View] [a3] ON ( a0.ContactsInfo_id = a3.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a4] ON ( a3.LastContact_id = a4.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a5] ON ( a3.NextContact_id = a5.[id] )
LEFT JOIN [Bkc_EBM_PersonType_View] [a6] ON ( a0.PersonType_id = a6.[id] )
LEFT JOIN [Bkc_EBM_Employment_View] [a1] ON ( a0.PrimaryEmployment_id = a1.[id] )
LEFT JOIN [Bkc_EBM_Client_View] [a2] ON ( a1.Client_id = a2.[id] )
LEFT JOIN [Bkc_EBM_Person_View] [a7] ON ( a0.Responsible_id = a7.[id] )
LEFT JOIN [Bkc_EBM_Department_View] [a8] ON ( a0.Department_id = a8.[id] )

Statistics

(2454 row(s) affected)

Table 'ReadRights'. Scan count 2455, logical reads 109411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 11102, logical reads 22266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Department'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 38986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 4826, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 83632238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_PersonType'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 9816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 231719 ms,elapsed time = 253491 ms.

Execution plan

http://rsdn.ru/File/22090/plan1.rar

Please post the view definitions.

It looks like your view definition for BKc_EBM_Employment has a bad join in it, You are processing 640Gb of data from the BKcEBM_Employment table

|||try forcing a recompile.

e.g.
select *
from ...
option (recompile)|||

Below is the view definition

Bkc_EBM_Employment is a table which connects Bkc_EBM_Person and Bkc_EBM_Client in a many-to-many relation.

ReadRights is a table which defines rights of user account to view particular document in system

processaccounts is a table, to which Application server writes corrspondence between current spid and user account id, before executing a query

USE [Oblik_CRM]

GO

/****** Object: View [dbo].[Bkc_EBM_Employment_View] Script Date: 10/04/2006 10:18:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[Bkc_EBM_Employment_View] WITH SCHEMABINDING AS

(SELECT [id], 1370 AS [id_class], cast([id] as varchar(64))+'_1370' AS [id_record], t.[EmployedPerson_id], t.[EmployedPerson_class_id], t.[Client_id], t.[Client_class_id], t.[Department_id], t.[Department_class_id], t.[Position], t.[PlaceOfWorkType_id], t.[PlaceOfWorkType_class_id], t.[RoleEmployee_id], t.[RoleEmployee_class_id], t.[FRC_id], t.[FRC_class_id], t.[InnerPhone], t.[IsPrimary], t.[IsFired], t.[EmploymentDate], t.[FiredDate], t.[FiredReason_id], t.[FiredReason_class_id], t.[Description], t.[RightToSign], t.[Heading], t.[Version] FROM dbo.Bkc_EBM_Employment t

inner join dbo.ReadRights r on (t.EmployedPerson_id = r.object_id) inner join dbo.ProcessAccounts pa WITH (NOLOCK) on (r.account_id = pa.account_id) where pa.spid=@.@.spid

)

|||

I rewrite the query without using viws. It helps a little because in new query data, that not needed by this query not queried by the views. But time of query execution is still to long. About 2 minutes

declare @.userID int

set @.userID = 104356

declare @.qp_0 int

set @.qp_0 = 0

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid INSERT ProcessAccounts WITH (ROWLOCK) (account_id, spid) VALUES (@.userID, @.@.spid)

SELECT

[a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail],

[a1].[FIO],

[a3].[ContactTime],

[a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO],

[a5].[Value],

[a6].[Position],

[a7].[ContactTime]

FROM

(

SELECT [a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail], a0.Responsible_id, a0.ClientType_id, a0.ContactsInfo_id, a0.IsTemplate FROM [Bkc_EBM_Client] [a0]

INNER JOIN [ReadRights] [a0r] ON ([a0].[id] = [a0r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a0pa] ON ( [a0r].[account_id] = [a0pa].[account_id] )

) [a0]

LEFT JOIN [Bkc_EBM_ClientType] [a5] ON ( a0.ClientType_id = a5.[id] )

LEFT JOIN

(

SELECT [a1].[id], [a1].[FIO] FROM [Bkc_EBM_Person] [a1]

INNER JOIN [ReadRights] [a1r] ON ([a1].[id] = [a1r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a1pa] ON ( [a1r].[account_id] = [a1pa].[account_id] )

) [a1] ON ( a0.Responsible_id = a1.[id] )

LEFT JOIN [Bkc_EBM_ContactsInfo] [a2] ON ( a0.ContactsInfo_id = a2.[id] )

LEFT JOIN

(

SELECT [a3].[id], [a3].[ContactTime] FROM [Bkc_EBM_Contact] [a3]

INNER JOIN [ReadRights] [a3r] ON ([a3].[id] = [a3r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a3pa] ON ( [a3r].[account_id] = [a3pa].[account_id] )

) [a3] ON ( a2.LastContact_id = a3.[id] )

LEFT JOIN

(

SELECT [a4].[id], [a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO], [a4].[PrimaryEmployment_id] FROM [Bkc_EBM_Person] [a4]

INNER JOIN [ReadRights] [a4r] ON ([a4].[id] = [a4r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a4pa] ON ( [a4r].[account_id] = [a4pa].[account_id] )

) [a4] ON ( a2.LastContactPerson_id = a4.[id] )

LEFT JOIN

(

SELECT [a6].[id], [a6].[Position] FROM [Bkc_EBM_Employment] [a6]

INNER JOIN [ReadRights] [a6r] ON ([a6].[EmployedPerson_id] = [a6r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a6pa] ON ( [a6r].[account_id] = [a6pa].[account_id] )

) [a6] ON ( a4.PrimaryEmployment_id = a6.[id] )

LEFT JOIN

(

SELECT [a7].[id], [a7].[ContactTime] FROM [Bkc_EBM_Contact] [a7]

INNER JOIN [ReadRights] [a7r] ON ([a7].[id] = [a7r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a7pa] ON ( [a7r].[account_id] = [a7pa].[account_id] )

) [a7] ON ( a2.NextContact_id = a7.[id] )

WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL )

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid

Statistics

(1750 row(s) affected)

Table 'ReadRights'. Scan count 5891, logical reads 222578, physical reads 425, read-ahead reads 283, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 6, logical reads 5891, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 7000, physical reads 98, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 33686288, physical reads 33, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 7000, physical reads 60, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo_View'. Scan count 0, logical reads 3500, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ClientType_View'. Scan count 0, logical reads 3500, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 33252, physical reads 43, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 111797 ms, elapsed time = 123889 ms.

Execution Plan

http://rsdn.ru/File/22090/plan4.rar

|||

Please post the scripts for all the tables involved and indexes.

To get performance you need to reduce the amount of data being read from Bkc_EBM_Employment

|||Do you have a where clause on your query ?|||

SimonS_ wrote:

Do you have a where clause on your query ?

Yes "WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL ) " (see query in the answer above) but there is no records in database filtered by this condition.

About database

'ReadRights' ~ 500000 records
'ProcessAccounts' 1 record
'Bkc_EBM_Person' ~ 2500 records
'Bkc_EBM_Client' ~ 1700 records
'Bkc_EBM_ClientType' ~ 20 records
'Bkc_EBM_ContactsInfo' ~ 4000 records
'Bkc_EBM_Contact' ~ 5000 records
'Bkc_EBM_Employment' ~ 2500 records

Very strange, but it sims RECOMPILE option helps. Worktable is created even right after SQL Server restart. If RECOMPLIE not used after server restart Worktable not created.

|||

The problem is query plans. Your query won't change from one user to the next or from one template to the next however this means the same query plan will be used. However the same query plan will not be optimal for all situations.

What can happen is that a plan is put in the cache based on the first set of parameters supplied, if this plan is not suitable for all queries you can end up with the problem above.

The recompile will address this at the expense of having to compile the query every time.

|||

Thank you for your help Simon. RECOMPILE is really helps.

Maybe in my case using of parameters in a query is not the best choise? Especially @.userID ?

|||Can you still post the CREATE table statements and CREATE View statements so I can understand your query better, its quite difficult with your use of views to understand what is joining to what.|||

Script will be quite large. Maybe by email?

|||try SQLForumsATonarcDOTcom|||I send the script. Please check.|||Another problem. In SQL Server 2000 option (recompile) is not supported. Is there any way to enforce SQL Server 2000 to recompile execution plan every time?

How to enforce SQL Server 2005 to use Worktable?

I have a problem in SQL Server 2005. In some cases SQL Server produces an execution plan of complex query (8 joins of views, some of views contains couple of joins) which does not contain a woktable creation in tempdb. As a result time of query execution increasion for about 5 seconds to about 4 minutes. All necessary indexes are created. It sims all data located in cache. Is there any way to enforce SQL Server to create worktable?

Query

SELECT [a0].[id],[a0].[Priority],[a0].[Heading],[a0].[DocumentDate],[a0].[LastName],[a0].[FirstName],[a2].[Name],[a1].[Position],[a4].[ContactTime],[a4].[Subject],[a0].[WorkPhone],[a0].[MobilePhone],[a0].[FaxNumber],[a0].[PrimaryEmail],[a5].[ContactTime],[a6].[Value],[a7].[id],[a7].[id_class],[a8].[id],[a8].[id_class],[a0].[FIO]
FROM [Bkc_EBM_Person_View] [a0]
LEFT JOIN [Bkc_EBM_ContactsInfo_View] [a3] ON ( a0.ContactsInfo_id = a3.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a4] ON ( a3.LastContact_id = a4.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a5] ON ( a3.NextContact_id = a5.[id] )
LEFT JOIN [Bkc_EBM_PersonType_View] [a6] ON ( a0.PersonType_id = a6.[id] )
LEFT JOIN [Bkc_EBM_Employment_View] [a1] ON ( a0.PrimaryEmployment_id = a1.[id] )
LEFT JOIN [Bkc_EBM_Client_View] [a2] ON ( a1.Client_id = a2.[id] )
LEFT JOIN [Bkc_EBM_Person_View] [a7] ON ( a0.Responsible_id = a7.[id] )
LEFT JOIN [Bkc_EBM_Department_View] [a8] ON ( a0.Department_id = a8.[id] )

Statistics

(2454 row(s) affected)

Table 'ReadRights'. Scan count 2455, logical reads 109411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 11102, logical reads 22266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Department'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 38986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 4826, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 83632238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_PersonType'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 9816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 231719 ms,elapsed time = 253491 ms.

Execution plan

http://rsdn.ru/File/22090/plan1.rar

Please post the view definitions.

It looks like your view definition for BKc_EBM_Employment has a bad join in it, You are processing 640Gb of data from the BKcEBM_Employment table

|||try forcing a recompile.

e.g.
select *
from ...
option (recompile)|||

Below is the view definition

Bkc_EBM_Employment is a table which connects Bkc_EBM_Person and Bkc_EBM_Client in a many-to-many relation.

ReadRights is a table which defines rights of user account to view particular document in system

processaccounts is a table, to which Application server writes corrspondence between current spid and user account id, before executing a query

USE [Oblik_CRM]

GO

/****** Object: View [dbo].[Bkc_EBM_Employment_View] Script Date: 10/04/2006 10:18:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[Bkc_EBM_Employment_View] WITH SCHEMABINDING AS

(SELECT [id], 1370 AS [id_class], cast([id] as varchar(64))+'_1370' AS [id_record], t.[EmployedPerson_id], t.[EmployedPerson_class_id], t.[Client_id], t.[Client_class_id], t.[Department_id], t.[Department_class_id], t.[Position], t.[PlaceOfWorkType_id], t.[PlaceOfWorkType_class_id], t.[RoleEmployee_id], t.[RoleEmployee_class_id], t.[FRC_id], t.[FRC_class_id], t.[InnerPhone], t.[IsPrimary], t.[IsFired], t.[EmploymentDate], t.[FiredDate], t.[FiredReason_id], t.[FiredReason_class_id], t.[Description], t.[RightToSign], t.[Heading], t.[Version] FROM dbo.Bkc_EBM_Employment t

inner join dbo.ReadRights r on (t.EmployedPerson_id = r.object_id) inner join dbo.ProcessAccounts pa WITH (NOLOCK) on (r.account_id = pa.account_id) where pa.spid=@.@.spid

)

|||

I rewrite the query without using viws. It helps a little because in new query data, that not needed by this query not queried by the views. But time of query execution is still to long. About 2 minutes

declare @.userID int

set @.userID = 104356

declare @.qp_0 int

set @.qp_0 = 0

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid INSERT ProcessAccounts WITH (ROWLOCK) (account_id, spid) VALUES (@.userID, @.@.spid)

SELECT

[a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail],

[a1].[FIO],

[a3].[ContactTime],

[a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO],

[a5].[Value],

[a6].[Position],

[a7].[ContactTime]

FROM

(

SELECT [a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail], a0.Responsible_id, a0.ClientType_id, a0.ContactsInfo_id, a0.IsTemplate FROM [Bkc_EBM_Client] [a0]

INNER JOIN [ReadRights] [a0r] ON ([a0].[id] = [a0r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a0pa] ON ( [a0r].[account_id] = [a0pa].[account_id] )

) [a0]

LEFT JOIN [Bkc_EBM_ClientType] [a5] ON ( a0.ClientType_id = a5.[id] )

LEFT JOIN

(

SELECT [a1].[id], [a1].[FIO] FROM [Bkc_EBM_Person] [a1]

INNER JOIN [ReadRights] [a1r] ON ([a1].[id] = [a1r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a1pa] ON ( [a1r].[account_id] = [a1pa].[account_id] )

) [a1] ON ( a0.Responsible_id = a1.[id] )

LEFT JOIN [Bkc_EBM_ContactsInfo] [a2] ON ( a0.ContactsInfo_id = a2.[id] )

LEFT JOIN

(

SELECT [a3].[id], [a3].[ContactTime] FROM [Bkc_EBM_Contact] [a3]

INNER JOIN [ReadRights] [a3r] ON ([a3].[id] = [a3r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a3pa] ON ( [a3r].[account_id] = [a3pa].[account_id] )

) [a3] ON ( a2.LastContact_id = a3.[id] )

LEFT JOIN

(

SELECT [a4].[id], [a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO], [a4].[PrimaryEmployment_id] FROM [Bkc_EBM_Person] [a4]

INNER JOIN [ReadRights] [a4r] ON ([a4].[id] = [a4r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a4pa] ON ( [a4r].[account_id] = [a4pa].[account_id] )

) [a4] ON ( a2.LastContactPerson_id = a4.[id] )

LEFT JOIN

(

SELECT [a6].[id], [a6].[Position] FROM [Bkc_EBM_Employment] [a6]

INNER JOIN [ReadRights] [a6r] ON ([a6].[EmployedPerson_id] = [a6r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a6pa] ON ( [a6r].[account_id] = [a6pa].[account_id] )

) [a6] ON ( a4.PrimaryEmployment_id = a6.[id] )

LEFT JOIN

(

SELECT [a7].[id], [a7].[ContactTime] FROM [Bkc_EBM_Contact] [a7]

INNER JOIN [ReadRights] [a7r] ON ([a7].[id] = [a7r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a7pa] ON ( [a7r].[account_id] = [a7pa].[account_id] )

) [a7] ON ( a2.NextContact_id = a7.[id] )

WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL )

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid

Statistics

(1750 row(s) affected)

Table 'ReadRights'. Scan count 5891, logical reads 222578, physical reads 425, read-ahead reads 283, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 6, logical reads 5891, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 7000, physical reads 98, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 33686288, physical reads 33, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 7000, physical reads 60, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo_View'. Scan count 0, logical reads 3500, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ClientType_View'. Scan count 0, logical reads 3500, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 33252, physical reads 43, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 111797 ms, elapsed time = 123889 ms.

Execution Plan

http://rsdn.ru/File/22090/plan4.rar

|||

Please post the scripts for all the tables involved and indexes.

To get performance you need to reduce the amount of data being read from Bkc_EBM_Employment

|||Do you have a where clause on your query ?|||

SimonS_ wrote:

Do you have a where clause on your query ?

Yes "WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL ) " (see query in the answer above) but there is no records in database filtered by this condition.

About database

'ReadRights' ~ 500000 records
'ProcessAccounts' 1 record
'Bkc_EBM_Person' ~ 2500 records
'Bkc_EBM_Client' ~ 1700 records
'Bkc_EBM_ClientType' ~ 20 records
'Bkc_EBM_ContactsInfo' ~ 4000 records
'Bkc_EBM_Contact' ~ 5000 records
'Bkc_EBM_Employment' ~ 2500 records

Very strange, but it sims RECOMPILE option helps. Worktable is created even right after SQL Server restart. If RECOMPLIE not used after server restart Worktable not created.

|||

The problem is query plans. Your query won't change from one user to the next or from one template to the next however this means the same query plan will be used. However the same query plan will not be optimal for all situations.

What can happen is that a plan is put in the cache based on the first set of parameters supplied, if this plan is not suitable for all queries you can end up with the problem above.

The recompile will address this at the expense of having to compile the query every time.

|||

Thank you for your help Simon. RECOMPILE is really helps.

Maybe in my case using of parameters in a query is not the best choise? Especially @.userID ?

|||Can you still post the CREATE table statements and CREATE View statements so I can understand your query better, its quite difficult with your use of views to understand what is joining to what.|||

Script will be quite large. Maybe by email?

|||try SQLForumsATonarcDOTcom|||I send the script. Please check.|||Another problem. In SQL Server 2000 option (recompile) is not supported. Is there any way to enforce SQL Server 2000 to recompile execution plan every time?

how to enforce precedence at data flow level?

I wish to use the same data to update 2 different tables.

There is no green arrow output from the OLE DB data destination, so I can't have another component following on from the first insert.

This means I have to use the Multicast to 'copy' the data prior to the first table insert.

I can then use the data to perform inserts to both tables.

However, there is an FK constraint between these two tables, so I need to wait until the first table insert has finished before performing the second table insert.

How can I do this? How can I make the second insert dependent on the first?

Hi ya,

Unfortunately there is no precedence at data flow level. Your best bet would be to put it in 2 data flows and make sure that the first data flow is on success.

If you do want to run it in a transaction then put a sequence container and put the transaction property for container as Required whille both data flows should be supported.

Sorry but i couldn't find anything else........ probably the big guys would answer if there is anything else?

Hope that helps

Cheers

Rizwan

|||That's a pain.

That means i'll have to build the dataset up all over again......and no i daren't cut and paste due to the endless xml/serialization errors I always/randomly seem to get that completely blow up the IDE

I'm surprised there is no 'rendezvous' component in the toolbox.

do you know if anyone has written a custom component like this?|||

Sure this is but then again you always have the option to select a custom script task and then programatically do the importing and saving of data........ though this is just an idea as i haven't tried it myself yet.

leave the thread open and we'll see if Moderators or MVPs has something else in their mind.

Cheers

Rizwan

|||

One way to do this would to have the DataFlow drop the data for the child table to a RAW file, then in a 2nd dataflow read the RAW and piopulate the table|||

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

|||

desibull wrote:

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

I just want to let my opinion be known on this topic:

There is no reason to have precedence constraints in a data flow. A data flow is designed to move data in buffers -- as fast as it can. To be able to enforce ordering is non-sense. Just break the data up into two or more data flows. I do not support the notion of precedence constraints in a data flow.|||

I agree with Paul - the easiest way to do this without having to reprocess the dataset is to drop it to a RAW destination. It's also extremely fast.

|||

Hi,

That means i was right. Great

Cheers

Rizwan

How to enforce integrity across databases

I would like to enforce integrity across two databases on the same server. A
s
far as I know there is no way to create a FK constraint pointing to another
database. So my options are either a trigger (which I would like avoid) or a
check constraint.
Does anyone have any expericence/recommendations they can share on how to
implement this?Using triggers.
AMB
"DBA72" wrote:

> I would like to enforce integrity across two databases on the same server.
As
> far as I know there is no way to create a FK constraint pointing to anothe
r
> database. So my options are either a trigger (which I would like avoid) or
a
> check constraint.
> Does anyone have any expericence/recommendations they can share on how to
> implement this?|||"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:27E1F221-1355-447A-B052-542680E9DD5F@.microsoft.com...
> I would like to enforce integrity across two databases on the same server.
As
> far as I know there is no way to create a FK constraint pointing to
another
> database. So my options are either a trigger (which I would like avoid) or
a
> check constraint.
> Does anyone have any expericence/recommendations they can share on how to
> implement this?
IIRC, CHECK constraints cannot reference outside their table, so you are
left with triggers, or data validation at the stored proc level.
I would suggest triggers in this case.
Rick Sawtell

how to enforce a trigger when update for each record when updates several records bulky?

I made a trigger on a table that fires when update happens, the trigger fires when attempting to update a single record (that is normally) but when trying to update several records bulky using one update statement it fires only once either.

My question is, how to enforce firing the trigger for each record when updates bulky? i.e. how to ensure that when I use the following update statement

UPDATE MyTableName SET ColumnName = 5

And there are 10 records that affected; that the trigger would fire 10 times? (I have the fact that it fires only once)

In SQL server, a trigger fires once per statement, not per row and this can not be changed. You must write your trigger to be able to handle a multiple row update. Post your trigger code and likely someone here can help you re-write it to work for multiple row updates.|||Thanks David, your reply was helpfulsql