Improving LobbyCentral Performance
November 1, 2016
As your LobbyCentral database grows, you may notice that searching and reporting will slow down. By adding indexes to the LobbyCentral database, you can decrease the search time by as much as 92%. The process is easy and only takes a few minutes to do.
This technical article walks through two options to improve your database performance.
Before you begin, please be sure that you have performed and verified a full database backup.
Migrate from MSDE to SQL Server
Earlier versions of LobbyCentral shipped with Microsoft’s desktop database engine called MSDE (now called SQL Server Express). Although it contains 90% of the features of SQL Server and behaves as such, MSDE was intended for desktop applications or small scale server usage.
MSDE proved to be a good solution for credit unions with light member traffic that did not need the performance of SQL Server. However, for credit unions with higher traffic, it may be necessary to use SQL Server to improve performance. Migrating from MSDE to SQL Server is as simple as performing a backup and restore operation. Contact
[email protected] for more information.
Adding Indexes
The LobbyCentral database contains several child tables that are linked in a many-‐ to-‐one relationship. For example, tblRequest_Service is a child table that contains the services worked, and is linked to tbl Request.
The child tables can, and often do, contain more records than other tables such as tblCustomer. In search and reporting, tables are joined together to bring all of the information together.
The time a database query takes to return a result is determined by the total number of table records. When a query is executed, the database server must examine each record to see if it meets the criteria for selection. To allow SQL Server to “jump” to a starting point, we use indexes.
A good analogy is a phone book that contains 50,000 names. When we open a phone book, we do not want to start with the first name and look through each one until we find Mary Smith. We jump to the “M” page, and then look for Mary. In SQL server, we can create an index on first name, so that SQL Server can skip names beginning with A through L.
In the LobbyCentral database, there are several tables that you can create an index for, to help the server skip records that will never match the query.
Add a non-unique, non-clustered index to the following tables:
tblRequestTicket.requestID
tblRequest_Service.requestID
SQL Management Studio 2005/2008 Express or Full
You can download SMS Express, which is a free version of SMS. Visit Microsoft.com/downloads or Google ‘sql management studio express’. Any version will work.
- Start SMS
- Connect to the database server. If you do not know where the database is located, you can look at the DATASOURCE property in c:\program files\fuhr software\lobbyCentral\server\lobbyCentralService.exe.config
- Expand Databases.
- Expand lobbyCentral.
- Expand Tables.
- Expand dbo.tblRequestTicket
- Right-‐click Indexes.
- Choose New Index.
- In Index Name, type “IX_requestID”
- Click the Add button.
- Click the box next to “requestID” to select it.
- Click OK to save the column.
- Click OK to save the index.
You can repeat the steps for the tblRequest_Service table to add the same type of index.
To test the result, run a Request Search query in LobbyCentral before and after the index is added.
If you have any questions regarding these procedures, please contact
[email protected].