The Wiert Corner – irregular stream of stuff

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

  • My work

  • 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,311 other followers

Archive for July 12th, 2017

https://www.youtube.com/watch?v=INzECItF6ew Delphi, where are you? 

Posted by jpluimers on 2017/07/12

Edit: in the mean time, the author took his video off-line. The G+ thread however is still interesting.

From a while ago:

[WayBack] https://www.youtube.com/watch?v=INzECItF6ew
Delphi, where are you? – Jacek Laskowski – Google+

 

YouTube video C++ Cool Debugging Tips and Tricks

–jeroen

Posted in C++, Delphi, Development, Software Development | 4 Comments »

Firebird: reset statistics for all indices using `set statistics index :index_name` – via: What is index selectivity?

Posted by jpluimers on 2017/07/12

A while ago, I needed to reset the index statistics for all indices in one our databases: a Firebird database.

I hadn’t done work like this in a while, so I made the cardinal mistake of Googling and using the first matching StackOverflow answer that seemed a perfect fit.

Boy was I wrong (:

In sql – My firebird query plan does not use correct index – Stack Overflow  I found the accepted answer (no less!) has this neat block to update index statistics for all indices:

    EXECUTE BLOCK
    AS
      DECLARE VARIABLE IDX VARCHAR(31);
    BEGIN
      FOR
        SELECT rdb$index_name FROM rdb$indices
        WHERE NOT rdb$index_name LIKE 'RDB$%'
        INTO :idx
      DO BEGIN
        EXECUTE STATEMENT 'update statistics ' || :idx
        WITH AUTONOMOUS TRANSACTION; 
      END
    END

The code consistently failed with this error:

    Script Error:
    
    Dynamic SQL Error
    SQL error code = -104
    Unexpected end of command - line 1, column 19

When I removed the lines inside the do beginend block, then the outer statements execute fine.

Incidentally, column 19 is right at the space at the end of after the 'update statistics '. Which meant that either the string concatenation was wrong, or the statement itself.

And yes, I should have Googled the Firebird documentation instead, as What is index selectivity? has the correct statement that recalculates statistics for all indexes:

set term !! ;

EXECUTE BLOCK AS
declare variable index_name VARCHAR(31);
BEGIN
for select RDB$INDEX_NAME from RDB$INDICES into :index_name do
execute statement 'SET statistics INDEX ' || :index_name || ';';
END!!

set term ; !!

–jeroen

Source: What is index selectivity?

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

 
%d bloggers like this: