Feedback regarding Visual Builder

[rit]

I've just started to try out the Visual Builder environment (as found in the latest beta release) and have the following feed back (for which there maybe config fixes for but I'm not currently aware of).

1) It seems that VB often tries to find out the number of underlying records in a table/view by doing a

SELECT COUNT(*) FROM X WHERE ('1'='1')

This has a major impact on performance if the table is very large or the view depends on additional constrants to limit it's logical size.

2) There is no attempt to control the issue of spaces within column names, For a MSSQL based link this would mean placing each column name within [].

3) In the Database Browser screen it would be very helpful to have the ListGrid that shows the tables and views defined with a filter editor as this would improve the speed that a table/view can be found in a large system. This also solves the issue I see with MSSQL connections where all the system views are listed as well as the database view.

Apart from the above, my eval is going as well as you can expect for someone who has not used the product before :)

Thanks

Roger

[rit]

I'm guessing I will be make a good few comments during my eval if that's OK.

Again within Visual Builder, I have got to the point of using a ListGrid to display the contents of a table with an enabled Filter Editor.

On text columns the following type of SQL is created (When looking for an email address.

LIKE LOWER('%rit@somewhere.co.uk%')

By placing the % at the start you are allowing the search to find the text anywhere within a field but at the cost of killing the database server if there are a large number of records to be searched. Even if an index is defined on the column the result is that with your default setting the whole index must be scanned rather than a seek taking place. This becomes a major issue if GUID/UUIDs are used within a system as you handle them as text.

The best solution I think I can ask you to look at is the setting of an property that only allows searching from the start of the column which would mean that you would not include the first %

Roger

[Isomorphic]

1) this isn't VB, it's the SQLDataSource. The total number of rows is useful information for the user and allows jump-scrolling into the middle of the dataset. For very large results, or where you prefer to trade usability for scalability, use the "progressive loading" approach described under ResultSet. We plan to enable you to turn this on on a per-DataSource basis as well (no ETA).

2) What specifically are you seeing? Issues accessing such columns? Errors?

About "%LIKE%" expressions - this is controlled by textMatchStyle, which can be set on individual requests or as a DataSource-wide default.

[rit]

1) this isn't VB, it's the SQLDataSource. The total number of rows is useful information for the user and allows jump-scrolling into the middle of the dataset. For very large results, or where you prefer to trade usability for scalability, use the "progressive loading" approach described under ResultSet. We plan to enable you to turn this on on a per-DataSource basis as well (no ETA).

2) What specifically are you seeing? Issues accessing such columns? Errors?

About "%LIKE%" expressions - this is controlled by textMatchStyle, which can be set on individual requests or as a DataSource-wide default.

1) You missunderstand, the issue is more with VB as it has certain default ways of displaying data. The 2 problem areas that I have found so far are

a) When selecting a table/view from within the Database Browser the dialog window shows the data within the table on the right hand side.
b) When dropping a DataSource onto a Component within VB the component is refreshed with data from the DataSource.

Both these actions are painful if a table has a large number of records and impossible if a view only works correctly when additional constrants are placed on it. (I have tables with 100M+ records and views that involve cross joins). If I can get past issues a) and b) I have found the settings to manage "progressive loading", but I'm having problems getting to that point as VB does not default to "progressive loading" when displaying info.

(the count(*) issue for a table will not be a problem for a database engine that keeps a running total of the number of records - MSSQL does not do this and instead does a index scan)

2) Yes, SQL level errors. The first place the problem shows up is within the Database Browser, while the columns (with spaces) can be show in the tree view (by clicking +) the instance you select the table an SQL error is returned from the SQL statement used to populate the right hand grid. From the debug screen it looks like your SQL generation engine does not use [] to wrap column names - so it can not support spaces.

3) Thanks for the info regarding "textmatchStyle". For anyone using VB and reading this thread this is exposed as 'Auto fetch Text match' under 'Data Binding' on components such as ListGrid.

[Isomorphic]

1) Again, this is why progressive loading is *planned* as a built-in feature, which will make it possible for non-developers to simply enable it. For now, you need a developer involved if you have a table where it's too slow to get a row count.

2) We'll check on this, but it's likely you'll want to get rid of such tables if you can. There's already the ability to have a separation between field name and user-displayed field title in DataSources, so there's no need to have pretty database column names, and it's more convenient in most cases for them to be valid identifiers.

[Isomorphic]

On (2), we do support quoted column names in our generated queries - it's switched off by default for all supported databases, but you can enable it for SQL Server by adding the following line to your server.properties file:

sql.sqlserver.quoteColumnNames: true

However, even after you've done that, there is an obscure bug in our SQL Server paged query support which means that you might still see problems in Visual Builder with column names with embedded spaces. We have fixed the issue; the fix will be present in nightly builds as of tomorrow.

[rit]

On (2), we do support quoted column names in our generated queries - it's switched off by default for all supported databases, but you can enable it for SQL Server by adding the following line to your server.properties file:

sql.sqlserver.quoteColumnNames: true

However, even after you've done that, there is an obscure bug in our SQL Server paged query support which means that you might still see problems in Visual Builder with column names with embedded spaces. We have fixed the issue; the fix will be present in nightly builds as of tomorrow.

Sorry for not replying sooner, but I had to go off and deal with other projects.

Thanks for the above as that resolves one of my issues. Microsoft based tools and SDKs are coded to allow users to use spaces without thinking about the long term issues caused. I'm guessing that MS likes this as it causes most cross platform tools to fail by default.

As for the future improvement to allow progressive loading, you need to consider the fact that when accessing a view without any constraints being provided doing a count(*) could result in no result ever being returned as MSSQL will try and count all the possible rows created by the view. Also with MSSQL there is no system table count it can return so it has to do a complete table/index scan to return the correct value.

