hbe02

just a general question:
what type of algorithm is run when you execute an SQL statement.. like SELECT something FROM somewhere..
binary search?? hashtable if your search by primary key?
what algorithms do, SELECT, UPDATE, DELETE use? is simply using SQL efficient? how can you optimize?

one more question..
Im using SQL through ADO.NET in my C++/C# .NET programs.. what is SQL server and how is it similar/different than using the ADO.NET plugin to connect to your databse?

thanks ya'll

DaWei

Any decent database has had the bejasus optimized out of it by experts. That is why should should let it do all the work you can express in your statements, instead of doing things piecemeal and finishing them up outside. There are a number of things you can do to help it make the most of its expertise. These will generally be detailed in a section of your documentation.

I don't know dink about ADO.NET, sorry.

__________________
Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code.
Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Jimbo

IIRC, indices are usually stored as B-trees of some sort, though sometimes a hash table is used. Don't know much beyond that though... like DaWei said, they have optimizations up to ying yang.

__________________
<insert disclaimer here>
<insert shameless plug for Visual Studio here>

dark_omen

I don't know what algorithm they use in SQL to find or manipulate data, never really thought of it (I would suggest googling it, and then come back and tell us). I think using the SQL language is efficient enough, some say that using stored procedures is better and can get data better, but I've done both and I don't really notice a difference. I would google SQL stored procedures and try them out too. And a SQL server is where all the DB's are stored, and ADO.net is the package of classes that can connect to the SQL server to retrieve and change data (at least I am pretty sure that's what it does).

lectricpharaoh

SQL is an interface specification; it describes the syntax used to query a database that supports this interface. As such, the details of the implementation are transparent to the code using SQL. As long as it does the job, it shouldn't really matter what algorithm the underlying implementation uses.

As pointed out by DaWei and Jimbo, these things are optimized by professionals who know how to do this a hell of a lot better than you or I could. The algorithms involved will most certainly vary (to some extent) between different databases, and even between different implementations of the same database (to accomodate differences in the platform architecture, for example). I would also expect that operations which are semantically identical may use completely different approaches depending on the specifics of the data involved, such as data type, size of the data set, and what not. Different needs manifest themselves with different data structures, and this will certainly influence the approaches used.

__________________
And once again, Probability proves itself willing to sneak into a back alley and service Drama as would a copper-piece harlot.
- Vaarsuvius, Order of the Stick