Books

November 14, 2007

Books borrowed from the library:

  1. Seduced By Success, Robert Herbold – “…gets to the heart of why successfull organizations and individuals often go into a tailspin, and how this can be avoided. His thorough reviews of specific companies we all know make this a very useful book…–Indra K Nooyi, CEO Pepsico.”
  2. Common Sense Direct & Digital Marketing, Drayton Bird – A book about the internet, direct individual targetting, testing, keeping customers, etc.
  3. Affluent Consumer, Michman and Mazze – It’s about the emerging affluent consumers.
  4. Marketing Metrics: 50+ Metrics Every Executive Should Master,  Farris Bendel Pfeifer Reibstein – It’s where marketing meets formulas and charts, with a hefty doze of Accounting.
  5. Game Production Handbook, Heather Chandler – A detailed exposition of the major components and its processes. Procedures are discussed in detail along with best practices.
  6. Secrets of The Game Business, Laramie – “Find out how the game business realy works. Explore all areas and find tips for starting your own. Covers middleware, licensing, designs, consumer profiling.”
  7. Ultimate Game Design, Tom Meigs – Old book, published in ’03.
  8. Game Design: The Art & Business of Creating Games, laMothe – Another old book published in ’01.
  9. e-loyalty, Ellen Smith.
  10. The New Direct Marketing, David Shepard Associates.
  11. Relationship Marketing, Roger Parker.

Books Bought:

  1. Divine Proportion
  2. Beginning Math And Physics
  3. Fast Forward MBA In Business Math

Stumbled Into:

  1. CLE
  2. Rajiv’s

Student Life:

  1. The last test did not go well. My worksheet’s Income Statement and Balance Sheet control total for Net/Loss Income did not match. It turned out an unknown account showed-up, incorrectly classified under revenue, which should have been under liabilities. Oh well.
  2. The acctg prof pointed one student in my direction, thinking I could help catch-up with prev lessons.
  3. Java, java, java. Wrote some Java code in Eclipse recently. Java is now the first choice language of the college where a friend of mine goes to.

Optimization:

  1. By carefully matching table indices to a specific code in a stored procedure, you can achieve fast scans and walks using cursors. Simply look into the where clause and figure out the columns, then index those columns. Though this technique does not apply across the board. You still have to profile each stored procedure you write against a gigabyte logfile to be sure. In my case, the size of the test logfile is now at 30 gig. Awesome!

Flex:

  1. Been rifling-through the targets, trying to get as many as possible. The last code I wrote was about item renderers, repeaters and components. I still have a lot of stuff to cover, though.

Cursors Can Be Deadly

October 4, 2007

When planning on writing SQL code, especially stored procedure code, you really need to think twice about employing the use of cursors. Here’s why:

  1. A single cursor will do just fine, provided you don’t mix it up with joined tables/views.
  2. A nested cursor will surely bring your server down to its knees. Mix it up with temptables and it will even slow down to a crawl.

At first, when you get it coded and tested, make sure that code runs against 10 gigabyte logfile. You will surely reward yourself for doing this test first before the code goes to prod, though.

In my case, the DB broke the 1Gigabyte barrier very early, by about 2 to 3 weeks after rollout. At that rate, we’ll be maintaining a huge DB soon, perhaps it will morph into a VLDB within 2 years.

Google Docs

I’ve never heard or read about Google Docs, what it does or what advantage it has over the competition, until I got into this odd situation.

I was firing-through the given assignments and noticed something not right with the way I was using technology in setting-up a repo for my assignments. it turns out Google has these nifty small tools for creating and publishing docs online. So I fired-up the browser and started using the following:

  • Presentation – Most of the lecture notes were converted into this format. I was able to convert all my notes in one sitting. it’s all online now, presentation format. Man, I’m telling you, this is cool.
  • Spreadsheet – This tool is a time saver, I simply entered all the numbers. Did all the standard steps in setting up the financial statements like Income Statement, Statement of Owner’s Equity and the Balance Sheet. Google Spreadsheet handled it very nicely. The cool thing about it is–it’s now available online.

Adding Performance Counter Code Into Your MSSQL Stored Procedure

May 24, 2007


Collecting information about performance data can be done in many ways. although one of the methods I often use is simply put the code in the body and let that code extract the numbers for me.

The code shown below contain the necessary statements needed to extract a number of seconds it take to run the procedure from line 06 to line 08. In the next paragraph, these lines of code will be explained in detail.

01  CREATE PROCEDURE sp_sample_stored_proc_01 AS
02  BEGIN
03	declare @start_time float
04	declare @end_time float
05	declare @elapsed_time float
06	select @start_time = @@CPU_BUSY * cast(@@TIMETICKS as float)
07      -- your normal routine code goes here.
08	set @end_time = @@CPU_BUSY * cast(@@TIMETICKS as float)
09	set @elapsed_time =  (@end_time - @start_time) / 1000000
10  END

