[firebird-support] Re: performance of subselect with group by

2014-12-15 Thread Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
15.12.2014 23:27, bjoern.rei...@fau.de wrote:
>
> select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
>
> costs 1000 Non indexed reads
>
> select * from test where Id in (
> select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
> )
>
> costs 1001000 non indexed reads
>
> Why?

IN predicate is always evaluated for the every row, because internally 
it's transformed into a correlated EXISTS equivalent:

select * from test where exists (
select * FROM test t
group by t.reference, t.key
having count(*) > 1 and min(t.Id) = ::id
)


Dmitry








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] UDF for linux 64 bit compiled with Freepascal 2.6.4

2014-12-15 Thread Roland Turcan k...@rotursoft.sk [firebird-support] 
Hello firebird-support@yahoogroups.com!

I  am  migrating my UDF from windows to linux, but all functions using
string as output when they are used crashes the server.
I  suppose  it  must  be  related  to  libib_util.so  and its function
ib_util_malloc.

My declaration is the following:

{$IFDEF WINDOWS}
function ib_util_malloc(l: integer): pointer; cdecl; external 'ib_util.dll';
{$ELSE}
function ib_util_malloc(l: integer): pointer; cdecl; external 'libib_util.so';
{$ENDIF}

the  same  code was built in he past over Freepascal windows and it is
in production without errors.

Is there anything I need to do different for linux?

PS: A piece of pascal code with string function would help.

Thanks in advance.

-- 
Best regards, TRoland
http://www.rotursoft.sk
http://exekutor.rotursoft.sk

[firebird-support] performance of subselect with group by

2014-12-15 Thread bjoern.rei...@fau.de [firebird-support] 
Hello,


On Firebird 2.5.3 (SS) 64 Bit


CREATE GENERATOR GEN_test_ID;

CREATE TABLE test (
id INTEGER NOT NULL,
reference  INTEGER,
keyINTEGER,
value  VARCHAR(200)
);


ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (Id);

SET TERM ^ ;

CREATE OR ALTER TRIGGER test_BIU0 FOR test
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
  IF (NEW.Id IS NULL) THEN
NEW.Id = GEN_ID(GEN_test_ID,1);
END
^

SET TERM ; ^


Insert 999 random records
dublicate one record (with new id of course


select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1

costs 1000 Non indexed reads

select * from test where Id in (
select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
)

costs 1001000 non indexed reads


Why?


Creating an index modifies only non indexed in indexed reads.


Björn









++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Trace cause of performance drop

2014-12-15 Thread Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
Hello Michael,

> I have a performance problem at a customer site.
>
> At this point I suspect one specific external user connection to cause
> the problems, but I can't pinpoint it so exactly just yet.
>
>
> But there is one table, where there is inserted records almost all the
> time.
>
> When the user connects, does whatever he has to (this will cause reads
> and some inserts into various tables).
>
> Then when the user completes the current routine, there will be updates
> of some 10-15 tables, which takes no time.
>
> Then an insert is done into one table. And the prepare of this insert
> can take up to 2 seconds. Normally it would be instantly.
>
>
> The entire database is around 96Gb
>
> Firebird is 2.5.
>
> Running Classic on Windows
>
>
> What will cause a prepare to become so slow?

What exact Firebird 2.5 version are you running? A vaguely remember an 
issue where prepare time can be quite long when the statement is 
referencing very large tables.

Btw, as you are on 2.5, you can simply use the Trace API to try to spot 
the slowest activities.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Performance optimation?

2014-12-15 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On Mon, 15 Dec 2014 16:01:27 +0100, "'checkmail' check_m...@satron.de
[firebird-support]"  wrote:
> Now I would like to make an analysis. At the time, I do this:
> 
> for select cast(ts as date) as mz from tablea where ts >= "criteria
from"
> and ts < "criteria to"
> group by mz)
> into :messzeit do
> begin
>   f_messwert = null;
>   MESSWERTE = '';
>   for select a.id_counter, sum(a.value) from tableb a left join
tablea
>   b
> on a.id_timestamp = b.id
>   where cast(b.ts as date) = :messzeit
>   group by a.id_counter
>   into :i_zae, :f_messwert do
>   begin
> if(f_messwert is null) then f_messwert = 0;
> MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' ||
> cast(:f_messwert as varchar(8)) || ';';
>   end
>   suspend;
> 
> end
> 
> The Result is one returned record for each day (day, conter 1 = 123;
> counter
> 2 = 222;.)
> 
> It takes a long time but I must integrate the tablea on the second part
of
> the statement. How can I optimize this in firebird?

