Editor performance very poor

scotthibbs

When SQL Prompt is on, editor performance is poor.....particularly when
entering multiple spaces or tabs. Is there any way to auto-indent? This REALLY slows down editor performance.

scotthibbs

Is there any way to temporarily disable SQL Promt to avoid the performance issue????? Something is definitely wrong with performance. Editing is becoming unproductive.

paschott

Looks like the 3.5 RC made some significant headway for me. You may want to give it a try.

-Pete

scotthibbs

Thanks for you reply.

Yes.....RC 3.5 is much better.

We found that the poor perfomance was in areas that were heavily indented.....as a result of using SQL Refactor.

In non-indented areas, performance is pretty good....3.5 even better.

Bart Read

Glad to see the situation has improved. We've made some fairly significant changes there. The one thing that hasn't changed unfortunately is that if you choose to indent a particularly large block of text all in one go it's still not that quick because it just sees one massive replacement and so scans over the whole lot again. This is something we're obviously going to revisit in future, but you should find that when you're just doing normal editing everything works much better.


Thanks,
Bart

scotthibbs

Actually....the performance issue is whenever you are just typing within a heavily indented block of code. For some reason, all operations seem slow in those areas.

For instance, if SQL Refactor reformats the code in a section to be indented to a depth of say 25 spaces....then you go to the middle of
the block and try to add a line. Entering the 25 spaces to get to the
proper location is extremely slow....you lose the cursor for up to a second or two. Completion function also seem slow when you finally get to the right position.

If you turn off SQL prompt off, the effect disappears. I don't know what SQL prompt is trying to do.....it's very strange behavior.

The 3.5 version improves a bit, but it's still very noticeable.

Bart Read

Hmm, interesting. We'll take a look into it and see what the problem is. I'd expect mass indentation to be slow but not normal typing so there's clearly something not right there.
Thanks,
Bart

Bart Read

This is extremely bizarre. So here's what I tried:

I took a 600 line SQL script that is mainly composed of SELECT statements against the AdventureWorks 2005 database. I wanted to get SQL Prompt working as hard as possible so I indented everything by 40 spaces to start off with and then headed down to about line 540 where I inserted about 35 newlines and then just held the space bar down to insert loads of spaces.

Meanwhile I had Task Manager open to look at the CPU usage and to be honest, all was well. Using between 5 and 12% CPU right up until I'd got to about column 470 with the space insertion, then the CPU usage jumped up to 30-40% and everything got a little bit on the jerky side.

I still have no idea what's causing this, however it might be useful to know a bit more about your machine:

- OS
- CPU type and speed
- Amount of physical memory

In the meantime I'm going to profile the application to try to see what the problem is.


Thanks,
Bart

ChrisRickard

<jk>Switch to tabs</jk> 8)

Bart Read

LOL. Please don't let this be the start of a religious war... I must admit that I'm more of a tab man myself, but anyway.

I've been able to identify two possible causes for this behaviour:

(i) Our AST parser outputs ASTs where child nodes are implemented as singly-linked lists, which means you can only really walk them in one direction. This causes a problem when we want to try to identify whether there's anything between a particular node and the caret when the caret does not fall exactly within the bounds of a node. Now, our method for checking for non-whitespace characters isn't quite as efficient as it might be and I have found that we are checking multiple times over overlapping ranges of characters.

(ii) If a syntax error is detected that corrupts the AST or causes no AST to be generated we fall back to using regular expressions to try to identify the beginning of any kind of supported SQL statement, and then we perform a recursive descent parse from that position up to the caret position. Now it turns out that this isn't always as efficient as I might like, although the circumstances under which this occurs are less frequent than (i).

