SQL Assist - Fetching Columns

After upgrading to the last service pack, SQL Assist has almost stopped working. Any action or an alteration the query results in message "Fetching Columns..." which can be anything from 30 seconds minimum to infinite. Each time then I need to crash the SQLAssist Service and restart, only to have the same issue again. SQLAssist is an integrated tool for my task and appreciate urgent assistance from Softtree on the subject.

Hi there.

Please describe your environment.
What service pack are you referring to? Do you mean Windows or some other product?
What is your database type and version?
Do you use SQL Assistant native SQL Editor or you use some other development environment?
What kind of database drive is being used?
Have you checked if there are database server side locks on the system catalog tables?

Environment Windows
Service Pack - 11.3.279
Database Type - IBM Netezza (I am connecting to this for past 5 years)
SQL - I use SQL Editor Lite from SQLAssist or Notepad++ sometimes
Database is an appliance - queries fired from my client using Aginity work 100% ok
Catalog Table Side locks - How do I check this?

1. Please pick a table in your database that you are working with and execute the following query, replace text in red with actual database, schema, and table names

SELECT min(nvl(ro.objid, o.oid)) as object_id
FROM _t_object AS o
JOIN _t_object_classes AS c
on c.objclass = o.objclass
JOIN _t_object AS u
ON u.objid = o.objschemaoid
JOIN _t_object_classes as sc
ON sc.objclass = u.objclass
AND sc.classname = 'SCHEMA'
LEFT JOIN _t_synonym AS s
ON s.oid = o.objid
LEFT JOIN "enter database name here".DEFINITION_SCHEMA._v_object_data AS ro
ON ro."OWNER" = s.synschema
AND ro."OBJNAME" = s.synobject
AND ro."DBNAME" = s.syncatalog
LEFT JOIN _t_object_classes AS rc
ON rc.objclass = ro.objclass
WHERE c.classname = 'TABLE'
AND o.objclass > 0
AND u.objname = 'enter schema name here'
AND u.objname = 'enter table name here'
AND o.objdb in (SELECT 0::OID as objid
UNION ALL
SELECT d.oid
FROM _t_database AS d
WHERE d.datname = 'enter database name here')

2. The above query will return object id for that table. Should be a single number. If the value is not returned, please verify you entered correctly database, schema, and table names.
After that please execute the following query and let us know how long it takes to run. Replace text in red with the number returned by the previous query, also enter database name where indicated.

SELECT 'OID' as column_name, 'OID' as data_type, 'NP' as col_attrib, 0 as column_position
FROM "enter database name here".DEFINITION_SCHEMA._v_obj_relation AS r
WHERE r.objid = enter object id here
AND r.rowidname = 'OID'
UNION ALL
SELECT c.attname AS column_name,
CASE WHEN c.format_type LIKE 'CHARACTER VARYING(%' THEN 'VARCHAR' || substring(c.format_type, 18, 255)
WHEN c.format_type LIKE 'NATIONAL CHARACTER VARYING(%' THEN 'NVARCHAR' || substring(c.format_type, 27, 255)
WHEN c.format_type LIKE 'CHARACTER(%' THEN 'CHAR' || substring(c.format_type, 10, 255)
WHEN c.format_type LIKE 'NATIONAL CHARACTER(%' THEN 'NCHAR' || substring(c.format_type, 19, 255)
WHEN c.format_type LIKE 'BINARY VARYING(%' THEN 'VARBINARY' || substring(c.format_type, 14, 255)
WHEN c.format_type = 'CHARACTER VARYING' THEN 'VARCHAR(ANY)'
WHEN c.format_type = 'NATIONAL CHARACTER VARYING' THEN 'NVARCHAR(ANY)'
WHEN c.format_type = 'CHARACTER' THEN 'CHAR(ANY)'
WHEN c.format_type = 'NATIONAL CHARACTER' THEN 'NCHAR(ANY)'
WHEN c.format_type = 'BINARY VARYING' THEN 'VARBINARY(ANY)'
ELSE c.format_type
END AS data_type,
CASE WHEN c.coldefault LIKE 'nextval(%' THEN 'A' ELSE '' END
|| CASE WHEN c.attnotnull THEN 'N' ELSE 'Y' END
|| ( /* P,F,U */
select nvl(upper(max(k.contype)), '')
from "enter database name here".DEFINITION_SCHEMA._v_relation_keydata_xdb AS k
where k.objid = c.objid
and k.attname = c.attname
)
|| ( /* I */
select nvl(max('I'), ' ')
from "enter database name here".DEFINITION_SCHEMA._v_table_dist_map_xdb as i
where i.objid = c.objid
and i.attname = c.attname
) AS col_attrib,
c.attnum AS column_position
FROM "enter database name here".DEFINITION_SCHEMA._v_relation_column_xdb AS c
WHERE c.objid = enter object id here
ORDER BY
column_position

Anything between 2.22 seconds to 2.39 seconds (note its 11:00 PM in UAE and the database in almost 1% usage)
Also, the Fetch Columns is taking 3 seconds

Thank you for the additional details. ~3 seconds a bit slow but still within reasonable times. Is the Fetching Columns... message generic or it's referring to a specific table whose column metadata it's trying to fetch? Do you see table names changing in the message while it's visible on the screen?