That is not surprising. You are executing a query and for each row you are
executing another query. Use joins and/or subqueries instead.

Mark

[firebird-support] Performance optimation?

2014-12-15 Thread 'checkmail' check_m...@satron.de [firebird-support] 
Hello,

 

I save values in some tables (simpler description) 

 

First a Table who saved the timestamp of the mensuration

Table A timestamps

ID primary key

TS timestamp

 

Second a Table with the measured data (25 records/measured sensors will be
saved every 10 Minutes, one record in Table A, 25 in Table B)

Table B mensuration

ID primary key

ID_counter integer of item to measure

ID_Timestamp foreign key of Table A

Value (double precision)

 

Now I would like to make an analysis. At the time, I do this:

 

for select cast(ts as date) as mz from tablea where ts >= "criteria from"
and ts < "criteria to"

group by mz)

into :messzeit do

begin

  f_messwert = null;

  MESSWERTE = '';

  for select a.id_counter, sum(a.value) from tableb a left join tablea b
on a.id_timestamp = b.id

  where cast(b.ts as date) = :messzeit

  group by a.id_counter

  into :i_zae, :f_messwert do

  begin

if(f_messwert is null) then f_messwert = 0;

MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' ||
cast(:f_messwert as varchar(8)) || ';';

  end

  suspend;

 

end

 

The Result is one returned record for each day (day, conter 1 = 123; counter
2 = 222;.)

 

It takes a long time but I must integrate the tablea on the second part of
the statement. How can I optimize this in firebird?

 

Thank you

 

Regards

 

Olaf

[firebird-support] Trace cause of performance drop

2014-12-15 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Hi
   
 I have a performance problem at a customer site.
 At this point I suspect one specific external user connection to cause the 
problems, but I can't pinpoint it so exactly just yet. 
 

 But there is one table, where there is inserted records almost all the time. 
 When the user connects, does whatever he has to (this will cause reads and 
some inserts into various tables).
 Then when the user completes the current routine, there will be updates of 
some 10-15 tables, which takes no time. 
 Then an insert is done into one table. And the prepare of this insert can take 
up to 2 seconds. Normally it would be instantly.
 

 The entire database is around 96Gb
 Firebird is 2.5.
 Running Classic on Windows
 

 What will cause a prepare to become so slow?
 

 It also seems as if the prepare is not evenly slow for all users. 
 

 
 Michael

Re: [firebird-support] Three trailing spaces?

2014-12-15 Thread Tim Ward t...@telensa.com [firebird-support] 
On 13/12/2014 09:14, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:


On 12-12-2014 15:38, Tim Ward t...@telensa.com [firebird-support] wrote:
> On 12/12/2014 14:15, Mark Rotteveel m...@lawinegevaar.nl
> [firebird-support] wrote:
>>
>> What is the default character set of your database (if I had to 
guess it

>> is UTF8), and what is your connection character set (if I had to guess
>> it's
>> NONE or not specified)?
>>
> Database charset is UTF8. Connection charset appears also to be UTF8.

If the connection character set is UTF8 (this needs to be specified
explicitly!) then it looks like the driver is misbehaving.



The call is

ibase_connect(TSL_DATABASE, TSL_FB_USERNAME, TSL_FB_PASSWORD, 
TSL_FB_CHARSET, TSL_FB_BUFFERS, TSL_FB_DIALECT);


where

define('TSL_FB_CHARSET', 'UTF8');   //NB: no dash, not 'UTF-8'




The 'problem' is that a CHAR(1) in UTF8 is sent as 4 bytes. If the
character in question is a one-byte UTF8 character, then the remaining
three bytes are 0x20 (a space). A driver should truncate the string to
the declared number of characters, but doesn't do that here.

A potential workaround might be to use VARCHAR(1) instead.

