Insertion and Updates on 20.000.000 tuples table.

[Andrix]

Hi,
I have a table with 20.000.000 of tuples.
I have been monitoring the performance of the insertion and updates,
but not convince me at all.
The table have 30 columns, what and 12 of it, are calcultated column.

The test that i do was this:

1 Insertion with all the columns and calculing the calcultated columns
in the insertion sentence.

1 insertion and all the columns calculated in @vars..

1 insertion with the basic fields, and 10 updates.

And the result was that the last test was the most performant.

What is your opinion?

[Erland Sommarskog]

Re: Insertion and Updates on 20.000.000 tuples table.

Andrix (elkpichico@gma il.com) writes:[color=blue]
> I have a table with 20.000.000 of tuples.
> I have been monitoring the performance of the insertion and updates,
> but not convince me at all.
> The table have 30 columns, what and 12 of it, are calcultated column.
>
> The test that i do was this:
>
> 1 Insertion with all the columns and calculing the calcultated columns
> in the insertion sentence.
>
> 1 insertion and all the columns calculated in @vars..
>
> 1 insertion with the basic fields, and 10 updates.
>
> And the result was that the last test was the most performant.
>
> What is your opinion?[/color]

That your posting is not very clear. I would take "calculated columns"
to mean "computed columns", but since you can't insert explicit values
in computed columns that does not really fit.

Why not post the code you used, so it's easier to understand what you
are talking about.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Andrix]

Re: Insertion and Updates on 20.000.000 tuples table.

Hi.

The test that i do, were this:
select @PK = .....
INSERT INTO shared_calc
VALUES (@PK,10,20,223, "calculo trivial",...... . ,@imp * @ohter, ....)

I mean that in the same Insert sentence, i do all the calcs to insert
in the table.

the other,
was
select @cal1 = @imp * @other
select @cal2 = @imp * @other - @umbral

select @PK = .....
INSERT INTO shared_calc
VALUES (@PK,10,20,223, "calculo trivial",...... . ,@calc1,@calc2, ....)

and the last one was:
select @PK = .....
INSERT INTO shared_calc
VALUES (@PK,10,20,223, "calculo trivial",...... . )

select @cal1 = @imp * @other
select @cal2 = @imp * @other - @umbral

update shared_calc_imp
set calc1 = @calc1
where pk = @PK

update shared_calc_imp
set calc2 = @calc2
where pk = @PK

thanks!

Andrix.


Erland Sommarskog wrote:[color=blue]
> Andrix (elkpichico@gma il.com) writes:[color=green]
> > I have a table with 20.000.000 of tuples.
> > I have been monitoring the performance of the insertion and updates,
> > but not convince me at all.
> > The table have 30 columns, what and 12 of it, are calcultated column.
> >
> > The test that i do was this:
> >
> > 1 Insertion with all the columns and calculing the calcultated columns
> > in the insertion sentence.
> >
> > 1 insertion and all the columns calculated in @vars..
> >
> > 1 insertion with the basic fields, and 10 updates.
> >
> > And the result was that the last test was the most performant.
> >
> > What is your opinion?[/color]
>
> That your posting is not very clear. I would take "calculated columns"
> to mean "computed columns", but since you can't insert explicit values
> in computed columns that does not really fit.
>
> Why not post the code you used, so it's easier to understand what you
> are talking about.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[--CELKO--]

Re: Insertion and Updates on 20.000.000 tuples table.

Do not store calculations in a table. You can do the calculations in a
VIEW, in the application or in computed columns (a proprietary
shorthand) for a VIEW. This will save you disk space of course. It
will also run faster, since reading from a disk is very slow compared
to math done in main storage. But the real benefit is data integrity,
which your approach will destroy.

[Erland Sommarskog]

Re: Insertion and Updates on 20.000.000 tuples table.

Andrix (elkpichico@gma il.com) writes:[color=blue]
> The test that i do, were this:
> select @PK = .....
> INSERT INTO shared_calc
> VALUES (@PK,10,20,223, "calculo trivial",...... . ,@imp * @ohter, ....)
>
> I mean that in the same Insert sentence, i do all the calcs to insert
> in the table.
>
> the other,
> was
> select @cal1 = @imp * @other
> select @cal2 = @imp * @other - @umbral
>
> select @PK = .....
> INSERT INTO shared_calc
> VALUES (@PK,10,20,223, "calculo trivial",...... . ,@calc1,@calc2, ....)
>
> and the last one was:
> select @PK = .....
> INSERT INTO shared_calc
> VALUES (@PK,10,20,223, "calculo trivial",...... . )
>
> select @cal1 = @imp * @other
> select @cal2 = @imp * @other - @umbral
>
> update shared_calc_imp
> set calc1 = @calc1
> where pk = @PK
>
> update shared_calc_imp
> set calc2 = @calc2
> where pk = @PK[/color]

And you are saying that the last test had the best performance?
Surprising.

But how many times did you run each test? Did you ensure that there
was no other load on the server? Did you work with the same table
that you just added to each time? Or did you recreate the table for
each test? And what were the results in numbers? Were the huge
differences?

Running performance tests requires care to avoid sources of error. One
very important is that any measurement below 50 ms contains to much
white noise to be reliable.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx