Pages

Search This Blog

Tuesday, January 26, 2010

SQL query and optimization

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

Table columns:
tblEmail (email varchar(255), IsDeleted int)
tblEmailPromotion (email varchar(255), PromotionID int)

Both tables without index

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 2:43 (2 minutes 43 seconds) to get the result

Change the question from "LEFT OUTER JOIN" to "IN" become

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

Guest what? great improvement. Takes 1:07 (1 minute 7 seconds) to load the result.

Now, we index table tblEmail column Email (Unique)

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 1:22 (1 minute 22 seconds) to get the result

Now, we run the 2nd query:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It only takes 27 Seconds

To make the 2nd query better:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

This will give 15 seconds.

Summary:

We need to choose the query wisely.
Index will help to improve data retrieving process but must be careful with indexes. Wrong indexes may cause you more problem.

Friday, January 22, 2010

Saturday, January 16, 2010

Add Yahoo PingBox to Facebook fan page using FBML

Well, there do have Yahoo! Messenger Pingbox for facebook application but it's only able to add to personal profile and not to fan page.

What i'm going to do here is to add Yahoo! Messenger Pingbox to fan page using facebook static FBML application. Here, i'm not going to discuss on how to use FBML application, to see how FBML works, please check http://socialwants.com/development/adding-facebook-fan-page-applications/

Now, i have my Yahoo! Messenger Pingbox code.

<object id="pingbox0000000000000" type="application/x-shockwave-flash" data="http://wgweb.msg.yahoo.com/badge/Pingbox.swf" width="240" height="420"><param name="movie" value="http://wgweb.msg.yahoo.com/badge/Pingbox.swf" /><param name="allowScriptAccess" value="always" /><param name="flashvars" value="wid=xxxxxxxxxxxxxxxxxxx--" /></object>


In order to make it work in FBML, i have to change the above code to below

<fb:swf swfsrc='http://wgweb.msg.yahoo.com/badge/Pingbox.swf' imgsrc='http://www.encelabs.com/images/aaaaa.jpg' flashvars='wid=xxxxxxxxxxxxxxxxxxx--' width='184'height='320'/>



where imgscr is the image that will be displayed before pingbox load.

I'm able to get it run in my facebook fan page. Hope it work for you.

Tuesday, January 12, 2010

Invoice Software

Recently have downloaded and tested on a few invoice software/invoice system. Some are very complicated, some are not user friendly and some are very old design.

Surprisingly i found a very simple with very attractive user interface invoicing software. It can easily generate a quite professional quotation and invoice. It include delivery note as well but i'm not using it. It come with professional invoice template and we can customize the quotations & invoices's template field and wording and also can upload company logo.

It's very user friendly and the search feature is also very simple. Just nice customer maintenance module, and reporting module.

For the paid version, a very simple inventory module included in the software. Payment receive module and revenue reporting module. One thing want to highlight here is that the reports can drill down to more details level. Love it.

Simple and nice with very low price. Maybe you can try it. The software called Xin Invoice. The support is not bad and also there are some simple demo can find on their web and youtube. It's just suit my needs but i'm not sure for those who is running more complicated business.