Hello all! I have been researching reasons for why our implementation of SalesLogix Web 7.5.4 seems to lag and I have come up with one possibility.

The NHibernate code passes queries to sp_execsql that do not use fully qualify table names. This of course causes the query plan to lag as there are a lot of reads.

I found the following key listed as what you should add to the web.config to fix the problem.

http://geekswithblogs.net/Billy/archive/2006/03/08/71736.aspx

However, I think the SLX code uses a compiled DLL and we cannot set this.

Is there a place within the SLX web code that I can set this?

Thanks,
Wiley

I figured this one would have an easy answer.  

Either the hibernate stuff is configurable or isn't.

Anyone know?

Wiley:

  Did you actually try making this change to the Web.config?

  You should be able to add it to the Web.config within the Portal that you are targetting. The item missing from the example is on what section to add this value.

  Also, keep in mind that you also have to account for the SLX OLEDB Provider. The provider will need to Parse the SQL Statement. I do not know if this is done before or after hibernate calls on the Web Client.

  All that said, you mentioned that your Implementation seems to lag. How did you narrow it down to this setting? Does it lag when only a single user is on the system (no load)? Does it lag with load when openning the web site directly from one of your Web Servers? Have you looked at network latency?

  In my experience, if your Servers are properly sized, and your application is lagging, the typical culprit will be Network latency, but there are many variables to take into account.

I have added the line in web config inside of <appSettings>.

Not sure this is where it will be read into the NHibernate stuff tho.

This is not the only setting I will be checking but it definitely caught my eye based upon the research and seeing the output of a trace.

If NHibernate passes object names in the statement string that are not fully qualified, the execution plan is not reused.

Owner info will have to be looked up on every execution.  That is pig slow.

This implementation has 13 licenses and no more than 5 people on at once.

The SQL server is 16 core, 32GB ram, big iron with RAID 10.

The web server is quad core  8 GB ram on raid.

They sit on a gigabit network in the same cab.  Sizing is not a factor.

Naturally, opening Web locally is faster but not by much.

As example, one of the lagging tables is leads.  There are 4000 leads.

Testing locally, when opened in the Windows EXE, it opens fast.

Testing locally, opened in Web, the lag is several seconds at best.

Once you add any slow connections at the user side (a few offices on slower DSL) then I see the network lag you refer to.

However, this seems to be a different issue and a known quantity. Optimizing the server setup on web seems the best course at this time.

Is there a way to set the value I listed?  Do I have it in the correct place?

Thanks,

W

OH, and thank you for the reply!!

Wiley:

  Have you used the SalesLogix Profiler to (and/or the SQL Profiler)?

  The SLX Profiler will show you the Queries (how they were submitted by the Application and how they were actually executed), the Time it took SLX to Parse, Prepare, Secure, Execute, GetRows and Log each execution.

  Also, it may help you shed some light as to what else is involved on a Page loading (e.g. Loading Picklists, Security Joins, etc).

  When working with this tool, if you notice high Execution times, you may probably start looking into the health of your Indexes and Statistics (but you may pick individual statements and run them on  SQL Management Studio and look at estimated and actual execution plans).

  Also, the SLX Profiler includes a Query Optimizer that may help you out as well.

  Finally, although this should be the first thing to look at:

  Is your site compiled? This is how you can tell for sure that your SLX Deployment is compiled:

    a) The presence of a file called "precompiledApp.config"

    b) The lack of any code file (e.g. Account.aspx.cs)

    c) The presence of .compiled files on the Bin folder

    d) The presence of App_Web_xxxxxx.dll files on your bin folder

  If your site is not compiled, calls to it may require compilation, which will always add to the server side processing.

  Along the same lines, make sure that you don't deploy your site with the Debug flag, and make sure that set your App Pool restart times (the default value may cause it to re-start during the Day, which would cause it to 

Raul,

I have used both profilers.  The SQL Profiler shows the query being executed without the owner specified.

What else should I look for on the SLX Profiler?  I see queries taking 20-30ms for Leads.

Where is the SLX Optimizer?  Have not seen that.

I am checking the debegging and compile status and will redeploy if those are out of whack.

Thanks!

Wiley

Wiley:

  20 to 30 ms is probably a good execution time, and should not have a lagging effect on the Application. Also, 7.5.4 uses memcache, so repetitive queries may not even hit the DB.

  I would typically worry of execution times over 100 ms (even then, that is a low threshold).

  I would suggest you look into the other layers of the application for answers to your overall issue. Even if you were to optimize the queries to run under 10ms I would doubt that you woud see any additional performance Improvements.

OK.  The only change I have done is re-reploying with a Pre-compile and Debug off.

Of course, that blew up Leads for some reason so I recompiled and redeployed again which worked.

I do not see any of the files you referenced but I did have the Precompile check box checked.

If you don't see the precompiledApp.config file, the app may not be pre-compiled.

You can Reset IIS and then start going through the application, pay attention on Task Manager, you should see almost no activity from csc.exe. If you see it highly active that means that compilation is taking place at runtime.

Is this supposed to be in the SlxClient root?

Yes.

Odd then.  re-Compile is checked but that file never shows.

How can I assure a pre-compile occurs?

Thanks!

I have ran into this in the past.

If you open the SLXClient folder while deploying, you will notice that once all fiels are copied, it starts compiling (you will see the precompiledApp.config), but at some point the files are just restored back and no evidence of pre-compilation on the site are left.

When that happens to me, I execute a Manual Compilation of the Site using aspnet_compiler.exe

IIRC the nhibernate config & mapping files are embedded as resources in some DLL, I can't remember which. But if you open some of the ones with names related to data, repository, entity models, etc in reflector and look at the resources you might find it there. If that is correct, then to change it you'd have to modify the resources of the assembly but you'd loose the change when Sage has an update (and also when you redeploy if you don't also update the support files\bin assembly with the modified one as well).

Anyway, not sure if that will help, but I seem to recall seeing the config in a resource before.

I must be into triple figures of attempted Precompiles with the checkbox ticked, hasn't worked to this day. Does it ever work? Anyway, precompilation will only help on the first load will it not?

From the original post and "speed" issues, the LAN client is faster than the web, not much you can do about it unfortunately, spent many a painful hour troubleshooting. The best performance improvements I got overall were by:

1) Improving database indexes for web specifically (the web runs completely different SQL to the LAN). To do this I did a very rudimentary trace and filtered by all SQL statements taking over, say, 1 second.

2) Switching to FireFox. This is so much quicker then IE (8). IE 9 is quicker, as is Chrome, but not supported (yet...) 

3) Running the web client from a higher spec'd client. The web client is pretty resource intensive as far as websites go. This is obviously not an option for most end users, but goes someway to demonstrating a lot of the speed issues are actually client side rather than things you can tweak and improve on the server.....

Just saying...

Cheers,

Nick

I find it particularly troubling that the compile options does not work.

I watch the CPU utilization when I load LEADS and I see the CPU spike to 20+ on every load.

Review of the site from Fiddler and Chrome Page Speed shows tons of JS and images that are not allowed to cache.

There has to be a way to optimize this pig.

Raul, is there a write up on how to compile from command line?

Thank you!