multiple rows insert: contains

Showing posts with label contains. Show all posts

Friday, March 30, 2012

how to exclude time portion of datefield in a textbox?

Hello,
The datasourece in my report contains a date field, and the time portion of
this date field is showing up. Is there a format function that I can use in
the textbox or do I need to remove the time portion at the datasource level?
If there is some kind of formatting I can do in the textbox - what does that
look like? What is the syntax?
Thanks,
RichIn the textbox properties, format tab, format code, use 'd'. The format
codes are from .Net you can also use the format function if you need to
concatenate. The elipsi button shows more options.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> Hello,
> The datasourece in my report contains a date field, and the time portion
> of
> this date field is showing up. Is there a format function that I can use
> in
> the textbox or do I need to remove the time portion at the datasource
> level?
> If there is some kind of formatting I can do in the textbox - what does
> that
> look like? What is the syntax?
> Thanks,
> Rich|||Thanks. I will give that a try. Although, I did tweak the datasource a
little and added this which also does the trick (just not at the report level
is the thing):
convert(char(8), s.CurExpireDate, 1) CurExpireDate
which returns say - 10/18/06 format
where CurExpireDate was the datefield in my datasource.
May I ask what the syntax looks like using 'd' in the report textbox?
= ...Fields!CurExpireDate.Value
"Steve MunLeeuw" wrote:
> In the textbox properties, format tab, format code, use 'd'. The format
> codes are from .Net you can also use the format function if you need to
> concatenate. The elipsi button shows more options.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> > Hello,
> >
> > The datasourece in my report contains a date field, and the time portion
> > of
> > this date field is showing up. Is there a format function that I can use
> > in
> > the textbox or do I need to remove the time portion at the datasource
> > level?
> >
> > If there is some kind of formatting I can do in the textbox - what does
> > that
> > look like? What is the syntax?
> >
> > Thanks,
> > Rich
>
>|||The expression in the textbox would the the same:
=Fields!CurExpireDate.Value
the textbox is applying the formatting to the contents of the textbox.
If you wanted to concatenate strings then the expression would look
something like this:
= "Generated on " & Format(Globals!ExecutionTime, "d")
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> Thanks. I will give that a try. Although, I did tweak the datasource a
> little and added this which also does the trick (just not at the report
> level
> is the thing):
> convert(char(8), s.CurExpireDate, 1) CurExpireDate
> which returns say - 10/18/06 format
> where CurExpireDate was the datefield in my datasource.
> May I ask what the syntax looks like using 'd' in the report textbox?
> = ...Fields!CurExpireDate.Value
>
>
> "Steve MunLeeuw" wrote:
>> In the textbox properties, format tab, format code, use 'd'. The format
>> codes are from .Net you can also use the format function if you need to
>> concatenate. The elipsi button shows more options.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
>> > Hello,
>> >
>> > The datasourece in my report contains a date field, and the time
>> > portion
>> > of
>> > this date field is showing up. Is there a format function that I can
>> > use
>> > in
>> > the textbox or do I need to remove the time portion at the datasource
>> > level?
>> >
>> > If there is some kind of formatting I can do in the textbox - what does
>> > that
>> > look like? What is the syntax?
>> >
>> > Thanks,
>> > Rich
>>|||Thank you for this information.
Rich
"Steve MunLeeuw" wrote:
> The expression in the textbox would the the same:
> =Fields!CurExpireDate.Value
> the textbox is applying the formatting to the contents of the textbox.
> If you wanted to concatenate strings then the expression would look
> something like this:
> = "Generated on " & Format(Globals!ExecutionTime, "d")
> Steve MunLeeuw
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> > Thanks. I will give that a try. Although, I did tweak the datasource a
> > little and added this which also does the trick (just not at the report
> > level
> > is the thing):
> >
> > convert(char(8), s.CurExpireDate, 1) CurExpireDate
> >
> > which returns say - 10/18/06 format
> >
> > where CurExpireDate was the datefield in my datasource.
> >
> > May I ask what the syntax looks like using 'd' in the report textbox?
> >
> > = ...Fields!CurExpireDate.Value
> >
> >
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> >> In the textbox properties, format tab, format code, use 'd'. The format
> >> codes are from .Net you can also use the format function if you need to
> >> concatenate. The elipsi button shows more options.
> >>
> >> Steve MunLeeuw
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> >> > Hello,
> >> >
> >> > The datasourece in my report contains a date field, and the time
> >> > portion
> >> > of
> >> > this date field is showing up. Is there a format function that I can
> >> > use
> >> > in
> >> > the textbox or do I need to remove the time portion at the datasource
> >> > level?
> >> >
> >> > If there is some kind of formatting I can do in the textbox - what does
> >> > that
> >> > look like? What is the syntax?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>

Wednesday, March 28, 2012

How to enfore a primary key range ??

