Slashdot Mirror

I think that the chart is rather accurate. I know the people that made it and one of them is in the SQL Standards committee (Åke Persson).

It may be really picky and unforgiving, however, and even a minor deviation from the standard might disqualify a feature from being listed as supported for a certain database. I can't say that that isn't the case. All I'm saying is that they guys that made the list know what they are talking about.

Åke Persson is also a compiler expert and he wrote this tool:

http://developer.mimer.com/validator/index.htm

You can use it to validate your SQL to SQL-92, -99 and 2003. Pretty nifty if I may say so.

Yes:

http://developer.mimer.com/validator/comparison/up d_comparison_chart.tml

Well, you can still do it the old way. And they don't necessarily send a perfumed paper letter to all developers announcing that they support the standard syntax.

All database vendors want you to use their proprietary constructs. But they also want to make it easy for you to move to their database. So in some cases they support the standard, but doesn't announce it.

Here's something you can try. INFORMATION_SCHEMA is the standard way to inspect a database to find tables and columns for instance. SQL Server 2000 supports it, but they aren't really encouraging its use over their proprietary approach. They make it really hard to find.

Here is a good comparison that I use:

http://developer.mimer.com/validator/comparison/up d_comparison_chart.tml

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere mortals should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

I am not worried about mere mortals, I'm worried about your application ID and password getting into the wild and picking up the pieces as someone with that information goes trouncing through the tables your application had underlying rights to in the database.

2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.

See OPENXML in SQL Server or this site http://vyaskn.tripod.com/passing_arrays_to_stored_ procedures.htm

3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.

If you have memory pressure on your database server, your query plan may not be around the next time your run it. From my understanding in all platforms, stored procedure query plans will persist longer then ad-hoq queries.

4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.

What's the difference in time and resources in writing the same code in a procedure one time instead of writing it twice at the end of your long death march of a project?

5) Buy or make a code generator that will generate data tier code for you (and possibly other code).

Yea, because we all know that code generators out there do such a wonderful job in generating high performance, efficient code.

6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart [mimer.com] this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here [mimer.com].

Unless you are writing an application to sell to the public trying to achieve database independence this is at best a pipedream unless you write code that is really simple. At that point, I hope the rest of your code is absolutely fast because your application performance is going to suffer due to poor dynamic SQL.

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere mortals should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

I am not worried about mere mortals, I'm worried about your application ID and password getting into the wild and picking up the pieces as someone with that information goes trouncing through the tables your application had underlying rights to in the database.

2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.

See OPENXML in SQL Server or this site http://vyaskn.tripod.com/passing_arrays_to_stored_ procedures.htm

3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.

If you have memory pressure on your database server, your query plan may not be around the next time your run it. From my understanding in all platforms, stored procedure query plans will persist longer then ad-hoq queries.

4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.

What's the difference in time and resources in writing the same code in a procedure one time instead of writing it twice at the end of your long death march of a project?

5) Buy or make a code generator that will generate data tier code for you (and possibly other code).

Yea, because we all know that code generators out there do such a wonderful job in generating high performance, efficient code.

6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart [mimer.com] this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here [mimer.com].

Unless you are writing an application to sell to the public trying to achieve database independence this is at best a pipedream unless you write code that is really simple. At that point, I hope the rest of your code is absolutely fast because your application performance is going to suffer due to poor dynamic SQL.

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere "mortals" should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.

3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.

4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.

5) Buy or make a code generator that will generate data tier code for you (and possibly other code).

6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here.

I have plenty more where that came from, but the wife needs the computer. Good luck though.

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere "mortals" should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.

3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.

4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.

5) Buy or make a code generator that will generate data tier code for you (and possibly other code).

6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here.

I have plenty more where that came from, but the wife needs the computer. Good luck though.

SQL:1999 has been defined for years and some of the DBMSs have actually implemented some of its features. Yes, SQL:1999 is big and perhaps bloated in comparison with SQL-92, but it's not that bad if you concentrate on the core parts. Some of the news in SQL:1999 are actually clarifications on stuff in SQL-92.

SQL:2003 will probably be agreed on this year.

- So I find it strange to use the more than a decade old SQL-92 as the platform for a book published in the year 2002.

Another thing: I'd say it's "ISO SQL" nowadays (or ISO/IEC SQL), not "ANSI SQL".

Apart from that, I've put the book on my Safari bookshelf and look forward to reading (at least parts of) it. Unfortunately, it seems that Safari's index of the book is currently lost.

I like the approach where you start by thinking about the standard way to do it, and then try to squeeze your design into a real-World product. It's too bad that the official standard isn't online; fortunately, it's possible to get something close to being official. When trying to set up an initial, standards compliant schema, it's nice to have an evaluation-installation of Mimer SQL to play with. I wish it were open source or at least had an official price tag.

I expect that the book could be relevant in connection with a page about SQL differences that I've started writing, after having had to port some SQL from PostgreSQL to MSSQL.

