[firebird-support] RE: Possible to use FIRST 1 inside a group by?
Thank you for the help everyone! I think Sean's looks the most efficient, as that will only run the sub-query once. Now that I know how to use this trick, I think I could use this in quite a few other places too ;-) >Try: >SELECT > T.ID > T.GroupID > T2.FKCode > T2.Value >FROM ( >select MIN (ID) as ID, GroupID >from MyTable >GROUP BY GroupID > ) T > JOIN MyTable T2 ON T2.ID = T.ID > >Sean
RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Using 2.5.4 FB Client to access a 1.5.3 DB database (Follow-Up)
At 08:36 a.m. 30/07/2015, 'stwizard' stwiz...@att.net [firebird-support] wrote: >The new Windows 2012 R2 64 bit server will have the 64 bit Firebird server >v2.5.4 install on it (But alternately may have Firebird 32 bit server >installed instead based on the answers given below). > >All client work stations are Windows 7 64 bit and currently have the Firebird >1.5.3 32 bit fbclient.dll installed on them and they all currently use the >Windows 2003 32 bit server with Firebird 1.5.3 Server running on it. The >application written in Delphi XE5 that connects to the Firebird server is >currently a 32 bit application. Will be a ways down the road before releasing >a 64 bit version of the application. > >So questions: > >1) Since the application is a 32 bit application, should I install the 32 >bit version of the Firebird server or is it OK to install the 64 bit Firebird >server on the Windows 2012 R2 64 bit server? The 32-bit clients can access the 64-bit server. >2) If I should be installing the 32 bit version of Firebird Server on the >Windows 2012 R2 64 bit server, then I would assume I would uninstall the >current 1.5.3 32 bit Firebird Client and install the new 2.5.4 32 bit Firebird >client, right? Just trying to sort out what you're asking here. You use a 32-bit client with a 32-bit application. So you'll want the 64-bit client on the *server* to use the shipped tools locally. You don't have to "uninstall" the client library, just delete it (or rename it, if there's a reason you want to keep it). The v.2.5.4 client will communicate with any database <= ODS 11.2. If you want to run the shipped tools on the 64-bit client boxes, you'll need the 64-bit client there, too. So put the tools and the 64-bit client and firebird.msg together in a folder that your PATH variable doesn't know about. > If I was to install a 64 bit Firebird client on the Windows 7 workstations, > would it work with either a 32 bit or 64 bit firebird server on the Windows > 2012 R2 server? Yes. > And would the 64 bit Firebird client on the Windows 7 workstations, work with > the 32 bit 1v.5.3 firebird server on the old Windows 2003 32 server? Yes. But the 64-bit client won't work with your 32-bit applications. The client belongs to the client layer, not the server layer. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of "The Firebird Book" and "The Firebird Book Second Edition" http://www.firebird-books.net __
RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Using 2.5.4 FB Client to access a 1.5.3 DB database (Follow-Up)
> I guess all can tell I’m a little confused on if 32 bit and 64 bit Firebird > server/client can be mixed and matched for v2.5.4 and if whatever is placed > onto the work stations would be able to connect to v1.5.3 Firebird server > running on the old windows 2003 32 bit server. The Firebird server and client 'bitness' is true mix-and-match -- you can access 32 or 64 bit server from 32 or 64 bit clients without any limitation/restriction. Separately, you can install 32 bit client or server on 64 bit OS, but can't install 64bit client or server on 32 bit OS. Sean
RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Using 2.5.4 FB Client to access a 1.5.3 DB database (Follow-Up)
Thanks Neil for your reply, Thanks to all who take the time to reply to the following. I have a follow up question that goes along with this subject and of course anyone can chime in. The new Windows 2012 R2 64 bit server will have the 64 bit Firebird server v2.5.4 install on it (But alternately may have Firebird 32 bit server installed instead based on the answers given below). All client work stations are Windows 7 64 bit and currently have the Firebird 1.5.3 32 bit fbclient.dll installed on them and they all currently use the Windows 2003 32 bit server with Firebird 1.5.3 Server running on it. The application written in Delphi XE5 that connects to the Firebird server is currently a 32 bit application. Will be a ways down the road before releasing a 64 bit version of the application. So questions: 1) Since the application is a 32 bit application, should I install the 32 bit version of the Firebird server or is it OK to install the 64 bit Firebird server on the Windows 2012 R2 64 bit server? 2) If I should be installing the 32 bit version of Firebird Server on the Windows 2012 R2 64 bit server, then I would assume I would uninstall the current 1.5.3 32 bit Firebird Client and install the new 2.5.4 32 bit Firebird client, right? Bottom line is: If I was to install a 64 bit Firebird client on the Windows 7 workstations, would it work with either a 32 bit or 64 bit firebird server on the Windows 2012 R2 server? And would the 64 bit Firebird client on the Windows 7 workstations, work with the 32 bit 1v.5.3 firebird server on the old Windows 2003 32 server? I guess all can tell I’m a little confused on if 32 bit and 64 bit Firebird server/client can be mixed and matched for v2.5.4 and if whatever is placed onto the work stations would be able to connect to v1.5.3 Firebird server running on the old windows 2003 32 bit server. Any help is really appreciated and of course if I need to clarify please ask. Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, July 29, 2015 4:13 AM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Using 2.5.4 FB Client to access a 1.5.3 DB database Hi Mike, I don’t have direct experience of using FB Client v2.5 to access v1.5 databases but my understanding is that it should work okay for you. We certainly access v2.1 databases with the v2.5 client okay and accessed v1.5 databases with the v2.1 client without issue. >From what I have read and experienced the clients are backwards compatible but >you have to be careful accessing a newer database with an older client which >doesn’t usually end well. I believe you would need to uninstall the v1.5 client first, then reinstall the v2.5 one. Cheers, Neil Pickles - n...@csy.co.uk Greetings All, We are installing a new Windows 2012 R2 64 bit server at the office and eventually will restore a Firebird 1.5.3 Database to 2.5.4 On one windows 7 64 bit client I will install the 2.5.4 firebird client. A few questions: 1) Do I need to uninstall the 1.5.3 client first? 2) After the 2.5.4 client is install and I’m able to access the new 2012 server successfully, can this same firebird client be used to connect to the old firebird 1.5.3 database on the 2003 windows server? Thanks, Mike [Non-text portions of this message have been removed]
RE: [firebird-support] Simple query takes too much time
> El 29/07/2015 02:07 p.m., 'Leyne, Sean' s...@broadviewsoftware.com > [firebird-support] escribió: > > Jorge: > > > >> Using MSAccess, a single query to this table takes an average of 14 ms. > > You think *14ms* is slow?!?!?!? > When a similar query over a table with 10 records takes 1 ms, yes, I think > it´s slow Note that the timing es for each record (14 ms x 7100 records: 99400 I note that the PK for this table is a BIGINT, is that true for your other tables? Do the rows in this table undergo a lot of activity which would create record versions? If so, what do the database header page statistics look like? An uncommitted transaction, combined with a lot of updates could result in slow query as the system would need to walk the version list to get the latest version. Sean
Re: [firebird-support] Simple query takes too much time
El 29/07/2015 02:07 p.m., 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] escribió:
> Jorge:
>
>> Using MSAccess, a single query to this table takes an average of 14 ms.
> You think *14ms* is slow?!?!?!?
When a similar query over a table with 10 records takes 1 ms, yes, I
think it´s slow
Note that the timing es for each record (14 ms x 7100 records: 99400 ms,
i.e., 99 seconds)
>> SELECT "GTIN" ,"ACTIVO" FROM "TRAZABILIDAD_MEDICAMENTOS" WHERE
>> ("GTIN"
>> = ?)
>> ^^
>> ^
>> PLAN (TRAZABILIDAD_MEDICAMENTOS INDEX
>> (PK_TRAZABILIDAD_MEDICAMENTOS))
>>
>> param0 = bigint, "7795312000796"
> Since you are using MS Access your query has not been prepared (ie.
> compiled), which is likely where most of the "query" time is going to.
>
> The engine must prepare statement for execution. Some tools/language allow
> for statements to be prepared once and then executed with the necessary
> parameters, which is a significantly faster access pattern when you are
> executing the same query over and over again.
Maybe, but even using Access other tables are fast!
I´m trying to figure why in similar scenario *this* table has slow access.
>
> Sean
>
>
>
> Posted by: "Leyne, Sean"
>
>
--
Jorge Andrés Brugger
Departamento de Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446- int. 103
Correo electrónico: jbrug...@dasu.com.ar
Website: www.dasu.com.ar
"El verdadero modo de no saber nada es aprenderlo todo a la vez." (George Sand)
--
Antes de imprimir este mensaje, piense si es verdaderamente necesario hacerlo.
Re: [firebird-support] Possible to use FIRST 1 inside a group by?
>Hi, > >I’ve got quite a tricky SQL query (well, at least for me it is, I’m hoping someone may have done this before) > >Here’s a simplified example of what I’m trying to do: > >I have a table with > >IDGroupID FKCode Value >1 1 ABC+5 >2 1 XYZ -5 >3 2 ABC+8 >4 2 XYZ -8 >5 3 ABC-2 >6 3 XYZ +2 > >I’d like to select the first record of each group, eg. > >IDGroupID FKCode Value >1 1 ABC+5 >3 2 ABC+8 >5 3 ABC-2 > >Any ideas? Very simple: select ID, GroupID, FKCode, Value from MyTable T where not exists(select * from MyTable T2 where t.GroupID = t2.GroupID and t.ID > t2.ID) If ID is the primary key and you have an index for GroupID, I would expect this to be quite fast. Sean's solution is an alternative that maybe have similar performance, the other suggestions ought to be avoided except for tiny tables. HTH, Set ++ 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] RE: Possible to use FIRST 1 inside a group by?
> This is solution: > select * from tabella A where Group_ID in (Select min(Group_ID) in tabella B > where B.ID=A.ID); Are you sure that works? It doesn't see to answer Maya original question. It also would read n^2 rows from the table. Sean > > ---Messaggio originale--- > > Da: 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] > Data: 29/07/2015 18.59.03 > A: firebird-support@yahoogroups.com > Oggetto: [firebird-support] RE: Possible to use FIRST 1 inside a group by? > > > Maya, > > > Might not be the most efficient method, but this works: > > Unfortunately, it is least efficient method -- you will read each table row > n^2 times > > Try: > > SELECT > T.ID > T.GroupID > T2.FKCode > T2.Value > FROM ( > select MIN (ID) as ID, GroupID > from MyTable > GROUP BY GroupID > ) T > JOIN MyTable T2 ON T2.ID = T.ID > > Sean > > > > > > [Non-text portions of this message have been removed] > > > > > Posted by: "Raffaele Confalone" > > > ++ > > > 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 > > >
Rif: [firebird-support] RE: Possible to use FIRST 1 inside a group by?
This is solution: select * from tabella A where Group_ID in (Select min(Group_ID) in tabella B where B.ID=A.ID); ---Messaggio originale--- Da: 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] Data: 29/07/2015 18.59.03 A: firebird-support@yahoogroups.com Oggetto: [firebird-support] RE: Possible to use FIRST 1 inside a group by? Maya, > Might not be the most efficient method, but this works: Unfortunately, it is least efficient method -- you will read each table row n^2 times Try: SELECT T.ID T.GroupID T2.FKCode T2.Value FROM ( select MIN (ID) as ID, GroupID from MyTable GROUP BY GroupID ) T JOIN MyTable T2 ON T2.ID = T.ID Sean [Non-text portions of this message have been removed]
RE: [firebird-support] Simple query takes too much time
Jorge:
> Using MSAccess, a single query to this table takes an average of 14 ms.
You think *14ms* is slow?!?!?!?
> SELECT "GTIN" ,"ACTIVO" FROM "TRAZABILIDAD_MEDICAMENTOS" WHERE
> ("GTIN"
> = ?)
> ^^
> ^
> PLAN (TRAZABILIDAD_MEDICAMENTOS INDEX
> (PK_TRAZABILIDAD_MEDICAMENTOS))
>
> param0 = bigint, "7795312000796"
Since you are using MS Access your query has not been prepared (ie. compiled),
which is likely where most of the "query" time is going to.
The engine must prepare statement for execution. Some tools/language allow for
statements to be prepared once and then executed with the necessary parameters,
which is a significantly faster access pattern when you are executing the same
query over and over again.
Sean
[firebird-support] RE: Possible to use FIRST 1 inside a group by?
Maya, > Might not be the most efficient method, but this works: Unfortunately, it is least efficient method -- you will read each table row n^2 times Try: SELECT T.ID T.GroupID T2.FKCode T2.Value FROM ( select MIN (ID) as ID, GroupID from MyTable GROUP BY GroupID ) T JOIN MyTable T2 ON T2.ID = T.ID Sean
[firebird-support] Simple query takes too much time
I´ve a medium-sized DB. Some tables have millions of records, but one in
particular has just 7100
Table DDL follows:
CREATE TABLE TRAZABILIDAD_MEDICAMENTOS (
GTIN D_GTIN NOT NULL /* D_GTIN = DECIMAL(14,0) */,
FECHA_ALTA D_FECHA /* D_FECHA = DATE */,
ACTIVO D_LOGICO DEFAULT 0 /* D_LOGICO = SMALLINT NOT NULL CHECK
(value in(1,0)) */
);
ALTER TABLE TRAZABILIDAD_MEDICAMENTOS ADD CONSTRAINT
PK_TRAZABILIDAD_MEDICAMENTOS PRIMARY KEY (GTIN);
Using MSAccess, a single query to this table takes an average of 14 ms.
Next is the trace log:
Statement 56728:
---
SELECT "GTIN" ,"ACTIVO" FROM "TRAZABILIDAD_MEDICAMENTOS" WHERE ("GTIN"
= ?)
^^^
PLAN (TRAZABILIDAD_MEDICAMENTOS INDEX (PK_TRAZABILIDAD_MEDICAMENTOS))
param0 = bigint, "7795312000796"
2015-07-29T13:34:58.1530 (1049:0x7f974f340168) COMMIT_TRANSACTION
farmaclick (ATT_107403, SYSDBA:NONE, ISO88591, TCPv4:192.168.1.54)
C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE:5500
(TRA_71743787, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)
14 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)
2015-07-29T13:34:58.1700 (1049:0x7f974f340168) START_TRANSACTION
farmaclick (ATT_107403, SYSDBA:NONE, ISO88591, TCPv4:192.168.1.54)
C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE:5500
(TRA_71743788, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)
2015-07-29T13:34:58.1710 (1049:0x7f974f340168) EXECUTE_STATEMENT_START
farmaclick (ATT_107403, SYSDBA:NONE, ISO88591, TCPv4:192.168.1.54)
C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE:5500
(TRA_71743788, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)
Most queries to DB takes 0 or 1 ms. even to much bigger tables.
What could be wrong with this specific table?
Using FB 2.5.4 64 bits on Ubuntu Server
Thanks!
--
Jorge Andrés Brugger
Departamento de Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446- int. 103
Correo electrónico: jbrug...@dasu.com.ar
Website: www.dasu.com.ar
"Aquel que tiene una opinión de sí mismo, pero depende de la opinión y los
gustos de los demás, es un esclavo" (Friedrich Gottlieb Klopstock)
--
Antes de imprimir este mensaje, piense si es verdaderamente necesario hacerlo.
[firebird-support] RE: Possible to use FIRST 1 inside a group by?
>I've got quite a tricky SQL query (well, at least for me it is, I'm hoping >someone may have done this before) >Here's a simplified example of what I'm trying to do: >I have a table with IDGroupID FKCode Value 1 1 ABC+5 2 1 XYZ -5 3 2 ABC+8 4 2 XYZ -8 5 3 ABC-2 6 3 XYZ +2 >I'd like to select the first record of each group, eg. IDGroupID FKCode Value 1 1 ABC+5 3 2 ABC+8 5 3 ABC-2 I think I just solved my own problem. Might not be the most efficient method, but this works: select ID, GroupID, FKCode, Value from MyTable T where T.ID = (select first 1 T2.ID from MyTable T2 where T.GroupID = T2.GroupID order by T2.ID)
Rif: [firebird-support] Possible to use FIRST 1 inside a group by?
This is solution: select * from tabella A where Group_ID in (Select min(Group_ID) in tabella B where B.ID=A.ID); ---Messaggio originale--- Da: Maya Opperman m...@omniaccounts.co.za [firebird-support] Data: 29/07/2015 13.49.41 A: firebird-support@yahoogroups.com Oggetto: [firebird-support] Possible to use FIRST 1 inside a group by? Hi, Ive got quite a tricky SQL query (well, at least for me it is, Im hoping someone may have done this before) Heres a simplified example of what Im trying to do: I have a table with IDGroupID FKCode Value 1 1 ABC+5 2 1 XYZ -5 3 2 ABC+8 4 2 XYZ -8 5 3 ABC-2 6 3 XYZ +2 Id like to select the first record of each group, eg. IDGroupID FKCode Value 1 1 ABC+5 3 2 ABC+8 5 3 ABC-2 Any ideas? Thanks in advance Maya [Non-text portions of this message have been removed]
[firebird-support] Possible to use FIRST 1 inside a group by?
Hi, I've got quite a tricky SQL query (well, at least for me it is, I'm hoping someone may have done this before) Here's a simplified example of what I'm trying to do: I have a table with IDGroupID FKCode Value 1 1 ABC+5 2 1 XYZ -5 3 2 ABC+8 4 2 XYZ -8 5 3 ABC-2 6 3 XYZ +2 I'd like to select the first record of each group, eg. IDGroupID FKCode Value 1 1 ABC+5 3 2 ABC+8 5 3 ABC-2 Any ideas? Thanks in advance Maya
R: [firebird-support] CAST CHAR to INTEGER
I'm sorry, I understood... it work! Messaggio originale Da: firebird-support@yahoogroups.com Data: 29/07/2015 11.20 A: Ogg: [firebird-support] CAST CHAR to INTEGER Hi all, how can I make this cast in Firebrid 2.5.4 (if is it possible?) DECLARE VARIABLE QTA CHAR (5);DECLARE VARIABLE XYZ INTEGER; XYZ = cast(rtrim(ltrim(QTA))) as integer; Let me know, best regardsAlpe -->
[firebird-support] CAST CHAR to INTEGER
Hi all, how can I make this cast in Firebrid 2.5.4 (if is it possible?) DECLARE VARIABLE QTA CHAR (5);DECLARE VARIABLE XYZ INTEGER; XYZ = cast(rtrim(ltrim(QTA))) as integer; Let me know, best regardsAlpe
RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Using 2.5.4 FB Client to access a 1.5.3 DB database
Hi Mike, I don’t have direct experience of using FB Client v2.5 to access v1.5 databases but my understanding is that it should work okay for you. We certainly access v2.1 databases with the v2.5 client okay and accessed v1.5 databases with the v2.1 client without issue. From what I have read and experienced the clients are backwards compatible but you have to be careful accessing a newer database with an older client which doesn’t usually end well. I believe you would need to uninstall the v1.5 client first, then reinstall the v2.5 one. Cheers, Neil Pickles - n...@csy.co.uk Greetings All, We are installing a new Windows 2012 R2 64 bit server at the office and eventually will restore a Firebird 1.5.3 Database to 2.5.4 On one windows 7 64 bit client I will install the 2.5.4 firebird client. A few questions: 1) Do I need to uninstall the 1.5.3 client first? 2) After the 2.5.4 client is install and I’m able to access the new 2012 server successfully, can this same firebird client be used to connect to the old firebird 1.5.3 database on the 2003 windows server? Thanks, Mike
