Autocomplete Very Slow

[iliask]

Autocomplete seriously slows down writting of queries as it freezes the editor every other second to lookup for possible matches. I hope you'll speed up the search code before it's released to the public.

Also it would be nice if you could use the Data_length or Checksum of a table to make a decision whether to rebuild it's TAG files on the startup.

[peterlaursen]

Quote:

Autocomplete seriously slows down writting of queries as it freezes the editor

On a 1 Ghz computer here I have this kind of 'feeling' too in very low degree if no other demanding task is running. However I think, that you should just type on. Keystrokes should be saved in the keyborard buffer. What happens if you just hold down a key like 'aaaaaaaaaaaaaaaaaa'? I think Ritesh told me that the SQLite was running in it own thread. Could Ritesh comment on it?

Quote:

you could use the Data_length or Checksum of a table to make a decision …

Good idea! Provided that it is possible to generate identical checksums of MySQL and SQLite database tables.

[Ritesh]

iliask wrote on Feb 24 2006, 11:51 AM:

Autocomplete seriously slows down writting of queries as it freezes the editor every other second to lookup for possible matches. I hope you'll speed up the search code before it's released to the public.

Also it would be nice if you could use the Data_length or Checksum of a table to make a decision whether to rebuild it's TAG files on the startup.

[post=”8908″]<{POST_SNAPBACK}>[/post]

Can you tell me your computer configuration?

Approximately, how many database/tables/columns are there in your MySQL host?

Rebuilding of the tag files are done in a separate thread so it should not effect your typing experience.

Also, you can disable building of tag files on each start up using Tools->Preferences

[peterlaursen]

Quote:

Also, you can disable building of tag files on each start up using Tools->Preferences

But that would COMPLETELY disable auto-complete wouldn't it?

[peterlaursen]

On this 1 Ghz computer of mine (the slowest that I have) typing 'mys' displays a small pop-up 'mysql – press TAB to insert'. From I type the 's' to the popup appears a splitsecond later SQLyog proces takes around 30% of available CPU resources according to Task Manager. Similar with other autocomplete features.

Now I figure that when CPU does not go higher here it was be because RAM access is the bottleneck on this system. It is DDR266 RAM. Maybe you have a 'monster' with for instance DDR2 RAM so that CPU-resources become the bottleneck?

I think that some of us (including me) could change our habits when using auto-complete. Instead of having ONE CONNECTION ONLY with all databases on a host, we could consider having MORE CONNECTIONS each with the databases that we need for particular tasks.

The most important optimization isssue for me with auto-complete is reduced disk-access at startup!

[Ritesh]

peterlaursen wrote on Feb 26 2006, 08:26 AM:

But that would COMPLETELY disable auto-complete wouldn't it?

[post=”8935″]<{POST_SNAPBACK}>[/post]

No.

It will basically stop rebuilding on start up from second time. By default it will rebuild the tag files on start up which might not be necessary if you use SQLyog to make schema changes. However, disabling this feature would require you to manually start a rebuild if you make changes to the schema OUTSIDE SQLyog.

[Ritesh]

We are going to make numerous optimizations in the Auto-complete options before the FINAL release. It would not create ANY difference in the experience with or without autocomplete

[peterlaursen]

I don't think Iliask is talking about build-time here. I understand that his keyboard becomes kind of non-responsive at run-time.

As I wrote I think the size of the connection-specific database and the relative performance of RAM and CPU might do this. I you buy a 'monster' of a MB with 1000 Mhz RAM (DDR2 Hyperchannel or similar technology) and equip it with an older slow processor.

There are some settings with the OS where you can give relative priority to applications (sqlyog) and system (keyboard).

[peterlaursen]

I overlooked that prefrences had changed a little with first public BETA.

I request then a 'save changes to tag file' option when closing program like editor has). Actually I think I would prefer NOT to rebuild at startup, but I would like to save changes that I made when I close. I think that could be made very faster if there was a HISTORY the of embedded database. Won't have to rebuild everything then but only exectue the changes of the session.

Actually it is only CREATE, ALTER, DROP and RENAME SQL that must be executed with SQLite, I believe.

[peterlaursen]

Or maybe even better: simply insert/update/drop a row to embedded database 'on the fly' when CREATE, ALTER, DROP and RENAME has been executed by mysql.

I know this is quite a new thing – MySQL statements will have to be parsed as it is not only SQL executed from GUI objects but also from SQL-pane. Looking back I think that is how it should have been in the first place.

But still this is not iliask's issue – his is a runtime issue with the editor pane an the keyboard.

[Ritesh]

Quote:

simply insert/update/drop a row to embedded database 'on the fly' when CREATE, ALTER, DROP and RENAME has been executed by mysql.

Indeed it does. On every CREATE/ALTER/DROP operation done through the SQLyog GUI will be handled separately and these operation does not require rebuilding the tag file.

@iliask problem is happening because on every key-press SQLyog tries to find a match for tool tip which requires it to issue a SELECT statement to Tags.db (which also accounts for 30-40% CPU usage) and slows down the typing experience.

We plan to put an option in BETA 2 where a user can disable TOOLTIP altogether. It will then show Autocomplete data only on TAB and . press.

[peterlaursen]

Ok … 😀

That means then that if SQLyog is the only client used that does changes to MySQL databases, then there should in principle be no need to rebuild at all (except for repair) ?

[sarat]

peterlaursen wrote on Feb 27 2006, 01:50 PM:

Ok …  😀

That means then that if SQLyog is the only client used that does changes to MySQL databases, then there should in principle be no need to rebuild at all (except for repair) ?

[post=”8952″]<{POST_SNAPBACK}>[/post]

Yes.

[peterlaursen]

Then it would be very nice if the setting 'rebuild at startup' was connection-specific!

When some day the connection settings are moved from .ini to .db file there are quite a lot of settings that could be connections-specific.

[peterlaursen]

@sarat

'verified just as you described it' as MySQL use to express themselves. Works great now. Thanks 😀

Now just a green 'u' icon for user strings and write to Embedded database as UTF-8 ..

[Author]

Posts