The Ji Village News

季庄新闻–Haidong Ji’s Blog

I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods in the end. I am reading the Safari online version. With so many pages, I can see the physical book can be bulky.

Anyway, some comments and observations:

1. Sybase, MySQL, and Sql Server’s definition and implementation of database and schema are somewhat close. I dislike Microsoft’s definition and usage of schema, especially in Sql Server 2005. It is very confusing to new or DBAs from other platforms.

Sybase, MySQL, and Sql Server all support the USE Database statement;

2. I like mysql command line tool and how the result set is displayed. It looks clean and neat to me, although, like command line tools from other database platforms, when the result set gets wider, it gets messy;

3. Similar to Sql Server, MySQL also has the system and status variables that starts with @@. And, like Sql Server, you can just do SELECT @@Variable to retrieve its value, for example, SELECT @@sql_mode;

4. Played with MySQL Query Browser a little bit. Ctrl - E is the keyboard shortcut for statement execution, the same as Sql Server. However, F5 does not execute the statement in MySQL Query Browser.

As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any lecturing at this stage. If the code is a stored procedure, I ask the author or stakeholder to give me sample execution statements, with parameters filled with typical values for production load. Sometime it is not possible to talk to the original author, because s/he may have left the company;

2. After that, I will get the database in question and restore it on my own workstation, with the data as close to production as possible. I will start running the code, and gather IO statistics and execution plan, and save the results for benchmark comparison later on;

3. Here is the raw code analysis stage, where code is put into an editor for analysis. In my case, it is VI or VIM. I find myself using the * command on all variable names and tables (make sure you are doing case insensitive search for Sql Server code), to see where they are used. This is also where I weed out the historical garbage code that is there but never being used anymore, such as unused variables, tables, etc. In the case of Sql Server, I mostly look for cursors, temp tables, and user defined functions;

4. Code simplification stage. I found a lot of bad performing code is unwieldy and unnecessarily complex. At this stage, I look for redundant WHERE clauses, convoluted AND and/or OR operators. This can be a tedious and time-consuming process. At this point, it is important to talk to the author or owner of the code, as a good understanding of data and entity relationship is crucial. Also, since I have gained some understanding from the steps above, it is easier to have an intelligent conversation with the code owner, with fresh perspective and momentum. Usually something good will come out of the conversation, and we will have something concrete to work on next;

5. I will make the changes discussed in step 4, once again, collect statistics and execution plan for comparison;

6. Repeat Step 4 and 5 a few times, when necessary. I usually start talking about better database design, code/design refactoring, set based operations, etc., as hopefully I have some credibility and gained the customer’s trust.

So what do you do to break down complex, low performance SQL code? What methodology, tools, tips, tricks you can share with me?

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.

Database vendors like to bash each other, sponsoring dubious “objective industry studies” to “prove” they are better than others. All of them do it. In my opinion, Oracle is particularly bad in this regard, compared against IBM DB2 or Microsoft Sql Server. Talking about “unbreakable Oracle” and software full of bugs, and in many cases you need to pay the Metalink membership to simply get to know that the issue you are dealing with is a bug. That is not to say Sql Server and DB2 don’t have bugs in their software, though.

Each of them wants you to convert your database to their platform. To that end, they provide some program to help you along. Oracle has a thing called Oracle Migration Workbench. Sql Server has something similar called Sql Server Migration Assistant.

More often than not, marketing people from those companies will tell you how great their migration program is. That it is easy to migrate, you will see performance improvement, etc. Don’t believe them.

A couple of months ago, I worked on a project to migrate a database from Sql Server to Oracle. This database has a lot of stored procedures, user defined functions, and linked server stuff. I talked to Oracle people, and they recommended their own Migration Workbench, with Sql Server plug-in. I downloaded them and started working.

Basically, the Migration Workbench tries to go through database code in T-Sql. For code that uses built-in T-Sql functions, the Migration Workbench creates functions with the same name in Oracle, and try to writes something similar in PL/Sql that makes an attempt to do what T-Sql function does. I didn’t look too deep into this, but I am suspicious at all of them. The reason I didn’t look too deep into them was that the whole effort was derailed by a bigger problem. Let me explain.

