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 means of a materialized
view. Updates will happen via a stored proc.
Which makes the most sense?
1. Blindly overwrite the value for all columns, even columns that haven't
changed.
2. Compare the current value and update each column at a time, leaving
unchanged columns alone.
3. Break out my tables more, so that infrequently updated columns are in
different tables.
I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 2  2028 
The number of dead tuples is the number of UPDATE statements you make.
You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
as optimal as you can get.
The only exception may be toasted fields, which would only apply if you
had fields with more than 2K of data or so...
Hope this helps,
On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote: 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 means of a materialized
view. Updates will happen via a stored proc.
Which makes the most sense?
1. Blindly overwrite the value for all columns, even columns that haven't
changed.
2. Compare the current value and update each column at a time, leaving
unchanged columns alone.
3. Break out my tables more, so that infrequently updated columns are in
different tables.
I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBgrv5Y5T wig3Ge+YRAmreAK Cu3rBCgH7EnOHJM AbpB2KgmaQfJACf c+6W
Sl00ea6BS4g0G1V 2mMfo6I4=
=ZawN
-----END PGP SIGNATURE-----
Thanks. Good to hear that the easiest solution for me is also the fastest.
On Fri, 29 Oct 2004, Martijn van Oosterhout wrote: The number of dead tuples is the number of UPDATE statements you make.
You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
as optimal as you can get.
The only exception may be toasted fields, which would only apply if you
had fields with more than 2K of data or so...
Hope this helps,
On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote: 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 means of a materialized
view. Updates will happen via a stored proc.
Which makes the most sense?
1. Blindly overwrite the value for all columns, even columns that haven't
changed.
2. Compare the current value and update each column at a time, leaving
unchanged columns alone.
3. Break out my tables more, so that infrequently updated columns are in
different tables.
I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sanjay Minni |
last post by:
What is the datatype to be used
for Primary Key columns for most
optimised access given that
- There will be a single column primary key only
- The values will only be integers (but as
strings) at least 12 digits (characters) long
- all positions will be occupied (no leading 0's)
- Tables may have upto 1m+ rows
|
by: Albretch |
last post by:
I am trying to insert some textual data belonging to an HTML page into
a table column with 'TEXT' as data type
mysql's maual _/manual.html#String_types tell you, you may insert up
to (2^16 - 1), that is 65535 characters, but I am getting errors when
I try to insert a column larger than 236 characters.
Initially I thought it might be because I had to escape some
characters, but after playing a some scenarios and just inserting a
bunch...
|
by: BCR |
last post by:
I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?
Chender
|
by: Mary |
last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger"
table containing numerous triggers - each of which is activated by an
UPDATE to a different column of this "trigger" table. When the
triggers are fired, various other operations are performed on other
tables in the database. The triggers are not created on these other
tables because other programs perform updates to these tables and we
do not want the triggers to fire...
|
by: Owen T. Soroke |
last post by:
Using VB.NET
I have a ListView with several columns.
Two columns contain integer values, while the remaining contain string
values.
I am confused as to how I would provide functionality to sort columns
based on the column header the user has clicked in both Ascending and
Descending formats.
| | |
by: Manish |
last post by:
Hey Guys
I am using a datagrid to extract information out of SQL Server
datbase. The fields extracted are category,week,budget,Last
Year,Forecast and Projection. Also i add a calculated column Week% to
the datatable. Also i add calculated row - SPLH(Revenue/Hours) and
AWR(Payroll/Hours) for each week. My grid layout is as follows-
Catgory Week Budget LY Fcst Projection Week%
Revenue 1 2000 1500 2400 2000
Cost ...
|
by: Aaron Smith |
last post by:
Ok. I have a dataset that has multiple tables in it. In one of the child
tables, I have a column that I added to the DataSet (Not in the
DataSource). This column does not need to be stored in the data on the
datasource. It simply gets the first name and last name of an instructor
and displays it in the grid. I have two major problems.... One, it
doesn't display in the column until the row is saved, (Even after
calling a refresh on the...
|
by: JohnR |
last post by:
I have a datatable as the datasource to a datagrid. The datagrid has a
datagridtablestyle defined. I use the datagridtablestyle to change the
order of the columns (so they can be different than the column order of the
datatable). I also allow the user to click on a column header to sort the
datagrid by that column.
I need to identify the row and column in the datatable when the user clicks
on a cell in the datagrid.
Using the...
|
by: Phuff |
last post by:
I'm trying to do something and its proving tricky. When someone clicks
the edit button I don't want to use the standard textbox, so I create a
templated column. I need 2 dropdowns and a textbox. When they select
a category in the first dropdown the second is populated correctly.
that works. Now I want the textbox's text to be populated with the
second dropdown when an item is selected, and for that text to be what
UPDATES the...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
