You’re offline. This is a read only version of the page.
Announcements
Hi All,
How can we go about requesting a change (if possible) for the T-SQL executed against the database when opening items in POS/SO and HQ Manager?
I can post the code for each later but HQ and SO Manager take around 14+ seconds to execute locally. It seems as if the queries execute 4 times and I don't understand why they need to do this?
We currently have 40-50 users hitting one database server that houses HQ and one Store Database. Refining or tuning the queries would help us get out of our current predicament as we are having slowdowns and locking occur.
Users constantly complain that RMS is slow and they are suddenly kicked out of the program. We run 1Gbps network and our 2012 Sql Database Server VM runs with Quad 3.2GHZ, 24GB of DDR3 and uses a Fusion IO solid state drive. We are not I/O pegged but RMS just seems to run terribly due to the queries for the Item table. We currently have 200,000 items and growing.
Any advice would be great!
*This post is locked for comments
I have a nightly maintenance plan that runs and rebuilds indexes to prevent fragmentation.
Here are the queries that I picked up with SQL Profiler that are running when opening SO Manager and clicking the item Tab:
SELECT ID, ItemLookUpCode, ItemType as ItemTypeText, Description, AvailableQuantity = Quantity - QuantityCommitted, Price, PictureName FROM Item Where Inactive = 0 UNION
SELECT ID, ItemLookUpCode, CASE ClassType WHEN 0 THEN 2 WHEN 1 THEN 8 WHEN 2 THEN 4 END, Description, null, null, '' FROM ItemClass WITH(NOLOCK) ORDER BY ItemLookupCode
GO
SELECT ID, ItemLookUpCode, ItemType as ItemTypeText, Description,CASE Inactive WHEN 0 THEN 'Active' ELSE 'Inactive' END As Status, AvailableQuantity = Quantity - QuantityCommitted, Price, PictureName FROM Item WHERE Inactive = 0 UNION
SELECT ID, ItemLookUpCode, CASE ClassType WHEN 0 THEN 2 WHEN 1 THEN 8 WHEN 2 THEN 4 END, Description,null, null, null, '' FROM ItemClass WITH(NOLOCK) ORDER BY ItemLookupCode
GO
SELECT Item.ID, ItemLookUpCode,ItemType as ItemTypeText, Supplierlist.reordernumber, Item.description,AvailableQuantity = Quantity - QuantityCommitted, SupplierList.Cost,Item.SupplierID as PrimarySupplierID,SupplierList.SupplierID FROM Item left join supplierlist on Item.ID = Supplierlist.ItemID
WHERE Inactive = 0
UNION
SELECT ID, ItemLookUpCode, CASE ClassType WHEN 0 THEN 2 WHEN 1 THEN 8 WHEN 2 THEN 4 END, null,Description, null, null,null,SupplierID FROM ItemClass WITH(NOLOCK) ORDER BY ItemLookupCode
GO
SELECT ID, ItemLookUpCode, ItemType as ItemTypeText, Description,CASE Inactive WHEN 0 THEN 'Active' ELSE 'Inactive' END As Status, AvailableQuantity = Quantity - QuantityCommitted, Price, PictureName FROM Item UNION
SELECT ID, ItemLookUpCode, CASE ClassType WHEN 0 THEN 2 WHEN 1 THEN 8 WHEN 2 THEN 4 END, Description,null, null, null, '' FROM ItemClass WITH(NOLOCK) ORDER BY ItemLookupCode
Hi Chazh,
If you are using SQL 2012, then less indexing will help you out. Since in SQL 2008 r2 Indexing was recommended for the faster processing for the date, but in SQL 2012 is not like data.Get your code reviewed.
#1
André Arnaud de Cal...
294,161
Super User 2025 Season 1
#2
Martin Dráb
232,942
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
