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 encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?
I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>
|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl. ..
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
> >I have seen when i run a select statement written by some devs which has some joins,etc.. , that
> >when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
> >commented out that states its encrypted.
> > So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> > profiler it shows something like ..
> > -- the text has been encrypted for security reason.
> > I found that magical.
> > Can someone tell me how its done ?
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005sql

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment)
will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...

>I have seen when i run a select statement written by some devs which has so
me joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in t
he textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query An
alyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a commen
t) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSF
TNGP03.phx.gbl...
>
>
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to do these joins

Pardon my newbieness.
I have four tables to join. I'll just call them Table1, Table2, Table3
and Table4.
Table1 is the main table. I want to do outer joins pointing from fields
in Table1 to fields in each of the other tables. So all records in
Table1 regardless of whether they match in the other tables.
todSelect t1.* from Table1 t1
LEFT JOIN Table2 t2
ON t1.SomeiD = t2.someID
LEFT JOIN Table3 t3
ON t1.SomeiD = t3.someID
...
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--
"Tod" <todtown@.swbell.net> schrieb im Newsbeitrag
news:1115757100.280539.176950@.o13g2000cwo.googlegroups.com...
> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||select * from
Table1 left join Table2 on Table1.fld1 = Table2.fld1
left join Table3 on Table1.fld1 = Table3.fld1
left join Table4 on Table1.fld1 = Table4.fld1
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join... (and
then the third Join...)
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>