The company I work for uses a 3rd party company to manage/develop our e-commerce database and our API. I only have read access to the database, but with read access, I am able to query all products and get what I need in about 1 second.
On our backend we use GraphQL to communicate with the API and pulling all products from the API takes 15 seconds, is full of bugs, and is unacceptable.
I have a pretty complex select query I wrote that I want to try and implement using typeorm. I only need this one query for the application.
select a.Disabled, a.recordnumber ItemID, CASE WHEN c.ParentID IS NOT NULL THEN 'IS Parent' ELSE 'IS NOT Parent' END AS ParentItem, a.SKU, b.ProductName, b.Description, a.Image MainImage, b.LanguageCode, a.CategoryID, a.SortOrder, a.Weight, a.Height, a.Length, a.Width, a.last_modified, a.FlagCancer, a.FlagBirthDefects,
d.recordnumber DiscountID, d.last_modified DiscountIDUpdated, e.AssociateTypeID PriceGroupID, f.OrderTypeID, g.RegionID, h.StoreID, d.Start DiscountStart, d.[End] DiscountEnd, d.Price, d.PriceCurrency, d.CV, d.QV, d.RewardPointsEarned RewardPoints
from INV_Inventory a
join INV_LanguageValues b on a.recordnumber = b.ItemID
left join
(
select a.ItemID ParentID
from INV_OptionItemsKeys a
group by a.ItemID
) c on a.recordnumber = c.ParentID
join INV_Discounts d on a.recordnumber = d.ItemID
join INV_Discount_ATypes e on d.recordnumber = e.DiscountID
join INV_Discount_OrderTypes f on d.recordnumber = f.DiscountID
join INV_Discount_Regions g on d.recordnumber = g.DiscountID
join INV_Discount_StoreMap h on d.recordnumber = h.DiscountID
where c.ParentID is not null and b.LanguageCode = 'en' and g.RegionID = 1
If you have any better ideas on how to get this done I'm definitely all ears and any help would be appreciated.
(Please don't reuse an alias (a); it makes it hard to follow the SQL.)
Consider the following modification. Instead of this expression
CASE WHEN c.ParentID IS NOT NULL
THEN 'IS Parent'
ELSE 'IS NOT Parent'
END AS ParentItem,
plus this LEFT JOIN
left join (
SELECT a.ItemID ParentID
from INV_OptionItemsKeys a
group by a.ItemID
) c ON a.recordnumber = c.ParentID
Just have this expression:
IF ( EXISTS( SELECT 1 FROM INV_OptionItemsKeys
WHERE ItemID = a.recordnumber
),
'IS Parent',
'IS NOT Parent'
) AS ParentItem,
(Maybe that can be reverse-engineered to typorm.)
On the other hand, since you have c.ParentID is not null, IS Parent is always the case??
Some of these indexes may be useful:
b: INDEX(LanguageCode, ItemID, ProductName, Description)
d: INDEX(ItemID)
e: INDEX(DiscountID, AssociateTypeID)
f: INDEX(DiscountID, OrderTypeID)
g: INDEX(RegionID, DiscountID)
h: INDEX(DiscountID, StoreID)
INV_OptionItemsKeys INDEX(ItemID)