DDL gets my goat

First things first. If you are not familiar with the term “gets my goat” then you can find a definition here . But put simply, it means things that you find frustrating or bothersome.

What is getting my goat today is the time it takes to get the DDL for a database object using the DBMS_METADATA package. It always seem slow to me. Now I must admit, this is very much me exhibiting “never happy” syndrome, because before DBMS_METADATA came along, generating DDL from the database was an absolute pain in the proverbial. Your choices were either:

• Rely on a 3rd party tool to do it, and more often than not, it never kept up with the latest features, or
• Rely on export/import to do it, which meant trawling through the generated SQL file with grep, sed, awk and the like to extract the DDL

DBMS_METADATA solved all of that, plus added transformations to manipulate the DDL, plus options for bulk export and selectivity over the output data type etc.  It’s a super cool tool so feel free to drop a comment saying I should get off my damn high horse and stop complaining about a tool that in reality has made my life a lot easier! Such comments are entirely justified.

But the reason I wanted to blog about DBMS_METADATA is that I occasionally see people complain about the speed at which SQL Developer will display the DDL for an object when you click on the “SQL” tab. There’s an example of this below:

The thing to remember here is that this has nothing to do with SQL Developer. There’s no flaw here in the product – it is just waiting for the underlying call to DBMS_METADATA to finish.

As many people will already know, once you have done one call to DBMS_METADATA in your session, typically all subsequent calls are a lot faster because we’ve now loaded the various dependencies required to execute it. We can take advantage of this fact with a nice preference in SQL Developer to mimic the “login.sql” functionality you see in SQL*Plus and SQLcl.  Go to your preferences screen, and nominate a script to run as part of the connection task.

All I have in my login script for SQL Developer is:

select dbms_metadata.get_ddl(‘TABLE’,’DUAL’,’SYS’) from dual;

but probably any object in your database would suffice. We just want to “warm up” DBMS_METADATA when we make our connection.  Here is the same DDL request in a fresh SQL Development runtime with that login script in place.

Now I know you are probably thinking: “Hasn’t that just shifted the slowness elsewhere?”, and that is a fair comment to make. But for me, this is more about perception. A brand new database connection always takes a little bit of time anyway, as the list of objects etc is loaded up to populate the navigator, so an extra second I don’t even notice.  I’m happy to have that such that my perception of the user experience after I’ve connection seems that much more snappy.

TL;DR: Get snappier DDL generation via a login script.

Enjoy!