Possibly save 2 hours of your time: I have only encountered this more recently in an application that deals with big dataset.
In this scenario, the application needs to provide a feature to download full datasets. And this dataset is huge (~75MB to ~125MB) in size. And there are many datasets.
It took awhile to query this data and do some calculation and send it back to the client app. So we decide to cache this data at the application level using System.Runtime.Caching.MemoryCache for subsequent faster performance. As you can see, the problem can become bigger as more datasets are downloaded and cached.
There are few options in no particular order:
- More expensive but easiest solution may be to increase memory on physical server to account for the size of the cache that can grow to.
- Recycle the application pool after reaching maximum virtual memory usage. See here for specifics.
- Set the CacheMemoryLimit on the MemoryCache object. This sets the amount of memory on the computer, in bytes, that can be used by the cache.
Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
- Execute SQL Task – Get the records that need to be processed
- Foreach Loop Container – Loop through each record
- Execute SQL Task – Process one record at a time
Create two user variables:
- i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
- i.e. CollectionID (Int32) – the current ID to be processed
The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?
You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).
Now you want to store your Result Set into a user variable for looping through later.
In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.
We want to a user variable to store the current processed id.
You can specify a query or stored procedure to process each record.
i.e. [schema].[usp_ProcessRecord] NULL, ?
The above example has two input variables where one is always NULL and the ? represents an input parameter.
In this example, the input is a large integer.