Sage 100

I just received this from one of my customers, I have found a query running in Sage by Mas_User and crushing our system. Can you please take a look and see if you can optimize it? Maybe make it a stored procedure and/or make the select statement more specific and add more to the where clause? The query is: (@0 varchar(8000)) SELECT * FROM [AP_VENDOR] ( NOLOCK ) WHERE [VendorName] > @0 ORDER BY [VendorName] , [APDivisionNo] , [VendorNo] Has anyone ever heard of an issue like this? Customer is on SAGE 100 V2015 PU1 Premium. I am thinking it may be a ALE lookup?

FYI - If you run a SQL profiler Job and catch this query being executed it will tell you what program in SAGE is executing this statement

That is very helpful. I will do that. Thanks Brett....

I sent you a screenshot of likely culprit. Also if you have the DEBUGIT=-- in the [ADO] section of the various INI files on the App Server, your Profiler Trace will show you the MAS program and Line No. If their IT didn't tell you the SQL query that's running long, the Duration column in Profiler is usually a quick identifier. The unit of measure is milliseconds (ms) so 45,111 for example is about 45 seconds.

Besides Vendor ALE lookup by Name where Find is clicked without search criteria, It could also happen when you start typing the vendor name in a Vendor No field. That would be the flippin' Auto-Complete feature. If your Profiler Trace shows that program / line as the culprit, you can test by turning off vendor auto-complete in either User Maintenance or System Configuration. I find that on Sage 100/Premium where SQL Server is a separate VM, auto-complete has a big impact on latency and have been turning it off for SQL clients.

LOL,, from customer regarding use of blank name lookups"" ""Heck, I do it all the time. I am sure others forget and get caught too.

Would it help to index the Vendor Name column. currently it is not indexed?

Are you sure it's not indexed? If you goto to SQL Mgmt Studio, there should absolutely be an index called KNAME with columns VendorName + APVendorNo + VendorNo. Also in the Profiler trace if the pvx program / line points to a pvx SELECT or READ stmt, there should be a KNO=""KNAME"" clause in it. It sounds like that is already the case since Brian said ""ORDER BY [VendorName] , [APDivisionNo]"". If index is in fact physically missing then have Brian run this while in the correct database: CREATE NONCLUSTERED INDEX KNAME_AP_Vendor ON dbo.AP_Vendor (VendorName ASC, APDivisionNo ASC, VendorNo ASC )

You are correct there is an index.. ... We are seeing this query being used by most of the AP users so I think you thoughta about the Autocomplete causing this is right on. I am going to disable it to see if it helps.