RE: [firebird-support] Efficient update on small subset of LARGE table?
You could try something like (not tested):
EXECUTE BLOCK AS
DECLARE VARIABLE MyDbKey char(8);
BEGIN
for select LrgTbl.rdb$db_key
from LargeTable LrgTbl
inner join Uppgiftshållare UH on UH.ECO_ID = LrgTbl.Hållare
inner join Uppgiftshanterare Uhant on Uhant.ECO_ID = UH.Hanterare
where LrgTbl.ÄrNull = 0
and UH.Propertynamn in ('This', 'That', 'Other', 'Extra')
and Uhant.ColX = 'Whatever' into :MyDbKey do
begin
update LargeTable
set whatever
where rdb$db_key = :MyDbKey;
end
END
Using RDB$DB_KEY is probably quicker than using the primary key (ECO_ID),
though I use it very rarely, so don't be surprised if there's some error above.
HTH,
Set
Re: [firebird-support] Efficient update on small subset of LARGE table?
Svein Erling Tysvær skriver:
You could try something like (not tested):
EXECUTE BLOCK AS
DECLARE VARIABLE MyDbKey char(8);
BEGIN
for select LrgTbl.rdb$db_key
from LargeTable LrgTbl
inner join Uppgiftshållare UH on UH.ECO_ID = LrgTbl.Hållare
inner join Uppgiftshanterare Uhant on Uhant.ECO_ID = UH.Hanterare
where LrgTbl.ÄrNull = 0
and UH.Propertynamn in ('This', 'That', 'Other', 'Extra')
and Uhant.ColX = 'Whatever' into :MyDbKey do
begin
update LargeTable
set whatever
where rdb$db_key = :MyDbKey;
end
END
Using RDB$DB_KEY is probably quicker than using the primary key
(ECO_ID), though I use it very rarely, so don't be surprised if
there's some error above.
OK, nice. I'll give it a shot on the next update.
Kjell
--
--
Kjell Rilbe
DataDIA AB
E-post: kjell.ri...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
* To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
SV: [firebird-support] System performance issue
Could it be the old Windows save point problem that Windows, on file access, makes a complete backup of the firebird file? Poul Dige (Sorry for the formatting but Outlook has its own ways...) Fra: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] På vegne af Robert martin Sendt: 12. december 2012 23:57 Til: firebird-support@yahoogroups.com Emne: [firebird-support] System performance issue Hi One of our clients has a machine where intermittently the system crawls to almost stop. Launching programs (like paint) takes about 2 minutes to open. The CPU usage is low (1-3 %) but HDD usage shows at between 97 - 100%. This situation can last 30 mins to 2 hrs before returning to normal. I raise the issue here because we have traced the cause to Firebird. When the machine is on a go slow, stopping the Firebird service fixes the problem. Sometimes starting the service again and connecting breaks the system again, sometimes it doesn't. I had though the issue may have been garbage collection but we have moved from FB 2.0 super server to FB 2.5.2 super classic and are still getting the problem. I'm pretty sure garbage collection is supposed to be a low priority activity, right? The system often enters 'slow more' straight after being rebooted. The weird thing is that while the system is slow and the HDD shows at around 100% use in resource monitor, the actually data throughput is about 1 ro 5MB a sec. This is can be much higher when the system runs normally. This made me think the HDD might be faulty but all indications are that its not (according to a hardware technician) and of course the problem fixes itself when stopping Firebird. System is i7 quad core (8 cores in Windows) 10 GB Ram Windows 7 64bit 16GB free hdd space Firebird 2.5.2 64bit DB size is 2.7 GB Any suggestions would be greatly appreciated. I know that there are monitoring functions built into FB 2.5.2, should I try those? If so any suggestions as to what to do / look for, I have not used the monitoring tables before. Thanks Rob [Non-text portions of this message have been removed]
Re: [firebird-support] Re: How to insert and see an image or photography?
Thank you very much Thomas.
Are you the author of SQL Workbench? Congratulations, very good job!!!.
Greetings.
Walter.
On Wed, Dec 12, 2012 at 7:44 PM, Thomas ijfqsftge...@spammotel.com wrote:
**
--- In firebird-support@yahoogroups.com, Lester Caine lester@... wrote:
W O wrote:
I know how to insert an image or photograpy to a table using a
programming
language and how to see that image or photography, but I don't know
how to
do without a programming language. So, I have two questions:
1. How to insert an image or photography without using a programming
language?
2. How to see that image or photography without using a programming
language?
The quick answer is you can't ... I don't know if any of the
management tools
support images, Flamerobin doesn't.
SQL Workbench/J can handle that. It does not automatically detect the type
of a BLOB, but you can display the image manually. It also can
export/import BLOB data e.g. through CSV files (the CSV file then contains
the filename of the blob data).
It also supports an extended SQL syntax where you can reference an
external file in an insert or update statement, e.g.
update foo
set img = {$blobfile=/foo/bar/image.png}
where id = 42;
http://www.sql-workbench.net
(I'm the author of that tool)
[Non-text portions of this message have been removed]
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
* To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
[firebird-support] Linux init script fix
https://bugzilla.redhat.com/show_bug.cgi?id=886578 The file /etc/init.d/firebird-superclassic was incorrect. We (James Calfee) reported a bug against the EPEL firebird-superclassic.x86_64 package and Redhat patched it.
[firebird-support] Re: Redirection
--- In firebird-support@yahoogroups.com, gu1tarrr tiagofdf.86@... wrote: Hi, I´m using Firebird version 2.1.0.17798 and I'm trying to use redirection feature by enabling it in firebird.conf file. I'm using BDE (Borland Database Engine) to connect to database. I use SERVER NAME string like `server1:server2:database.fdb' and it connects successfully but when I try to execute some SQL (SELECT * FROM table, for example ), firebird stops making my application to crash, it results as windows error. I'm using windows 7 professional, 32 btis. Connecting like `server1:database.fdb' or `server2:database.fdb' it works fine, but that is not the ideia. I've already tried with older and newer versions of firebird but it still the same. How can I solve this problem? Can you, please, help me with this? Did you enabled Redirection=true in firebird.conf? also you should try if it works from flamerobin or isql tool
[firebird-support] Re: Redirection
--- In firebird-support@yahoogroups.com, mariuz mariuz@... wrote: --- In firebird-support@yahoogroups.com, gu1tarrr tiagofdf.86@ wrote: Hi, I´m using Firebird version 2.1.0.17798 and I'm trying to use redirection feature by enabling it in firebird.conf file. I'm using BDE (Borland Database Engine) to connect to database. I use SERVER NAME string like `server1:server2:database.fdb' and it connects successfully but when I try to execute some SQL (SELECT * FROM table, for example ), firebird stops making my application to crash, it results as windows error. I'm using windows 7 professional, 32 btis. Connecting like `server1:database.fdb' or `server2:database.fdb' it works fine, but that is not the ideia. I've already tried with older and newer versions of firebird but it still the same. How can I solve this problem? Can you, please, help me with this? Did you enabled Redirection=true in firebird.conf? also you should try if it works from flamerobin or isql tool Hi, Yes, I did enable Redirection (Redirection = 1) in firebird.conf. And Yes, i tried to connect with isql tool, but it stills the same, firebird stops making ISQL to crash... Same situation with my application. I also did disable firewall only for testing... still nothing. If you want, you can make a simple test, connect like 127.0.0.1:127.0.0.1:database.fdb and try to run some SQL on it. In my case it connects successfully, the problem comes when I try to execute some SQL. Thanks for your reply.
[firebird-support] System performance issue
Hi One of our clients has a machine where intermittently the system crawls to almost stop. Launching programs (like paint) takes about 2 minutes to open. The CPU usage is low (1-3 %) but HDD usage shows at between 97 - 100%. This situation can last 30 mins to 2 hrs before returning to normal. I raise the issue here because we have traced the cause to Firebird. When the machine is on a go slow, stopping the Firebird service fixes the problem. Sometimes starting the service again and connecting breaks the system again, sometimes it doesn't. I had though the issue may have been garbage collection but we have moved from FB 2.0 super server to FB 2.5.2 super classic and are still getting the problem. I'm pretty sure garbage collection is supposed to be a low priority activity, right? The system often enters 'slow more' straight after being rebooted. The weird thing is that while the system is slow and the HDD shows at around 100% use in resource monitor, the actually data throughput is about 1 ro 5MB a sec. This is can be much higher when the system runs normally. This made me think the HDD might be faulty but all indications are that its not (according to a hardware technician) and of course the problem fixes itself when stopping Firebird. System is i7 quad core (8 cores in Windows) 10 GB Ram Windows 7 64bit 16GB free hdd space Firebird 2.5.2 64bit DB size is 2.7 GB Any suggestions would be greatly appreciated. I know that there are monitoring functions built into FB 2.5.2, should I try those? If so any suggestions as to what to do / look for, I have not used the monitoring tables before. Thanks Rob ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
RE: [firebird-support] Efficient update on small subset of LARGE table?
Kjell, I would hope that the new approach would takes 2X natural scan. How would that performance be? What new approach are you referring to? SL The approach I was thinking about suggesting. 2 X time for natural scan would be fair enough. It took 1½ hour to execute an update using execute block with for select do update where the select picked PK:s from a temporary table I filled with a select like the above. SL This is the basic approach I was going to suggest. SL Where I think there might be some optimization possible is to use a 'simple' UPDATE statement with WHERE EXISTS references to the temporary table keys (forcing the UPDATE WHERE to use a natural scan of your large table) SL I think that SELECT ... DO UPDATE approach is generating some overhead, which the simple UPDATE would not have since it would be doing a in place update of the row at the cursor position. SL Come to think of it, you could also try modify your current solution to use a CURSOR approach, since the UPDATE would also be in-place.
[firebird-support] Lost database ...
Moving to the right list for some follow up ... Having been running Firebird for many years without a single problem, I've just lost a database! Firebird 2.5.1 on Linux Went to back up the database so I could move it over to another machine to work on. I got an error page 1 is of wrong type (expected 2, found 9) during the backup ( via Flamerobin ) Trying to reconnect I got - file not found - and now there is no sign of the file in the folder! The automatic backup had failed as well, so I'm back with an earlier version, and everything else seems to be running fine on the machine. Several other websites are fine. So 'what the f has happened and why?' Alexey posted ... I think it's well-known problem with database file at Linux http://www.ib-aid.com/articles/item70 See Disappeared” files on Linux/Unix/HP-UX/... But this is the first time I've even heard of this and now I need to establish what I did wrong. The database was not 'replaced', it had been created from a backup of the development machine and has been running for a couple of weeks only. Since I have a number of more critical (i.e. Larger) sites running on the same machine what do I need to do to prevent problems. I DON'T restore over an existing file, I will always create a new file and rename the alias so how is a 'replace' created? Also was the problem with the backup a result of the problem, or did it cause it? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
