Register to post in forums, or Log in to your existing account
 

Post new topic  Reply to topic     Home » Forums » Zugg's Blog Goto page 1, 2  Next
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sat Feb 12, 2005 5:35 am   

An SQL and ADO rant
 
Actually, this is a rant about modern database designs in general...

So here is what I was doing. I was playing with some of the new database components in zApp along with a Grid component. For fun I tried connecting the grid to my customer database...wait...wait...wait...wait. OK, the database only has 50,000 records or so, so what's the slowdown?

Well, I remember some of this from the ADO days with zMapper. If you have an ADO Recordset with CursorLocation set to adUseClient, then ADO reads in the ENTIRE DATABASE as soon as you connect to it. Doesn't matter what your query is.

Now, when you set the CursorLocation to adUseServer things are a bit better. But the GRID component still causes the entire database to be queried and downloaded.

This is a huge step backwards. With the old non-SQL databases, the GRID component in Delphi was smart enough to only fetch the data needed for display. But in this supposed "modern" age of networked databases and multi-tier applications, it seems to be impossible to only fetch a small number of records from a database.

In ADO there is a property called MaxRecords that is supposed to limit the number of rows returned from a query. But none of the OLEDB providers seem to implement it (at least not Jet/MSAccess or MySQL).

There isn't even a STANDARD way to do this using SQL! Some databases use the syntax of SELECT TOP nn whereas MySql uses the syntax LIMIT nn at the end of the SQL statement.

So, when trying to write a GENERAL SQL application, you can't do it. There is no way to limit the number of records returned from the database server. After all, you should NEVER want to query more than a couple of thousand records at most.

No wonder all of the database servers in the world are getting bogged down. Do the vendors do this on purpose so people will buy more servers?

Sure, I've seen the arguments on the Web..."Force your customers to limit their queries". Yeah right. Like a customer care about the exact SQL syntax of their query and knows in advance how many records are going to be returned. Or maybe they make a typo. No matter what you try, a customer is always going to end up accidentally querying for more data than they thought. And then they get to wait and wait and wait, along with any other user of that same database server.

This is just insane. Why couldn't the idiot developers implemented the MaxRecords property that was put into ADO for this very purpose? Or why doesn't SQL itself have a standard way of limiting the number of records returned from a query.

I also noticed that .NET doesn't solve any of this. Their grids have the same troubles. The problem is the underlying database components and not even ADO.NET fixes this. In fact, there still isn't even any way to query whether or not a provider implements MaxRecords. The Supports method doesn't have any option for this.

Displaying record sets in "pages" is one of the most basic database methods that is used. You see it all the time on the Internet. When I do a Google search, it only fetches what it needs for the page of queries I'm looking at. It doesn't try to fetch the entire result. Because they have optimized their backend server and are not using crap like ADO.

I surely wish there was an alternative to ADO that would allow you to write general-purpose database applications without worrying about the backends. That's what I want to do with zApp and eMobius. No wonder every company feels the need to develop their own database or file structure when there are no decent standards that are worth using.

I you have some experience in these kind of issues, let me know. What I'd *love* to see is a grid component that can connect to most any database and only fetch the data needed to be displayed in the grid itself. Is that too much to ask? Apparently in this multi-tiered world of Enterprise applications, it is.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sat Feb 12, 2005 7:13 am   
 
Got to love those kludges...

I've set zApp so that when you are using the Microsoft Jet OLEDB provider, it will automatically insert the "TOP nnn" clause using the current MaxRecords property to limit the query.

I noticed that when using the MySQL ODBC provider, it automatically adds the LIMIT clause when you set the MaxRecords. The direct MySQL OLEDB provider seems to be full of bugs and won't even fetch simple queries.

However, the ODBC provider has a bug. If you try to add your own LIMIT clause to the query, it causes an error when MaxRecords is set since the stupid driver doesn't check for an existing LIMIT clause before adding it's own...so you get two LIMIT statements, which causes an SQL error.