In T-Sql, all variable names start with @ symbol. In fact, in many places, variable names are simply column names prefixed with the @ symbol. This can be pretty easy to read. And it actually works very well.

However, PL/Sql variable names don’t follow that convention. So, as Migration Workbench goes through T-Sql stored procedures, it simply strips off the @ symbol from the variable names. This basically renders all the code useless.

This is just very, very dumb. Oracle is a multi-billion dollar company, and you would think they should know this. Instead of stripping off the @ symbol, it could replace it with some kind of prefix. But it does not do that.

Another problem is with the identity field. Once again, one would expect that the Migration Workbench converts it to Oracle sequence, but it doesn’t. It changes that to NUMBER.

Eventually, the project was canceled. The moral of the story: migration from one RDBMS to another is not as easy as it sounds. It is doable. Sometimes it is probably easier to just write everything from scratch.

In Oracle, there are 3 places that I know of that are important for monitoring: the bdump, where background process error is stored; udump, where user trace error is dumped; and cdump, the core dump, where Oracle internal error is dumped. cdump is in the binary format, you can use “strings -a” to look at things inside. All other trace and log files are text files that you can open up and read for yourself.

On Unix/Linux/Solaris based systems, these folders are located under:

$ORACLE_BASE/admin/$ORACLE_SID/bdump, udump, and cdump

To find out your $ORACLE_BASE and $ORACLE_SID, you can do:

echo $ORACLE_BASE and echo $ORACLE_SID

There is also the alert log, usually in the format of alert_$ORACLE_SID.log format, under the bdump folder.

There can be numerous trace and log files in these folders. To troubleshoot and correlate events with some problems, you can do:

ls -ltr

to sort files in ascending order according to date and time, and open up the file that is closest to the time when problem occurred. Those trace and log files may hold telltale signs of what you need to examine further.

For Sql Server, by default, the error log file is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. And that is the only place you need to worry about. If you turn on certain trace, the trace info will also be logged into error log.

Don’t confuse Oracle’s .trc file with Sql Server’s .trc file. Oracle’s trace file is in text format, generated when certain trace flags are turned on. Sql Server’s trace file are in binary format, and can be read using Sql Server Profiler.

I mentioned here that SELECT INTO in Sql Server is functionally similar to CREATE TABLE AS in Oracle. Oracle also has SELECT INTO, but it is used for assigning query results to a variable.

Here is a PL/SQL code snippet:

declare MyVariable varchar2(20);

Begin

select ColumnName into MyVariable from MyTable where MyID = SomeInteger;
dbms_output.put_line('Hello ' || MyVariable);

End

In the above example, a column value for a particular record is assigned to MyVariable and printed out.

How do you assign select results into T-Sql variables in Sql Server then? Here is a code sample that does the same thing above:

declare @MyVariable varchar(20)

select @MyVariable = ColumnName from MyTable where MyId = SomeInteger

print 'Hello ' + @MyVariable

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050;

If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue

rollback

If “no row affected” is returned, the person can continue until s/he can find it out.

I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Sql Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For Sql Server 2005, when in interactive mode of sqlcmd, use

:r c:MyFolderMyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:MyFolderMyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

. c:MyFolderMyScript.sql (on Windows)

Note there should be a backward slash right before the dot. In fact, please pay attention to all backward slashes in Windows directory names. Or

source ~/MyFolder/MyScript.sql (on *nix)

to run a script file.

For batch mode, use

mysql -h MyHost -u MyUser -t -p < ~/MyFolder/MyScript.sql

The -t is not necessary here, but it presents the results in table/grid format, therefore more readable.

In Oracle sqlplus, while in interactive mode, use

start c:MyFolderMyScript.sql (on Windows)

or

@ ~./MyFolder/MyScript.sql (on *nix)

Note that if the script file name has the .sql extension at the end, it is not necessary to type .sql. sqlplus assumes the .sql extension.

For batch mode, make sure you have exit as the last line of the script file, and run this

sqlplus MyUser@OracleSID @c:MyFolderMyScript.sql