Has anyone been able to improve the querying performance of the PartTran table on the server? We have a little over 11 million rows and to query the entire table it takes around 20 minutes. It just so happens that one of the TranTypes I need to use accounts for 92% of the data in the table. That TranType is STK-MTL. I have created an Index just to see if that would improve it but I am not experienced in that area. Ideally I want to query the Index from three views that I created which have TranType criterias on them. The below example is one of my views:
SELECT PartNum AS PartTran_Issue_PartNum, TranDate AS PartTran_Issue_TranDate, TranQty AS PartTran_Issue_TranQty
FROM Erp.PartTran AS PartTran_Issue
WHERE (TranType = ‘STK-MTL’) OR
(TranType = ‘STK-KIT’)
You want to get back all 11 million rows? That seems pretty large
What’s the end goal? That’s a lot of data.
20 minutes is excessive , what was the new index you created ? The values in that field are not varied enough to benefit from an index just on that.
How’s your index fragmentation overall in the DB?
The example view that I posted in my question will return back around 10 million rows. I would like to get the Last TranDate and Last TranQty by PartNum out of the view. I was not able to get the LAST_VALUE statement to work in the SQL View but I was able to get it to work when querying the view. Screenshots below are the index setup. Maybe I’m going down the wrong path on this, please feel free to tell me to snap out of it if I am.
For dense indexes they recommend just using an inner join.
So your code would look something like:
SELECT pt.PartNum, pt.TranDate, pt.TranQty
FROM ERP.PartTran pt
INNER JOIN (
SELECT PartNum, MAX(TranDate) AS _max
FROM ERP.PartTran
GROUP BY PartNum) x ON pt.PartNum=x.PartNum AND pt.TranDate=x._max
hi ,
how about if I would like to create query to calculate as opening quantity from various part, I call PartWhse as primary then inner join PartTran as child with various TranType to calculate the opening from first day of history, and this was killing me if the user would like to call all part or by plant.
Is there any better solution for this?
Especially our current row has grow in multi-million rows there.
yes query with top level and subquery level will give you what you after, use Minimum TranDate aggregation function and group by on the subquery after selecting relevant link fields between the two queries.
You may wish to think about using azure data synch to create a reporting database. Or just think about using a reporting database in general for strategic datasets like the 11 million rows you have.
That is a lot of data like Jose said and to query that willy-nilly at any time for any reason isn’t a great approach. Don’t get me wrong, I do it too, but if you can use a reporting database that is ideal. See the " Distributed Applications:" section under the “When to use” section in the link.
Furthermore, adding indexes for reporting purposes may have a conflicting effect on the way the application uses the table (someone here can correct me if I am wrong). They won’t necessarily read and write optimally with the same indices.
@utaylor Yes you are correct the post is quite old but I still need to find the solution for my report.
Thus will study the suggestion to Sync for Azure which I’m not experience for this.
I really frustrated what I’m trying to collect the period cut-off opening on hand which Epicor does not keep it.
User may need to call back the previous opening and closing balance might be for a batch of part on specific warehouse, the problem was I need to recalculating back from day 1 transaction in order to get the opening for all the part which this was a pain-full task.