At least zApp checks for a TOP clause before modifying the SQL query when using the Jet provider. Geez...once again I'm apalled at the lack of software quality out there. And I can't believe given the popularity of MySQL that nobody has written a decent OLEDB provider. Guess the Microsoft OLEDB spec is hard to implement (gee, there's a surprise).

My lack of spelling is probably equally apalling Wink
Reply with quote
mr_kent
Enchanter


Joined: 10 Oct 2000
Posts: 698

PostPosted: Sat Feb 12, 2005 7:44 am   Re: An SQL and ADO rant
 
*snicker*

Announcing zDB and ZQL, the fully-customizible data solution from Zugg Software, maker of the world-famous and award-winning eMobius email program.
Available Fall of 2011.


Having toppled Microsoft's Outlook/Outlook Express as the market leader in personal/small business email software, Zugg Software is once again taking aim at the behemoth in Redmond. This time Zuggsoft's sole programmer, Mike Potter, is betting that small and medium businesses want an alternative to Microsoft's UDA.

Citing the lack of implementation of ODBC standards -established almost twenty years ago- in today's popular database solutions, Mr. Potter is leveraging his work in xml scripting to make data storage as customizable as his popular eMobius has made email.

                                                                           -Neuters
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sat Feb 12, 2005 6:19 pm   
 
ROFL...you made my day Razz
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sat Feb 12, 2005 9:12 pm   
 
Well, I might have found an option. There is a project called ZEOS at www.zeoslib.net which makes some database components that are compatible with the TDataset structure in Delphi. They support MySql directly, along with Postgres, MSSQL, Firebird, and lots of other databases. They also have an ADO mode that provides access to anything via ADO. So far it's working very well and is very fast.

The full list of databases supported natively are: MySQL, Postgresql, Interbase, Firebird, MSSQL, SyBase, Oracle, DB2, and anothing via ADO or ODBC. Pretty impressive list. For the MySQL and Postgres support you need a DLL file distributed (for MySQL, it's the libmysql.dll that comes with MySQL for example). Also, these components are supported in both Windows *AND* Linux (using Kylix). So, it would make porting zApp to Linux easier without the ADO dependance (if I ever did that).

The nice part about using this would be that you wouldn't need ADO in order to do databases with zApp like MySQL. You'd only need MDAC/ADO installed if you wanted to use ADO directly (like for MS Access files). And the native MySQL support is a lot better than the support provided via the OLEDB or ODBC drivers. So, in a sense, the database support in zApp would be just as good as the MySQL support in PHP, not to mention the support for all the other databases.

I'm still evaluating it, but it looks very promising and it would be trivial to replace my ADO calls with Zeos calls.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sat Feb 12, 2005 11:32 pm   
 
Cool. Took about an hour to convert from ADO to ZEOS. That sure was easy. The zApp EXE file only grew slightly, so having all of the DB drivers compiled in doesn't add anything (or balances what was used by ADO which is now removed).

The MySQL DLL file is about 300K. So, considering that the ADO/MDAC was about 10MB or so, that's a pretty big savings!

Turns out that ZEOS is based upon the JDBC spec. So, there's actually a lot of driver support for it and it's well tested.

So far the only problem I'm having is getting a Jet database table via ADO (a mapper database) to update via the GRID component. Works with a MySQL database, but now with ADO. I've posted to their support forum, but may need to investigate the code.

Also, this whole thing is free including source code, even for commercial development. My dreams come true! If this works out, you'll see some cross promotion with the ZEOS folks and this enhanced database support (and NOT using ADO) is going to be a huge selling factor for zApp!

And I must say that I'm loving this zApp modularity. My Connection and Query components were descendants of Delphi ADO components. I basically just changed it to inherited from the Zeos components and then tweaked some stuff. I didn't have to make ANY changes to the DBVIEW.ZML zApp file. It runs just like it used to with a total change of database backend. And that's exactly how this is all supposed to work!

If you have any bad experiences with JDBC, let me know. They've added a lot more stuff to it for Zeos, but I'd be interested in how stable their basic architecture is.
Reply with quote
Rainchild
Wizard


Joined: 10 Oct 2000
Posts: 1551
Location: Australia

PostPosted: Sun Feb 13, 2005 3:46 am   
 
Sounds nice - I might have to look at that ZEOS stuff for my own work bits. We typically use MSSQL but having DB interoperability would be nice. How does it get around the SQL syntax differences of the different DB engines, or does it expect you to write the SQL for the DB you use (in which case you have to have a query for each of the languages)?

I hate how there's no standard for SQL, its almost lucky that the word "SELECT" works in each different DB engine :P
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Sun Feb 13, 2005 6:42 am   
 
I'm pretty sure that if you need something that is database specific you need to customize your SQL statement. But it handles all of the basic queries that I've tried. But stuff like the LIMIT clause for MySql isn't converted to TOP when you query M$ databases or anything like that.
Reply with quote
Rorso
Wizard


Joined: 14 Oct 2000
Posts: 1368

PostPosted: Mon Feb 14, 2005 9:37 pm   
 
I would be very careful with GPL stuff.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Tue Feb 15, 2005 1:55 am   
 
This isn't GPL. It is a specific license that is very clear about the free use for commercial applications. No need to be paranoid. Just requires careful reading of licensing agreements. But I think we have covered that before and probably don't need to continue it again in this thread.
Reply with quote
Nezic
Apprentice


Joined: 10 Oct 2000
Posts: 119
Location: Colorado

PostPosted: Tue Feb 15, 2005 6:27 pm   
 
I don't really think you need to be paranoid about the GPL anyway. Rolling Eyes (but that was a different discussion)

-Nezic

[edit] Though, I guess the other discussion was about the website, and not an actual product, so I imagine more consideration would be needed.. but it's a moot point:P
Reply with quote
slicertool
Magician


Joined: 09 Oct 2003
Posts: 459
Location: USA

PostPosted: Wed Feb 16, 2005 12:31 am   
 
We've been having problems with a generic reports vendor for ASP and MSSQL.... the company I'm working at has hundreds of thousands to tens millions of records in some of the databases which keep statistical data. We set the max record limit in the reports to 100k and then told it to run one of the more basic reports... after about 30 seconds, we recieved an application unavailable error which we just shrugged off as an application timeout... upped the time-out limit to 5 minutes and got the same error again at about 30-45 seconds.

We checked the application log on the server and ASP.NET had to close, because the reports software had taken up over 60% of the server's memory for that single report and the server decided to kill the process out of self preservation.

When you are using 305MB of memory on a dev server to run a single instance of a single ASP page, there is something wrong.

We reset the max record limit in the ASP reports to use 1000 records and it worked just fine....

Gotta love enterprise solutions that aren't scalable enterprise-wide.
_________________
Ichthus on SWmud: http://www.swmud.org/
Reply with quote
Castaway
GURU


Joined: 10 Oct 2000
Posts: 793
Location: Swindon, England

PostPosted: Wed Feb 16, 2005 3:19 pm   
 
There IS an SQL standard, several in fact (latest is SQL 2003) .. The problem is, like HTML and browsers, some DBs support some parts of the standard, some others, some add bits..

The standard way to get a restricted number of rows is to use the ROW_NUMBER(OVER .. ) function, and restrict it in the WHERE clause, eg:

SELECT id, name FROM names WHERE ROW_NUMBER(OVER ORDER BY id) < 100

needless to say, I think MySQL doesnt support this.. Oracle, DB2, Postgres all do, as far as I recall.. (I have a list somewheres..)
So the only way you're going to be able to generalise, is through an extra library or whatever, which converts some standard-ish SQL into something that each database understands. On the other hand, anyone actually using the App to do some database related stuff, would usually pick which they want and optimise their SQL for that, so I hope theres not to much hiding of details going on.

Lady C.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 5:59 pm   
 
Slicertool, how were you accessing MySql from ASP? I thought ASP only used ADO.NET for database access. And if so, I haven't seen *any* good drivers for MySQL and ADO that work properly. But maybe you had some other component doing the MySQL acces?

Castaway, thanks for the info! It's pretty sad to have a standard like SQL and then not have it followed. If there was already a standard for limiting the size of the returned dataset, then why did MySQL go and add the "LIMIT" clause and Microsoft added the "TOP" clause. Why didn't they just use the syntax already defined in the standard? Is this a case of the ROW_NUMBER being added late in SQL? As with HTML I have noticed that the earlier standards get implemented, then vendor-specific stuff gets added, and when the standard "catches up" the vendors are less likely to add support for the new standard when they are already doing it in a vendor-specific way.

Also, I have noticed with SQL that the *Functions* are not very universally supported across vendors unfortunately. For example, the square-root function in Oracle is SQRT, whereas in MS Access it's SQR. To do uppercase in Oracle is UPPER while in Access it is UCASE. To take the length of a string in Oracle is LENGTH and in Access it is Len. In fact, it's easier to find function names that are different, rather than the same.

So, is there a link for the *official* SQL standard? And what databases are the best at sticking to the standard. I'm guessing that Oracle is following the standard and Microsoft is ignoring it...is that correct? In any case, the Functions seem to be an area where the SQL standard is often ignored by vendors.

zApp is not going to muck with this kind of stuff. I will probably figure out a way to implement the MaxRecords limit across all the different databases, because I feel this is a feature that is heavily needed in modern databases, and if zApp made it easy to use, then maybe people would use it and stop trying to return millions of rows from their queries. But you'll always be able to set MaxRecords to zero which will turn off any mucking around with the SQL query that you submit.

Anyway, I'd be interested in any useful links on SQL comparisons of different databases. I'm still pretty new to this.
Reply with quote
Darker
GURU


Joined: 24 Sep 2000
Posts: 1237
Location: USA

PostPosted: Wed Feb 16, 2005 7:21 pm   
 
SlicerTool said MSSQL, not MySQL. Confusing iddnit?

As far as standards, this might be helpful: http://www.dbazine.com/gulutzan3.shtml
_________________
Darker
New and Improved, for your Safety.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 7:21 pm   
 
I'm not finding much about ROWNUMBER. Also, from your syntax it looks like you need a index key to use it, which isn't what I want. I want a statement that limits the total number of rows fetched. In other words, something like what MySQL does:

SELECT * FROM Table LIMIT 100

No ifs, ands, or buts...this will only return 100 rows from the table. Doesn't care what the data structure is, what the index keys are, or anything. It's the same as the MSSQL statement:

SELECT TOP 100 * From Table

I don't see any standard for this kind of thing. One of the databases I'm investigating for local database files (replacing MS Access) is the Embedded Firebird system. Firebird is the ongoing Open Source Interbase project when Borland released Interbase into the public domain. Firebird has an embedded mode that allows access to local files without a full-blown server (just a DLL file). Unfortunately, I'm not finding any way to limit the number of returned rows in Firebird either.

All I want is some easy way to prevent a database server from sending me millions of rows of data! Is that too much to ask? Come on, users are mostly stupid...at some point someone is going to enter a query the ends up returning the entire database. And I *never* want that to happen. When populating a GRID component, a thousand rows or so is more than anyone will ever page through. This should be some simple SQL keyword, like the LIMIT or TOP example, and not some complicated function call like ROWNUMBER.


Last edited by Zugg on Wed Feb 16, 2005 7:30 pm; edited 1 time in total
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 7:30 pm   
 
Well, I found the statement for Firebird 1.5. You're going to love this...in Firebird, the syntax is:

SELECT FIRST 100 * FROM Table

No, they just *couldn't* use the same keyboard as MSSQL...that would be copying Microsoft. Sigh.

Turns out in Oracle they have a keyword called ROWNUM so you can do

SELECT * FROM Table WHERE ROWNUM <= 100

So much for the SQL *standard*!
Reply with quote
Darker
GURU


Joined: 24 Sep 2000
Posts: 1237
Location: USA

PostPosted: Wed Feb 16, 2005 7:46 pm   
 
Might also want to check out http://sqlite.org/

Limit works there (see the Syntax, SELECT help). The FAQ http://sqlite.org/faq.html is also useful.
_________________
Darker
New and Improved, for your Safety.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 9:40 pm   
 
Quote:
SlicerTool said MSSQL, not MySQL. Confusing iddnit?


Blah... missed that, thanks. Makes a *lot* more sense now Rolling Eyes

More news on this. First, I got the patch that allows WideString fields in ADO databases to be updated properly. So, Zeos is now working as well as the previous ADO components (if not better).

In looking for a local database to replace MS Access, I've now looked at both the Embedded Firebird stuff, as well as SqLite. I'm really liking SqLite for "lite" local database files. Anyone have any experience with SqLite?

So far, both SqLite and Firebird are a *lot* faster than ADO access. In some cases, I'm talking about an order of magnitude speed improvement opening the database (doesn't read it all into memory at once), and in simple query access. And huge speed improvements when inserting and updating records. I'm really starting to see the kind of overhead that ADO and Jet were adding and it's really staggering.

When I'm done with zApp and eMobius, I'm going to have to redo the zMUD map database format and start using one of these other alternatives and get away from ADO. Can't believe I let myself get sucked into that.
Reply with quote
Rainchild
Wizard


Joined: 10 Oct 2000
Posts: 1551
Location: Australia

PostPosted: Wed Feb 16, 2005 9:41 pm   
 
Question... and this might sound silly, but ok we got "SELECT TOP 100 * FROM table" ... fine, but how do you do "SELECT ROWS 100 TO 200 OF * FROM table".

I wouldn't have thought 'top' being all that useful in an every day scenario. Fine for limiting results, but if you are wanting to dynamically populate a list view (eg use OwnerData), and the database has 10,000 records, and you want to display records 7492 to 7522 how do you do that without returning 7522 rows and scrolling to the last 30?

Or is this where you start using server-side cursors (if they are even supported by the DB).
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 9:42 pm   
 
Oh, and yeah, not only does SqLite implement LIMIT, but it also has the optional OFFSET which makes paging implementations really easy! Everyone else should take lessons from this!

Now, unfortunately Zeos only currently supports SqLite 2.8 but 3.x support is supposed to be ready soon.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Wed Feb 16, 2005 9:50 pm   
 
Wow, Darker, that http://www.dbazine.com/gulutzan3.shtml article was wonderful!

Just confirms my belief that there really isn't any such thing as "standard SQL" and that really none of the vendors are any better than others. It would be really interesting to see this table filled out for other databases such as MySQL and Firebird/Interbase.

The other interesting thing I noticed is that there isn't any mention of this concept of restricting dataset size. Nothing about the LIMIT or TOP keywords. So, is this just not really part any SQL standard document at all?

I really liked the quotes at the end of the article. They really echo my feelings about all of this. A great article on how to stick with semi-portable SQL code. Thanks for the link!
Reply with quote
Rainchild
Wizard


Joined: 10 Oct 2000
Posts: 1551
Location: Australia

PostPosted: Thu Feb 17, 2005 1:47 am   
 
It's a pity that SQL Lite doesn't support foreign keys. It's one of those basic features that you need in a relational database.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA

PostPosted: Thu Feb 17, 2005 3:36 am   
 
Well, yes and no. I agree that for a big client/server database, foreign keys are important. But when I was working with ADO/MS Access in the zMUD mapper I found that if I created foreign keys then the database operations for changing data got *very* slow. I ended up taking out all relationships like that and handled them all manually. This means a bit more work when I delete records, and of course means that it's possible to mess things up and have dangling data in the database, but the speed difference more than made up for these headaches.

When working with local databases, speed and resource usage are the primary concerns. Local database development is *very* different than true client/server databases. I think SQLite is nice because it lets you use the same types of queries on local databases. I think of it as an MS Access replacement, rather than a MySQL or Oracle replacement. And in that sense, while having foreign key support would be nice, it's not really a show-stopper. You can always just handle the relationships yourself.
Reply with quote
Rainchild
Wizard


Joined: 10 Oct 2000
Posts: 1551
Location: Australia

PostPosted: Thu Feb 17, 2005 5:20 am   
 
Yeah I guess I see zApp / eMobius (being for a small workgroup) as needing the foreign keys etc. I definately wouldn't want my email stored in Access, that's for sure :)

Although, there may not be a whole lot of relations in the mail store, I can see there being a potential for heaps in the address book (especially if someone/you writes a CRM component to the address book). Plus I think its important to be able to do the whole start transaction, insert data, commit / rollback to keep data integrity to a maximum (especially if there's some likelyhood of corrupting the database and losing all your emails!).

And I suppose that I'd rather use MSDE 2000 over Access any day, though it doesn't necessarily make much sense from a zMapper perspective. I am curious what the performance would be like, having my zMUD maps hosted on a MSDE 2000 database that was on my intranet server and hosting the map up to copies of zMUD for me, my GF, and my 2 flatmates who all play the same MUD... it's probable that the network might make it unplayable unless cached on the local machine, but it does make me wonder.
Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » Zugg's Blog All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
© 2009 Zugg Software. Hosted on Wolfpaw.net