Thread: Query tool results in grid

I've attached a preliminary patch to display the results of the Query Tool
using a grid control instead of a list. I've got one major problem with it
I'm a little stumped with though. The grid control doesn't seem to
consistantly maintain the focus, which makes it difficult to copy data
from it at the moment.

I figured I'd send this patch off to the list to see if anyone had any
suggestions on solving it. I'm out of ideas at the moment.

Longer term, it probably makes sense to merge a lot of the code from the
Data Edit grid with this code. In the mean time, I think it's worth
getting this patch in first once the focus issue is resolved.

Ed

edigeronimo@picoip.com wrote:
> I've attached a preliminary patch to display the results of the Query Tool
> using a grid control instead of a list.

Sorry to frustrate you, but using the grid is provenly even more
ineffective (on _all_ platforms) than the current one. wxGrid is known
to degrade drastically for bigger sizes. That's why View Data uses its
own implementation of wxTable. In general, wxGrid is an ugly piece of
code difficult to handle, wx-dev have plans to rewrite it from ground
for good reasons.

We need a rewrite of ctlSqlResult, but it must base on a virtual
wxListBox or wxListView, i.e. maintaining the data ourselves. On the
long run, extending ctlSqlBox to use it in View Data would be desirable.

Regards,
Andreas

> We need a rewrite of ctlSqlResult, but it must base on a virtual
> wxListBox or wxListView, i.e. maintaining the data ourselves. On the
> long run, extending ctlSqlBox to use it in View Data would be desirable.

My concern isn't speed but rather functionality. It doesn't really matter
to me if wxGrid is 10x slower than wxListView if wxListView doesn't do
what I need. It would make my life a lot easier in at my job if I could
copy arbitrary subsets of the results into the clipboard. I almost never
want to copy entire rows at a time. As far as I can tell from the
documentation, you can't do that with wxListView. As far as I'm concerned,
this feature is the difference between pgAdmin being pleasant to use
verses being one of those programs you curse at all the time.

My full intentions were to improve the implementation as soon as I got the
base functionality working ok. I assumed a custom table class would be
necessary for decent performance. I also intended on sharing as much code
as possible between the result view and the view data grid.

Also, I'm not seeing the relevance of ctlSQLBox. It doesn't sound useful
at all to me for dealing with table data.

If you still think I should stop, let me know. If I'm missing something in
the documentation and there is a better way to get the functionality I
want, let me know.

Ed

On 20/2/06 19:29, "Edward Di Geronimo Jr." <edigeronimo@xtracards.com>
wrote:

>> We need a rewrite of ctlSqlResult, but it must base on a virtual
>> wxListBox or wxListView, i.e. maintaining the data ourselves. On the
>> long run, extending ctlSqlBox to use it in View Data would be desirable.
>
> My concern isn't speed but rather functionality. It doesn't really matter
> to me if wxGrid is 10x slower than wxListView if wxListView doesn't do
> what I need.

Unfortunately though, speed is the major complaint we get about that
particular part of the code. People use it (as was partly intended) to fine
tune their queries, and 10 minutes later are posting to a mailing list
asking why PostgreSQL is so slow, not realising that much of elapsed time is
not spent running their query or transferring the data, but populating the
grid.

This problem is partly alleviated by handing the double timing display, but
that still rarely helps.

> It would make my life a lot easier in at my job if I could
> copy arbitrary subsets of the results into the clipboard. I almost never
> want to copy entire rows at a time. As far as I can tell from the
> documentation, you can't do that with wxListView. As far as I'm concerned,
> this feature is the difference between pgAdmin being pleasant to use
> verses being one of those programs you curse at all the time.

Also agreed (yes, we want the best of all worlds :-p ) - copying partial
rows as well as full ones is important.

> My full intentions were to improve the implementation as soon as I got the
> base functionality working ok. I assumed a custom table class would be
> necessary for decent performance. I also intended on sharing as much code
> as possible between the result view and the view data grid.

'K.

> Also, I'm not seeing the relevance of ctlSQLBox. It doesn't sound useful
> at all to me for dealing with table data.

No, I'm not sure about that either.

> If you still think I should stop, let me know. If I'm missing something in
> the documentation and there is a better way to get the functionality I
> want, let me know.

No, please don't stop! We just need to make sure this is going the right
way. I'm afraid I can't look into the details to offer any help myself at
the moment, but I'm sure Andreas will keep an eye on any discussion.

My first though is that we need to abstract the data away from the grid so
that it loads into memory, and then just the visible rows before declaring
to the user that the query is complete. Then, background population of the
grid can occur as the user scrolls, 100 or 1000 rows (or whatever) at at
time on demand. In theory that should mean that pretty much any grid or
listview control can be used as only the UI will be constrained by it's
speed, not the query itself.

Regards, Dave.

> Andreas Pflug wrote:
>
> We're not talking about 10 times, but about exponentially rising times!

Even with a custom table implementation? I just tried a select * in the
query tool on a table with 87k rows in the query tool. It took 4 seconds
to run the query and 24 seconds to display it. I tried loading the same
table in the view data window, and it took about 4 seconds to load via a
stopwatch estimate. Scrolling was instantaneous. Regardless of the
accuaracy of a stopwatch estimate, it was far less than 24 seconds for the
data to load and me to scroll through it all.

>> This problem is partly alleviated by handing the double timing display,
> Not really. This is *the* major issue on the query tool.

I strongly disagree. The speed certainly isn't great, but it is managable.
Using a list instead of a grid is an issue that severely limits the
usefulness of the tool. It's fine when I'm doing development work, but
it's horrible for when someone steps into my office and asks me for a
random piece of data.

I do understand the speed issue though. I guess the speed issue would piss
off the new users, and scare some of them off before they understand the
problem. Whereas the clipboard issue just pisses off the people who've
already decided to use Postgres.

> We're here at the edge of an older discussion. I'm against pushing
> ctlSqlResult to do everything, while sacrificing other basics (as
> peripheral as speed :-) I do see the need for a sophisticated data
> manipulation tool, but to me that's finally not pgAdmin but an
> additional tool. to speak M$, you wouldn't use MSAccess to fire
> arbitrary queries, but use ISQLW, and use MSAccess for data manipulation.

I've never worked with Access, so I can't really comment, but my
impression of Access was that it was nothing like pgAdmin. pgAdmin is a
cross of SQL Server's Enterprise Manager and Query Analyzer. pgAdmin does
just about everything Query Analyzer does. The only significant difference
between the two is the horrible clipboard support for query results in
pgAdmin.

> You might try to enhance the View Data tool's clipboard facilities.

I already did that a few weeks ago. This work is largely based on that.
The usefulness of it is limited though compared to having the
functionality in the Query Tool.

> Please *dont* try to handle data retrieval different from what it is
> now. We do have the problem of long GUI updates, but we also have
> consistent behaviour on the libpq side, making it comparable to psql and
> so forth. This must remain guaranteed, so any cursor implementation is
> nonacceptable.

I assumed Dave meant to retrieve all the data into pgAdmin, store it in a
custom table object, and then only insert it into the grid control as
necessary. That sounds to me like it would satisfy everyone's concerns.

Ed

> -----Original Message-----
> From: Edward Di Geronimo Jr. [mailto:edigeronimo@xtracards.com]
> Sent: 21 February 2006 03:03
> To: Andreas Pflug
> Cc: Dave Page; pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Query tool results in grid
>
> >> This problem is partly alleviated by handing the double
> timing display,
> > Not really. This is *the* major issue on the query tool.

Huh? I said speed is the problem, for which the timer partly helps by
seperating the query/transfer time from the display time. What are you
saying is the problem?

> I strongly disagree. The speed certainly isn't great, but it
> is managable.
> Using a list instead of a grid is an issue that severely limits the
> usefulness of the tool. It's fine when I'm doing development work, but
> it's horrible for when someone steps into my office and asks me for a
> random piece of data.
>
> I do understand the speed issue though. I guess the speed
> issue would piss
> off the new users, and scare some of them off before they
> understand the
> problem. Whereas the clipboard issue just pisses off the people who've
> already decided to use Postgres.

Agreed. There is no reason with a little work why both goals cannot be
met.

> > We're here at the edge of an older discussion. I'm against pushing
> > ctlSqlResult to do everything, while sacrificing other basics (as
> > peripheral as speed :-) I do see the need for a sophisticated data
> > manipulation tool, but to me that's finally not pgAdmin but an
> > additional tool. to speak M$, you wouldn't use MSAccess to fire
> > arbitrary queries, but use ISQLW, and use MSAccess for data
> manipulation.

Yes, this is an old discussion. pgAdmin has *always*, since the very
first version aimed to provide data manipulation as well as querying
capabilities.

