The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,419 other followers

Archive for the ‘Database Development’ Category

How to read data from old delphi application Paradox databases without BDE?

Posted by jpluimers on 2018/08/30

Interesting question that raise some good tips: [WayBack] How to read data from old delphi application Paradox databases without BDE? I search for freeware or open source solution. – Jacek Laskowski – Google+

–jeroen

Posted in Database Development, Delphi, Development, Paradox, Software Development | Leave a Comment »

Quickly generate queries for all non-system tables in your database in Firebird or InterBase

Posted by jpluimers on 2018/07/26

Change at will:

  select 'select * from ' || r.rdb$relation_name as query
    from rdb$relations r
   where 1=1
     and r.rdb$system_flag <> 1 -- no system relations
     and r.rdb$view_source is null -- only tables
order by r.rdb$relation_name

For the EMPLOYEE demo database, this results in:

select * from COUNTRY
select * from CUSTOMER
select * from DEPARTMENT
select * from EMPLOYEE
select * from EMPLOYEE_PROJECT
select * from JOB
select * from PROJECT
select * from PROJ_DEPT_BUDGET
select * from SALARY_HISTORY
select * from SALES 

–jeroen

Posted in Database Development, Development, Firebird, InterBase, Software Development | Leave a Comment »

Getting the Firebird server version from a client using SQL

Posted by jpluimers on 2018/07/25

Since Firebird 2.1, you can get the first 3 numbers of the version with this SQL statement:

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;

You get it in this format:

RDB$GET_CONTEXT                                                                                                                                                                                                                                                
---------------
2.5.7                                                                                                                                                                                                                                                          

Which means you will not get the build number, which for most purposes is still quite OK.

–jeroen

via: [WayBackHow to detect the server version?

Posted in Database Development, Development, Firebird | Leave a Comment »

Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements

Posted by jpluimers on 2018/07/19

The testing was done with Firebird 2.5.x x86 on Windows 7 x64.

Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBackPkLab – Firebird: Tree data mangement with recursive CTE).

However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:

with 
  recursive 
  sequence(n) as (        
    -- When you select more than 1024 values, this error occurs:
    -- Error while fetching data:  Too many concurrent executions of the same request    
    select 0 -- start
    from rdb$database
    union all
    select sequence.n + 1
    from sequence
    where sequence.n < 1023 -- finish
  )
select sequence.n 
from sequence
--where sequence.n in (24, 38) 
order by sequence.n

It however is a pretty quick and CU bound solution: on my system ([WayBackAMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.

In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?

Read the rest of this entry »

Posted in Database Development, Development, Firebird, OracleDB, SQL, SQL Server | Leave a Comment »

Firebird: the way you compare dates highly influences fetch times

Posted by jpluimers on 2018/07/18

I was amazed the influence on how you compare dates would be a factor 1000 change in fetch times even though I wrote about date, time and timestamp conversions before in Source: Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes.

All queries were ran at 20170410.

Slow

select id, date '1899-12-30' + data.datadate as datestamp
from   data
where  1=1
-- slow: prepared 0.066 sec; fetched 10.549 sec 250 rows
    and date '1899-12-30' + data.datadate >= '2017-04-08' -- last 2 days
    and date '1899-12-30' + data.datadate <= '2017-04-10' -- today

Fast

select id, date '1899-12-30' + data.datadate as datestamp
from   data
where  1=1
-- fast: prepared 0.063 sec; fetched 0.009 sec 250 rows
    and data.DATADATE >= date 'Now' - date '1899-12-30' - 2 -- last 2 days 
    and data.DATADATE <= date 'Now' - date '1899-12-30' -- today 

Fast

select id, date '1899-12-30' + data.datadate as datestamp
from   data
where  1=1
-- fast: prepared 0.070 sec; fetched 0.011 sec 250 rows
    and data.DATADATE >= date '2017-04-10' - date '1899-12-30' - 2 -- last 2 days 
    and data.DATADATE <= date '2017-04-10' - date '1899-12-30' -- today

Fast

select id, date '1899-12-30' + data.datadate as datestamp
from   data
where  1=1
-- fast: prepared 0.073 sec; fetched 0.009 sec 250 rows
    and data.DATADATE >= date '2016-04-08' - date '1899-12-30' -- specific date range start 
    and data.DATADATE <= date '2016-04-10' - date '1899-12-30' -- specific date range end

The table looks like this:

CREATE TABLE "DATA" 
(
  ID                        INTEGER         NOT NULL,
  DATADATE         DOUBLE PRECISION,
  DATATIME         DOUBLE PRECISION
);
CREATE ASC INDEX DATA_DATADATE ON "DATA" (DATADATE);

–jeroen

Posted in Database Development, Development, Firebird | Leave a Comment »

 
%d bloggers like this: