473,396 Members | 1,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 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 1573
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bernhard Schmidt | last post by:
------=_NextPart_000_0030_01C34C51.B8F4D1A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi dear mysql list members =20 i have setup a mysql...
12
by: Dan Greenblatt | last post by:
I am writing some software that, among other things, needs to track the state of database tables. This includes occasionally checking the table to see what records or added, modified, or deleted....
4
by: Jason | last post by:
Looking for some insight from the professionals about how they handle row inserts. Specifically single row inserts through a stored procedure versus bulk inserts. One argument are people who say...
10
by: Anton.Nikiforov | last post by:
Dear all, i have a problem with insertion data and running post insert trigger on it. Preambula: there is a table named raw: ipsrc | cidr ipdst | cidr bytes | bigint time | timestamp...
2
by: Ben | last post by:
I'm designing a system where I'll be making frequent updates to rows, but some columns will change far less frequently than others. All columns will be read with equal frequency, though probably by...
0
by: polocar | last post by:
Hi, I have noticed a strange behaviour of CurrencyManager objects in C# (I use Visual Studio 2005 Professional Edition). Suppose that you have a SQL Server database with 2 tables called "Cities"...
20
by: technocraze | last post by:
Hi guys & commnunity experts, Does anyone knw how to go about checking for existing data in an MS Acess table? I have tried out the following code using vb but doesnt seem to work that well? Can...
3
by: Subrat Das | last post by:
Hi, I have a java application which calls a stored procedure to insert data into a table.Multiple threads of java call the same procedure at the same time. Sometimes it happens that few threads send...
0
by: John K Masters | last post by:
I am fairly new to Python and am trying to get to grips with pysqlite2. SELECT via connection.cursor. But I have not, despite frantic googling, found how to INSERT a list of tuples into a sqlite...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.