I’ve tested the CSV Import with 30,000 row files on a limited shared host quite successfully. On a more robust hosting solution provider, it should be possible to import 50,000 CSV files. With that said ever host is different so it is really impossible to say what the max is but a safe conservative number should be 10,000.
Categories are created as needed but hierarchy is not. Generally this is not an issue because the hierarchy can be adjusted after the import. But since you plan to have hundreds for each parent, that would be quite laborsome.
My guess the number of categories has to do with this question (you?) sent via the contact form:
“how do I get states as category and cities as sub-categories – are the created on import?”
My suggestion would be to not to use categories for this. I would use the address fields for this data. Yo can allows users to filter by this by enabling the Region and Locality permalinks feature on the Connections : Settings page under the Advanced tab. With these enabled, those parts of the addresses become links that when click will filter the results by the region/locality that was clicked.
To better expose this to your users you could add the State (region) links to you sites nav by adding custom links to it. Many users do this another solution would to have a template custom developed for you that would allow you visitors to filter by that data just like they can with categories. I do provide custom development solutions and could provide a quote. You can contact me via the Contact link at the bottom of the page if you would like to pursue this option.
Hope that helps!