Well I support development of a product which "claims" to be RDBMS independent... I know wat it takes to put forward a claim like that... I an more than me my senior colleagues have to break there head every time we have a porting... I remember the things I had faced, right from my senior porting the ER design from one db to other db and then creating the physical design of db and more than that security architecture... And haash how can i forget those incompatible functions and operators... No offences Mr Gates... But SQL Server has some out of the blue function arguement (what will be the most logical sequence of arguments if you wanna convert an expression to other data type, i bet most of you will miss the actual sequence used by SQL Server) and that concatenation operator X-( ... Well all of them have some or the things different... I can go on and on in blog post on that topic... Well lets see what features are good to have in DB2 (may or may not be inspired by other vendors), especially to from the performance angle... Lets try to list 10 of them (it's gonna be tuff for me, esp after the viper and cobra series)...
1) SQL Server 2008 has something called filter based indexes: Many of you might have faced an issue where you have multiple status to be stored in same column, but you will generally query on one/two of them as filter, other status are basically for display in inquiry. So how good it wud had been if you can index only those two status which are your filter criteria and save indexing cost and space... Your queries will be optimized for those two status only (like you say check and do not to enforce it and query is optimized for valid values)
2) Hierarchical Queries: I have worked with Oracle Hierarchical queries and have found them to be performing better than the recursive SQLs used to write pseudo-Hierarchical queries (yes that is what i will call them, for records recursive sql is available in Oracle 11g onwards)
3) Something as good as OEM in Oracle to monitor the database (and it should be free, hence optim is ruled out :P)
4) Forced and offline re-org after table/column alter should be fixed (no other RDBMS i have worked with requires this and yes i will count this as a performance issue in addition to being an availability issue)
5) Well there are queries generated by hibernate which uses ROW_NUMBER to window a table and fitler based on rownum<=N to select top N rows over a criteria, say last update date time. These kind of statements are automatically converted to SELECT TOP N for sql server 2008. It would had been nice if DB2 did the same, in an easier way (it is still possible with OPTIMIZEFORNROWS in db2cli.ini)
6) The optimizer should be able to guess, if the column(s) in join predicate is also part of some filter (where) predicate, and should apply the filter twice to get a better cardinality expectation. Well, I am not very sure if any RDBMS does this currently. But try it out and see the difference in expected cardinality yourself...
7) It will be good to have a better concurrency control and locking mechanism which can compete with that of oracle
8) Query Hints should be made more flexible and easy to use in prepared statements, on lines with Oracle and SQL Server
9) There should be function based indexes (currently the feature can be implemented with a little bit of work around by adding a computed column and indexing it)
10) Some good way of indexing low cardinality tables (like oracle has bit map indexes)
Well let's see how much db2 galelio has to offer!!! Have look:
DB2 Galileo Early Adoption Program
About Me
- Sameer
- Singapore, Singapore, Singapore
- I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!
1) You could use an MQT or MDC
ReplyDelete2) Don't understand the difference. Oracle hierarchical query is just an earlier, non-standard way to do same as recursive query. DB2 UDB has always supported the recursive SQL (ANSI) standard.
3) Agreed.
4) Reorg is required after a drop column rather than all alterations; agree this would be nice if could be avoided.
5) ORDER BY DESC FETCH FIRST 10 ROWS ONLY - select top 10 clause would *not* be nice, since it is non-standard SQL. You started this article by saying porting is a pain... adherence to standards by Microsoft etc. would help.
Run out of time, but the locking was an interesting point...
1) Agree... But adds an extra overhead of referesing MQT... MDC is quite interesting and would like to explore more on this..
ReplyDelete2) I have recently used oracle hierarchical and db2 recursive queries... I had to write a lot of correlated subqueries to ensure there is no cyclic relation which ends up in an infinite loop... Where as with oracle it was as simple as to use a NO CYCLE clause... As the record counts were not very huge so I could not compare much on performance, but there was a little dip when I used recursive query compared to hierarchical query... I also confirmed this trial on an Oracle 11g db which supports both...
3) :)
4) :)
5) I meant the query optimizer (or may be preprocessor/compiler) should identify a range based on a row_number clause and accordingly append an OPTIMIZE for N rows to it before generating a plan... I will still write like
select col1, col2 from (select col1, col2, row_number() over(order by idpk_column) rn from dummy_tab) temp where rn between 1 and 10
where as query fired will be:
select col1, col2 from (select col1, col2, row_number() over(order by idpk_column) rn from dummy_tab) temp where rn between 1 and 10 OPTIMIZE for 10 ROWS
i do agree that using DB2 is a pain
ReplyDeleteWell it is just a matter of perspective... If you have come from a Oracle world, you might find it so... I guess we can jott down 10 equally important points which should be there in SQLServer/Oracle but not there... Every product has its own history and own features, comparing two will be unfair towards people involved in development... What's good is db2 is going best to catchup with dev and dba requirements...
ReplyDeleteI don't think DB2 is a pain. I think IBM is a pain. DB2 is a great database engine, extremely stable and feature rich. However, IBM does not market is well. They concentrate on bringing out different version levels. Many third party vendors don't support DB2 9.5 + . While Microsoft is brining SQL Server cheaper, IBM is raising the DB2 license cost. SQL Server comes with SSIS/SSRS/SSAS, but DB2 doesn't even come with a decent tool. Please don't say Control Center and the free optim is a tool !
ReplyDeleteWell I do not agree completely... I agree Control Center is not that good a tool but I do not think Optim is that bad... And well you can pay a visit to dbi Software http://www.dbisoftware.com/ for some Awesome third party tools...
ReplyDeleteWhy third party? Can't IBM provide the same as a single bundle the way Microsoft does? Every time for each tool you need to individually pay IBM? Also I don't see Optim having all the functionalities at one place. It's always purchase 10 different tools for 10 different uses and with the memory usage for these tools any administrator's pc would surely die.
ReplyDeleteIBM, just a note "get started with more user friendly tools"