I have been able to fairly easily apply a fix for (i) that seems to work well, however although my automated tests pass, and the limited manual testing I've carried out seems to exhibit no side-effects, it's possible that this fix may have unforeseen consequences and since we have already run through around 90% of our other tests I am unwilling to take a chance of breaking something by checking this in at this stage since it's in code that's right in the core of the parsing logic and so obviously this is used extensively by the code that works out what to provide in terms of auto-completion. So this fix will not make it into the 3.5 final release. However once this is out I am happy to provide you with a download link for an installer with a version of SQL Prompt that would include this fix. It probably won't solve your problem completely and I did notice that performance does degrade after I've inserted somewhat more than 1000 spaces on the line in question, which is more than double the number I had to insert before applying the fix.

Problem (ii) is more difficult to solve, although it is solvable, but I certainly won't be fixing it before the 3.5 release because it involves possibly up to 8 hours work and any bugs would certainly result in bizarre side effects elsewhere. It's really too late in the day to be contemplating a fix like that.

In the meantime there are two things you could contemplate doing in order to improve the situation. The first, as Chris jokingly suggested, actually is switch to using tabs rather than spaces. The second is to click on the SQL Prompt > Options menu item. Then go to the Variables and Parameters screen on the Listed Candidates tab and experiment with reducing the value in the Lines field under Search a fixed number of lines from the caret (faster). Despite the name of this field what it really does is define the maximum size of the block of code SQL Prompt will attempt to parse. For reasons too dull to go into it's actually twice this size (number of lines above and below the caret), although most of the time SQL Prompt will actually parse much less. What it does is to specify an outer limit. However be warned: if you make this value too small you may find that the auto-completion list isn't populated properly because SQL Prompt ends up trying to parse partial SQL statements and this breaks the AST generation.

Hope that helps.


Thanks,
Bart

EsioN

Some behaviors I'm experiencing:

I got the following resulte while testing SP3.5 in a server configured as "Connections to ignore".

After pasting the script in QA, my CPU use goes to 100% by QA. It's ok, SP3.5 is parsing the code, but note:
It is a connection to ignore... Does it make sense to parse this connection?
The most part of the code, is commented code. Again, does it make sense to parse this?
But ok, probably there is a good reason do SP team thinking this behavior. I think we talked about this in SP3.0 beta discussion.

Going ahead.

My question is: if I navigate in the code using the arrows keys, or point the cursor using the mouse, in some parts of the script, no problems. No delays.
But if I put the cursor from columns 1 to end of the first word + blank space , my cpu use goes to 100% again for some moments!!! Too stange!!
Ex->exec sp_rename.... cursor from column 1 to 6
now, (removig exec command form all senteneces) so:
Ex->sp_rename ....cursor from colum 1 to 11


The code (as you can see, there is a lot of blank spaces, no tabs):

