We have a Excel sheet which have more than 10000 records.We thought to dump the data into lookup and iterate over it, but before doing this process it would be good to know where lookup data will be stored, if it uses system memory/cache will there be any side effects or memory issues with this much amount of data.
If Lookup is not good option in this case then looking for alternatives.
LookupTable data is stored in memory and is not written to disk. You would need to investigate the memory usage for your particular table to determine if it would have any adverse effects. Depending on what you want to do, you may find iterating your data in memory faster than attempting to do so in an automation. I doubt there would be much difference time-wise if you iterated the data as a DataTable within an automation than simply iterating it in Excel within an automation. Time savings would be much greater if you performed the iteration in a C# script, but you'd need to be versed in C# to do that effectively.