Traffic to your site: Try to win a poken if you like innovation.

On my blog I do several experiments to get more visitors to my blog site. For instance:

1. Is their a difference in publishing on monday or at the end of the week?
2. Which keywords are popular to get traffic?
3. Commenting on other blog sites.
4. Write on someones wall about my blog site when accepting a facebook request.
5. Twitter about a new post.
6. Mentioning my blog site in my LinkedIn profile.
7. Follow other bloggers.
8. Etc......

Image credit: Mazda6 (Tor)

My boss is doing an experiment which I have not tried till now. He started a competition to win a poken. Let see if it works. The principal of a community is not only to get something but also to give something. This applies for both sites. Another way to give is to write interesting content. And indeed, I like to read the blog of my boss: http://www.aadjemonkeyrock.com/. I have no idea if the experiment will work. We will see.

Do you have experiences with a competition to give something away? Was it successful?

What is Twitter, why should I use it in my daily business?

Since more than a year I'm an user of Twitter. Sometimes I got questions from people why I uses Twitter. This weekend, I  got this question from the 90 years old grandma of my wife. She does not have a computer but has read some articles in the newspaper about Twitter. See some examples of twitter articles in the dutch newspaper NRC Handelsblad.

The first reaction of people who see Twitter for the first time is:
Why should I tell what I'm doing?
Of course this is a valid question. Please read next post of Computerworld: Twitter: The how to get started guide for businesspeople to get more understanding of the use of Twitter.

Happy Twittering

Guidelines to write well performing queries.

During my daily work I have done a lot of performance analyzes at customer sites. One part of a performance analyze is analyzing resource intensive queries on the database. For instance CPU usage, disk I/O. Sometimes these queries can be improved by rewriting the query without losing functionality. This blog contains the most common mistakes I have seen in bad performing queries.

Image credit: Dave Ward

1. Avoid use of SELECT * in SQL queries. Retrieve only the columns you need. This will avoid bookmark lookups and will result in less data traffic from SQL server to client. Do you buy everything in the supermarket and at home throw away what you do not need?
   Example:
   SELECT ResourceID, FullName
   FROM Resources
   
   Instead of
   SELECT *
   FROM Resources
    
2. Create only result sets when necessary. This will avoid unnecessary data retrieval. If you want to know if something exists it is not interesting to know how much time it exists.
   Example:
   IF EXISTS (SELECT 1 FROM Resources WHERE RESID > 1005)
   
   Instead of
   IF EXISTS (SELECT COUNT(*) FROM Resources WHERE RESID > 1005)




3. Look to the execution plan of your query. It should contain an Index Seek instead of an Index Scan or Table Scan.
       
4. Use parameterized queries for queries which will be used a lot. This will save unnecessary query plan generations and the Procedure cache is used more efficient.
   Example:
   SELECT FullName, PhoneNumber
   FROM Resources
   WHERE RESID=@P1
   
   Instead of
   SELECT FullName, PhoneNumber
   FROM Resources
   WHERE RESID=1005
    
5. Write query with correct data type. Check data type of a column via sp_help <tablename>
   Example:Column ID Datatype INT
   SELECT Fullname
   FROM Resources
   WHERE RESID = 1005
   
   Instead of
   SELECT Fullname
   FROM Resources
   WHERE RESID = '1005'
    
6. Avoid SQL roundtrips. Combine queries into one query. Buying a bottle of beer and chips bag at once is always faster than first buying a bottle of beer and go home and then go back to the supermarket to buy a chips bag.
   Example:
   Query 1: SELECT FullName FROM Resources WHERE ID = 1005
   Query 2: SELECT PhoneNumber FROM Resources WHERE ID = 1005
   
   Combined Query: SELECT FullName, PhoneNumber FROM Resources WHERE ID = 1005
    
7. Remove JOINS which are not part of the SELECT or WHERE clause.
   
   SELECT A.Column2, A.Column3
   FROM Table1 A
   INNER JOIN Table2 B ON A.Column1 = B.Column5
   WHERE A.Column4 = 20
   
   Remove INNER JOIN Table2 B ON A.Column1 = B.Column5
   
   New query:
   SELECT A.Column2, A.Column3
   FROM Table1 A
   WHERE A.Column4 = 20
    
8. JOIN a table on columns which are part of an index. In most cases one of the 2 columns is the primary key column.
   
   SELECT C.Name
   FROM Activities A
   INNER JOIN Contacts C ON C.ID A.ContactID
   WHERE A.TYPE = 10010002
   
   C.ID is the primary key column of table Contacts.
   A.ContactID is part of an index on table Activities.
    