> I've never worked with Access, so I can't really comment, but my
> impression of Access was that it was nothing like pgAdmin.
> pgAdmin is a
> cross of SQL Server's Enterprise Manager and Query Analyzer.
> pgAdmin does
> just about everything Query Analyzer does. The only
> significant difference
> between the two is the horrible clipboard support for query results in
> pgAdmin.

Agreed. Query Analyser does manage to do both quite well and is a good
benchmark to aim for IMNSHO.

> > You might try to enhance the View Data tool's clipboard facilities.
>
> I already did that a few weeks ago. This work is largely
> based on that.
> The usefulness of it is limited though compared to having the
> functionality in the Query Tool.
>
> > Please *dont* try to handle data retrieval different from what it is
> > now. We do have the problem of long GUI updates, but we also have
> > consistent behaviour on the libpq side, making it
> comparable to psql and
> > so forth. This must remain guaranteed, so any cursor
> implementation is
> > nonacceptable.
>
> I assumed Dave meant to retrieve all the data into pgAdmin,
> store it in a
> custom table object, and then only insert it into the grid control as
> necessary. That sounds to me like it would satisfy everyone's
> concerns.

Yes, I did, which is pretty much what I believe Andreas has also
suggested in the past.

*How* that is implemented is another issue. I suspect that Andreas means
he wants it encapsulated into ctlSqlResult, overriding the existing data
population methods, rather than an extra layer of complexity between the
data retreival code and ctlSqlResult.

Regards, Dave.

Dave Page wrote:
>
>
>
>>-----Original Message-----
>>From: Edward Di Geronimo Jr. [mailto:edigeronimo@xtracards.com]
>>Sent: 21 February 2006 03:03
>>To: Andreas Pflug
>>Cc: Dave Page; pgadmin-hackers@postgresql.org
>>Subject: Re: [pgadmin-hackers] Query tool results in grid
>>
>>
>>>>This problem is partly alleviated by handing the double
>>
>>timing display,
>>
>>>Not really. This is *the* major issue on the query tool.
>
>
> Huh? I said speed is the problem, for which the timer partly helps by
> seperating the query/transfer time from the display time. What are you
> saying is the problem?

Hm, apparently we have the word omission issue again...
IMNSHO the timing issue isn't really allevated, but remains as the major
issue.
>
> *How* that is implemented is another issue. I suspect that Andreas means
> he wants it encapsulated into ctlSqlResult, overriding the existing data
> population methods, rather than an extra layer of complexity between the
> data retreival code and ctlSqlResult.

What I mean is:
Recoding ctlSqlResult based on the current wxGrid seems a waste of time
to me. We're currently using wxGrid for View Data, and have enough
problems with it to get its in-line editing to work. Marking for copying
appears still non-flawless to me too.

While it certainly is a good idea to have a super ctlSqlResult2 or
something, based on wxGrid2 or whatever, that's capable of handling all
our needs for View Data or the Query Tool, the current base classes
don't seem appropriate.

Regards,
Andreas

> What I mean is:
> Recoding ctlSqlResult based on the current wxGrid seems a waste of time
> to me. We're currently using wxGrid for View Data, and have enough
> problems with it to get its in-line editing to work. Marking for copying
> appears still non-flawless to me too.

After the changes I made to the View Data grid for copying data, the only
thing you can't do is highlight completely arbitrary groups of cells and
copy them. You can copy any an individual cell, any arbitrary set of
entire rows, any arbitrary set of entire columns, or a rectangular
selection. I didn't allow copying anything else simply because it becomes
a nightmare trying to figure out a logical arrangement for the data in the
clipboard. If you have a suggestion on handling it, say so.

> While it certainly is a good idea to have a super ctlSqlResult2 or
> something, based on wxGrid2 or whatever, that's capable of handling all
> our needs for View Data or the Query Tool, the current base classes
> don't seem appropriate.

I really don't see the issue. I have pgAdmin 1.2.2 and the current dev
version installed on my Windows system. Comparing the dev version with my
results in grid patch against 1.2.2, the grid consistantly displays
results in about half the time the list does. As the data grows larger,
the performance difference increases. This is with just a basic wxGrid.
The View Data grid with its custom table class seems to be interactable as
soon as the query to load the data finishes, and has instant response
time.

If wxWidgets isn't very good, then it means either we deal with its flaws
or we use something else. It's not a reason to sacrifice basic
functionality. Being able to run queries is of limited use if you can't
easily use the results.

Ed