coc-db timeout on database with a large number of tables

[pnetherwood]

I'm getting an timeout error message from CoC source db timeout after 5000ms on a database with a large number of tables but its fine on all of the other databases. The database has 43K tables and its nor surprising that it times out. It works great on all of the other databases which are < 1000 tables.

It might be a useful feature to be able to cache the tables. I'm using vim-dadbod-ui which has already loaded the tables. Would it be possible to have an option for coc-db to use the table list already loaded in vim-dadbod-ui?

[kristijanhusak]

I am using tables from dadbod-ui if available. I assume the issue happens for columns, since I don't have those in dadbod-ui, and i do a query to get them. What type of db it is?

Try running this query and let me know how long it takes to finish?

select table_name,column_name from information_schema.columns order by column_name asc

[pnetherwood]

Its MS sql server. Your column query returns 430K rows. The query takes 9s in Sql Server Management Studio and 9s from sqlcmd on Windows. I'm using vim in WSL/Ubuntu1804 and sqlcmd from the mstools package. In WSL its actually quite quick and takes 5s. However, from vim using dadbod or dbext its takes 14s. It did a quick profile in vim when running through dadbod:

FUNCTIONS SORTED ON TOTAL TIME
count  total (s)   self (s)  function
    2  14.806830   2.345907  db#execute_command()
    2  10.777259   5.540528  <SNR>168_filter_write()

the filter_write() method is in vim-dadbod and its not surprising it take a while to filter that many lines. Its a monster query which ever way you time it TBH.

[kristijanhusak]

Yeah, I'm not sure what's the best way to tackle this. Only idea that I have is to use some kind of streaming, but that would still cause CoC to timeout after 5 sec, since it would take longer than that.

I could introduce an optional variable that would disable fetching columns, or limit it to the current table (in cases where you select queries from specific dadbod-ui table), which should solve this issue. It limits the possibilities of the plugin, but table autocompletion should work.

[kristijanhusak]

After the timeout, do you still get some autocompletion or it's completely broken?

[kristijanhusak]

I just figured out that it's not the issue with the query itself taking long, but the amount of data that it returns. Vim has hard time filtering all that data while typing. I'll check if I can improve on that, and maybe add some grouping by table, so it's easier to filter out. Shoot any ideas if you have.

[pnetherwood]

Once CoC has timed out then nothing is listed for that source. However, it works fine on other sql server database we have. Some sort of caching is the only thing I can think of. I assume you're doing the full query and full filtering everytime the popup comes up? Perhaps another option is to allow users to override the column query and let them do the filtering.

[kristijanhusak]

I did some optimizations. Can you give it another test and see if it's maybe better?

[pnetherwood]

I did a CocUpdate and got verion 19. I'm afraid I didn't notice any improvement though. If I do "+ New Query" and start typing in insert mode it locks up then times out after I've just typed in 'se' of the select keyword.

I don't know how context aware you can make it but the completion in Sql Server Manager only comes for tables when it expects a table name and for columns when an alias has been defined or the table has been entered. If you could so that then you'd only need to query the columns for a given table.

[kristijanhusak]

I just published a new version with an optional configuration to do this.

Try adding this to vimrc:

let g:vim_dadbod_completion_force_context = 1

And give it a try. Hopefully it will help.

[kristijanhusak]

If this will not work, I'll introduce doing queries in the autocompletion process. Currently I do that when you create a buffer, but if these big results cause these issues, will chunk them up by table.

[pnetherwood]

I think its a little better but unfortunately it still times out. BTW, for dadbod-ui, I've implemented queries for sql server for the table properties such as keys, constraints etc if you're interested.

[kristijanhusak]

Yeah those could be helpful. Feel free to create PR for those.

[kristijanhusak]

I introduced fetching columns on demand (when in context), for all databases where columns count goes over 10k. I would appreciate if you would update and give it a test to see if it works now.

[pnetherwood]

Alas its still the same. Is there anyway I can introduce some tracing so I can help you debug it?

[kristijanhusak]

Not really, especially not with coc. Looks like the issue is with too much tables, not the columns. By "same", you mean timing out immediately when starting to type? Can you try opening sql buffer and wait like 30 sec and then type to see if it works? Is that db locally on your machine or some remote?

[pnetherwood]

When I say the 'same' it basically the same timeout behaviour as before. I open a "New Query" from dadbod-ui and start typing: 'se' then it pauses for 15 seconds and the Coc PUM appears with no DB sources listed then every character I type it pauses again. Waiting 30 seconds didn't help. The DB is remote but on our network. There is no noticeable delay at all on a the other sql server databases all of which are remote. Maybe there's no easy fix. Perhaps have an option to disable completion for specific databases because I'd love to be able to use the rest of the features and have completion on the rest of the databases. I had trouble with dbext too on that database and I had to setup a cut down dictionary and I was using coc dictionary completion.

[kristijanhusak]

Yeah i can provide an option to ignore autocompletion for certain databases. Would you do one more test, but without coc? Here are the steps:

1. Open up vim, wait like 10 sec
2. execute CocDisable
3. Open new query from dadbod-ui
4. execute setlocal omnifunc=vim_dadbod_completion#omni
5. type some query with half of table name in it, for example, if you have table users, do select * from us
6. Execute omni completion, <C-x><C-o>

Let me know does it freeze, if yes how long, and do you get any results after some time (don't wait more than 30 sec).

[pnetherwood]

It freezes for about 30 seconds but no completion menu comes up and :messages shows:

[coc.nvim] Disabled
Error detected while processing function vim_dadbod_completion#omni[19]..vim_dadbod_completion#alias_parser#parse:
line   12:
E339: Pattern too long
Error detected while processing function vim_dadbod_completion#omni:
line   38:
E715: Dictionary required
Error detected while processing function vim_dadbod_completion#omni:
line   40:
E712: Argument of filter() must be a List or Dictionary
Error detected while processing function vim_dadbod_completion#omni:
line   42:
E712: Argument of map() must be a List or Dictionary
Error detected while processing function vim_dadbod_completion#omni:
line   62:
E745: Using a List as a Number
Pattern not found

[kristijanhusak]

Thanks, this was super helpful! Parsing alias seems to be a problem. I now changed it to work in a different way. Do an update and see if it's better.

[pnetherwood]

Marvellous. That did the trick. No timeouts. Very responsive. Nice work.

[kristijanhusak]

Great! Thank you for testing it. I don't have access to any database that's even near that size.

Closing this. If you run into any other issues, feel free to open separate issue.

[pnetherwood]

Thanks for your hard work and being so responsive. I love what you've done with dadbod-ui and the completion. No need to use the bloated SQL Server Management Studio for everyday now. The thing about databases they don't lend themselves well to a fzf style of browsing so having a tree based browser is essential I think. Since I'm not constantly looking at the database I need to go in and browse it and look at the table definition, its columns and the typical data content to remind myself how to use it which is where dadbod-ui fits in nicely. Thanks again.