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? 4 1574
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
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
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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....
|
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...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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,...
| |