[Isomorphic]

Good to know, thanks. If it becomes critical to have the progressive loading feature in place, consider Feature Sponsorship as a way to get this done. It would be a small sponsorship as there's not much left to do.

[rit]

Good to know, thanks. If it becomes critical to have the progressive loading feature in place, consider Feature Sponsorship as a way to get this done. It would be a small sponsorship as there's not much left to do.

Sorry but the only likely Sponsorship I'm likely to offer is that of purchasing 1 copy of the pro edition for an in-house tool, but at the moment I still have not got my head around the way that forms operate (which is my task for tonight).

[rit]

Just started to try out the QuoteColumnNames option from within VB (latest build), but some incorrect SQL gets generated

The console log is below, but the SQL looks like this

SELECT * FROM (SELECT *, ROW_
NUMBER() OVER (ORDER BY x.""APR_APR"" DESC") AS rowID FROM (SELECT TOP 100 PERCENT APR."APR_MonthlyInterestRate", APR."APR_AnnualInterestRate", APR."APR_APR", APR."APR_BorrowingFe
eRate", APR."APR_Term" FROM dbo.APR WHERE ('1'='1') ORDER BY APR."APR_APR" DESC) x) y WHERE y.rowID BETWEEN 1 AND 76

The section OVER (ORDER BY x.""APR_APR"" DESC") is wrong as the column name is double quoted and an extra quote has been placed at the end of DESC. Other times I have seen OVER (ORDER BY x.""APR_Term"""), where again the quotes are not correct. All of these errors showed up when I tried to click on a column title to sort the contents.






=== 2010-11-23 00:14:28,156 [sor3] DEBUG AppBase - [builtinApplication.__APR_Table_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-11-23 00:14:28,156 [sor3] DEBUG AppBase - [builtinApplication.__APR_Table_fetch] No public zero-argument method named '___APR_Table_fetch' found, performing generic dataso
urce operation
=== 2010-11-23 00:14:28,156 [sor3] INFO SQLDataSource - [builtinApplication.__APR_Table_fetch] Performing fetch operation with
criteria: {} values: {}
=== 2010-11-23 00:14:28,156 [sor3] INFO SQLWhereClause - [builtinApplication.__APR_Table_fetch] empty condition
=== 2010-11-23 00:14:28,171 [sor3] INFO SQLDataSource - [builtinApplication.__APR_Table_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWh
ereClause ORDER BY $defaultOrderClause
=== 2010-11-23 00:14:28,218 [sor3] DEBUG SQLDataSource - [builtinApplication.__APR_Table_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWh
ereClause
=== 2010-11-23 00:14:28,218 [sor3] DEBUG SQLDataSource - [builtinApplication.__APR_Table_fetch] Eval'd row count query: SELECT COUNT(*) FROM dbo.APR WHERE ('1'='1')
=== 2010-11-23 00:14:28,218 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] DriverManager fetching connection for SQLServer via jdbc url jdbc:sql
server://dca-sql05-mi.dca.zopa.com:1433;DatabaseName=Rialto;User=SA;Password=password#23
=== 2010-11-23 00:14:28,218 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Passing JDBC URL only to getConnection
=== 2010-11-23 00:14:28,343 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Returning pooled Connection
=== 2010-11-23 00:14:28,343 [sor3] INFO SQLDriver - [builtinApplication.__APR_Table_fetch] Executing SQL query on 'SQLServer': SELECT COUNT(*) FROM dbo.APR WHERE ('1'='1')
=== 2010-11-23 00:14:28,359 [sor3] DEBUG SQLServerDriver - [builtinApplication.__APR_Table_fetch] SQL Server version is '9.00.4035'
=== 2010-11-23 00:14:28,359 [sor3] DEBUG SQLDataSource - [builtinApplication.__APR_Table_fetch] Using SQL Limit query
=== 2010-11-23 00:14:28,359 [sor3] DEBUG SQLDataSource - [builtinApplication.__APR_Table_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM (SELECT *, ROW_
NUMBER() OVER (ORDER BY x.""APR_APR"" DESC") AS rowID FROM (SELECT TOP 100 PERCENT APR."APR_MonthlyInterestRate", APR."APR_AnnualInterestRate", APR."APR_APR", APR."APR_BorrowingFe
eRate", APR."APR_Term" FROM dbo.APR WHERE ('1'='1') ORDER BY APR."APR_APR" DESC) x) y WHERE y.rowID BETWEEN 1 AND 76
=== 2010-11-23 00:14:28,359 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] DriverManager fetching connection for SQLServer via jdbc url jdbc:sql
server://dca-sql05-mi.dca.zopa.com:1433;DatabaseName=Rialto;User=SA;Password=password#23
=== 2010-11-23 00:14:28,359 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Passing JDBC URL only to getConnection
=== 2010-11-23 00:14:28,484 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Returning pooled Connection
=== 2010-11-23 00:14:28,515 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] DriverManager fetching connection for SQLServer via jdbc url jdbc:sql
server://dca-sql05-mi.dca.zopa.com:1433;DatabaseName=Rialto;User=SA;Password=password#23
=== 2010-11-23 00:14:28,515 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Passing JDBC URL only to getConnection
=== 2010-11-23 00:14:28,640 [sor3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.__APR_Table_fetch] Returning pooled Connection
=== 2010-11-23 00:14:28,671 [sor3] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2010-11-23 00:14:28,671 [sor3] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2010-11-23 00:14:28,671 [sor3] INFO Compression - /tools/visualBuilder/vbOperations.jsp: 4335 -> 1155 bytes

[Isomorphic]

Sorry, that's a regression introduced by the earlier bugfix. Fixed now.