We all must have faced to delete the duplicate entries in a table.
Here’s the simple solution to that. 
The scenario is:
The table has 3 columns, A, B, and C. Column A is unique for all rows
but the columns B and C can be duplicated across rows. Now if you need
to remove the rows where B and C are duplicated.
DELETE
FROM daTable
WHERE a NOT IN
( SELECT MIN(a)
FROM daTable
GROUP
BY b,c )
Connection pooling is a technique used to avoid the overhead making of new database connection every time. An application or server object requires access to a database. This technique of “pooling” connections is based on the fact that most applications only need a thread to have access JDBC connection when they are actively processing a transaction, which usually take only milliseconds to complete. While not processing a transaction, the connection would otherwise sit idle. Instead, connection pooling allows the idle connection to be used by some other thread to-do his useful work.
Connection pooling enables the ODBC driver to re-use existing connections to a given database from a pool of connections, instead of opening a new connection each time the database is accessed. By enabling connection pooling you can improve the overall performance of your application by lowering the time taken to open a connection to a database in the connection pool.
When a thread needs to do work against a MySQL or other database with
JDBC, it requests a connection from the pool. When the thread is finished using the connection, it returns it to the pool, so that it can be used by any other threads. The overhead time for establishing a database connection is typically around 1 to 3 seconds. This is the time it takes to locate the database server to establish communication channel and exchange information. Sun has standardized the concept of connection pooling in JDBC through the JDBC-2.0 “Optional” interfaces, and all major application servers have implementations of these APIs that work fine with MySQL Connector.
An index is a database structure designed to facilitate faster data retrieval Indexes provide a way for SQL Server to organize pointers to the data required An index in a database works the same way as an index in a reference book.
Why Index?
Speed
Without index SQL Server must perform a table scan or read every row in a table
Speed up query joins between tables
Indexing Architecture
SQL Server indexes are stored as B-Tree (Balanced Tree) structures
There are three levels of a B-Tree:
Root Level
Leaf Level
Intermediate Level
A B-Tree structure is built for every index defined in SQL Server
Types of Indexing
There are two types of SQL Server indexes
Clustered
Non-Clustered
Both are built upon B-Tree structures
Clustered Index
The data is physically stored in the sorted order
The leaf pages are the actual data
Only one clustered index is allowed per table
Generally offers better performance than non-clustered index
Non-Clustered Index
Non-clustered indexes create a logical order and therefore, the leaf page merely points to the actual data Create up to 249 Non-clustered indexes per table. Non-clustered index are larger than clustered indexes because of the extra level of leaf pages
Index Creation
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,…n ] )
Indexes can be created at the same time table is created or at a later time
Create clustered first before adding non-clustered indexes.
When to use Clustered vs. Non-Clustered index
Clustered
Columns that are used in range queries
Columns used in ORDER BY or GROUP BY queries
Queries returning large result sets
Static look up tables with large data set
Non-Clustered
Columns used in aggregate functions
Foreign Keys
Queries returning small result sets
Primary keys that are sequential ID keys, such as identity columns
What NOT to index
Tables with a small number of rows
Might be more efficient to do table scan
Columns with small range of values
For example: (yes/no, 0/1, 1,2,3)
Tables with heavy transaction (insert,update,delete) activity but very few select queries run against it
Tables with indexes slow down data modifications
Columns not used in queries
If the column(s) are defined as TEXT, NTEXT, or IMAGE data types
Additional Tips
Try to create indexes on columns that have integer values
Integer values have less overhead than character values
When creating indexes, try to make them unique indexes
Columns used in joins should have an appropriate index
Don’t accidentally add the same index twice on a table
Don’t over index your OLTP tables
Remove unused indexes.
Re-indexing
Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database
Frequency depends on transaction activity in database
This will rebuild the indexes so that the data is no longer fragmented
Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance
Use DBCC DBREINDEX(@TableName,’ ‘,90) for specific tables
Use Update Statistics Command
after bulk insert/update to table
Index Tuning Wizard
Powerful tool designed to help you identify existing indexes that aren’t being used
Recommends new indexes that can be used to help speed up queries
Excellent starting point for indexing if you aren’t sure what to index
Index Tuning Wizard
Don’t blindly accept every recommendation made by the wizard
Sometimes the wizard will not recommend an index
Review each recommendation carefully, sometimes rewriting a query will do more good than adding an additional index
Conclusion
Indexing properly can dramatically improve overall query and application performance
There are many factors to consider when deciding to index
Index wisely
A cool widget enables your website visitors to get in touch with you in real time, directly on your Instant Messenger (MSN Messenger, GoogleTalk, Yahoo! Messenger, AOL AIM, ICQ, Jabber…). If your messenger status is online, visitors can send you an instant message through the Plugoo Buddy in your messenger buddy list. You can thus chat with them; answer their questions; more over communicate with prospect customer when they need you.
Site visitors need neither to download any software nor create an account before they can contact you. They never have access to your instant messaging information (username). Conversation between a visitor and you is strictly private – other simultaneous visitors on the website don’t see the conversation. You don’t have to change your habits; you keep using your favorite messenger on your desktop or from your mobile phone.
You embed a Plugoo on your web site. Visitors on your website can see the Plugoo and write through it a message for you.
As this Plugoo is directly connected to your Instant Messenger, it forwards you in real time all the messages your website visitors can send to you. You reply to your visitors from your Messenger while they are currently browsing your website.
You can also use myPlugoo, a simple link http://www.myPlugoo.com/… to let your friends IM you from any web site, email or blog. For instance, you can add this link to your email signatures or to your blog/forum comments… This way, your emails recipients or other Net surfer have the opportunity to pop-up your Plugoo Widget with just one click and start a LIVE conversation with you!
For more details, surf http://www.plugoo.com/
Multithreading is a powerful tool for creating high performance applications, especially those that require user interaction. Microsoft .NET has broken down the barriers that once existed in creating multithreaded applications.
Newer operating systems, such as Windows 2000, support pre-emptive multitasking, which allocates each thread a time slice. When the time slice of the currently executing thread has elapsed, the thread is suspended by the operating system, context of the thread is saved, context of another thread is loaded, and the other thread then resumes execution according to its previous state. This gives the appearance that multiple threads are executing at the same time and helps prevent the system from becoming unresponsive from a single thread. On systems that have more that one-processor threads are distributed across all of the processors so there really are multiple threads executing at the same time.
.NET has been designed to support multi-threaded operation. There are two main ways of multi-threading in .NET: -
Every thread has a priority. You can assign different priorities to the threads in application. That means you can tell the system which thread can be done first, which thread can interrupt others and which thread cannot be interrupted. Each thread with the highest priority can interrupt a thread with a lower priority. Below are values for thread priority -
A thread can be stopped for a given period of time. To make a thread stop you can use sleep method with number of milliseconds to sleep. The thread will resume after the specified milliseconds.
We can stop a thread by using suspend() method. This way thread will wait until you call it back to action. To call a thread back to action you can use resume() method.
We can stop or abort a thread at any time by using abort() method. This will destroy all the data related to that thread.
.Net makes it easy to add multithreading to your application. By this you can make the application more interactive and can increase the user’s experience.
Happy Programming!
SQL Injection is a technique where an attacker/hacker creates or alters existing SQL queries to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build a SQL query.
Using SQL injection, a user can damage the database, delete tables, insert fake data into database, steal secure information from the database and can delete the database as well.
To avoid this issue, the code should have the ability to avoid this injection. There should be proper validations on the server end to avoid SQL injection.
The following example will make the process of SQL injection clear.
Example: Login form - when you enter text in the Username and Password fields of a login screen, the data you input is typically inserted into an SQL command. This command checks the data you’ve entered against the relevant table in the database. If your input matches table/row data, you’re granted access. If not, you’re knocked back out.
How to Test for SQL Injection Vulnerabilities:
Suppose we enter the following string in a Username field: `OR 1=1
The authorization SQL query that is run by the server, the command which must be satisfied to allow access, will be something along the lines of:
SELECT * FROM users WHERE username = `USRTEXT `AND password = `PASSTEXT`
…where USRTEXT and PASSTEXT are what the user enters in the login fields of the web form.
So entering `OR 1=1 — as your username, could result in the following actually being run:
SELECT * FROM users WHERE username = ` OR 1=1 — `AND password = `
1 is always equal to 1. So if you grant an access this means website is not secure.
Prevention Techniques: These are some of the few basic techniques that, if applied to the code, will go a long way in making the website more secure and robust.
Editing Lengths Of Form Components: There should be proper validation for field lengths. To restrict input fields to the absolute minimum- usually anywhere from 7-12 characters is fine. Doing so will make long queries unable to be input, since the field is only enough characters for smaller queries. This will actually not prevent an SQL injection, but will make the work harder for those trying to make use of one.
Data Type Validation: There should be proper data type validation implemented on all the form fields. Numeric fields should allow only the numeric values and text field should allow only the textual data. If a user had to input an age, make sure the input is an actual number. If it was a date, make sure the date is in proper format. Using this we cannot avoid sql injection, however it makes work harder for those trying to exploit an SQL server.
User Privileges: User privileges at database level should be applied properly. The main user that will be used in creating connection should not have the privileges to delete tables or database etc. It is better to be able to create a “super user” in one’s own database that can create, drop, and edit tables at will. The security-obsessive webmaster will want to make individual users that can only do one or two tasks at a time. This method is still useful for throwing attackers off track, as well as minimizing risk from areas of a website that aren’t critical to the security of the database.
Magic Quotes: Magic quotes are horrible for portability issues, performance issues, and they mess with other data that doesn’t need to be escaped
No matter how good a developer is, each one makes few mistake while writing HTML code. And I am talking precisely about mistakes which the Search Engines don’t like - some small ones and some fatal search engine disasters. These mistakes happen not because we don’t know how to write an HTML code but because after we are done writing the code, we say to our selves, it’s done and it’s looking fine; why bother change it now for Search Engines. Not every developer is a Search Engine guru and very few know (or bother to know) that couple of such coding mistakes here and there might cause the site to choke to death. But don’t panic yet, it’s easy to avoid these mistakes - just consider the following while you code a website next time:
Make it a thumb rule to avoid repeating yourself. If it’s a commonly used object property, place it in CSS. If it’s a repeated Javascript, store it in an external file and call where ever required.
Don’t mess up the code with unused or unclosed DIV’s. If a DIV opens, it should close as well and if it doesn’t, it shouldn’t open. It’s as simple as that.
The most common web developer mistake - every page has it’s own unique title and description, please do not put the title and description tag in the include files.
Please make all links and references to images, CSS and JavaScript root relative by starting them with a slash, “/”. Dreamweaver users, please set the “Links relative” option to “Site root” in the Site Definition wizard. This is handy because root relative links don’t break when files are moved from one directory to another.
Don’t put too many files in a single directory. Keep the heirachy going and manage the files in different directories.
Use Validator to keep the code and CSS clean. Clean code is easier to manage then something that’s messed up.
Don’t use <br> for <p> tag as it’s unprofessional and results in inconsistent layout in different browsers.
Avoid spacer graphics and nested tables. Use heading tags, unordered lists and numbered lists to organize content instead.
Make it a habit to put a forward back slash ‘/’ at the end of every URL.
Image credit:Flickr
While every one around the SEO corner is busy hoarding the page rank of their site using nofollow attribute in quest to rank the important pages of a website, I am quite sure this practice (page rank sculpting) is just an accident waiting to happen as webmasters will abuse it up to an extent that Google will do what they have done to ‘keyword meta tag’ – start ignoring it. After the public announcements and claims done by Rand Fishkin, Stephen Spencer, Danny Sullivan and even Matt Cutts, that directing the link juice to the important pages of a website improves the Search Engine Ranking of a website, the SEO community is using nofollow on anything they don’t want to rank for, claiming it a ‘wastage of link juice’ otherwise. But I personally believe that this nofollow practice is taking the SEO community no where, as webmasters have a license to get away to rank their websites even with a poor internal navigation and hence poor user experience. So obviously the nofollow era is facilitating more spammy websites making it to the top of the Search Engine lists. It’s just matter of time, when Google will take an evasive action.
If we have a look at the origin of a nofollow attribute, we find that nofollow attribute was made with the primary motto to combat comment spam (which it has failed miserably as comment spammers are still employed). Further Google found that nofollow can also help Google bots to firstly determine the most important pages out of huge websites with complex blog categories in little time and secondly the webmasters could use nofollow while linking to some website content which they don’t want to get associated with and vote to. Here is a recent precise statement made by Matt Cutts on use of nofollow attribute:
“The nofollow attribute is just a mechanism that gives webmasters the ability to modify PageRank flow at link-level granularity. Plenty of other mechanisms would also work (e.g. a link through a page that is robot.txt’ed out), but nofollow on individual links is simpler for some folks to use. There’s no stigma to using nofollow, even on your own internal links; for Google, nofollow’ed links are dropped out of our link graph; we don’t even use such links for discovery. By the way, the nofollow meta tag does that same thing, but at a page level.”
Now getting back to my anti-nofollow visionary, the nofollow tags also facilitate a fake information architecture. As we know Google ranks the sites with solid internal navigational architecture higher on SERP as these are the sites which provide rich user experience to the Google users as the important content is just 3 clicks away, nofollow isn’t helping to their cause. Coz what most webmasters are doing at the moment is hoarding the page rank and shooting it on the targeted pages even when the target page is nothing but pure crap and weakly linked from other pages of the site.
Having said that, please don’t take me wrong, as I am not questioning the effectiveness of page rank sculpting, because it’s working great at the moment, but I am not too sure how long will this nofollow rampage last: I am afraid not too long. So I have a simple advise to give – use nofollow but only after you have crystallized your internal navigation and don’t rely 100% on this nofollow ploy as you might soon see Google derank the websites with weak internal structure indulging in page rank sculpting. I am looking forward to SMX Advanced and hopeful that it might clear up the mounted clouds over rank sculpting dilemma.
In my last post on SEO feast, I offered you so many things to eat – PR Bar, optimizer beer, Energy drinks and what not. But did you ever wonder, what do Search Engine bots eat? Yes they love eating fresh content & HTML on your website; but there are few things on your website, that you never want to feed bots with like - flash, silly javascripts, duplicate content, affiliate links (that pass link juice) etc. So how do you stop these dumb bots – Simple, by defining robots meta tag values:
<meta name=”robots” value=” <value> “
I just happened to stumble upon this cute flicker pic of five robots, I could relate to Google, Yahoo, MSN, Ask & Altavista. So I just simply used my paint brush and wrote their names accordingly. Looks so cool to me !
And now that you have met the bot family, let’s discuss different values you can serve to these witty Search Engine bots:
ge will not appear in its results.BTW, you may also want to go through our comprehensive article on Working with robots.txt file. I hope you enjoyed the post. Cheers!
Image credit: flickr.
Web BuzZ-BuZz, Web 2.0 BuzZ-BuZz
There has been so much of buzz about Web 2.0. Many webmasters have embraced weblogs, mash-ups, RSS feeds, bookmarking, social networking, tag clouds, wikis etc. But what excites me most about Web 2.0 are Tag clouds that so many website owners are using these days, for higher website rankings and an even richer user experience. For those who haven’t heard of these lexical clouds before, this is how a tag cloud for flickr looks like:
There are so many websites today, that are using tag clouds but the reason why I mentioned only flickr is because flickr was the first Web 2.0 website to use this concept successfully. Check out the tag cloud for Technorati:
Tag clouds, for better user experience
Tag cloud according to Wikie is - “ A visual depiction of user-generated tags used typically to describe the content of web sites. Tags are usually single words and are typically listed alphabetically, and the importance of a tag is shown with font size or color. Thus both finding a tag by alphabet and by popularity is possible. The tags are usually hyperlinks that lead to a collection of items that are associated with a tag thus describing the item and enabling keyword-based classification and search of information”. Flickr says - “Tags are like keyword or category labels, and they can help visitors find items which have something in common“. The simplest way to explain a tag cloud is that it is a Web 2.0 way of categorizing, organizing and navigating your website content with keyword links.When a user saves, bookmarks or votes (what ever they may call it) for some blog post, picture, video etc on a web 2.0 website like flickr, technorati, del.icio.us etc, he/she has to enter a keyword that the user thinks best describes it.
If you look at the picture above, you will notice that each tag (or keyword link) have different font sizes. The font size of these tags depend on the number of times keyword has been used to tag that an item. More the items are associated with the keyword, the more it is popular and bigger will be the size of the font. So it’s an intuitive means of navigation on a website.
When the user clicks on a tag, he/she is navigated to a tag page which on flickr looks likes this:
The tag page comprises of the most recent items tagged by visitors using that particular keyword they have clicked on. Please note that these tag clouds are not necessarily the only way to reach to the items on the website, but they serve as an alternative & intuitive way of finding them. Most often then not, you will also find the item’s tag adjacent to the item, clicking on which will take you to the other items tagged using that keyword tag. ‘Related tags’ are the other common tags which contain that same item.
Tag clouds: not just usability, it’s also an amazing SEO tool
So you still have doubts, if you should incorporate these lexical clouds on your website or not? I will give you one more good reason to do so - With Tag clouds, you consolidate your website’s internal linking which means you have better strategical control on your link equity (juice) and since the anchor text of these tags is extremely keyword rich, it boosts the ranking of your website or weblog far more than what conventional navigation bar does and hence increases the website’s ranking for significantly larger variation of keywords. You yourself will be surprised to see the keywords tags (including long tail keywords) that people will associate your content with (even wordtracker would have failed to find those). Bloggers will be glad to know that WordPress 2.3 comes with tagging in-built. Ultimate tag warrior is an awesome plugin to create a colorized tag cloud or heat map.
Other Tag cloud resources:
How to make Tag clouds using PHP, MySQL, CSS
Tag clouds using ASP.Net
Have you ever had to make the choice for your business of what shopping cart to use? You can have your own customized solution developed, or choose from a range of pre-built shopping cart options available like Volusion, XCart, Shopsite etc. Each of these shopping carts provide a host of features available ranging from a robust back end, to a user friendly front end along with SEO compatibility.
At first it’s quite tough when you know nothing about each of the different types of shopping carts and software packages. Which one do you choose is a tough choice to make.
Today I’m going to do a review of an all-in-one externally hosted shopping cart system, called Volusion. My review is mostly based on what I have read on the “Volusion” website and our own experience while integrating “Volusion” for a few of our clients.
All in one solution Volusion provides all the tools to create an online ecommerce company. Their software provides total ecommerce solutions including ROI tracking software, SEO optimization software, hosting by RackSpace.com, email marketing software, affiliate software and much more. This ensures that the buyer will not have to work with different providers for all the above mentioned features thus saving time, energy and money.
The components of the solution are completely customizable, and the site can be designed as per the industry and the target audience. Volusion gives enough creative independence to the site owner to have his website designed as per the requirements.
Some companies charge a percentage of sales from the site owner, but Volusion does not. This ensures that the cost of site maintenance remains the same as your online business grows. Volusion charges only a monthly fee for the hosting of your online store, and additional band width (as your business grows) can be purchased at an additional cost.
The pricing structure for Volusion plans is good, with an entry level plan starting at $20 a month, going up to a $100 platinum unlimited plan. All in all, Volusion seems to be providing good shopping cart software with almost all the required features. There are often inherent problems with jack-of-all service providers, but looking through the features of the Volusion website, they certainly have their game together based on their website presentation.
When searching for a web development company for a new website, it is wise to also evaluate the level of post development support that you can expect to receive.
Post development support may include guidance on how to use some of the back end features, adding any new features at a later stage, receiving suggestions, evaluations of new ideas etc. What is often overlooked is support in terms of website marketing. Just having an entity out there in the World Wide Web is not enough. It is critical that just before the website is about to be completed, a complete Online Marketing Plan is worked out so that it can be launched along with the website. This Online Marketing plan should focus on getting traffic and conversions both in the short term and long term after finding out the amount and kind of dependency required on search engines for getting traffic, exploring other sources of getting traffic like banner advertisements etc., This should be a complete plan that would also evaluate the traffic to find out how it is interacting with the website, what is the conversion rate etc. and necessary actions should be taken on the basis of this to get a high conversion rate.
Hiring a development company that can provide support in working out a right marketing mix as well, ensures that your website is a successful online business.
