SQL inherently inefficient?

It is very easily to write crappy and highly inefficient SQL, and code that handles data via SQL; there might even be some inefficient implementations of the SQL spec. But SQL itself is not inherently inefficient.


Also, note that the code snippet you posted is not "SQL." It looks like it's code using a wrapper class to construct SQL queries on the fly. This can be inefficient, especially if you don't use the class well, but in general it's going to be such a trivial overhead that it isn't worth panicking over.

C# 3.0 is going to be great for people like you. Check it out. This is right up the alley of code-integrated querying, and a better abstraction than using a separate class to handle it (although that's approximately what happens behind the scenes anyway).

How do you propose to handle queries such as ((name LIKE 'l%ser' AND value='something') OR NOT value LIKE '%lol%') AND sex='apex'?

It seems a bit clunky to me.

[blatent flamebait]When mySQL finally decide that they should become a serious database and that they need to finally implement stored procedures, a working referential integrity framework as well as the rest of the other 'standard' features, there won't be a problem anymore. Seriously [grin][/blatent flamebait]

Anyway. Of course, the overhead is minimal. Parsing a string isn't THAT hard, and SQL typically isn't used in performance critical code. In fact, keeping the load off the server is typically much more important than serving the client really fast.

Your "class" examples could be done, but I still point you at the strings you pass to it. I helped implement an OOP abstraction for a relatinoal database at work. It sucked, big time. Permitting nested queries with such a framework is a pain in the ass. (Not that it would matter with mySQL, it doesn't implement them either [grin])

What I did was registering a class for each table, and provide a Update() method. For the queries, I passed an array of the fields I wanted (still registered), and provided methods to build the where conditions. (Each class had it's own relations stored so it would automatically build the annoying "redundant where conditions") In the end, I still had to provide my own query mechanism since some weird query are hard to implement this way.

I wasn't referring to the efficiency of SQL itself so much as I was referring to having to build a string, and then break that same string back apart.

For something very speed critical, is there a better way to handle this?

I thought mySql had prepared statements? If so, this sort of execution should be a thing of the past.

Quote:

Original post by gandolf1212 I wasn't referring to the efficiency of SQL itself so much as I was referring to having to build a string, and then break that same string back apart. For something very speed critical, is there a better way to handle this?

No. The DBMS waits for a string.

Edit: Like has been said, stored procedures are the way to go, but mySQL doesn't offer them yet.

Yeah, I see what you guys are saying - that the speed trade off is worth the flexability. It does work well, just conceptually seems like a bad idea.

I do wonder what percent of time is spent parsing the string and what percent is acctually fetching the data.

Quote:

Original post by gandolf1212 I do wonder what percent of time is spent parsing the string and what percent is acctually fetching the data.

It's minimal and not worth worrying about. [smile]

If you really want to gain speed in this area, I think you would have to come up with some sort of optimized compiled queries (IIRC, MSSQL's extended stored procedures do something like that). Note that in most database engines queries are cached anyway.

The sample code you provided look pretty horrible to me anyway. As already pointed out, when queries get more complex (joins, unions, cursors, etc.), it 'll make the code pretty much unreadable.

Stored procedures (if available), indices and well structured queries are still the best way to minimize execution time.

What you're talking about isn't really a problem with SQL, but the C++ interface to it. SQL isn't too inefficient in itself (and in any case, the time it takes to parse a query is nothing compared to the time it takes to execute it on a real database, so it's not like it makes much difference)

But SQL is made to be a separate language which your program (or language) can interface with. If you made it OO, it'd be easier to work with from C++, but how about other languages that don't have proper (if any) OO support?

Quote:

(Not that it would matter with mySQL, it doesn't implement them either )

mySQL does have nested queries. It's a recent addition though, but they're there. [wink]

That kind of abstraction sounds like Ruby on Rails

Quote:

For something very speed critical, is there a better way to handle this?

As gets said time and time and time again you need to actually try it and measure things before you start saying it's to slow.

For most definitions of "very speed critical" you wouldn't be talking to some remote server in the first place. And if for some reason you had to anyway a few trivial string manipulations probably won't even show up in the noise compared to round trip and processing time of the query.

According to the docs MySQL does have stored procedures. I haven't played with them myself so I don't know what if any limitations there are with them.