Slow Custom Smartlist 1

[lic2chill]

Running Microsoft Small Business Financials 9.00.0115 with Dexterity 9.00.0063.0 on SQL Server 2000 sp4 and Windows Server 2003 R2

We have a Custom Smartlist built under Accounting > Account Transactions that is searching for a user = 'fred', date range of 9/1/2010 - 9/30/2010 with source = 'GJ'. This takes about almost 2 minutes to return results (40 rows).

I have built the equivalent query in Server Manager Studio and returned the 40 row result in less than 1 second. There are 46,556 rows in the table if I omit the where clause and just do the join on the GL00100 and GL20000 tables and bringing back all records takes about 2 seconds.

I'm trying to figure out what is taking so long to do this query. The standard Smartlist items return data relatively quick. Any ideas???

I've already searched the forum and other existing solutions don't seem to apply here.

TIA for help and suggestions.

 

[VictoriaYudin]

When you say "Custom SmartList", do you mean you have created a Favorite under the out-of-the-box Account Transactions SmartList? Or do you actually have some kind of a customization going on? Just want to get that cleared up first to make sure we're all talking about the same thing.

In general, you cannot compare running a SmartList in SBF to running a query directly in SQL. Do you have SBF installed on the SQL Server by any chance? If so, can you run the same SmartList there? It may take a bit longer that running the query in SQL directly, but it should not be 2 minutes. If you're able to do this in SBF on the server, that will give you a good idea for how long it should take. It should be very similar on a workstation. If it's significantly slower, then I would suspect a network connectivity or resources issue.

I don't have SBF installed anywhere anymore, but to give you an idea of GP 9.0 on SQL 2000, an Account Transactions SmartList with about 10 columns, searching by 3 criteria takes less than a second to return 61 rows (on a workstation, not directly on the server).

Victoria Yudin
Dynamics GP MVP 2005 - 2010
Use Crystal Reports and SSRS with GP:

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[lic2chill]

Victoria,

Thanks for your very quick reply. I realized that the query would run faster using the query tool, I just wanted to make sure it wasn't some indexing issue.

Yes, a favorite has been created by one of the users. I do have SBF installed on the SQL Server and the Smartlist took about 48 seconds to run vs. 1 min 50 seconds on a workstation.

That's a fairly significant difference. I can't completely rule out network connectivity but I'm gravitating more toward resources. Any clues about what to look for?

 

[VictoriaYudin]

The easy stuff, of course, would be RAM and processor (on both the server and the workstation). The size of the page file on the server and the amount of RAM that is allocated to SQL server will make a difference. Also, what edition of SQL Server? If you're running MSDE that can certainly contribute to less than optimal results because of its limitation on concurrent transactions.

48 seconds directly on the server sounds slow as well, but this may depend on the total number of records in your tables. What's the total count of all the records in the following 3 tables:
GL10001
GL20000
GL30000
The system I am testing on has just under 55,000 records in those 3 tables combined, and like I said, I am getting 61 results back almost instantly.

Some other things to test would be whether time of day (ie how busy the network is) makes a difference. If you run the same SmartList with no other users in SBF...or at a time of day when there is little activity in general, are the results the same?

Victoria Yudin
Dynamics GP MVP 2005 - 2010
Use Crystal Reports and SSRS with GP:

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[lic2chill]

We are a small business and only have 5 users. Rarely is anyone taxing the system. Server is dual Pentium, 2 GB RAM, memory to SQL Server is dynamically allocated up to 2 GB. We are running SQL Server 2000 Ent Ed.

GL10001 - 11 rows
GL20000 - 47,295 rows
GL30000 - 178,985 rows!

The owner is very concerned with keeping historical data but is realizing that this is probably affecting performance. What is the best way to archive the data? Owner doesn't want to delete historical data before he is certain we can re-load data elsewhere if needed.

 

[VictoriaYudin]

I don't know if this is such a huge amount of data to warrant trying to archive it. There is really no easy process for that.

Is this a dedicated SQL Server for SBF? If not, what else is it doing? 2GB of RAM is the minimum I would ever consider for a SQL Server. Again, that is just one of the things to look at, there are many others. There is also recommended maintenance for SQL Server, we typically set up weekly SQL jobs to re-index, update statistics, etc. Do you know if you have something like that set up?

There are so many variables with this, it's not really something easy to cover in a forum post. If this is a concern, I would recommend talking to your SBF partner (or someone experienced with SBF/GP and SQL Server) so they can look at your SQL Server setup and possibly recommend some improvements.

Victoria Yudin
Dynamics GP MVP 2005 - 2010
Use Crystal Reports and SSRS with GP:

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[lic2chill]

No, it is not dedicated but that is something that I've brought up before with management. I will push a little harder to get SQL Server off of this machine.

As far as the maintenance, I have been doing this periodically but it's probably a good idea to automate it. I'm an IT department of 1.

The main problem is that MS is dropping support on SBF and everyone says, "just upgrade" but small business budget says otherwise.

 

[VictoriaYudin]

There are still plenty of partners that will support SBF, but it's starting to get difficult...we just upgraded our last SBF customer to GP, so like I said, I don't even have it installed anywhere anymore. The concepts for most of it are just like GP, but GP 9.0 is also ending support in January of 2011. And, of course, support for SQL 2000 ended a while ago. :-(

Victoria Yudin
Dynamics GP MVP 2005 - 2010
Use Crystal Reports and SSRS with GP:

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[lic2chill]

Thanks for all your help. I've just finished setting up the maintenance plan for the databases and see if this has any positive impact on performance.

I'll check it all again on Monday and see what happens. Maintenance will be performed Sunday AM. Have a great weekend.