Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTem
p]([TR]) ON
[PRIMARY]
GO
----
--
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GOI think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create
a
second index for this column. you could also consider create a primary key
for your temp table.
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:

> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the be
st
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvail
Temp]([TR]) ON
> [PRIMARY]
> GO
> ----
--
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailT
emp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null|||Thanks but developers are telling me that TR column will have duplicates...
..
"David Browne" wrote:

> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
[PRIMARY]
GO
-----
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GO
I think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create a
second index for this column. you could also consider create a primary key
for your temp table.
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:

> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
> [PRIMARY]
> GO
> -----
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>
|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null
|||Thanks but developers are telling me that TR column will have duplicates......
"David Browne" wrote:

> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
[PRIMARY]
GO
-----
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GOI think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create a
second index for this column. you could also consider create a primary key
for your temp table.
--
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:
> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
> [PRIMARY]
> GO
> -----
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null|||Thanks but developers are telling me that TR column will have duplicates......
"David Browne" wrote:
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> >I have the following DDL/indexes and the following Stored procedure. I am
> > getting 28 seconds for per user which is unexceptable. What will be the
> > best
> > way to index this table ?
> >
> > UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> > usique numbers........
> >
> > Thanks for any help.
> >
> > CREATE TABLE [dbo].[CostCompAvailTemp] (
> > [UserName] [char] (40) NOT NULL ,
> > [TR] [decimal](10, 0) NOT NULL ,
> > [RHour] [smallint] NULL ,
> > [Def] [decimal](5, 3) NULL ,
> > [Price] [decimal](4, 2) NULL ,
> > [RDL] [smallint] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> > [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> > ON
> > [PRIMARY]
> > GO
> >
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

performance of select

Here is DDL in my way. Other information is masked.
Table a
sri int, PK, Clustered
num varchar(7) Nonclustered
.
.
.
p uniqueidentifier Nonclustered
Table s
num varchar(7), PK,Clustered
ssi tinyint --can have either 1 or 2
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
--uses index s on p, runs in a fraction of second
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
--uses index scan on num, takes around 8-25 seconds. Have any thoughts why
it takes so long, how to make this use an index s on p.SELECT ssi, COUNT(*) FROM s
GROUP BY ssi
How many rows have an ssi value of 2, compared to an ssi value of 1? If
there are a lot more rows that have ssi = 2 than 1, then the optimizer will
choose an index scan, because it would be the better method to find matching
rows through the select statement.
"S" wrote:

> Here is DDL in my way. Other information is masked.
> Table a
> sri int, PK, Clustered
> num varchar(7) Nonclustered
> .
> .
> .
> p uniqueidentifier Nonclustered
> Table s
> num varchar(7), PK,Clustered
> ssi tinyint --can have either 1 or 2
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> --uses index s on p, runs in a fraction of second
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> --uses index scan on num, takes around 8-25 seconds. Have any thoughts why
> it takes so long, how to make this use an index s on p.|||Ratio is few hundreds to millions
2 - few hundreds
1 - millions. Is that what causing the daly to find the records.
"Mark Williams" wrote:
> SELECT ssi, COUNT(*) FROM s
> GROUP BY ssi
> How many rows have an ssi value of 2, compared to an ssi value of 1? If
> there are a lot more rows that have ssi = 2 than 1, then the optimizer wil
l
> choose an index scan, because it would be the better method to find matchi
ng
> rows through the select statement.
>
> --
> "S" wrote:
>|||Could be...
You can update the statistics of table "a", preferably WITH FULL_SCAN,
and see if that makes any difference.
Gert-Jan
S wrote:
> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
> "Mark Williams" wrote:
>|||Try modifying your queries:
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
uses index s on p, runs in a fraction of second
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
Since your are using the query in the context of EXISTS, you can use SELECT
1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
earlier today). Specifying sri in the select list may be affecting which
indexes are chosen.
Other than that, run the statements below and post the text execution plans
here.
SET SHOWNPLAN_TEXT ON
GO
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
-
"S" wrote:

> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
>|||Table s has 8 million records but only a few hundred have ssi=2, remaining
records
have ssi= 1
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Filter(WHERE:([a].[p]=NULL))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([s].[num]))
|--Clustered Index
Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
|--Index
S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_num] ), SEEK:([a].[num]=[s].[num])
ORDERED FORWARD)
for ssi= 2
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
PREFETCH)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
| |--Index S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_p),
SEEK:([a].[p]=NULL) ORDERED FORWARD)
|--Clustered Index
S(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), SEEK:([s].[num]=[a].[num]),
WHERE:([s].[ssi]=1) ORDERED FORWARD)
"Mark Williams" wrote:

> Try modifying your queries:
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> uses index s on p, runs in a fraction of second
>
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> Since your are using the query in the context of EXISTS, you can use SELEC
T
> 1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
> earlier today). Specifying sri in the select list may be affecting which
> indexes are chosen.
> Other than that, run the statements below and post the text execution plan
s
> here.
> SET SHOWNPLAN_TEXT ON
> GO
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
>
> -
>
> "S" wrote:
>
>|||Which order are the plans in? Is the ssi = 1 query first?
"S" wrote:
> Table s has 8 million records but only a few hundred have ssi=2, remaining
> records
> have ssi= 1
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Filter(WHERE:([a].[p]=NULL))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([s].[num]))
> |--Clustered Index
> Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
> |--Index
> S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_num] ), SEEK:([a].[num]=[s].[num])
> ORDERED FORWARD)
>
> for ssi= 2
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
> PREFETCH)
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> | |--Index S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_p),
> SEEK:([a].[p]=NULL) ORDERED FORWARD)
> |--Clustered Index
> S(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), SEEK:([s].[num]=[a].[num]),
> WHERE:([s].[ssi]=1) ORDERED FORWARD)
>
> "Mark Williams" wrote:
>|||Yes.
"Mark Williams" wrote:
> Which order are the plans in? Is the ssi = 1 query first?
> --
> "S" wrote:
>|||I am sorry. I think I gave the incorrect order of execution plans.
ssi =1 uses index s on both s and a
ssi=2 uses index s on a and index scan on s
"S" wrote:
> Yes.
> "Mark Williams" wrote:
>|||I tried replicating your data set on a much smaller scale. I created and
populated table s with about 100,000 rows with the same distribution of ssi
=
1 and ssi = 2 rows. Created and populated table a with an IDENTITY column fo
r
the PK, foreign key relationship to s on the num column, and nonclustered
indexex on p and num.
Ran both the queries and they had identical execution plans. Don't know why
you are getting different ones.
"S" wrote:
> I am sorry. I think I gave the incorrect order of execution plans.
> ssi =1 uses index s on both s and a
> ssi=2 uses index s on a and index scan on s
> "S" wrote:
>