Mark
--
Mark Rotteveel





--
Tim Ward

AW: [firebird-support] group by date

2014-12-15 Thread 'checkmail' check_m...@satron.de [firebird-support] 
Hello Mark,

 

I don’t know why, but I haven’t get this e-mail. Thank you very much!

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 15. Dezember 2014 09:00
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] group by date

 

  

On Sun, 14 Dec 2014 15:46:52 +0100, "Olaf Kluge   
check_m...@satron.de
[firebird-support]" <  
firebird-support@yahoogroups.com> wrote:
> Any ideas? 
> 
> Thank you 
> 
> 
> Am 12. Dezember 2014 15:15:40 MEZ, schrieb "'checkmail'
>   check_m...@satron.de [firebird-support]"
> <  firebird-support@yahoogroups.com>:
>>Hello @ll,
>>
>>I would like to group my records by day or month. The date is saved as
>>timestamp. If I do the following, 
>>
>>SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages
>>FROM te_messzeiten m
>>GROUP BY EXTRACT(day FROM m.messzeit)
>>
>>I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. -
>>daily
>>bzw. 1 2014, 2 2014 - monthly
>>
>>How can I realize this?
>>
>>Thank you
>>
>>Best regards
>>
>>Olaf

I already answered this, 5 minutes after your initial question. See
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125829

Mark

RE: [firebird-support] Re: Firebird 1.5 - CURRENT_TIMESTAMP difference

2014-12-15 Thread 'Bogdan' bog...@mordicom.si [firebird-support] 

Sent: Monday, December 15, 2014 8:50 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Firebird 1.5 - CURRENT_TIMESTAMP difference

 

  

On 14 Dec 2014 14:14:21 -0800, "emad...@yahoo.com.au [firebird-support] 
 "
 wrote:
> Unfortunately we're using 1.5 so "NOW" isn't available to us. 
> 
> We're running the statements in real-time so a 30 minute difference is
> definitely wrong.. It is also exactly 30 minutes which leads me to
believe
> there's some kind of funny offset due to timezone or something to that
> effect that we aren't seeing for whatever reason. I thought if I
figured
> out where the "CURRENT_TIMESTAMP" statement is pulling its time from I
> would know where to look for the issue.

Do you also get this 30 minute difference if you execute SELECT
CURRENT_TIMESTAMP FROM RDB$DATABASE from ISQL and/or Flamerobin, or only
inside your application?

What is your own timezone, and what is the server timezone?

Mark

The last time i've seen such behaviour, we found there was another job 
scheduled every 30 minutes
that took all the resources

Regards

Bogdan

 

 



---
avast! Antivirusna zaščita. E-pošta je čista. Ne vsebuje virusov in zlonamerne 
programske kode. Prenesite si avast! Antivirusno zaščito iz www.avast.si.
http://www.avast.com

Re: [firebird-support] Re: How do you write dynamic sql in Firebird

2014-12-15 Thread 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support] 
Hello Dmitry,

>12.12.2014 23:08, 'Martijn Tonies wrote:
>>
>> But using ROWS instead of FIRST makes using a parameter possible
>
>FIRST (:CNT) does the trick as well.

Oh, good one, let's see if I can remember. ;)


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Re: [firebird-support] group by date

2014-12-15 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On Sun, 14 Dec 2014 15:46:52 +0100, "Olaf Kluge check_m...@satron.de
[firebird-support]"  wrote:
> Any ideas? 
> 
> Thank you 
> 
> 
> Am 12. Dezember 2014 15:15:40 MEZ, schrieb "'checkmail'
> check_m...@satron.de [firebird-support]"
> :
>>Hello @ll,
>>
>>I would like to group my records by day or month. The date is saved as
>>timestamp. If I do the following, 
>>
>>SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages
>>FROM te_messzeiten m
>>GROUP BY EXTRACT(day FROM m.messzeit)
>>
>>I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. -
>>daily
>>bzw. 1 2014, 2 2014 - monthly
>>
>>How can I realize this?
>>
>>Thank you
>>
>>Best regards
>>
>>Olaf

I already answered this, 5 minutes after your initial question. See
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125829

Mark