none
How to speed up remote File Table access? RRS feed

  • Question

  • 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


    Thursday, June 25, 2020 2:25 PM

Answers

All replies