Is the issue reproducible with a very simple script containing only one simple SELECT statement?

Next steps are to see if it's an application side issue

Which version have you upgraded from? Have you restarted your computer yet after the upgrade. If not yet, can you please try that?

Please run self-diagnostics test and send the results to supportATsoftttreetech.com (replace AT with @). To run the test, open SQL Assistant's main Options dialog. On the About tab click the link at the very bottom "run self-diagnostics...

Topic - http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=35477#35477

Answering your questions:

- Now in office hours the time is 28.21 Seconds !!
- Yes, even a simple select statement is tested
- computer is restarted (also uninstalled and installed the s/w)
- I upgraded from SQLAssist 6.
- Self diagnostic test has some errors

*************************************************************
* COM Objects (x86) *
*************************************************************

Loading SqlAssist.CSmsAddin... FAILED
Error = 0x80070002: The system cannot find the file specified.

Loading SqlAssist.CSmsAddin10... FAILED
Error = 0x80070002: The system cannot find the file specified.

Thank you. Reinstallation will not help, your user settings are preserved in the system. And upgrading from a very old version 6 to the latest 11 is suboptimal. It may be still trying to use the old queries from your settings file copied from version 6.

Here is what i would like to try.
Open SQL Assistant main options dialog.
In the left bottom corner click the Import/Export button
Select the Load Default Options checkbox
Click the Import button.

Please test after that. Hopefully that wluld be sufficient. If not, we will look how to optimize the Columns query for your version of Netezza to make it run faster in your database.

I did the steps but no change. The query took 24.7 seconds to complete. Let me know what information you will need from my side.

There are couple of things that can be tried next.

1. You can simplify the queries used by SQL Assistant to query system catalog tables. This is a tradeoff for the features, it should run faster, but return less useful data, and kind of degrade the functionality available. SQL Assistant won't be able to show indexes in the intellisense popups and the data grid data editing feature will be disabled as it won't know which columns are indexed.

2. You can try reindexing your system catalog tables. This is advisable if you haven't done it in a while. As documented system tables get fragmented and the need to be groomed periodically.
See https://www.ibm.com/support/pages/puredata-system-analytics-netezza-maintenance-tasks-optimal-performance for how to reindex the tables and make queries run faster

For the number 1 above, here is a simplified version of the Columns + Keys query.
Open SQL Assistant Options dialog.
Click DB Options tab
On the left hand side expand DB Queries section
Locate there Netezza Columns + Keys query, choose version 7.1. IMPORTANT, it's important that you modify the correct query otherwise the change won't have any effect.
On the right side replace the current code with the code below.

Code:

SELECT 'OID' as column_name, 'OID' as data_type, 'NP' as col_attrib, 0 as column_position
FROM "$DB_NAME$".DEFINITION_SCHEMA._v_obj_relation AS r
WHERE r.objid = :OBJECT_ID
AND r.rowidname = 'OID'

UNION ALL

SELECT
c.attname AS column_name,
CASE WHEN c.format_type LIKE 'CHARACTER VARYING(%' THEN 'VARCHAR' || substring(c.format_type, 18, 255)
WHEN c.format_type LIKE 'NATIONAL CHARACTER VARYING(%' THEN 'NVARCHAR' || substring(c.format_type, 27, 255)
WHEN c.format_type LIKE 'CHARACTER(%' THEN 'CHAR' || substring(c.format_type, 10, 255)
WHEN c.format_type LIKE 'NATIONAL CHARACTER(%' THEN 'NCHAR' || substring(c.format_type, 19, 255)
WHEN c.format_type LIKE 'BINARY VARYING(%' THEN 'VARBINARY' || substring(c.format_type, 14, 255)
WHEN c.format_type = 'CHARACTER VARYING' THEN 'VARCHAR(ANY)'
WHEN c.format_type = 'NATIONAL CHARACTER VARYING' THEN 'NVARCHAR(ANY)'
WHEN c.format_type = 'CHARACTER' THEN 'CHAR(ANY)'
WHEN c.format_type = 'NATIONAL CHARACTER' THEN 'NCHAR(ANY)'
WHEN c.format_type = 'BINARY VARYING' THEN 'VARBINARY(ANY)'
ELSE c.format_type
END AS data_type,
CASE WHEN c.coldefault LIKE 'nextval(%' THEN 'A' ELSE '' END || CASE WHEN c.attnotnull THEN 'N' ELSE 'Y' END AS col_attrib,
c.attnum AS column_position
FROM
"$DB_NAME$".DEFINITION_SCHEMA._v_relation_column_xdb AS c
WHERE
c.objid = :OBJECT_ID

ORDER BY
column_position

Click Ok, and see if this makes a difference.

Puff !!! It works .... Yes, there is a change and i tried before applying and after.
There is a substantial change in performance and works almost like before.
Thank you for your help.
In case if there is something I will revert else we can close the call for now.

Thank you for confirming.

I think that grooming system tables may make things run faster and help all applications including SQL Assistant.

Thanks, I checked with the tech team and they confirm doing a regular grooming over weekend tasks.