How efficient is searching in SQL database?
Hi I'm trying to write data that I have std::map<key, value>
into a sql database. In map the keys are sorted for easy lookup but when I would create a table in sql of map items, how hard would it b开发者_JAVA技巧e to search through the table to get a record by its key id?
The searching would be easy, the efficiency would depend on whether or not your indexing correctly.
Around about as hard as learning how to use the SQL SELECT statement.
What may well be inefficient would be constructing and deconstructing multiple SQL select statements where all you want is a different value in the "where" clause for the variable.
Stored-procedures could well be far more efficient for doing this.
You question is vague, but it seems like you're comparing apples and oranges. SQL is designed to efficiently search scalable data. C (or any other language) key-value pairs are limited to RAM, so are not very scalable. There are overheads in communicating to a RDBMS. There is memory pressure, design efficiency (i.e. your chosen data types and indexes), the algorithm C++ implements for the lookup (hash/b-tree) etc.
At the end of the day, the correct question to ask is, "Which tool is best for the job?" and leave it at that.
Assuming that you are inserting the data from a map into a database table, you have the primary key for your table i.e. the key you use for the map and as map doesn't allow duplicate keys you have unique key for each record.
Create an index on the key for the table. Do create an index, else all your queries will do a full table scan and all the benefits of having unique rows goes down the drain. But beware, if you map has only 100 rows or something, you will unnecessarily create humongous overhead by creating an index on the table.
Creating indexes would be more or less same across datbases, but it would be quite difficult to estimate the efficieny without knowing which database you are using and how much data is going to be stored in the table.
If the data fits to the memory std::map would be much more efficient then any DB.
Once I've tested against Sqlite3 with in-memory database and std::map was faster by an order of magnitude (and sqlite is very fast in this case faster then any other RDBMS I've seen)
Reason: it provides direct access to data structure it has no any intermediate layer and allows to walk over tree very fast.
However if your data is very big or you don't want to use too much memory RDBMS is good solution and given index on key it would be quite fast.
精彩评论