OK, so my first post on the Integration Blog, isn’t exactly about integrations but it is something I have been working with lately and have had some questions about so I thought it was a good jumping off point.

With SalesLogix and its ability to extend the database structure to support customizations it often grows in scope beyond what was originally designed, as far as the SQL back end is concerned.  It is important to consider what the implications on server performance are when adding large data tables that require extensive lookup or query activity against them.  SQL utilizes indexes to increase search performance.  Without properly designed indexes on large data sets, SQL performance on a system can be decreased enough to cause a system wide slow down in applications utilizing the SQL server.

Lets take a look at a real world situation in SalesLogix.  Lets say I am going to be building a new table as a one-to-many relationship off the Account table to store information about Widgets.  Here is the table as it is automatically built in the SalesLogix Administrator:

I have not added any of my custom attributes describing Widgets, only the automatic SalesLogix fields exist.  I will now add the many attributes of the Widget, including Color, Shape, Weight, Height, Density, Notes, Version, Cost, and any other attributes, including one important one CONTACTID.  This will tie the Widget to the SalesLogix Contact who is in possession of the Widget.  The reason this field is important is because it will be used for building a lookup and a grid in the SalesLogix interface.

So here is our table so far:

At this point we can save our table, build our screens and everything will work fine.  For a while.  The reason what we have done to this point may cause problems down the road is because we have a field (CONTACTID) that will often be used by SalesLogix for Lookups and for the use in queries. 

By default SalesLogix only indexes the Primary Key, and if present, the Foreign Key of a table when it is created.  Unless you physically add an index on other fields they will not exist.  Also, the indexes SalesLogix adds are usually simple single field indexes that may not be the most efficient type to use, depending on circumstances.

Now at this point I do not want to get into a big discussion on indexes and their usage.  For this article I will just say that their use is critical in the performance of your SQL server and therefor directly impact SalesLogix performance.

So what can be done to help define what indexes are needed and make sure your SalesLogix customizations utilize SQL server resources as efficiently as possible?  SQL server has a couple of built in tools just for this purpose.

Single query examination

Examination based on a utilizing the SQL Profiler.

For this week I will focus just on using the Single query examination approach.  A future article will talk about the SQL profiler approach.

Now lets quickly build an Account tab in SalesLogix that contains a data grid showing widgets.  After adding the grid to a new form, I set up the SQL properties of the grid as follows:

On the Grid Details:

On the Layout I add a few columns.  Notice I created a local join here to the CONTACT table to allow me to display some columns from the CONTACT table associated to my Widget’s Contact like LASTNAME and WORKPHONE:

Lastly I decide I want to sort by CREATEDATE:

Again, at this point I can save the form and it will work fine, though not efficiently.  Lets take a look at the SQL statement the data grid’s SQL property has created.  Click the View SQL button in the Query Builder dialog.  Here is the statement:

SELECT A1.WIDGETSID, A1.CREATEDATE, A1.COLOR, A1.HEIGHT, A2.LASTNAME A2_LASTNAME, A2.WORKPHONE A2_WORKPHONE FROM WIDGETS A1 LEFT OUTER JOIN CONTACT A2 ON (A1.CONTACTID=A2.CONTACTID) WHERE A1.ACCOUNTID = :BindID ORDER BY A1.CREATEDATE ASC

So this SQL statement looks fine, and again would work normally.  Issue would start to appear as you got several thousand or hundreds of thousands of rows into the Widget table.  The Widget tab would start to open slower and slower and while one user was accessing the tab other users might see that SalesLogix was less responsive.  Why?  Because the way this screen is built requires SQL to perform some heavy data crunching as it stands right now.

Lets finally take a look at how we see what this Widget tab is really doing in SQL.

If we copy the above SQL statement and log into SQL Query analyzer (I will be utilizing SQL 2000 for my SQL database level) as SA.  We can then take a look at what this tab is really doing:

Now in the Query window, I have formatted the Query to be easier to read and changed the WHERE clause to have a valid value, rather than :BINDID.  I also have appended the table names with the sysdba. prefix since I am currently logged in under the sa context.

Now go to the Query…Display Estimated Execution Plan menu item in the Query Analyzer.  Something like following will appear below the query window:

Now we can start to see what each component of our query is doing at the SQL server level.  So what to look for?  Well any steps that take a large percent of the query cost (like the Sort in the example) as suspect.  Also if you ever see a Table Scan those are especially intensive on the server resources.  Knowing just what to do is not easy based on this.  Again, SQL has another tool you can use.

Go to the Query…Index Tuning Wizard menu item.  The Wizard will open:

Click Next.

Select Thorough.  Click Next.

Click Next

Click the Select All Tables button and click Next.  SQL will now analyze the current SQL query.

After it is done analyzing it will show you what recommendations it has made:

In the above example, we see based on the recommended changes we can see an 89% increase in performance!  Go ahead and click Next.

The wizard now allows you to apply changes immediately, schedule them for later using the SQL agent, and/or export them to a file which you can then open in query analyzer and run from there.

So there you have it.  Using these tools can significantly improve speeds on your SalesLogix system.  Try it out on some of your slower opening screens.  IN some cases you can not see what your SQL queries are (Data grids are easy but other screens sometimes are harder to figure out).  For other screens you can utilize the SLX Profiler to isolate long running queries and analyze those.

A couple of points:  Running the index tuning wizard can be intensive, especially if you run the recommended changes.  While indexes are being built tables can be locked and unavailable to others.  I recommend doing these kind of database changes in a test system first, and then during off-hours on the production system.