exec sp_rename 'PK__ACESSO_USUARIO_D__71139959','PK__ACESSO_USUARIO_DEPOSITO_CLIENTE', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        PK__ACESSO_USUARIO_D__71139959                                                                       PK__ACESSO_USUARIO_DEPOSITO_CLIENTE                                                                  ACESSO_USUARIO_DEPOSITO_CLIENTE
exec sp_rename 'PK__FLUXO_FERRO_NIOB__0AD36B5C','PK__FLUXO_FERRO_NIOBIO_REL', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 PK__FLUXO_FERRO_NIOB__0AD36B5C                                                                       PK__FLUXO_FERRO_NIOBIO_REL                                                                           FLUXO_FERRO_NIOBIO_REL
exec sp_rename 'PK__FLUXO_FERRO_NIOB__440BE8B8','PK__FLUXO_FERRO_NIOBIO_REL_AGRUPAMENTO', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     PK__FLUXO_FERRO_NIOB__440BE8B8                                                                       PK__FLUXO_FERRO_NIOBIO_REL_AGRUPAMENTO                                                               FLUXO_FERRO_NIOBIO_REL_AGRUPAMENTO
exec sp_rename 'PK__FLUXO_FERRO_NIOB__45F4312A','PK__FLUXO_FERRO_NIOBIO_REL_USUARIO', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         PK__FLUXO_FERRO_NIOB__45F4312A                                                                       PK__FLUXO_FERRO_NIOBIO_REL_USUARIO                                                                   FLUXO_FERRO_NIOBIO_REL_USUARIO
exec sp_rename 'PK__IMP_DESPESA_DESP__2C345F27','PK__IMP_DESPESA_DESPACHANTE_ITEM_DOCSAP', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    PK__IMP_DESPESA_DESP__2C345F27                                                                       PK__IMP_DESPESA_DESPACHANTE_ITEM_DOCSAP                                                              IMP_DESPESA_DESPACHANTE_ITEM_DOCSAP
exec sp_rename 'PK__INVOICE_NOTA_FIS__60A82766','PK__INVOICE_NOTA_FISCAL', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    PK__INVOICE_NOTA_FIS__60A82766                                                                       PK__INVOICE_NOTA_FISCAL                                                                              INVOICE_NOTA_FISCAL
exec sp_rename 'PK__NOTA_FISCAL_ENTR__41EE961C','PK__NOTA_FISCAL_ENTRADA', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    PK__NOTA_FISCAL_ENTR__41EE961C                                                                       PK__NOTA_FISCAL_ENTRADA                                                                              NOTA_FISCAL_ENTRADA
exec sp_rename 'PK__ORDEM_PRODUCAO_C__22AAF4ED','PK__ORDEM_PRODUCAO_CONTROLE_STATUS', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         PK__ORDEM_PRODUCAO_C__22AAF4ED                                                                       PK__ORDEM_PRODUCAO_CONTROLE_STATUS                                                                   ORDEM_PRODUCAO_CONTROLE_STATUS
exec sp_rename 'PK_PAIS_AGRUPAMENTO','PK__PAIS_AGRUPAMENTO', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  PK_PAIS_AGRUPAMENTO                                                                                  PK__PAIS_AGRUPAMENTO                                                                                 PAIS_AGRUPAMENTO
exec sp_rename 'PK__PESQUISA_DESENVO__78B4BB21','PK__PESQUISA_DESENVOLVIMENTO_SUB_CONTA', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     PK__PESQUISA_DESENVO__78B4BB21                                                                       PK__PESQUISA_DESENVOLVIMENTO_SUB_CONTA                                                               PESQUISA_DESENVOLVIMENTO_SUB_CONTA
exec sp_rename 'PK__PRODUTO_TBP__5907059E','PK__PRODUTO_TBP', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 PK__PRODUTO_TBP__5907059E                                                                            PK__PRODUTO_TBP                                                                                      PRODUTO_TBP
exec sp_rename 'PK__SUB_CLIENTE_ENDE__276FAA0A','PK__SUB_CLIENTE_ENDERECO', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   PK__SUB_CLIENTE_ENDE__276FAA0A                                                                       PK__SUB_CLIENTE_ENDERECO                                                                             SUB_CLIENTE_ENDERECO
exec sp_rename 'PK__SUB_CONTRATO_ESP__5B24583A','PK__SUB_CONTRATO_ESPEC_GRANUL', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              PK__SUB_CONTRATO_ESP__5B24583A                                                                       PK__SUB_CONTRATO_ESPEC_GRANUL                                                                        SUB_CONTRATO_ESPEC_GRANUL
exec sp_rename 'PK__SUB_CONVERTE_MOE__562A98F3','PK__SUB_CONVERTE_MOEDA_DOLAR', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               PK__SUB_CONVERTE_MOE__562A98F3                                                                       PK__SUB_CONVERTE_MOEDA_DOLAR                                                                         SUB_CONVERTE_MOEDA_DOLAR
exec sp_rename 'PK__SUB_VENDA_ENTREG__3B76A2B7','PK__SUB_VENDA_ENTREGA_CONTRATO', object  --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             PK__SUB_VENDA_ENTREG__3B76A2B7                                                                       PK__SUB_VENDA_ENTREGA_CONTRATO                                                                       SUB_VENDA_ENTREGA_CONTRATO

Bart Read

OK thanks Esio, we'll take a look into it for the patch release.
Cheers,
Bart