Performance Question

Obviously you do not want to have an open connection to a database
when your program is waiting for user input but if you are doing a
bunch of processing after a user request is it better to open and
close connections when you need access to the database or open once,
use multiple times and then close when you know you will not need
access again?

I guess what I am asking is, what is more important? Minimizing your
concurrent connections to a database or the performance hit of
repeated open and closing a connection.

I know there probably isn't one correct answer but what should I take
into consideration?

John.
The general rule of thumb here is "open late, close early".

Open your connection just before you need to use it, and close it as soon as possible. In my opinion, you would do this each time you need a connection. The reason for this is that by default, your connections are pooled. Normally, when you open a connection that uses an identical connection string as one that has already been opened and closed, you application will grab that connection again. Therefor, the connection does not need to be initialized and authenticated to SQL Server again.

Generally, I design my methods to perform as few database operations as possible with a goal of creating methods that are loosely coupled and highly cohesive. I create and open the connection within the method, use it, then close it. I have analyzed the connection performance with Performance Monitor and this seems to work well. Sometimes it is necessary to loop through some data, and process some database operation one record at a time. In this case I will usually open the connection just before the loop, and close it immediately after.

Ultimately, you may need to experiment a little for yourself. Every situation is different, and calls for different design considerations. You may be interested in having a look at theData Access Application Block from Microsoft for somebest practices.

Hope this is helpful.
|||I agree with NetProfit. I would like to add a couple of points to think on. Its not just for Db hits either, but you should also try to avoid chatty interface to the DB as well, to cut down on network roundtrips. I would also say that generally its better to get the whole result set back, close the connection ASAP and then work on the data. I've seen plenty of solutions that mis-use the datareader.|||Thanks. I had forgetten about connection pooling. I am looking forward to reading the Data Access Application Block article too.

John.

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 probelm with large table

I have a web page to allow user to create Sales Order. For this i have 2 tables, one for the informations (Sales Order) about the order and the other table (Sales Order LineItems ) contains the details of the order. For exemple for one order if i order 2 CD i will have 2 rows for this order in the Sales Order LineItems table.

When i've created the page i've made some test by creating 100 orders. Everything was working fine. Now yesterday i've tried to create 100 000 orders and 400 000 details rows. Since i 've created all thoses order the performance are not good at all. When i tried to open an order on my web page it can take up to 20sec juste to load the page. This make no sense at all since all my queries return only information for one order.
The problem is probably with SQL Server since when i've tried to open my webpage if i tried to launch another query everything is really slow. All my columns have Indexable properties set to yes.

Any idea ? I'm using ADO.NET and most of my Queries are inside StoredProc.

Thanks!

What is your table structure like?

Indexes?

|||

Can u share with us your table structure and if possible query u are trying to execute.

Basic Performance suggestion:

1. Use of index (smart use) No overuse

2. Keep ur statistics up to date.As in ur case u added so many rows it might have invalidated statistics on table.

3. Look for fragmentation in the tables. use dbcc showcontig to find out if any.

4. avoid using select * use column name in select

5. Use show plan to find out index(If u have any on the tables) is being used by SQL Server or not

6. Look for scans (Table scan or index scan) ..any case seek is better

so many others ..

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegroups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom
<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegro ups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegroups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David

performance of MSDE

Is anyone aware of any functionally exceptions of the MSDE
version of SQL, other than Microsoft's listed ones, I have
read in user forums that there are also perfomance
knobbling.
http://www.microsoft.com/sql/evaluat...iew/default.as
p
We have the choice of buying a multi-user database with
either an Access or MSDE SQL back end, the supplier is
currently developing the database to use the SQL backend.
We cannot afford to upgrade to the Std version later on.
rob,
I suspect that if the performance of MSDE becomes too slow due to database
throttling that an Access database would also be too slow.
Ginny Caughey
..Net Compact Framework MVP
Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/...ity/newsgroups
"rob" <another@.psotago.org.nz> wrote in message
news:22c4d01c45e38$45e45d30$a601280a@.phx.gbl...
> Is anyone aware of any functionally exceptions of the MSDE
> version of SQL, other than Microsoft's listed ones, I have
> read in user forums that there are also perfomance
> knobbling.
> http://www.microsoft.com/sql/evaluat...iew/default.as
> p
> We have the choice of buying a multi-user database with
> either an Access or MSDE SQL back end, the supplier is
> currently developing the database to use the SQL backend.
> We cannot afford to upgrade to the Std version later on.
|||I assume they are referring to the workload governor:
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights