|
Home > Archive > microsoft.public.cert.mcdba > June 2002 > CI vs nonCI indexes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
CI vs nonCI indexes
|
|
|
| I was trying to test the assertion that says an INSERT will take long for a
table that has a CI (clustered index) vs. the same table that has a nonCI.
Below is the SQL script for creating the table I have used and the stored
procedure that gets called by a C++ application in a loop.
I get almost the same kind of results in both cases. I expected the nonCI
INSERT to be faster than the CI INSERT.
Any thoughts?
Danut
==================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test_Table]
GO
CREATE TABLE [dbo].[Test_Table] (
[id] [uniqueidentifier] NOT NULL ,
[field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[long1] [int] NULL ,
[x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[Test_Insert]
GO
CREATE PROCEDURE [dbo].[Test_Insert]
@field1 nvarchar(50),
@long1 int
AS
SET NOCOUNT ON
INSERT INTO dbo.Test_Table
VALUES ( NEWID(), @field1, @long1
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
,
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
5678901234567890123456789'
)
RETURN 0
GO
| |
| Russell Fields 2002-06-23, 6:15 am |
| Danut,
To get a test that would tell you anything you need to insert a lot more
rows than that. Try inserting a few thousand rows for your tests.
On the other end, select speed can degrade greatly without a clustered
index. The heap table that you get has to handle inserts and splits just
like a clustered table, but the mechanism for doing this is very expensive
if there are many inserts or splits.
Russell Fields
"Danut" <danutzp@hotmail.com> wrote in message
news:OIs6NxV5BHA.1456@tkmsftngp02...
> I was trying to test the assertion that says an INSERT will take long for
a
> table that has a CI (clustered index) vs. the same table that has a nonCI.
>
> Below is the SQL script for creating the table I have used and the stored
> procedure that gets called by a C++ application in a loop.
> I get almost the same kind of results in both cases. I expected the nonCI
> INSERT to be faster than the CI INSERT.
>
> Any thoughts?
>
> Danut
>
> ==================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Test_Table]
> GO
>
> CREATE TABLE [dbo].[Test_Table] (
> [id] [uniqueidentifier] NOT NULL ,
> [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [long1] [int] NULL ,
> [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') =
> 1)
> drop procedure [dbo].[Test_Insert]
> GO
>
>
> CREATE PROCEDURE [dbo].[Test_Insert]
> @field1 nvarchar(50),
> @long1 int
> AS
> SET NOCOUNT ON
>
> INSERT INTO dbo.Test_Table
> VALUES ( NEWID(), @field1, @long1
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> ,
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> 5678901234567890123456789'
> )
>
> RETURN 0
> GO
>
>
>
| |
|
| Hi Russell,
In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
iterations that finally resulted in 100,000 records.
Should I go to 1,000,000 records to see a difference?
Danut
"Russell Fields" <rlfields@sprynet.com> wrote in message
news:eA164bX5BHA.1020@tkmsftngp07...
> Danut,
>
> To get a test that would tell you anything you need to insert a lot more
> rows than that. Try inserting a few thousand rows for your tests.
>
> On the other end, select speed can degrade greatly without a clustered
> index. The heap table that you get has to handle inserts and splits just
> like a clustered table, but the mechanism for doing this is very expensive
> if there are many inserts or splits.
>
> Russell Fields
>
>
> "Danut" <danutzp@hotmail.com> wrote in message
> news:OIs6NxV5BHA.1456@tkmsftngp02...
> > I was trying to test the assertion that says an INSERT will take long
for
> a
> > table that has a CI (clustered index) vs. the same table that has a
nonCI.
> >
> > Below is the SQL script for creating the table I have used and the
stored
> > procedure that gets called by a C++ application in a loop.
> > I get almost the same kind of results in both cases. I expected the
nonCI
> > INSERT to be faster than the CI INSERT.
> >
> > Any thoughts?
> >
> > Danut
> >
> > ==================
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
=
> 1)
> > drop table [dbo].[Test_Table]
> > GO
> >
> > CREATE TABLE [dbo].[Test_Table] (
> > [id] [uniqueidentifier] NOT NULL ,
> > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [long1] [int] NULL ,
> > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > (
> > [id]
> > ) ON [PRIMARY]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
=
> > 1)
> > drop procedure [dbo].[Test_Insert]
> > GO
> >
> >
> > CREATE PROCEDURE [dbo].[Test_Insert]
> > @field1 nvarchar(50),
> > @long1 int
> > AS
> > SET NOCOUNT ON
> >
> > INSERT INTO dbo.Test_Table
> > VALUES ( NEWID(), @field1, @long1
> > ,
> >
>
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > ,
> >
>[/color]
'01234567890123456789012345678
901234567890123456789012345678
9012345678901234[c
olor=green]
> > 5678901234567890123456789'
> > )
> >
> > RETURN 0
> > GO
> >
> >
> >
>
>[/color]
| |
| Tibor Karaszi 2002-06-23, 6:15 am |
| You can also try having something like 100000 rows. Then rebuilding your indexes, having
fillfactor of 100. Then try adding, say, 100000 more. From an architectural viewpoint, having cl
ix on a key that is randomly distributed should cost quite a bit. Due to page splits.
Otoh, there's a KB talking about the cost of INSERT when you *don't* have a cl ix, as time is
spent looking for free space. Perhaps these two offset eachother?
I'd try a 3:rd alternative, do the inserts having an IDENTITY as cl id. Would e interesting to
compare to the two above.
Anyhow, I'm curious. Let us know :-).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Danut" <danutzp@hotmail.com> wrote in message news:eY4xayX5BHA.1892@tkmsftngp04...
> Hi Russell,
>
> In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
> iterations that finally resulted in 100,000 records.
>
> Should I go to 1,000,000 records to see a difference?
>
> Danut
>
> "Russell Fields" <rlfields@sprynet.com> wrote in message
> news:eA164bX5BHA.1020@tkmsftngp07...
> > Danut,
> >
> > To get a test that would tell you anything you need to insert a lot more
> > rows than that. Try inserting a few thousand rows for your tests.
> >
> > On the other end, select speed can degrade greatly without a clustered
> > index. The heap table that you get has to handle inserts and splits just
> > like a clustered table, but the mechanism for doing this is very expensive
> > if there are many inserts or splits.
> >
> > Russell Fields
> >
> >
> > "Danut" <danutzp@hotmail.com> wrote in message
> > news:OIs6NxV5BHA.1456@tkmsftngp02...
> > > I was trying to test the assertion that says an INSERT will take long
> for
> > a
> > > table that has a CI (clustered index) vs. the same table that has a
> nonCI.
> > >
> > > Below is the SQL script for creating the table I have used and the
> stored
> > > procedure that gets called by a C++ application in a loop.
> > > I get almost the same kind of results in both cases. I expected the
> nonCI
> > > INSERT to be faster than the CI INSERT.
> > >
> > > Any thoughts?
> > >
> > > Danut
> > >
> > > ==================
> > > if exists (select * from dbo.sysobjects where id =
> > > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
> =
> > 1)
> > > drop table [dbo].[Test_Table]
> > > GO
> > >
> > > CREATE TABLE [dbo].[Test_Table] (
> > > [id] [uniqueidentifier] NOT NULL ,
> > > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [long1] [int] NULL ,
> > > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > > (
> > > [id]
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > if exists (select * from dbo.sysobjects where id =
> > > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
> =
> > > 1)
> > > drop procedure [dbo].[Test_Insert]
> > > GO
> > >
> > >
> > > CREATE PROCEDURE [dbo].[Test_Insert]
> > > @field1 nvarchar(50),
> > > @long1 int
> > > AS
> > > SET NOCOUNT ON
> > >
> > > INSERT INTO dbo.Test_Table
> > > VALUES ( NEWID(), @field1, @long1
> > > ,
> > >
> >
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > ,
> > >
> >[/color]
> '01234567890123456789012345678
901234567890123456789012345678
9012345678901234[
color=darkred]
> > > 5678901234567890123456789'
> > > )
> > >
> > > RETURN 0
> > > GO
> > >
> > >
> > >
> >
> >[/color]
>
>[/color]
| |
|
| Here are some results:
Iteration CI (tps) nonCI (tps)
50,000 78.16454 109.45100
100,000 43.05151 94.10236
I have started in both cases with an empy database.
There were 10 virtual users (10 concurrent C++ threads), each calling the stored procedure using OLE DB.
For the CI: after about 100,000 iterations the test app was started to fire
"Timeout expired" (Microsoft OLE DB Provider for SQL Server)
If I can find some time I will try with the two solutions you propose.
Danut
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:OUJTs9e5BHA.2156@tkmsftngp05...
> You can also try having something like 100000 rows. Then rebuilding your indexes, having
> fillfactor of 100. Then try adding, say, 100000 more. From an architectural viewpoint, having cl
> ix on a key that is randomly distributed should cost quite a bit. Due to page splits.
>
> Otoh, there's a KB talking about the cost of INSERT when you *don't* have a cl ix, as time is
> spent looking for free space. Perhaps these two offset eachother?
>
> I'd try a 3:rd alternative, do the inserts having an IDENTITY as cl id. Would e interesting to
> compare to the two above.
>
> Anyhow, I'm curious. Let us know :-).
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Danut" <danutzp@hotmail.com> wrote in message news:eY4xayX5BHA.1892@tkmsftngp04...
> > Hi Russell,
> >
> > In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
> > iterations that finally resulted in 100,000 records.
> >
> > Should I go to 1,000,000 records to see a difference?
> >
> > Danut
> >
> > "Russell Fields" <rlfields@sprynet.com> wrote in message
> > news:eA164bX5BHA.1020@tkmsftngp07...
> > > Danut,
> > >
> > > To get a test that would tell you anything you need to insert a lot more
> > > rows than that. Try inserting a few thousand rows for your tests.
> > >
> > > On the other end, select speed can degrade greatly without a clustered
> > > index. The heap table that you get has to handle inserts and splits just
> > > like a clustered table, but the mechanism for doing this is very expensive
> > > if there are many inserts or splits.
> > >
> > > Russell Fields
> > >
> > >
> > > "Danut" <danutzp@hotmail.com> wrote in message
> > > news:OIs6NxV5BHA.1456@tkmsftngp02...
> > > > I was trying to test the assertion that says an INSERT will take long
> > for
> > > a
> > > > table that has a CI (clustered index) vs. the same table that has a
> > nonCI.
> > > >
> > > > Below is the SQL script for creating the table I have used and the
> > stored
> > > > procedure that gets called by a C++ application in a loop.
> > > > I get almost the same kind of results in both cases. I expected the
> > nonCI
> > > > INSERT to be faster than the CI INSERT.
> > > >
> > > > Any thoughts?
> > > >
> > > > Danut
> > > >
> > > > ==================
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
> > =
> > > 1)
> > > > drop table [dbo].[Test_Table]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Test_Table] (
> > > > [id] [uniqueidentifier] NOT NULL ,
> > > > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [long1] [int] NULL ,
> > > > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > > > (
> > > > [id]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
> > =
> > > > 1)
> > > > drop procedure [dbo].[Test_Insert]
> > > > GO
> > > >
> > > >
> > > > CREATE PROCEDURE [dbo].[Test_Insert]
> > > > @field1 nvarchar(50),
> > > > @long1 int
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > INSERT INTO dbo.Test_Table
> > > > VALUES ( NEWID(), @field1, @long1
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > )
> > > >
> > > > RETURN 0
> > > > GO
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
| Andrew J. Kelly 2002-06-23, 6:15 am |
| Danut,
This is an absurd test. No one in there right mind would put a CI on a GUID and expect to get peak insert performance. What is the real intent of the test? If you wish to see what the difference is between inserts on a CI and Non-Ci then use a realistic schema. But then again what will that tell you? It will all depend on what the column is you use for the CI and the values being inserted.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Danut" <danutzp@hotmail.com> wrote in message news:uTfrR7i5BHA.1868@tkmsftngp04...
Here are some results:
Iteration CI (tps) nonCI (tps)
50,000 78.16454 109.45100
100,000 43.05151 94.10236
I have started in both cases with an empy database.
There were 10 virtual users (10 concurrent C++ threads), each calling the stored procedure using OLE DB.
For the CI: after about 100,000 iterations the test app was started to fire
"Timeout expired" (Microsoft OLE DB Provider for SQL Server)
If I can find some time I will try with the two solutions you propose.
Danut
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:OUJTs9e5BHA.2156@tkmsftngp05...
> You can also try having something like 100000 rows. Then rebuilding your indexes, having
> fillfactor of 100. Then try adding, say, 100000 more. From an architectural viewpoint, having cl
> ix on a key that is randomly distributed should cost quite a bit. Due to page splits.
>
> Otoh, there's a KB talking about the cost of INSERT when you *don't* have a cl ix, as time is
> spent looking for free space. Perhaps these two offset eachother?
>
> I'd try a 3:rd alternative, do the inserts having an IDENTITY as cl id. Would e interesting to
> compare to the two above.
>
> Anyhow, I'm curious. Let us know :-).
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Danut" <danutzp@hotmail.com> wrote in message news:eY4xayX5BHA.1892@tkmsftngp04...
> > Hi Russell,
> >
> > In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
> > iterations that finally resulted in 100,000 records.
> >
> > Should I go to 1,000,000 records to see a difference?
> >
> > Danut
> >
> > "Russell Fields" <rlfields@sprynet.com> wrote in message
> > news:eA164bX5BHA.1020@tkmsftngp07...
> > > Danut,
> > >
> > > To get a test that would tell you anything you need to insert a lot more
> > > rows than that. Try inserting a few thousand rows for your tests.
> > >
> > > On the other end, select speed can degrade greatly without a clustered
> > > index. The heap table that you get has to handle inserts and splits just
> > > like a clustered table, but the mechanism for doing this is very expensive
> > > if there are many inserts or splits.
> > >
> > > Russell Fields
> > >
> > >
> > > "Danut" <danutzp@hotmail.com> wrote in message
> > > news:OIs6NxV5BHA.1456@tkmsftngp02...
> > > > I was trying to test the assertion that says an INSERT will take long
> > for
> > > a
> > > > table that has a CI (clustered index) vs. the same table that has a
> > nonCI.
> > > >
> > > > Below is the SQL script for creating the table I have used and the
> > stored
> > > > procedure that gets called by a C++ application in a loop.
> > > > I get almost the same kind of results in both cases. I expected the
> > nonCI
> > > > INSERT to be faster than the CI INSERT.
> > > >
> > > > Any thoughts?
> > > >
> > > > Danut
> > > >
> > > > ==================
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
> > =
> > > 1)
> > > > drop table [dbo].[Test_Table]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Test_Table] (
> > > > [id] [uniqueidentifier] NOT NULL ,
> > > > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [long1] [int] NULL ,
> > > > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > > > (
> > > > [id]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
> > =
> > > > 1)
> > > > drop procedure [dbo].[Test_Insert]
> > > > GO
> > > >
> > > >
> > > > CREATE PROCEDURE [dbo].[Test_Insert]
> > > > @field1 nvarchar(50),
> > > > @long1 int
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > INSERT INTO dbo.Test_Table
> > > > VALUES ( NEWID(), @field1, @long1
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > )
> > > >
> > > > RETURN 0
> > > > GO
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
|
| What is it absurd?
The database I am taking care of has many tables with the PK a UNIQUEIDENTIFIER field and a CI on it.
Why is it UNIQUEIDENTIFIER? Because this is the way it was designed long time ago, I just inherited the database.
Why is is CI? Because this is the fix a Microsoft guy recommended about one year ago for fixing deadlocks in the database.
Danut
"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message news:u8DbjPj5BHA.2156@tkmsftngp07...
Danut,
This is an absurd test. No one in there right mind would put a CI on a GUID and expect to get peak insert performance. What is the real intent of the test? If you wish to see what the difference is between inserts on a CI and Non-Ci then use a realistic schema. But then again what will that tell you? It will all depend on what the column is you use for the CI and the values being inserted.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Danut" <danutzp@hotmail.com> wrote in message news:uTfrR7i5BHA.1868@tkmsftngp04...
Here are some results:
Iteration CI (tps) nonCI (tps)
50,000 78.16454 109.45100
100,000 43.05151 94.10236
I have started in both cases with an empy database.
There were 10 virtual users (10 concurrent C++ threads), each calling the stored procedure using OLE DB.
For the CI: after about 100,000 iterations the test app was started to fire
"Timeout expired" (Microsoft OLE DB Provider for SQL Server)
If I can find some time I will try with the two solutions you propose.
Danut
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:OUJTs9e5BHA.2156@tkmsftngp05...
> You can also try having something like 100000 rows. Then rebuilding your indexes, having
> fillfactor of 100. Then try adding, say, 100000 more. From an architectural viewpoint, having cl
> ix on a key that is randomly distributed should cost quite a bit. Due to page splits.
>
> Otoh, there's a KB talking about the cost of INSERT when you *don't* have a cl ix, as time is
> spent looking for free space. Perhaps these two offset eachother?
>
> I'd try a 3:rd alternative, do the inserts having an IDENTITY as cl id. Would e interesting to
> compare to the two above.
>
> Anyhow, I'm curious. Let us know :-).
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Danut" <danutzp@hotmail.com> wrote in message news:eY4xayX5BHA.1892@tkmsftngp04...
> > Hi Russell,
> >
> > In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
> > iterations that finally resulted in 100,000 records.
> >
> > Should I go to 1,000,000 records to see a difference?
> >
> > Danut
> >
> > "Russell Fields" <rlfields@sprynet.com> wrote in message
> > news:eA164bX5BHA.1020@tkmsftngp07...
> > > Danut,
> > >
> > > To get a test that would tell you anything you need to insert a lot more
> > > rows than that. Try inserting a few thousand rows for your tests.
> > >
> > > On the other end, select speed can degrade greatly without a clustered
> > > index. The heap table that you get has to handle inserts and splits just
> > > like a clustered table, but the mechanism for doing this is very expensive
> > > if there are many inserts or splits.
> > >
> > > Russell Fields
> > >
> > >
> > > "Danut" <danutzp@hotmail.com> wrote in message
> > > news:OIs6NxV5BHA.1456@tkmsftngp02...
> > > > I was trying to test the assertion that says an INSERT will take long
> > for
> > > a
> > > > table that has a CI (clustered index) vs. the same table that has a
> > nonCI.
> > > >
> > > > Below is the SQL script for creating the table I have used and the
> > stored
> > > > procedure that gets called by a C++ application in a loop.
> > > > I get almost the same kind of results in both cases. I expected the
> > nonCI
> > > > INSERT to be faster than the CI INSERT.
> > > >
> > > > Any thoughts?
> > > >
> > > > Danut
> > > >
> > > > ==================
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
> > =
> > > 1)
> > > > drop table [dbo].[Test_Table]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Test_Table] (
> > > > [id] [uniqueidentifier] NOT NULL ,
> > > > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [long1] [int] NULL ,
> > > > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > > > (
> > > > [id]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
> > =
> > > > 1)
> > > > drop procedure [dbo].[Test_Insert]
> > > > GO
> > > >
> > > >
> > > > CREATE PROCEDURE [dbo].[Test_Insert]
> > > > @field1 nvarchar(50),
> > > > @long1 int
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > INSERT INTO dbo.Test_Table
> > > > VALUES ( NEWID(), @field1, @long1
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > )
> > > >
> > > > RETURN 0
> > > > GO
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
| Andrew J. Kelly 2002-06-23, 6:15 am |
| My point is what is it that you are trying to find the answer to? This is your original question:
>>I was trying to test the assertion that says an INSERT will take long for a
>>table that has a CI (clustered index) vs. the same table that has a nonCI.
A Clustered index on a GUID will almost certainly take longer than a Non-CI overall. This is due to the fact that at some point you will get pagesplits on your data pages. This is a fact and can not be avoided when the CI is on a random value like that. This will also affect query perfomance in the long run. Since that is pretty straight foward on how this works I assumed you have another reason for this test? Is it that you are not getting acceptable insert times in your application, are you worried about fragmentation or what? If you want to keep the CI on the GUID column then you need to maintain proper fill factors to keep the splitting to a minimum and logical fragmentation to a minimum.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Danut" <danutzp@hotmail.com> wrote in message news:et1VVaj5BHA.2128@tkmsftngp02...
What is it absurd?
The database I am taking care of has many tables with the PK a UNIQUEIDENTIFIER field and a CI on it.
Why is it UNIQUEIDENTIFIER? Because this is the way it was designed long time ago, I just inherited the database.
Why is is CI? Because this is the fix a Microsoft guy recommended about one year ago for fixing deadlocks in the database.
Danut
"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message news:u8DbjPj5BHA.2156@tkmsftngp07...
Danut,
This is an absurd test. No one in there right mind would put a CI on a GUID and expect to get peak insert performance. What is the real intent of the test? If you wish to see what the difference is between inserts on a CI and Non-Ci then use a realistic schema. But then again what will that tell you? It will all depend on what the column is you use for the CI and the values being inserted.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Danut" <danutzp@hotmail.com> wrote in message news:uTfrR7i5BHA.1868@tkmsftngp04...
Here are some results:
Iteration CI (tps) nonCI (tps)
50,000 78.16454 109.45100
100,000 43.05151 94.10236
I have started in both cases with an empy database.
There were 10 virtual users (10 concurrent C++ threads), each calling the stored procedure using OLE DB.
For the CI: after about 100,000 iterations the test app was started to fire
"Timeout expired" (Microsoft OLE DB Provider for SQL Server)
If I can find some time I will try with the two solutions you propose.
Danut
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:OUJTs9e5BHA.2156@tkmsftngp05...
> You can also try having something like 100000 rows. Then rebuilding your indexes, having
> fillfactor of 100. Then try adding, say, 100000 more. From an architectural viewpoint, having cl
> ix on a key that is randomly distributed should cost quite a bit. Due to page splits.
>
> Otoh, there's a KB talking about the cost of INSERT when you *don't* have a cl ix, as time is
> spent looking for free space. Perhaps these two offset eachother?
>
> I'd try a 3:rd alternative, do the inserts having an IDENTITY as cl id. Would e interesting to
> compare to the two above.
>
> Anyhow, I'm curious. Let us know :-).
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Danut" <danutzp@hotmail.com> wrote in message news:eY4xayX5BHA.1892@tkmsftngp04...
> > Hi Russell,
> >
> > In my tests I have used 10 Virtual Users (10 concurrent threads) and 10,000
> > iterations that finally resulted in 100,000 records.
> >
> > Should I go to 1,000,000 records to see a difference?
> >
> > Danut
> >
> > "Russell Fields" <rlfields@sprynet.com> wrote in message
> > news:eA164bX5BHA.1020@tkmsftngp07...
> > > Danut,
> > >
> > > To get a test that would tell you anything you need to insert a lot more
> > > rows than that. Try inserting a few thousand rows for your tests.
> > >
> > > On the other end, select speed can degrade greatly without a clustered
> > > index. The heap table that you get has to handle inserts and splits just
> > > like a clustered table, but the mechanism for doing this is very expensive
> > > if there are many inserts or splits.
> > >
> > > Russell Fields
> > >
> > >
> > > "Danut" <danutzp@hotmail.com> wrote in message
> > > news:OIs6NxV5BHA.1456@tkmsftngp02...
> > > > I was trying to test the assertion that says an INSERT will take long
> > for
> > > a
> > > > table that has a CI (clustered index) vs. the same table that has a
> > nonCI.
> > > >
> > > > Below is the SQL script for creating the table I have used and the
> > stored
> > > > procedure that gets called by a C++ application in a loop.
> > > > I get almost the same kind of results in both cases. I expected the
> > nonCI
> > > > INSERT to be faster than the CI INSERT.
> > > >
> > > > Any thoughts?
> > > >
> > > > Danut
> > > >
> > > > ==================
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable')
> > =
> > > 1)
> > > > drop table [dbo].[Test_Table]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Test_Table] (
> > > > [id] [uniqueidentifier] NOT NULL ,
> > > > [field1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [long1] [int] NULL ,
> > > > [x0] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x1] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x2] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x3] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x4] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x5] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x6] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x7] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x8] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > > [x9] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > > ALTER TABLE [dbo].[Test_Table] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED
> > > > (
> > > > [id]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > if exists (select * from dbo.sysobjects where id =
> > > > object_id(N'[dbo].[Test_Insert]') and OBJECTPROPERTY(id, N'IsProcedure')
> > =
> > > > 1)
> > > > drop procedure [dbo].[Test_Insert]
> > > > GO
> > > >
> > > >
> > > > CREATE PROCEDURE [dbo].[Test_Insert]
> > > > @field1 nvarchar(50),
> > > > @long1 int
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > INSERT INTO dbo.Test_Table
> > > > VALUES ( NEWID(), @field1, @long1
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > ,
> > > >
> > >
> > '01234567890123456789012345678
901234567890123456789012345678
9012345678901234
> > > > 5678901234567890123456789'
> > > > )
> > > >
> > > > RETURN 0
> > > > GO
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
| Gert-Jan Strik 2002-06-23, 6:15 am |
| Usually, you are using a database not just to put data in it, but also
to get data out. IMHO it is not very useful to analyze insert
performance without analyzing select performance at the same time.
Usually performance tuning is a trade off between high concurrency and
high cache hit ratio, high performance in modifications vs high
performance in selections. In most situations it is not very useful to
have very fast inserts when a selection on the data takes minutes
(caused by a bad query plan, caused by bad design/tuning). It is also a
consideration whether maintenance plans can be run (reindexing etc.),
and how often they will be run.
Gert-Jan
| |
| Danut Prisacaru 2002-06-23, 6:15 am |
| "Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message
news:e$Yp19j5BHA.2048@tkmsftngp07...
>My point is what is it that you are trying to find the answer to?
I'm trying to improve the real application.
>This is your original question:
>>I was trying to test the assertion that says an INSERT will take long for
a
>>table that has a CI (clustered index) vs. the same table that has a nonCI.
I didn't want to load the bandwidth with explanations about the real app.
That's why I have just extracting the essence of the problem.
>A Clustered index on a GUID will almost certainly take longer than a Non-CI
overall.
This is what I was trying to find out using my tests.
>This is due to the fact that at some point you will get pagesplits on your
data pages.
>This is a fact and can not be avoided when the CI is on a random value like
that.
I knew the theory but I need a solid proof before making a decision and go
for the changes.
>This will also affect query perfomance in the long run.
>Since that is pretty straight foward on how this works
>I assumed you have another reason for this test?
As I said I need a solid proof for my managers to show them the problem.
>Is it that you are not getting acceptable insert times in your application,
>are you worried about fragmentation or what?
>If you want to keep the CI on the GUID column then you need to maintain
proper
>fill factors to keep the splitting to a minimum and logical fragmentation
to a minimum.
I can't do that. The app will be hosted by many clients and they do not want
to allocate resource for this kind of job.
These were the results I got for the test app. I did the same thing with the
reall app and I got almost the same results. A real scenario was used. After
this test three tables have their PK changed from CI to NCI. Now I am trying
to test a bigger piece of the whole app and see what happens with the other
tables and their CIs.
Iteration CI (tps) nonCI (tps)
50,000 78.16454 109.45100
100,000 43.05151 94.10236
--
>Andrew J. Kelly SQL MVP
>Targitinteractive, Inc.
Danut
PS And another story: it looks like we may have to port the SQL database to
Oracle. It is going to be fun. I saw no UNIQUEIDENTIFIER data type in
Oracle.
| |
|
| It seems to me that the main purpose for Clustered indexes is in faster
retrevial when result needs to be in sorted order, or finding a range of a
value.
I do not see how Clustered indexes would help with deadlocks, and if you
then use nonClustered indexes on same file, they have the burden of
traversing the Clustered index to find the record. I guess this saves time
in having to keep nonClustered indexes upto date!
Good luck w/Oracle
--
Ralph Page MBA, MCP
----------------------------------------------------------------------------
---------
"Do not be too moral. You may cheat yourself out of much life so.
Aim above morality. Be not simply good, be good for something."
-- Henry David Thoreau
----------------------------------------------------------------------------
---------
|
|
|
|
|