Using Query Builder, I built a query with 16 tables- 8 fields in the ‘select’ statement, 9 fields in the ‘where’ statement, and 4 sub queries in the ‘where’ statement. I will acknowledge that I am not a DBA or a developer. I know SQL well enough- with 20 years experience (10 with Hyperion/Brio)
In any case, I organized the tables in a 4x4 grid to help better visualize the tables and the fields. I found it very very difficult to navigate the query builder work space with so many tables.
First, managing what I have in the ‘select’ and the ‘where’ clause was so confusing and cumbersome because I had to constantly scroll left and right/up and down. In addition, in couple of fields that were not in the ‘select’ statement, I had complex sub-queries in the ‘where’ clause. On two instances, I accidentally clicked on the field name in the table window in the QueryBuilder work space and the field was removed work space at the bottom of the screen (select, where, having clauses).
Second, if I was joining a field from table A1 to D4, the window would not auto scroll down while I was dragging the field from table A1 to D4 (on a grid). I zoomed out as much as I could before it became difficult to read the field names in the table windows.
Third, the GUI feedback from the application was slow as a turtle. And I encountered 2 out-of-memory exception, freezes, and GUI residues (window outline, drag marks, etc.). TDP is running on an i7/3.4GHz, Win7 with 20gb ram connected to a 23 inch monitor.
QueryBuilder feature was one of decision making features for purchasing TDP. In building this query, the whole experience with QueryBuilder was just awful and very very frustrating.
Is there anything in the works to improve the QueryBuilder experience?
Here are some suggestions for working in the Query Builder with a lot of tables and columns.
There is a Full Screen button on the View menu. This gives you a bit more real estate by collapsing the tool bars. You will then need to use the right click menu for actions.
When dealing with tables that have a lot of columns it is common that not all columns are used. If you place your tables in the ER Diagram there is a hide columns editor. Here you can only show the columns you work with as well as change order of columns and this will greatly help on diagram space. Just save the ER Diagram file or send these tables to the Query Builder. We have open enhancement to add the hide columns editor to Query Builder but it is not currently scheduled. QAT-382
There is also a zoom feature where you can size the tables smaller to increase real-estate. This could be used when you mentioned dragging one column to a table that is not visible. I did enter an enhancement for adding scroll for this. QAT-1474.
Re out-of memory and slow response with sub tables. This should not be happening. i would like to track this down. If you run into this again can you capture the error stack? Or can you send me your excecutiontrace.log file located in your appdata dir? That would be a start. To fully get into this I really would need the DDL for each table and your Query Builder file. I would happy to allocate some developer time to this if i was able to recreate this issue. We have no outstanding issues like this on the Query Builder.