9. Avoid a lot of OR statements in the WHERE CLAUSE. Use a UNION. Because of the 3 different columns in the WHERE clause the clustered index will be used.
   
   Example: Table with Clustered index on ID and 3 indexes on: City, Type and the last one on Birthday. Next query will result in a clustered table scan.
   SELECT FullName
   FROM Resources
   WHERE City = 'Delft'
       OR Type = 1
       OR BirthDay > '2000-01-01'
   
   Next query will use the 3 indexes:
   SELECT FullName
   FROM Resources
   WHERE City = 'Delft'
   UNION ALL
   SELECT FullName
   FROM Resources
   WHERE Type = 1
   UNION ALL
   SELECT FullName
   FROM Resources
   WHERE BirthDay > '2000-01-01'
10. Avoid ORDER BY columns which does not change the ORDER BY. This is unnecessary overhead.
    Example: Tabel Resources with Primary key on ID and Index 1 on RESID and Index 2 on FullName
    SELECT FullName FROM Resources
    WHERE City = 'Delft'
    ORDER BY RESID, FullName
    
    Remove the FullName in the ORDER BY. This will avoid an Index Scan on index FullName and a Hash Match
11. Configure your SQL Server management Studio to connect Read Uncommitted to the database. By default SQL Server management Studio will connect Read Committed. By connecting Read Uncommitted, you will avoid that you lock colleagues or will be locked by other colleagues.  In SQL Server management Studio select: Tools, Options, Query Execution, SQL Server, Advanced, SET TRANSACTION ISOLATION LEVEL: Read Uncommitted).
     
12. Test your queries on a database with enough content in it. Performance of a query on a table with only 10 records is always fast. It will only guarantee that the syntax of the query is correct. You can't make any conclusions of this query regarding performance. If your application should perform on a table with 1 million records. Test your queries on a table with 1 million records.
     
13. Avoid using temporary tables. Creation of temporary tables can cost a lot of disk I/O.
     
14. Use SET NOCOUNT ON at the beginning of SQL batches, Stored Procedures and Triggers.
     
15. Define on every table a primary key. If you do not configure, SQL Server will do it in the background. This is additional overhead.
     
16. Incomplete or over complete WHERE CLAUSES. Read the database documentation of the application to understand the business logic. Knowing the business logic you can define the correct WHERE clause.
     
17. UPDATE multiple records in one query. For instance one update statement for 10 order lines:
    
    UPDATE OrderLines
    SET Modified = '2009-02-02 10:00:00.002'
    WHERE ID in(14235,14236,14237,14238,14239,14240,14241,14242,14243,14244)
    
    Instead of
    UPDATE OrderLines
    SET Modified = '2009-02-02 10:00:00.002'
    WHERE ID = 14235
    
    UPDATE OrderLines
    SET Modified = '2009-02-02 10:00:00.003'
    WHERE ID = 14236
    .
    .
    .
    UPDATE OrderLines
    SET Modified = '2009-02-02 10:00:00.013'
    WHERE ID = 14244

Enterprise Search features in Exact Synergy Enterprise

In my previous post I explained why we build our own search engine in Exact Synergy Enterprise. In this blog post I will describe the features we have build in the search engine of Exact Synergy Enterpise.

Image credit: paloaltosoftware

• Relevance ranking. Display the most relevant documents for your search. Relevance ranking of a word in document is based on different components. For instance, the number of times a word exists in a document. Is it part of the title etc....
• Attachment search. Documents can have attachments. These attachments are indexed on the same way as the document content itself.
• Synonyms. Within an Enterprise people can talk about the same things in different ways. For instance, within the company people talk about the Supportdesk. However customers are talking about the helpdesk. Assume a customer is doing a search on your web site for helpdesk, you want to display the contact information document of your Supportdesk. For this situation the Supportdesk is a synonym for Helpdesk.
• Search suggestions also known as Do you mean? Everybody can make type mismatch which you did not realize when you type your keywords. If you type 'debttor' we will suggest 'debtor'.
• Expertise search. Within your company you have people with a lot of knowledge: The Experts. New people in your company, or people on different locations should find these people. The experts are based on the documents they have written. If you do not write documents about your expertise the Expertise search will not find you. The basis for an expert is to share his or her knowledge.