471,570 Members | 998 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,570 software developers and data experts.

Insertion and Updates on 20.000.000 tuples table.

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?

Jun 23 '06 #1
4 1488
Andrix (el********@gmail.com) writes:
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?


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, es****@sommarskog.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
Jun 23 '06 #2
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:
Andrix (el********@gmail.com) writes:
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?


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, es****@sommarskog.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


Jun 25 '06 #3
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.

Jun 26 '06 #4
Andrix (el********@gmail.com) writes:
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


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, es****@sommarskog.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
Jun 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Dan Greenblatt | last post: by
10 posts views Thread by Anton.Nikiforov | last post: by
2 posts views Thread by Ben | last post: by
reply views Thread by John K Masters | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.