Hello,
what tool do you use to visually make a graph of a database ?
Actually i use Visio, but it's slow, and most of all it's not possible to see
diff between 2 visio files
thanks !
select * from Table1
where ((currency = 1) and (foreignvalue = 123.45)) or ((currency = 2)
and (foreignvalue = 23.45)) ...
yes but with around 100 currencies it's will be an heavy query.
i was thinging to move this inside an UDF
ie:
where UFDConvert(Price, FromCurr, ToCurr) = xxx
not sure
Hello,
i want to permit user to store their objects with price in their preferred
currency. this is not a big problem, but i also want to permit users to search
among all the object in their currency
Ex :
i want all object with price 100 USD or
i want all object with price 100 EUR
How do
Ann, thanks you so much for such good explanation !
can not wait more for the V3, hope soom the first beta will be ready
and yes you are right they move their C++/ISAPI to JAVA :) by the way
that
a curious choice ...
Why would that be a curious choice?
because they already have hundred of developer working on the c++ isapi,
thousands of thousands lines of code, why move all of them to java (exept if
the
Hello,
how to use the x notation (ex ID=x'049094') inside parameter ?
When i do
insert into ... (Field1) Values (?);
with the parameter x'049094' i receive an error
probably i can write any char i want (from #0 to #255) inside parameter and i
don't need the x notation ?
Yes, for some types of sharded applications all that's needed is the
ability to produce unions and the relative performance of those doesn't
matter.
Exactly. And as far as I understand, the feature OP requested was
actually just that, nothing more, nothing less.
yes exactly nothing
But realize that there are very real problem with those types of analysis
solutions when dealing with OLTP databases -- otherwise there would be
solutions available (that don't have license fees that are more than the
total sending cost of sending a child to a US Ivy League school for 4
I am not sure what your purpose of your DB is, but if I recall correctly you
did something with session data in this big table. Perhaps for that purpose
the use of another database like one of the noSQL database is better.
yes, that is another story this session database... in fact firebird
not sure to understand what you mean, but sharding it's a proven technologie
that it's really unbeatable ! just try for exemple sphinx, select * from ...
where ... order by xxx against 5 different host is 5 time faster than on a
single host ! i test it's true !!
and it's a rule scall
At the moment no. A pre-SQL language interface to InterBase does allow
references to multiple
databases in a single statement, but the SQL interface does not. The major
reason is that optimizing
cross-database joins is a black art, and without optimization, performance
is unpredictable.
Dear Sean,
i thing it's a easy thing to do (one or 2 days of developpement?) but that
can
add an incredibble feature to firebird ...
Comments like I think it's a easy thing to do... do not show you in a good
light!
First i say i think and second as i already do it i can even confirm
Sean, thanks for the defense, but hey, I'm used to that.
but NO NO NO it's was never never an attach... ouch big misunderstanding here
... oulalala i will now read more carefully my message before to send it !
Yes, for some types of sharded applications all that's needed is the
ability to
Hello,
We have a table where user can do classic search against different field
select
...
from
tableA
where
field1n and
field2=i;
now i want to permit user to also filter by keyword
something like
select
...
from
tableA
where
field1n and
field2=i and
thanks but i don't want to do a full table scan :) i want to use indexed search
of course
Thanks Dmitry,
Memory is cheap, just buy it.
i was speaking of more than 128GB of memory :)
About splitting table I have an opinion:
if some rows are being accessed more than other, than split table.
This will
- make row size less for most reading rows
- make less pages for most reading
ooops sorry my fault i made a mistake
yes, i want to say
Select
(select ...) AS MyFIELD2
IIF(MyField2 is null, select , Null) As MYField3
From
--- In firebird-support@yahoogroups.com, Mark Rotteveel mark@... wrote:
On Tue, 20 Mar 2012 06:46:53 -, nathanelrick nathanelrick
it's a bad new that i have no way :(
when i try the CTE, the prepare seam to grow (seam i just do now some tests).
also CTE are a little hard to use in my situation ...
Having said that, a year or so ago, Ann Harrison wrote that repeating a
subquery doesn't add much to the execution speed,
Dear vlad,
yes the prepared statement pooling is working like a charm ! the perf of the
database are huge now !
but the problem is that i can not do pooling of prepared statement for UPDATE
(can not let the connection open) :(
hello,
where is it used the SEGMENT SIZE we declare when we create a blob ?
ex:
MyBlobField BLOB SUB_TYPE 1 SEGMENT SIZE 4096
hello,
how many time to keep a connection before to recycle it ? 1 hours? 6 hours?
24 hours ? undefinitively ?
same for my readonly read commited transaction... how many time to keep it
active before to recycle (commit) it ?
and finally same for my readonly read commited statement (prepared
OK, thanks...
so it's confirm :
1/ try to avoid table with big number of fields (mostly try to make the size of
a record (size used by all fields) more little as possible)
2/ try to use a pool of parametized statement (read only read commited)...
because here it's only the prepare that is
We hope that whole statistics subsystem will be reworked in v3.
from http://www.firebirdsql.org/en/server/
We expect to see Alpha 1 released in Q1 2012
is it still true ?
Many of the great achievements of the world were accomplished by tired and
discouraged men who kept on working
i Understand now ! it's was the fault of yahoo that automatiquely add the old
messages history ... i will now delete this history before to post ... thanks
no no, i don't blame firebird at all ! (sorry for the !)
In one of your mails you wrote you use a transaction pool. I personally do
not understand the use of that, since transactions with Firebird should be
as short as possible. If you keep transactions open for a long time,
performance
Thanks for your study that show the problem i try to point !
I can only image that the unrelated contents of a table somehow impact on
the performance of an index - no idea why.
yes, and worse, with blob instead of varchar it's the same ... but i always
learn than blod are store outside the
always good to read after you :)
hello,
in the firebird.conf you have this 2 params :
#
#
# How often the pages are flushed on disk
# (for databases with ForcedWrites=Off only)
#
# Number of unflushed writes which will accumulate before they are
# flushed, at the next transaction commit. For
In all three cases you are 50ms, so what's the problem? Your niggles
are in the millisecond area right now. ;-)
oupps, but 50 ms it's ok when you do time to time, but when you have 50 client
(and that not too much for a web service) doing this at the same time then the
50 ms become 0.5
In the prepare step, roughly explained, the engine parses the SQL text,
checks the syntax and compiles it into a Firebird native executable form
(BLR).
Yes this what i understand too, and this why 50 ms seem huge ...
My guess is that you are assigning the SQL text over and over again,
C'mon. This ain't bad. *g* Ever tried to do the same with the big guns
Oracle, MSSQL etc.?
big gum? but Firebird is not a nuclear weapon compare to them ? :)
I'm afraid, this is only possible (if at all), if your middle-tier is
somehow caching prepared statements/objects available for
Can you please stop top-posting, because this removes the context of
your reply. Or do you read a book from the end to the beginning? ;-)
sorry i not understand ? i simply in yahoo.com go to the message and click
reply to the message that all ? what i do wrong ? what is top-posting ?
Thanks Svein,
Is your query,
select IDObj From DESCRIPTION where ID='ID_HAS_NEVER_EXISTED', or
select IDObj From DESCRIPTION where
ID='ID_THAT_IS_RECENTLY_DELETED_UPDATED_OR_ADDED'?
First case ID='ID_HAS_NEVER_EXISTED', so unfortunatly it's not this ..
also i do the test on a fresh
understand something . moving the page size from 8 to 16 reduce by
10 the speed of the 2 first query, but still 10x more slower than the last
variante
--- In firebird-support@yahoogroups.com, Mark Rotteveel mark@... wrote:
On Wed, 07 Mar 2012 09:23:11 -, nathanelrick nathanelrick@...
wrote
You've presented the list with a problem that we haven't been able to solve
given the information available. And no, the index entries are NOT spawn
in the record page - index entries use only the key values, they're stored
on index pages, and they're prefix compressed. I haven't a clue what
several test to confirm it
so it's mean that the data of the index is stored INSIDE the page of the
reccord ??
--- In firebird-support@yahoogroups.com, nathanelrick nathanelrick@...
wrote:
Dear Mark,
Quite simple: with a field of VARCHAR(1) on 8K pages it needs to read
at least
why bigint ? you want to store more than 2.3 milliards row ?
integer = 4bytes
bigint = 8bytes
GUID = 16 bytes
and read just my previous article to understand that size of reccord are very
important in speed (can be x10 to x100 more faster some case, but do some test)
--- In
-
From: nathanelrick
To: firebird-support@yahoogroups.com
Sent: Thursday, March 01, 2012 7:31 PM
Subject: [firebird-support] Re: what is the most fastest isolation
level ?
The problem is that the read_committed transaction will fail if it
tries to update a record
Thanks Dmitry,
i will try to simulate this to see how 2 wait transactions that locks each
other ... thanks i will come back here with the result !
Maybe you have 2 wait transactions that locks each other.
Since you are using no_read_committed, it locks even
on reading, so, any reading in
@yahoogroups.com, nathanelrick nathanelrick@...
wrote:
I become crazy i not understand why After a fresh backup/restore,
on this table
CREATE TABLE DESCRIPTIONS
(
IDObj VARCHAR(15) NOT NULL,
type SMALLINT NOT NULL,
Lang VARCHAR(3) NOT NULL,
Descr VARCHAR(1),
PRIMARY
Dear Mark,
Quite simple: with a field of VARCHAR(1) on 8K pages it needs to read
at least two pages if the VARCHAR is filled for over 80%, for smaller
VARCHARs there is still a relatively high chance it will need to read 2
pages. For page sizes of 16K this is less, but still relatively
) in the past
were not thread safe. Don't know about current status, but perhaps that is
part of your problem.
Benno
- Original Message -
From: nathanelrick
To: firebird-support@yahoogroups.com
Sent: Thursday, March 01, 2012 7:31 PM
Subject: [firebird-support] Re: what
Thanks svein,
this is the database statistics
Flags 0
Checksum12345
Generation 24702533
Page size 8192
ODS version 11.2
Oldest transaction 24694658
Oldest active
Hello Thomas,
I would be interested in a gstat -r -i yourdatabase output before
and after the update statement.
i launch the gstat -r -i yourdatabase as soon as i read you message but still
running ... so it's take lot of time to analyze :(
a soon as i have the result i will post it here
I would be interested in a gstat -r -i yourdatabase output before
and after the update statement.
Database header page information:
Flags 0
Checksum12345
Generation 24711032
Page size 8192
ODS
Update Session Set
data=...
Expiry_date= NOW + 15minutes
where
id=... and
Date_expired DATEADD(-5 minute to CAST('now' AS TIMESTAMP))
ok, in fact we already do like this, but by 1 minute instead of by 5 minutes
... :(
select ...
where id not exist = also 1200 ms !
what i can do to speed it up ?
--- In firebird-support@yahoogroups.com, nathanelrick nathanelrick@...
wrote:
hello,
the table :
CREATE TABLE DESCRIPTIONS
(
IDObj VARCHAR(15) NOT NULL,
type SMALLINT NOT NULL,
Lang VARCHAR(3
Hello,
I have a table that i use to store session
Table Session
ID: VARCHAR(16); /* GUID ID */
Expiry_date: Timestamp;
Data: Varchar(1000);
i use the table like this only :
Insert into Session(ID,Expiry_date,Data)
VALUES (CHAR_TO_UUID(NewGUID), NOW + 15minutes,...)
Update Session Set
The problem is that the read_committed transaction will fail if it
tries to update a record version created by a concurrent transaction,
even if that transaction has committed. It's kinda dumb, but that's
the way it's implemented. Read-committed transactions follow the same
update
How many records approx.?
one hours a go i just do an select count(*) from session but still now answer
so i thing a lot ...
By using isc_tpb_no_rec_version you basically disable Firebird's MVCC
capabilities, so you explicitly tell Firebird to not use the back-record
version mechansim
747 6127
Email: vince.duggan@...
Live happily ever active
firebird-support@yahoogroups.com wrote on 2012/02/28 05:03:59 PM:
[image removed]
[firebird-support] Update take hours to delete records :(
nathanelrick
to:
firebird-support
2012/02/28 05:04 PM
How many records approx.?
ok, the select count(*) return ... 4 300 000 record (for last 3 days)
hello,
what is the most fastest isolation level ?
i know the behavior of each, but i need to know the difference in speed /
resource usage between each of them ...
isc_tpb_concurrency
isc_tpb_consistency
isc_tpb_read_committed + isc_tpb_rec_version
isc_tpb_read_committed +
hello,
the table :
CREATE TABLE DESCRIPTIONS
(
IDObj VARCHAR(15) NOT NULL,
type SMALLINT NOT NULL,
Lang VARCHAR(3) NOT NULL,
Descr VARCHAR(1),
PRIMARY KEY (IDObj, type, Lang)
);
With around 40 millions records
now the query :
select Descr From DESCRIPTIONS where (IDObj='XXX') AND
no idea ? do you thing it's could be a bug in firebird (2.5) ? 1 second to
retrieve on record by his primary index seam very much too long :( and even if
their is no record founded it's take 1 second to return :(
--- In firebird-support@yahoogroups.com, nathanelrick nathanelrick@...
wrote
was thinging that the
transaction simply wait (undefinitively?) that rec are committed instead of
raising a deadlock ...
thanks !
--- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote:
On Mon, Feb 27, 2012 at 3:38 AM, nathanelrick nathanelrick@... wrote:
what is the most
Hello,
it's when i want to reset aggregated data.
I have a temp table where i add a row with +1 and -1 as Item_count_Delta
everytime someone add a new item in the product table.
at the end of the days i calculate the item count of all products by
aggregating all rows from the temp table
Now
Hello,
this is my query :
Select
MAINTABLE.ID
from
MAINTABLE
Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj
Join THIRDTABLE on THIRDTABLE.ID_obj=MAINTABLE.ID_obj
where
(MAINTABLE.name = 'jean' or
SECONDTABLE.name = 'jean' or
THIRDTABLE.name = 'jean')
I have an
thanks svein,
UNION work like a charm here !
--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
svein.erling.tysvaer@... wrote:
Hello,
this is my query :
Select
MAINTABLE.ID
from
MAINTABLE
Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj
Join THIRDTABLE on
ok thanks seam to work (but i don't know how is is internally optimized) ...
but seam ok!
--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
svein.erling.tysvaer@... wrote:
oops, now i have another probleme
Select
First 10
MAINTABLE.ID
from
MAINTABLE
where
milanb@... wrote:
nathanelrick wrote:
i want to do something like
update or insert into mytable(ID, amount) values (123, amount + 100);
how to do this ?
Use MERGE:
http://www.firebirdsql.org/refdocs/langrefupd25-merge.html
If you have an older Firebird version and still want
use a string with a separator of your choice for the array ?
ex: tab1#tab2#tab3#
while position('#' in myarray) 0 do
Currenttab = substring(...);
end
--- In firebird-support@yahoogroups.com, sergiogarciaruso
sergiogarciaruso@... wrote:
Hi. I would like to parameterize a
Hello,
i m in multi user database.
I want to clean a table of all entries from one user (rec with field
id_user=xxx) and insert new entries, BUT i must be sure that noone is not
actually doing any insert on this table for this user. i can not lock the table
because this will affect all the
Hello,
Why when i create a Primary Key that is also a foreign key 2 index are created
? or i make a mistake somewhere ?
You could use a sequence/generator for that without multi user problems and
very fast.
not really (but the idea is good), because i need also field like
NB_database_update_by_user ... :( and in this way i will need to create as
much sequence/generator than i have users (thousands of
hello,
i want to do something like
update or insert into mytable(ID, amount) values (123, amount + 100);
how to do this ?
Hello,
I need to create a table where every days i will add around 2 000 000 rows, to
delete them every night (after compiling them to produce some statistics). so
the table will receive around 60 000 000 rows every month
I m afraid that this will cost a lot of IO on the hard drive and will
First, stop worrying about the disk space being wasted. That's a problem
for Access and similar. Real database managers don't suffer from that
kind of problem. Period
Based on my experience, it is usually a lot faster to drop a table and
recreate it, than to delete all the records. I'd
Not drop database, just the table. But perhaps your app has the same
problem with this: insufficient rights...?
yes, sorry i mean drop the table ... not insufficient right, but it's a multi
user application, and i can not stop all the user to use the table for the time
i delete/recreate
Some calculations will need the full table to give accurate averages and the
like?
Create a RAM disk and just store a couple of single day files, one live and
one
processing the previous day. No need to 'delete' the content, just delete the
file when finished?
ufortunatly the stat i
i uderstand why the count(*) can be very slow because it's must read all the
data to know how many reccord ... but is their any way to give a limit to the
count(*) ? for exemple if more than 1 row read then stop the count(*) and
return 1
something like
select min(1, count(*))
Hello,
Is it normal that the gfix -sweep run for more than 7 days ? our database is
pretty big (+300 Go) but 7 days it is not too much ?
Also, during this sweep, is it safe to kill the gfix or better to shunt down
the Firebird service of the fb_inet_server.exe ?
by kill mean
NET STOP Firebird Server - DefaultInstance
this is not safe too do to this during a sweep ?
--- In firebird-support@yahoogroups.com, Alexey Kovyazin ak@... wrote:
Hello,
No, this is not normal.
No, it's not safe to kill fb_inet_server (though I think you'll have no
choice)
Hello,
Is it possible to change the charset of all columns and also the default
database charset to NONE? i just want to change the charset definition, not the
data himself. This because i want that it is the client to be responsible to
know in with charset he store the data.
i know their is
Hello,
when i execute several insert (or update or delete), is it possible to create a
trigger that will be raise not on each insert/update/delete but at the end of
these bulk inserts ? i can off course call the stored procedure from the
program but for good database integrity i prefer that
thanks !
also i notice that with octet UPPER(..) not work but with none it's work (on
the ascii char) ... (both with connection charset to none)
--- In firebird-support@yahoogroups.com, nathanelrick nathanelrick@...
wrote:
Hello,
I want to store the data in the database as is
As i
immediately after being fired).
That's not a very elegant solution, though, but better than nothing.
cheers
Tomasz
On 2012-02-02 14:05, nathanelrick wrote:
Hello,
when i execute several insert (or update or delete), is it possible to
create a trigger that will be raise not on each insert
Hello,
I want to store the data in the database as is
As i understand for the connection charset i have only the NONE choice, but
for the field definition i can choose between NONE and OCTETS ... both are
very similar as i understand, so much similar that i don't understand the
difference
79 matches
Mail list logo