I've been working on a vb.net/mssql system (the front end is winforms) that uses file tables for pretty small part images. The file sizes range from about 50kb to 800kb. Most are in the 300kb range. There are about 3000 of them. During development I set
up SQL Express 2017 on my client's network for them to do testing. All the areas of the program that make use of the images run fast on their local network. There's a part of the program where we pull up images of all the parts for a single customer. Even
customers with 100 active parts pull up fast.
We recently moved this to an Amazon cloud service so we could test that way. Everything runs as expected. We've got it set up with 16GB of RAM and SQL 2017 Standard. We originally set it up with 32GB but that cost a lot
more and reducing it to 16GB didn't make a noticeabledifference in performance. Everything runs fast except the feature I mentioned above.
Instead of under 1 second, it can take half a minute. I isolated the problem by running the view that populates the front end by using SSMS remotely. The view I created to populate the front end runs VERY fast when I comment out the line that gets the file_stream
data. But when I put that line back in, things slow down tremendously.
Here's the code in my view:
SELECT
p1.CustomerID
, p1.PartID
, p1.PartNumber
, p1.PartDescription
, p1.PartAKA
, dbo.FNC_PartRecipeHTProcessCodes(p1.PartID) AS ProcessCodes
--, ISNULL(pim.file_stream, (SELECT TOP 1 BlankImageRedBorder FROM dbo.tblOddJunkForWORP)) AS file_stream
,
CASE WHEN p1.OpSignOffPerson IS NULL THEN CAST(0 AS INT) ELSE CAST(1 AS INT) END +
CASE WHEN p1.QaSignOffPerson IS NULL THEN CAST(0 AS INT) ELSE CAST(1 AS INT) END +
CASE WHEN p1.SaSignOffPerson IS NULL THEN CAST(0 AS INT) ELSE CAST(1 AS INT) END +
CASE WHEN p1.MlSignOffPerson IS NULL THEN CAST(0 AS INT) ELSE CAST(1 AS INT) END
AS NumTFSSignOffs
, ISNULL(nwo.NumberOfWorkOrders, 0) AS NumberOfWorkOrders
, pd.PartIsDormant
FROM
dbo.tblParts1Header p1
INNER JOIN dbo.VW_PartDormancy pd ON p1.PartID = pd.PartID
LEFT OUTER JOIN SupportFiles.ftblPartImages pim ON p1.PartImageStreamID = pim.stream_id
LEFT OUTER JOIN (
SELECT
PartID
, COUNT(*) AS NumberOfWorkOrders
FROM
dbo.tblWorkOrders
WHERE
DATEADD(MONTH, (SELECT TOP 1 NumMonthsUntilPartDormancy FROM dbo.tblSystemSettings), DateTimeCreated) >= GETDATE()
AND DateWorkOrderVoided IS NULL
GROUP BY
PartID
) nwo ON p1.PartID = nwo.PartID
Again, it's lickity split with the line commented out above but very slow with it. Even when only 40 or so rows are returned it takes like 10 seconds. Their regular customers have over 100 parts so we're talking about half a minute most of the time. That's
not tolerable.
Is there a way to make file table access faster? I searched and couldn't find anything useful. And I really would rather not have to store the images on their local network because they have 2 locations in 2 different cities. I'd have to store them at both
locations and write something to keep them in synch.
I should note that ALL the 3000 images are in a single folder. I have code to split them up into sub-folders but I haven't implemented that yet. I know that when there are too many files in a folder, Windows has a hard time finding things and can slow down.
I've done this sort of thing before on other systems where I create sub-folders and limit the number of files. It makes a big difference even on a local network. But I didn't expect 3000 files to cause such a slowdown when it's remote like this since even
when they are all in the same folder on a local network it runs very fast. I've never run into problems until hitting in the tens of thousands of files. Not with these small file sizes anyway.
Thank you!
Keith
