Tech Sharing

Tech Sharing

Advertisement

Posts Tagged ‘ Tips ’


In SQL, basically there are two ways to Insert data into a table; one is insert it one row per time, another way is insert multiple rows at a times.


For the first method (insert one row per time) is the most common way to insert data into database that we will use normally, and the sample sql syntax as below:


Insert into table1 (col1, col2, col3)
values (value1, value2, value3)


The other insert method that we seldom use, but it may be useful if you like to copy and insert more then one row of record into a table at a time. You may include the Select statement to specify the data that you want to insert into the tables.


The SQL syntax as below:

INSERT INTO table1(col1, col2, cols3)

SELECT value1, value2, value3
FROM table2
WHERE value4 = 1998


There is another way of writing the same SQL:


Select value1, value2, value3

Into table1

From table2

WHERE value4 = 1998

Normally to get the number of row for a particular table, where we will do is as the following query:


select count(*) from Table_name


Yes, you may get your result very fast if your table is very small, but if your table is more then 100k rows, then you may face some delay on that. What the above query do is actually it run the full table scan to get the row count. The scaning can be Logically or Physically. Logically mean that the SQL server only scan from the catch memory, whereas physically scan mean that it really scan through your table.


Actually there is anther alternative way to get the total row count for a table. You can ultilize the sysindexes system table for this purpose.


There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:


SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(‘table_name’) AND indid < 2


There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.


The example:

SET STATISTICS IO ON

GO

SELECT count(*) FROM tbTest

GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

GO

SET STATISTICS IO OFF

GO





Result:

———–

10000


(1 row(s) affected)


Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.

rows

———–

10000


(1 row(s) affected)


Table ‘sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.






I believe all the DBA know that how important is “Index” in the database if you would like to optimize the database server performance. It maybe a pain for your server if you create the index wrongly.


Here are some check lists on the index optimization that you should bear in mind and should go through. This is the foundation of the index optimization but it mean and help you a lot, if you already follow and apply all the checklist below, then you may need to try out more experiment to get more optimize:


Index optimization checklist:

  • Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on columns which are used in JOIN Condition.
  • Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of the entire insert, updates, deletes to the table.
  • Smaller Index Key gives better performance than Index key which covers large data or many columns
  • Index on Integer Columns performs better than varchar columns.
  • Clustered Index must exist before creating Non-Clustered Index.
  • Clustered Index must be created on Single Column which is not changing and narrow in size. Best candidate is primary key.
  • Non-clustered Indexes increases performance of the query that returns fewer rows and rows has wide selectivity spectrum.
  • Each table must have one Clustered Index.
  • If column have low selectivity avoid creating Index on that column as it slow down the rows modification and system will not get benefit from Index.
  • Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
  • Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
  • Rebuild Index frequently using ALTER INDEX and De-fragment Index to keep performance optimal for Indexes.

mssql2005This whole morning just spent about 1 hours+ just to try out one very simpel query on MS SQL 2005.

I keeping get the null value whenever i run this query, even in my program or run directly from the MS SQL 2005 management studio.

Finally I found out that is you are concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL.


With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, String + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is String?


This is the case 1

Update tableA
set field1 + ‘My value’
where field2 = 1


This is the case 2

select ‘test ‘ + null + ‘ string’

returns
————
NULL


This is the solution

set concat_null_yields_null off
select ‘test ‘ + null + ‘ string’

returns
————
test string

Recently just start to learn on one of the famous e-commerce shopping cart – Magento. But found out that is not as simple as other open source shopping cart such as osCommerce, ZenCart or even Joomla Virtual Mart.


It take me about 1 – 2 days just to find out how to set my default product category at the home page after i found out this video clip. So just copy the video clip here to share with those who are facing this problem as well.



The code to added to your home page

{{block type=”catalog/product_list” category_id=”xxx” template=”catalog/product/list.phtml”}}




Paypal fee calculator

By on March 23, 2009

paypal-logoPaypal Fee Calculator is a small and simple tools for you to calculate the amount of money that you going to charge your customer if you want to transfer the finance charges to them.


Paypal fee calculator available in 2 difference mode which are simple mode and advance mode. If you just want to find out how much your customer need to pay to you using the Paypal account without involve any currency convertion, then just enter to the simple mode.


What you need to do is enter finance charges at the bottom of the page, then enter the amount you wish to received at the top of the page. The Paypal calculator will help to you calculate how much you should ask your customer to pay to you in order to get a exactly the amount.


[More]

Email signature SEO

By on February 23, 2009

email-signature-logoA small tips for all the website owner. You may include your web blog url, web site url or RSS link in your email signture to help you in attracting some free traffic to your site.


You may design the signature in HTML form with the editor tools such as Macromedia Dreamwaver or Microsoft front page, then attach it in your email client. But for Gmail Webmail user, you need a tools such as WiseStamp to help you to attach it, as by default Google don’t allow HTML signature.


With the website url and the RSS link provided, you may attract your receiver who are actually wish to know you better. It may be useful for those who like to send the forwarded email to your friend.


I did this few weeks ago and it did help me in increasing my web blog traffic and also increase the subscriber number for by feed.


Hopefully this small tips can help you all.


My sample siganture in my Gmail

My sample siganture in my Gmail




Gmail shortcut key

By on February 23, 2009

shortcut-keyGmail are provided with the full access/navigate the webmail using your keyboard shortcut key. The keyboard shortcuts actually help you to save time by allowing your hand just stick on your keyboard.


But in order to avoid to crash with your browser or system shortcut key, Gmail pre-define shortcut may be not so familiar for you. You need to remember your most common use key such as next/previous email, send email, delete email, reply email.


How to turn on the keyboard shortcuts?

  1. Login to your Gmail
  2. Click on setting on the top right corner
  3. Go to general
  4. Select the “Keyboard shortcuts on”
  5. Save the change


The following is the shortcut key that may be useful for you:


[More]

google-adsense-logo

Google AdSense official blog had announce that now the site owner can customize the font face for Google AdSense ad unit. This is a great news for all of the webmaster because now you have more flexibility when you design the ad to be place in your website.



“However, you can also select custom font faces for specific ad units that you’ve created with the Manage Ads feature. When creating new ad units, you’ll be prompted to select between the default ‘Standard AdSense font family’, your new account-wide font face (if you’ve selected one), or another font face. You can also change the appearance of existing ad units, by visiting the Manage Ads page of your account and updating your font choices for specific ad units. Once you’ve changed the font face of an ad unit away from the default, those selections will be maintained even if you change your account-wide settings later.”


Click here to read more detail

font_control_settings



font_control_manageads





interlink-logoSome of you may not know that what is Interlink actually? This is the most easy way for you to increase the number page indexed page in Google when the Google Bot visit your page.


Put it in the simple word, interlink actually is the link url within your blog. For example, I wrote a post is regarding the “WordPress SEO Plugin“, so I try create a link to my other blog post which may relate to this topic as well. It just like you try to link up all the related content within your blog.


How it helping in SEO?

Google Bots are actually crawl the Internet world by the link. But sometime the Google bot are so busy and it may miss out to index some of your post. So by creating a link to other article you actually create a new path or road to show the Google Bots to index that article as well. By creating a new road for Google Bots to crawl your blog, you actually can decrease the percentage of unindex topic in your blog post. By making the Google Bot happy, it actually will pay a return to improve your search engine ranking.



[More]