Sage 100

MAS200 SQL 4.50.3 - Client is complaining of very slow performance in printing the GL Detail Report (among other GL reports) since the upgrade from 3.71 SQL a few weeks ago. I can see this slow performance at the server as well, so it's not a workstation issue that I can tell. I have purged 2005 and prior summary/detail history, and have imported the ""Deleted"" status to 7,000 of the 11,000 accounts as they were set up in error in older years, and now I'm just waiting for Period End to purge them out. Does anyone have any suggestions to speed these guys up as SQL does not offer the C/S ODBC Driver?

Did you check AV?

Sounds like a database maintenance issue. SQL server should be pretty snappy for pulling reports. It's been a long time since I did any DBA type stuff, but we used to run into this when we were running queries, and we would add fields to the index and optimize our queries. Or I could be totally wrong.

Tested disabling AV, turning on ""low speed connection"" and ""spawn tasks"" in User Maintenance, and no difference. Does anyone with SQL optimization experience have some general suggestions I can pass on to the client's DBA to implement?

But is it also slow with non-G/L reports? Maybe 20 - 30 % of clients I've upgraded from legacy to BF MAS complain the G/L Detail Report is notably slower than the non-G/L reports (from other BF modules). But if you do think it's MAS SQL specific, the cache settings for all reports are stored in a file called Sy_SQLOptimize.m4t in MAS_SYSTEM folder (not the database). If you DFDM that you can see a field called OptimizationType for the 6 GL_DetailReport_rpt records. It represent a cache mode. When they did 4.45 and 4.50 SQL testing they built a performance tool called PT (and its cousins PTR and PTS). At Summit last year they showed us how this tool (which was run from SYZCON) could be used to run a trace to figure out if an un-optimal cache setting is being used for a report. Then you DFDM adjust it to the right setting and the report ran faster. What I'm saying is you can potentially have your Dev team test the GL Detail Report using the PT tool.

Thank you for your time in answering. Not many users to begin with, but all other module reports seem to run at ""normal"" speed. It's really just the GL Detail and all Financial reports. With over a million records in the GL_DetailPosting table alone, I know that's a lot, but not for SQL. I have closed out of the client connection for the day, but isn't DFDM gone from MAS200 4.50 SQL now? I will research on this ""PT"" you speak of.

That's beautiful...just beautiful. Thank you sir.