esbreidenbach wrote:
I have a routine I use in my application which runs a separate query on every table and view in the database. The purpose of these queries is really just to obtain the field definitions of the underlying table, not to return any actual data.
The routine uses the following generic SQL:
Select * from TableName/ViewName where 1 = 0;
My problem is that these queries can run very slowly on views which are really meant to have additional where clause statements, and even on tables with large numbers of records. The intention of the where clause "where 1 = 0" was to eliminate any query activity, but it is not working out this way.
Is there a query which I can execute that will run very fast (and return no data), or any other ideas on how to do what I want?
Helen Borrie answers:
You only need to prepare the query to get hold of the information you want.
Robert Martin answers:
If you are after field definitions the best way would to just query the RDB$ tables. RDB$Fields probably has a lot of the data you are looking for.
and John Khoo adds:
Try this.
sSQL:='SELECT T.RDB$FIELD_NAME,'+
'CASE f.RDB$FIELD_TYPE '+
'WHEN 261 THEN "BLOB" '+
'WHEN 14 THEN "CHAR ("||F.RDB$FIELD_LENGTH||")"'+
'WHEN 40 THEN "CSTRING" '+
'WHEN 11 THEN "D_FLOAT" '+
'WHEN 27 THEN "NUMERIC (18,"||F.RDB$FIELD_SCALE||")"'+
'WHEN 10 THEN "FLOAT" '+
'WHEN 16 THEN "INT64" '+
'WHEN 8 THEN "INTEGER" '+
'WHEN 9 THEN "QUAD" '+
'WHEN 7 THEN "SMALLINT" '+
'WHEN 12 THEN "DATE" '+
'WHEN 13 THEN "TIME" '+
'WHEN 35 THEN "TIMESTAMP" '+
'WHEN 37 THEN "VARCHAR" '+
'ELSE "UNKNOWN" '+
'END,'+
//'F.RDB$FIELD_LENGTH,'+
//'F.RDB$FIELD_SCALE,'+
'T.RDB$FIELD_SOURCE,'+
'T.RDB$NULL_FLAG,'+
'T.RDB$DEFAULT_SOURCE '+
'from RDB$RELATION_FIELDS T '+
'LEFT JOIN RDB$FIELDS F ON T.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME '+
'WHERE RDB$RELATION_NAME="'+sTblName+'" '+
'ORDER BY RDB$FIELD_POSITION'
sTblName is the table name.
Svein Erling Tysvaer answers:
Just to explain what you effectively did. The optimizer saw that it had no index that could be used to help getting what you specified in the WHERE clause, and decided it had to look through every single row in your table/view to fetch rows that satisfied your criteria. WHERE 1=0 can be useful in optimization when you want to eliminate the use of certain indexes, but used the way you did, I'd say it clearly shows how a human mind differs from Firebirds logic ('just return an empty set, 1=0 is never true' vs. 'is 1=0 true for this row? No, let's check the next row to see whether 1=0 is true there...').
Thanks for a nice giggle.