Here's a table that summarizes the standards compliance for a few database systems.

I'm not happy that this is required either but there you are.

Take a look at this table for some info on standards compliance.

MySQL is not on the table but I suspect that it would fail terribly.

For our product (on Windows, alas) we use Mimer SQL that is available on several platforms. Granted, they are closed source, but runs on several platforms, and a trial version (up to 10 concurrent connections) are freely downloadable.

If you want to compare databases, check out this comparison chart.

http://developer.mimer.com/validator/comparison/co mparison%20chart.tml

If you want to know if a statement is ANSI/ISO compliant, use the Mimer SQL Validator. It lets you validate against SQL-92, SQL:1999 and SQL:200x (Draft).

You can find some good documentation here

It is the documentation for a proprietary database called Mimer SQL Engine but it is very close to the SQL standard so it should work on many databases.

Now, admittedly, many databases are not adhering to the standard very well but that situation is actually improving.

You can also try the SQL validator they have here. It'll help you adhere to the standard. You might learn a thing or two too.

In many cases you can do the same thing both in a proprietary way and in the standard way for any given database. Given that choice I'd suggest that you use the standard.

You can find some good documentation here

It is the documentation for a proprietary database called Mimer SQL Engine but it is very close to the SQL standard so it should work on many databases.

Now, admittedly, many databases are not adhering to the standard very well but that situation is actually improving.

You can also try the SQL validator they have here. It'll help you adhere to the standard. You might learn a thing or two too.

In many cases you can do the same thing both in a proprietary way and in the standard way for any given database. Given that choice I'd suggest that you use the standard.

You can likely change to a different database later as long as you don't use proprietary functions. Make sure you write standard SQL.

And before you say that what you write is SQL... You might not be. Check a few of your statements with an SQL Validator

Check out Mimer SQL Engine

http://developer.mimer.com

runs on Windows, Unix, Linux and VMS

Client/server. Multiuser.

Supports Embedded SQL, ODBC and JDBC

Advanced optimistic transaction control

High level of SQL standard conformance

Stored procedures

For any questions, feel free to ask me /per@mimer.se

If you want a database that's really easy to administer, performs with the best of them, follows the SQL-99 standard, runs on Linux and is affordable, take a look at mimer.com for a free download.

Yeah right!

I am implementing a SQL based database server on Linux (Mimer). I would love to be able to do it that way, but it would require a new level of asynchronousness from the Linux kernel. In my opinion, one problem with most UNIX OS implementations is that the OS thinks it can suspend CPU processing too easily. How could the database server schedule (say) 10 asynchronous file read or write requests and be notified when they complete? And how do you do efficient and scaleable asynchronous net I/O? poll() and select() don't scale well when you have thousands of simultaneous connections. I am not aware of any I/O completion port architecture on Linux.

I understand that TUX is able to get much greater performance under Linux by not using the conventional kernel APIs and doing the stuff directly in the kernel. But I feel very reluctant about moving an entire SQL database server into the kernel. There must be better ways...

Another problem with the "optimal" single-process event-driven approach is that it tends to turn your code "inside-out". If you have subroutines that calls each other, and when you are 20 routines deep on the stack, you get a database cache miss and need to perform an asynchronous database disk read. How do you reschedule to another task in the process?

user-mode threads

Turn your code inside-out and resolve all cache misses at top level (ugh!) (or set a flag and return up to the main dispatcher? And how do you get back?)

co-routines (anyone have a good package?)

abuse setjmp() and longjmp()

Generally, only the first alternative have proper debugging support.

What does the server DO?

How much I/O is done per transaction?

How much CPU-bound processing is done per transaction?

Does the server use threads? Is there a pool of threads (how many) or is a thread created for each transaction?

What OS calls does the Linux port use? Which socket calls? Is a new connection created for every transaction? select() or poll()? blocking or nonblocking sockets? etc, etc...

Is the data file opened with O_SYNC to emulate transactional properties?

How were the binaries compiled? Which compiler?

Given that this site seems to be very windows centric, how do I know that the Linux port is done properly by someone who know what she is doing? Can I see the source please?

It would also be nice to see some profiling data from the benchmark. Where is the bottleneck?

CPU utilization

Kernel/user mode utilization per processor

network packets per second

disk traffic per second

Also note that their term "transaction" is probably not a database transaction with ACID properties (as the industry standard TPC benchmark mandates). An ACID transaction needs to store updated data permanently on disk (not file buffers) before the transaction commits. I don't think the test does this.

I am implementing a database manager on Linux and there are some areas where I think NT has better capabilities than Linux (disk I/O for ACID transactions), but that is another story, since this benchmark probably doesn't do ACID transactions.

per@nospam.mimer.se
www.mimer.com

Since that's what my univ runs, It's what i mess around with at home. (That and the fact that it's made in Uppsala, where I live ;)

Anyway, check it out at http://www.mimer.com.

Don't hate the media, become the media.