Hi there,
I was in troubles with a UPDATE+IN statement:
The following command use to take about 5 minutes to
be done:
UPDATE requisicao SET conclusao='3' WHERE reg IN
(SELECT reg FROM requisicao WHERE now()-data>'15
days');
The table 'requisicao' has only about 400 lines (!!).
If I change it to:
UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
45, 87, 98, 129, 350, 389);
I have detected that the major problem isn't in the
amount of lines changed, but in the subselect.
How can I solve/optimize it? I would like to use the
IN, but in the last case I would make a software
change.
Thanks in advance and
Best regards,
Marcelo Pereira
Brazil
Yahoo! Mail - o melhor webmail do Brasil http://mail.yahoo.com.br
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 4 2334
In versions 7.3 and before, IN was a dog compared to the equivalent EXISTS
statement. But given your timings, how long has it been since you did a
VACUUM ANALYZE FULL on your database?
Or REINDEX on any of the indexes?
Hope this helps,
On Mon, Nov 03, 2003 at 10:52:44AM -0300, MaRcElO PeReIrA wrote: Hi there, I was in troubles with a UPDATE+IN statement: The following command use to take about 5 minutes to be done: UPDATE requisicao SET conclusao='3' WHERE reg IN (SELECT reg FROM requisicao WHERE now()-data>'15 days'); The table 'requisicao' has only about 400 lines (!!). If I change it to: UPDATE requisicao SET conclusao='3' WHERE reg IN (12, 45, 87, 98, 129, 350, 389); I have detected that the major problem isn't in the amount of lines changed, but in the subselect. How can I solve/optimize it? I would like to use the IN, but in the last case I would make a software change. Thanks in advance and Best regards, Marcelo Pereira Brazil Yahoo! Mail - o melhor webmail do Brasil http://mail.yahoo.com.br ---------------------------(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/ "All that is needed for the forces of evil to triumph is for enough good men to do nothing." - Edmond Burke "The penalty good people pay for not being interested in politics is to be governed by people worse than themselves." - Plato
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE/pmGyY5Twig3Ge+YRAtg1AJ4miT2uOXTpSxkS+2JIHoZEY7xaZw CeJ/3a
q0Jc8pwJizY2t+mhZr8ycSc=
=Cd9/
-----END PGP SIGNATURE-----
On Mon, 3 Nov 2003, [iso-8859-1] MaRcElO PeReIrA wrote: Hi there,
I was in troubles with a UPDATE+IN statement:
The following command use to take about 5 minutes to be done:
UPDATE requisicao SET conclusao='3' WHERE reg IN (SELECT reg FROM requisicao WHERE now()-data>'15 days');
The table 'requisicao' has only about 400 lines (!!).
If I change it to:
UPDATE requisicao SET conclusao='3' WHERE reg IN (12, 45, 87, 98, 129, 350, 389);
I have detected that the major problem isn't in the amount of lines changed, but in the subselect.
How can I solve/optimize it? I would like to use the IN, but in the last case I would make a software change.
The easiest is wait for 7.4 where IN optimizes better than it has in the
past and see if that resolves the problem, otherwise, try
changing the query into an exists form.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Is the query below any different from what you are trying to accomplish.
UPDATE requisicao SET conclusao='3' WHERE now()-data>'15 days' ;
if you want to use the exact query more efficiently you may rewrite
using EXISTS as
UPDATE requisicao AS a SET conclusao='3' WHERE
EXISTS (select * from requisicao where reg=a.reg and
now()-data>'15 days' ) ;
Best Regards
Mallah. Hi there,
I was in troubles with a UPDATE+IN statement:
The following command use to take about 5 minutes to be done:
UPDATE requisicao SET conclusao='3' WHERE reg IN (SELECT reg FROM requisicao WHERE now()-data>'15 days');
The table 'requisicao' has only about 400 lines (!!).
If I change it to:
UPDATE requisicao SET conclusao='3' WHERE reg IN (12, 45, 87, 98, 129, 350, 389);
I have detected that the major problem isn't in the amount of lines changed, but in the subselect.
How can I solve/optimize it? I would like to use the IN, but in the last case I would make a software change.
Thanks in advance and Best regards,
Marcelo Pereira Brazil
Yahoo! Mail - o melhor webmail do Brasil http://mail.yahoo.com.br
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Oops sorry AS is not allowed. UPDATE requisicao AS a SET conclusao='3' WHERE EXISTS (select * from requisicao where reg=a.reg and now()-data>'15 days' ) ;
probably below will work.
UPDATE requisicao SET conclusao='3' WHERE
EXISTS (select * from requisicao as a where reg=requisicao.reg and
now()-data>'15 days' ) ;
Regds
Mallah.
Hi,
Is this right?? Can I use AS on UPDATE???
Regards,
Marcelo
--- ma****@trade-india.com escreveu: > Is the query below any different from what you are trying to accomplish.
UPDATE requisicao SET conclusao='3' WHERE now()-data>'15 days' ;
if you want to use the exact query more efficiently you may rewrite using EXISTS as
UPDATE requisicao AS a SET conclusao='3' WHERE EXISTS (select * from requisicao where reg=a.reg and now()-data>'15 days' ) ;
Best Regards Mallah.
> Hi there, > > I was in troubles with a UPDATE+IN statement: > > The following command use to take about 5 minutes to > be done: > > UPDATE requisicao SET conclusao='3' WHERE reg IN > (SELECT reg FROM requisicao WHERE now()-data>'15 > days'); > > The table 'requisicao' has only about 400 lines (!!). > > If I change it to: > > UPDATE requisicao SET conclusao='3' WHERE reg IN (12, > 45, 87, 98, 129, 350, 389); > > I have detected that the major problem isn't in the > amount of lines changed, but in the subselect. > > How can I solve/optimize it? I would like to use the > IN, but in the last case I would make a software > change. > > Thanks in advance and > Best regards, > > Marcelo Pereira > Brazil > > Yahoo! Mail - o melhor webmail do Brasil > http://mail.yahoo.com.br > > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you > checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ----------------------------------------- Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
Yahoo! Mail - o melhor webmail do Brasil http://mail.yahoo.com.br
-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dan Berlin |
last post by:
I have two tables:
T1 : Key as bigint, Data as char(20) - size: 61M records
T2 : Key as bigint, Data as char(20) - size: 5M records
T2 is the smaller, with 5 million records.
They both have...
|
by: Gent |
last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My
problem is that this slows down the system significantly. I have
narrowed down the problem to the size (Lines of code) that need...
|
by: Neil |
last post by:
I previously posted re. this, but thought I'd try again with a summary of
facts.
I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that
is linked to the database via ODBC...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: dd_bdlm |
last post by:
I have made some alterations to my company's database so that variable
rates are calculated on postcode area.
This is currently been expanded from a query which featured 12 IIf
statements to one...
|
by: mp |
last post by:
Hi,
MS Access DB, C#, VS, SQL
I have implemented search with SQL statements like follows:
SQLString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE
'"+txtWordManipulation.Text+"%' ORDER BY...
|
by: MadMan2004 |
last post by:
Hello all!
I'm having a problem with a project I'm working on and I'd like to ask
for anyone's input that might be helpful. I'm building a rather large
front-end application connecting to an...
|
by: Geoff Jones |
last post by:
Hi All
I hope you'll forgive me for posting this here (I've also posted to ado site
but with no response so far) as I'm urgently after a solution.
Can anybody help me? I'm updating a table on...
|
by: dunleav1 |
last post by:
I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array...
|
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:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
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: 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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: 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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |