I know that
locate has to have a database generated and is much faster, and that
find does not need a database generated and is not as fast. So in what situations is
locate more efficient/affective/give a better end result?
As you said, locate has a database and is therefore much faster, while find has to traverse the filesystem. But locate's database only contains filenames (with their paths), while find can select files based on other meta data like mtime or size. So it is my resume versatile. Also the database has to be updated in a batch effort, usually nightly, so files which have been created modified recently do not appear correctly. Also you can expect every modern Unix system to have find available, while not all systems have locate's database enabled.
So, locate is faster, use it to find the location of a file, find is more versatile. There is no universal better.
locate database is updated once per day. So if you need to search for anything that is newer than one day, you may need
find for that.
On the other hand, the larger the filesystem you need to search, the faster
locate will be over
find. Especially so if the filesystem is on a network share.