I've inherited the following situation...
The table contains 4 columns... script below
Note that the first 3 columns denote the primary Key...
Actually what is really meant is the following...
Let's say the values for one row are as follows...
Code= A
LowVal = 25
HighVal=50
UseThis=Fred
What they want to be implied by this row... if Code=A and the test val is
between 25 and 50 UseThis= Fred
They want to disallow any row that overlaps from being added... such as the
following...
Code= A
LowVal = 30
HighVal=40
UseThis=Joe
How can you enforce something like this ?
CREATE TABLE [dbo].[Table1] (
[Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[LowVal] [decimal](6, 0) NOT NULL ,
[HighVal] [decimal](6, 0) NOT NULL ,
[UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code],
[LowVal],
[HighVal]
) ON [PRIMARY]"Rob" <rwchome@.comcast.net> wrote in message
news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> I've inherited the following situation...
> The table contains 4 columns... script below
> Note that the first 3 columns denote the primary Key...
> Actually what is really meant is the following...
> Let's say the values for one row are as follows...
> Code= A
> LowVal = 25
> HighVal=50
> UseThis=Fred
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
> They want to disallow any row that overlaps from being added... such as
> the following...
> Code= A
> LowVal = 30
> HighVal=40
> UseThis=Joe
> How can you enforce something like this ?
>
> CREATE TABLE [dbo].[Table1] (
> [Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
> [LowVal] [decimal](6, 0) NOT NULL ,
> [HighVal] [decimal](6, 0) NOT NULL ,
> [UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code],
> [LowVal],
> [HighVal]
> ) ON [PRIMARY]
>
You'll have to use a trigger for this, eg:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.LowVal
and l.HighVal > r.LowVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
David|||Thanks David,
Maybe I am doing something wrong, but I was able to add the following rows
after applying the trigger...
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',20,500,'FRED')
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eDxVWD1CGHA.344@.TK2MSFTNGP11.phx.gbl...
> "Rob" <rwchome@.comcast.net> wrote in message
> news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> You'll have to use a trigger for this, eg:
> create trigger Table1_no_overlap
> on Table1 for insert, update
> as
> begin
> if exists
> (
> select *
> from Table1 l
> join Table1 r
> on l.LowVal < r.LowVal
> and l.HighVal > r.LowVal
> )
> begin
> raiserror('Change would create overlapping range.',16,1)
> rollback transaction
> end
> end
>
> David
>|||Hi, Rob
Use the following trigger:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
where i.Code<>t.Code or i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
If you want to allow overlapping ranges for different codes (but not
for the same code), the trigger would be like this:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.Code=i.Code and (
t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
)
where i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
Razvan|||Rob wrote:

> Thanks David,
> Maybe I am doing something wrong, but I was able to add the following rows
> after applying the trigger...
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',20,500,'FRED')
> Rob
>
Try it like this. Notice that I've added an extra constraint, modified
the join in the trigger and added CODE to the join. That's my reading
of what you want to achieve. Test carefully.
ALTER TABLE table1 ADD CONSTRAINT ck_table1_lowval_highval
CHECK (lowval <= highval) ;
GO
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
Your trigger doesn't allow any row to be inserted.
Razvan|||Razvan Socol wrote:
> Hi, David
> Your trigger doesn't allow any row to be inserted.
> Razvan
You're right. Here's a correction:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
and (l.LowVal <> r.LowVal
or l.HighVal <> r.HighVal)
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
My understanding of the original post is that the following rows are
not allowed (but your trigger allows them):
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',100,150,'FRED')
Rob wrote:
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
The following rows would be ok:
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',101,150,'FRED')
Razvan|||Thank you both Razvan and David...
Sorry I was not clear on this, actually same HighVal on one row may be equal
to LowVal on another...
The code applied uses > LowVal and <= HighVal...
Rob
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135755688.241085.105660@.g49g2000cwa.googlegroups.com...
> Hi, David
> My understanding of the original post is that the following rows are
> not allowed (but your trigger allows them):
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',100,150,'FRED')
> Rob wrote:
> The following rows would be ok:
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',101,150,'FRED')
> Razvan
>|||You might want to add some other constraints. I would not allow the
low and high values to be the same; disjoint ranges will allow you to
use a more readable BETWEEN predicate.
CREATE TABLE Table1
(foo_code CHAR (10) NOT NULL,
low_val DECIMAL(6,0) NOT NULL,
high_val DECIMAL(6,0) NOT NULL,
use_this CHAR (10) DEFAULT '{{ none }}' NOT NULL,
CHECK (low_val <= high_val)
PRIMARY KEY (foo_code,low_val, high_val),
UNIQUE (foo_code,low_val),
UNIQUE (foo_code, high_val)
);
Besides not having overlaps, you might want to avoid gaps in the
ranges.
CREATE TRIGGER Table1_No_Gaps
ON Table1 FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(SELECT *
FROM Table1 AS T1
GROUP BY T1.foo_code
HAVING MAX(high_val)- MIN(low_val) +1
= SUM(high_val - low_val + 1)
BEGIN
RAISERROR ('Code Range Errors',16,1);
ROLLBACK TRANSACTION;
END;
END;sql

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]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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?