How can you increase your query performance in SQL Server

Partitioning is the key, You can greatly increase your query performance by making use of the partitioning, partitioning is the horizontal splitting of rows in a table or index defined on a table, smaller logical units that act as one table or index, this would improve your querying performance because you are querying several smaller tables rather than on much larger one. And its advantages increase when the hosting server has multiple processors, which allow the partitions to be queried in parallel in the same query, and when the partitions are spread across multiple file groups.

 

In SQL Server 2005, administration is much easier because the database engine maintains the partitions in the background, allowing you to focus on other tasks (remember speed to the market mindset).

It’s not practical to partition every table in your database. The typical usage scenario for a partitioned table is one that is very large and is experiencing degrading performance over time. I’ll walk you through the steps of partitioning a large table.

 

Partitioning example:

The following example demonstrates how to partition a table that stores archived data to increase query performance. Before diving into the code for the example, here’s a brief overview of the moving parts that make up a partitioned table.

 

Partition Functions

When a table is partitioned, it is broken horizontally into smaller table partitions that are used together to assimilate the whole table. To do this, SQL Server has to know how to break the table into smaller parts. This is where a Partition Function comes into play. A Partition Function is the logic that maps the rows of a table or index into predefined partitions based on the values in a field of data. This will be the first partition object I create in the example.

 

Partition Schemes

You tell SQL Server how to horizontally split a table with a Partition Function. You also need to indicate how the partitions will be stored in the database. In SQL Server 2005, you map a table partition to a filegroup, which is basically a logic grouping that the database uses to store its data files and transaction log files. Every database has at least one filegroup named the Primary filegroup and additional filegroups for administrative and performance purposes. In a Partition Scheme, you can define that all partitions are mapped to the same filegroup, or you can use the scheme to split up the partitions across filegroups. The advantage is that, when the filegroups are split across individual disks, SQL Server will be better equipped to use resources when retrieving data. This advantage becomes even greater when you are running queries against the partitioned tables on a server with multiple processors.

 

Creating a partitioned table:

Step one is to define the partition function that you will use to map out the partitions in the table. In the Partition Function defined below, I use three partitions, one for each product type in the SalesArchive table, which I will define later. These partitions will essentially break the SalesArchive archive table into three different tables that are automatically maintained by SQL Server.

 

CREATE PARTITION FUNCTION [pf_Product](VARCHAR(10)) AS RANGE LEFT

FOR VALUES (N’ProductType1′, N’ProductType2′, N’ProductType3′)

 

The range in which a function is defined determines which boundary the partition values will belong. RANGE LEFT Specifies that partition values will be less than or equal to the values defined in the Partition Function.

 

The Partition Function is created, so now I need to create the Partition Scheme. For example, I will map all partitions to the primary filegroup. If I wanted to map the partitions to different filegroups, I would include the filegroup name ordinally in the filegroup list. Here’s the script for the partition scheme:

 

CREATE PARTITION SCHEME [ps_Product] AS PARTITION [pf_Product]

ALL TO ([PRIMARY])

 

Now that I have the tables set up for the partitions, I can create the SalesArchive table and load data into it. At the end of the CREATE TABLE statement, the Partition Scheme uses a field name from the table to tell SQL Server how to map the data from the table to partitions.

IF OBJECT_ID(‘SalesArchive’)>0
DROP TABLE [SalesArchive];
GO
CREATE TABLE [dbo].[SalesArchive]
(
[SaleID] [int] IDENTITY(1,1),
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)ON [ps_Product](Product)
GO

DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=10000)
BEGIN
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType1′, DATEADD(mm, @i, ‘3/11/1919′), DATEPART(ms, GETDATE()) + (@i + 1))
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType2′, DATEADD(mm, @i, ‘3/11/1927′), DATEPART(ms, GETDATE()) + (@i + 2))
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType3′, DATEADD(mm, @i, ‘3/11/1908′), DATEPART(ms, GETDATE()) + (@i + 3))
SET @i = @i + 1
END

 

I’m going to make sure the partitions are working correctly by running some queries on the data. The following query returns all rows from the SalesArchive table and uses the $partition function to indicate which partition the returned row is a member of:

 

SELECT $partition.[pf_Product](Product), *

FROM SalesArchive

 

This query returns all partitions that are mapped to the SalesArchive table:

 

SELECT * From sys.partitions

WHERE OBJECT_NAME(object_id) = ‘SalesArchive’

 

Here are some great online references to learn more, and see some performance comparisons:

http://www.sqljunkies.com/article/f4920050-6c63-4109-93ff-c2b7eb0a5835.scuk

http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

http://www.microsoft.com/technet/technetmag/issues/2007/03/Partitioning/default.aspx?loc=en

 

ZOHO beats Microsoft and Google!

A small private company called Zoho is making an open challenge to both Google and Microsoft in the office applications! While Google is trying to take on MS Office by providing online office applications with additional and great feature set, Zoho is attempting to give users the free full feature set and take advantage of the benefits of a web-based experience with tagging, sharing, and collaboration.

Microsoft still hasn’t released an online version of the MS Office suite, so we don’t know what its online office suite strategy will be, except if our sweet Egyptian insiders could tell us =)

Try it now www.zoho.com, and let us know what do you think about Zoho’s chances against Microsoft and Google?

Zoho includes, Zoho writer (rich editor), Zoho sheet (spreadsheets), Zoho show (presentations), Zoho meeting (online conferencing), Zoho Projects (Projects management tool), Zoho CRM (CRM solution), Zoho Creator (DB online applications), Zoho Mail (email client), ..etc Check out these screenshots:

Additional links:
Zoho on facebook: http://www.zoho.com/news/zoho_office_apps_on_facebook.html
Zoho wins a “100 best products of 2007 Award”: http://www.zoho.com/news/zoho_award_from_pcworld.html

Worst Egyptian IT companies to work for

The worst mid/large-size Egyptian IT companies to work for, everyone would have his own list, after long discussions with friends and the people who have worked in different Egyptian IT companies, we came up with the following short list for the worst companies to work for, I’ll try to maintain this list from time to time:

Sakhr:  you won’t get paid soon enough, could you imagine that someone never gets paid for more than 2 months what if s/he has a family?! I think this wasn’t the right move from Sakhr’s management to risk its big name among the IT communities even if there was a market drop, also they don’t follow a good management model, a specific  problem I heard from an insider, that the fair (not good enough) people have been promoted into managerial positions where some other great young people are below them, an inherited promotion system.

Linkdotnet: you will work more than what you are supposed to work to catch their fuzzy deadlines, not good enough environment, many many people are complaining from their unprofessional management staff, and they don’t have competitive salaries.

DMS:  the worst salary you can imagine, their employees have to sign a contract with a huge amount as a penalty term, and some other papers when they start on certain projects with penalty amount relative to the project size.

Delta: the developers have spy cameras above them! I can’t imagine how did they decide to deploy this system, but I think it’s all about their inherited mentality, and above all, they are still giving the nineties’ salaries.   

I’m currently trying to convince the Egyptian CIS Alumni Association to publish an annual report about the worst and best Egyptian IT companies operating in the Egyptian market based on a rich study, so it can help the CIS fresh graduates to find their best place to work.

Web development trend

The social networking boom like facebook and linkedin, and the e-commerce as well, has increased the demand on the web development skills, pushing the developers’ pay up 26 percent in the past 12 months. According to a research by SkillMarket/ATSCo (Association of Technology Staffing Companies).

 

There was a time when web development was about CGI and JavaScript, not any more! Now many bloggers and articles from various sources suggest a career path focused on honing skills for web development can pay rich dividends!

 

Research disaster around

I believe the right diagnosing of the problem is the first block of the problem solution, we are not here to blame anyone or blog more about the Egyptian crisis, we will try to get to the crux of the problem, discuss, propose thoughtful solutions, exploring the challenges and see how could our generation roll out the research in Egypt.

 

First, we begin with the universities, everyone knows the teaching assistants misery inside the Egyptian universities, for who don’t know, I’ll explain it briefly, the teaching assistants are the brightest, and best hardworking students in any scientific schools in Egypt, none of these schools can support them financially where they could live a respectable life inside Egypt and also support them financially so they could uncover their innovative power into a touchable product or patents, and for your information some of them know this and some others don’t, their problem has lots of branches and consequences, we can discuss this story from stem to stem later. These good minds can’t find the fine place to grow and give anything back to the community as long as they can’t survive themselves! Ok, now let’s take a closer look on the potential legislation gaps,  I had a conversation couple days ago with Prof. Ahmed Hamad – one the most respected well-known professors  among the CIS schools – so I can figure out the following, the point behind it that there no motivations for professors to mentor and help the young researchers, after the professor completes his research set (individual and group researches) in its time limit s/he finds nothing more to do, and it leaves no choice except looking for outside business, and this shots the research in heart and head.

 

Where is our national strategic research plan?! 

The strategic orientation for the research projects is a must, the world now is widely open so you can research in whatever you want and you can easily find all the resources you would need, but this is not the matter, the matter is the set of fields where we should invest into, and suit our economical growth and push our current national developing plans. this should be defined by a high level authority that sets the strategic research plans which will also play some other roles, I know there is an entity playing this role but it’s not effective and it hasn’t the right culture and mindset to make a big moves toward the research strategic planning and deals with it as our nation final fight. A quick example: why should we invest our time and money on the wireless security?! There are many questions come up once we select a topic, how would this benefit our community? Our economy? Our industry? And if there is an answer for this, would we have the fund where we could compete the globe?!, this is not a pessimistic way to look at the things, in my opinion we should always push our time and money into our strategic fields where we could get the optimum revenue and inject our economical growth at this stage.

 

Where is our mentor?!

Everyone has to have a mentor, one of the greatest helpers and the connecting point between the community and the university is the Alumni Associations which we almost don’t have at all. These associations work on mentoring the fresh graduates and undergraduates, and settle them on the right track, which should be aligned with the national strategic plan. And it also guarantees the continuous assessment and training for our human capital.

 

So, I can summaries the problem into the following points:

• The human capital is not appropriately supported inside the Egyptian university
• No strategic planning for the research in the universities and research institutes, and so Egypt
• Almost no communication between the industrial entities and the university
• No available knowledge base for the needs of the social and industrial sectors in Egypt
• No strong Alumni Associations

One last closing fact to let you know how big the disaster is, the united sates issued an average of 495 patents every single day last year, and the entire region (Arab World) has granted a total of exactly 298 patents over the past eight years!! Imagine! It’s figure that speaks volumes about innovation..

 

Canada immigration tips and tricks!

Are you planning to immigrate to Canada? Did you submit your immigration application yet? Is your application on the processing state? For all people all over the world who are looking forward  to live and work in Canada, especially who are submitting their applications into the Cairo, Egypt visa office. this series  is devoted to the tips and tricks which would help you speedup your Canadian immigration application preparation and processing.

 

Stay tuned!