Lines 01, 02, 10

The syntax for defining a stored procedure is shown in lines 01, 02 and 10. In this example, the name of the procedure get declared as sp_sample_stored_proc_01, having no parameter definition. While lines 02 and 10 define the body of the procedure.

01  CREATE PROCEDURE sp_sample_stored_proc_01 AS
02  BEGIN
     o  o  o
10  END

Lines 03, 04, 05

@start_time, @end_time and @elapsed_time are names or labels used to store values needed to calculate the elapsed time. And they are declared using the following syntax:

03	declare @start_time float
04	declare @end_time float
05	declare @elapsed_time float

Lines 06, 08, 09

When execution reaches line 06, it takes a snapshot of two system values contained in two system variables named: @@CPU_BUSY and @@TIMETICKS. An expression is evaluated before storing the result in @start_time

06	select @start_time = @@CPU_BUSY * cast(@@TIMETICKS as float)

The execution path goes on until it reaches line 08, which then takes another snapshot of two system values; calculates the expression and stores the result in @end_time.

08	set @end_time = @@CPU_BUSY * cast(@@TIMETICKS as float)

At this point, the procedure is almost finished and will jump out of its execution frame. But before jumping out, it calculates the expression defined in line 09.

09	set @elapsed_time =  (@end_time - @start_time) / 1000000

@elapsed_time contains a value of how much time it took to run the lines of code from line 06 to 08.

Recommendation

You can extend the code so that it saves @elapsed_time to a logfile.


MySQL 5.0 Stored Procedure Programming Tutorial

May 23, 2007


MySQL Stored Procedure is a new feature release in version 5.0, positioning the database a step closer in features to the leading database products. In this article, you’ll be guided on how stored procedures are created using several tools described below.

Before we start the tutorial, let’s have a quick check on the tools you will need. Listed below are the following you can download from the Web. Also, the code used in this tutorial were tested by these tools, and your results may vary depending on how close or far you are from the tools mentioned below.

The article assume you already have the tools installed and you are now ready to follow the steps. However, you can still follow and learn what makes sense if you are running a different copy of Linux or a different MySQL Administrator version.

What this article will show you are the following steps:

  1. Create a new schema.
  2. Create a new table.
  3. Create a new stored procedure.
  4. Testing the stored procedure.

Create A New Schema

Launch MySQL Administrator from Applications-> Programming-> MySQL Administrator, then select View from the menu bar. The View menu appears, now select Catalogs from the list. You’ll see a list show at the bottom left of the panel. Right click on that list and select Create Schema. A dialog box appears containing a textbox for entering the schema name. Enter a name in the textbox and click the OK button.

Create A New Table

This step will create a new table. Select the newly created schema from the schema list located at the lower left part of the panel. You’ll see five buttons at the lower part, select Create Table. The table editor dialog box shows up, from there you can start naming the table and also add the columns as well. Use the table definition below to populate the dialog box. Click Apply Changes to save and create the table.

CREATE TABLE `mmorpg_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) default NULL,
  `user_url` varchar(100) default NULL,
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) default NULL,
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) default NULL,
  `latest_ping` datetime default NULL,
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Create A New Stored Procedure

There are four tabs at the top part of the form. Select the Stored Procedures tab to open the list. Click the Add Procedure button and a routine name dialog box will show up, enter the name of the procedure and select OK. At this point, you can simply copy-and-paste the stored procedure code shown below. Click Execute button to commit the code.

CREATE DEFINER=`root`@`localhost`
    PROCEDURE `setUser`(OUT _id int, _uid varchar(60),
                                         _pwd varchar(64), _nm varchar(50),
                                          OUT _retcode int)
BEGIN
	declare _cnt int;
	declare _errno int;
	set _retcode = 0;
	case _id
		when 0 then
			select count(*) into _cnt from mmorpg_users
				where user_login = _uid;
			if _cnt = 0 then
					insert into mmorpg_users (user_login, user_pass, user_nicename)
						values (_uid, _pwd, _nm);
					select last_insert_id() into _id;
			else
					set _retcode = 1;
					select ID into _id from mmorpg_users
						where user_login = _uid;
			end if;
		else
			update mmorpg_users
				set 	user_login = _uid,
					user_pass = _pwd ,
					user_nicename = _nm
			where ID = _id;
	end case;
	select * from mmorpg_users where ID = _id;
END

Testing The Stored Procedure

At this point, you are now going to test the stored procedure. Open the Terminal program from Applications->Accessories->Terminal. Run mysql with the following:

desktop:~$ mysql -u root

Then enter the following text inside mysql.

mysql> call setUser(@id, 'John', 'btd19x', 'redball', @retcode);

Conclusion

This is just one sample implementation of a MySQL stored procedure, walking you through the steps. With these tools, you can now expand to even create more procedures for your own applications. Perhaps even write sophisticated ones as well.