Is there any limit on the .csv file size. eg. 10 mb ( in both manual upload and using file collector approach)
Uploading huge files on the server using file upload functionality is not recommended. The best is to place the file on SFTP and then use File collector.
If the file is enormous, it will take some time to process. The only possible way to reduce time is to multiple workflows and break the file into smaller chunks. Use each workflow to process one piece, this method will provide you with some parallelism, but eventually, data is inserted inside the same table/database. There will be a performance improvement with the concurrent workflows.
There is no file size restriction when using the SFTP way of uploading data(via File Collector).