Per Person Per Day Food Requirement - The Easy Version

The mass taken in the following calculation is 50 kg per person. This is the average mass of population when taking into account the entire population including men, women and children. Mass of a grown up man is 75 kg on average, of a grown up woman is 62.5 kg.

40 calories per kg body-weight per day is for average work. Farming requires 50 calories and office work 30. At the two extreme ends, an athlete requires 60 and a person in comma 20.

Food has macro nutrients, micro nutrients, fiber, water. This is the water present inside the food itself i.e. not the water drunk separately. Usually 10 to 20 percent of food by mass is fiber and an equal amount is water.

Per day per person, macro nutrients are consumed in grams, micro nutrients in micro or milli grams, fiber in grams, water in grams.

The three macro nutrients are carbohydrates, proteins, fats. The two micro nutrients are vitamins, minerals.

Calories come from macro nutrients only. Micro nutrients are used for repairing of body. Fiber is used for diluting the food so that it can be digested easily. Water is used for cleaning and cooling.

Even though all macro nutrients provide energy, not all of the macro nutrients are used for the actual physical work of body. Only energy from carbohydrates is used for actual physical work. Energy from proteins is used only if energy from carbohydrates is not enough; usually proteins are used for providing the stuff that repairs the body. Fats are used as an energy reserve and to make the fats layer below the skin, this layer kills most of the germs and viruses that tries to penetrate the body and keep body warm. Vitamins act as catalysts. Nutrients are also used as stuff to repair the body along with proteins.

In spite of energy used for physical work comes from carbohydrates and not from proteins or fats, since they are also needed therefore in calculation of food requirements they are also calculated, so the following calculation is valid and highly applicable.

One calorie is 4.2 joules. One Calorie (with capital c) is 1000 calories. In food calculations calories is always Calories. Therefore, in this article wherever (other than this paragraph) calorie is written with small c or capital c it means the larger unit, the Calorie. One calorie is the amount of energy needed to raise temperature of 1 gram of water 1 degree Celsius. One Calorie is the amount of energy needed to raise temperature of 1 kg of water 1 degree Celsius.

Carbohydrates would be referred as CH, proteins as P, fats as F in the charts below.

Ideal Proportion of Calories from Macro Sources:

CH: 56% = 1120 calories = 280 grams (4.0 calories / gram).
P: 14% = 280 calories = 70 grams (4.0 calories / gram).
F: 30% = 600 calories = 65 grams (9.2 calories / gram).

Total: 100% = 2000 calories

Item   Grams    CH      P       F       Calories  Rs/Kg    Rs 

Fruit      250  31.25   -       -       125         90      22.50
Milk       250  12.20   8.30    9.10    167         66      16.50
Wheat      125  83.33  15.00    3.44    425         38       4.75
Oats        63  33.50   5.00    -       154         68       4.25  
Rice        63  43.75   6.25    2.72    225        130       8.13
Pulses      63  31.25  15.63    4.08    225        100       6.25
Chicken     16  -       3.91    0.20     18        250       3.91
Fish        16  -       2.81    -        11        250       3.91
Mutton      16  -       3.91    0.20     18        400       6.25
Beef        16  -      12.50    -        50        300       4.69
Oil         31  -      -       31.25    288        130       4.16
Sugar        8   7.81  -       -         31        130       1.01
Honey        8   6.25  -       -         25       1040       8.13
Butter       8  -      -        6.25     58        350       2.73
Dry Fruits   8  -       1.56    3.90     42       1040       8.13 
Spices      31  15.63   6.25    3.13    116        100       3.13

Total      969  265     81     64      1976                104.27
                 54%    16%    30%

All minerals needed by body can be achieved by above mentioned honey. Vitamins can also be achieved from the above mentioned items. Fiber and water are present in all of the above items except oil and sugar. Fiber and water are not present in oil and sugar because both of them have 100% place occupied by one or more macro nutrients.

Amount:

The amount calculated above is Rs. 104.265. It should be noted that the calculation in case of fruits and meat is for the actual thing, not for the shells and seeds and bones etc. It should be noted that the food purchased from market has about one third of such stuff. Therefore in case of fruits an addition of Rs. 11.25 must be taken into account because fruits must be bought 375 grams instead of 250 grams. In case of meat the majority of non-eaten stuff is bones and there is some fat still present when the food reaches the table. We can take a ballpark figure of one third there too. Total amount estimated for meat is Rs. 18.76 therefore Rs. 9.38 must be added because instead of 62.5 grams 93.75 grams meat must be purchased. Total additions therefore is Rs. 11.25 + Rs. 9.38 = Rs. 20.63. The final amount therefore becomes Rs. 104.265 + Rs. 20.63 = Rs. 125.

Note: This article was written using prices in Pakistani rupees in Karachi as of 23rd Sep 2010.

Optimum Resiliency

This post is about the optimum point of resiliency, in terms of redundancy. These are three concepts in the first line, let me explain them one by one:

(1) Resiliency: Resiliency is the ability of a system to handle problems while operating normally or near-normally. Its the extend to which the system can pass through unexpected and rare but huge problems, intact. A normal repairing system for normal day-to-day issues-solving is some other kind of resiliency I am not talking here. I am talking about surviving through problems when the problems hit all of a sudden and there is no time of repairing. I am talking about the first-class set of problem, the huge ones, the rare ones, like earth quakes, country breaking up in parts, tsunamis, even nuclear attacks. These kinds of problems are very rare to occur but they do occur, even nuclear bombs have been already used twice. Since these huge problems are very rare in occurance people tends to set aside little resources for them and when these problems occur entire systems simply wipes out.

(2) Optimum Point: Optimum in the sense of economics. Sure you can build very resilient systems (factories, companies, political parties, countries etc) if you not have to worry about economics but in real world financial laws must be obeyed.

(3) Redundancy: Redundancy is setting aside extra sub-systems to be replaced when a working sub-system fails. As long as the working sub-system don't fail, the redundant sub-system would sit idle. One very simple way to achieve resiliency is through redundancy. There are other ways too, like making strong systems but today I want to talk about only that kind of resiliency that is due to redundancy.

In natural world we see resiliency every where. In our bodies there are two eyes for example while one is enough for day to day operations. In a family there is a father and a mother, even though for most part only one is enough. In water supply we have rain water, river water, ground water etc. In a govt we have district, provincial and federal. In armed forces we have army, rangers, police. Multiple engines in an aircraft. Multiple types of food categories for each component of food, for example for carbohydrates we have grains, fruits, honey, sugar, vegetables etc, for proteins we have meat, pulses, egg, milk etc. Multiple items of food in each food category, for example multiples types of grains such as rice, wheat etc in grains.

One important thing that we must note about resiliency is that the redundant sub-system can readily work in the place of failed sub-system without any modification of the redundant sub-system, means it should be mission-ready all the type, we can't afford any adjusting time.

Another thing to note is that its always a sub-system that is redundant, not the entire system. Its because in middle of operation its hard enough to switch on a new sub-system and sometimes we can't simply jump to a new system altogether, for example, in middle of flight, if one engine fails, we can switch on another engine, but shifting to another aircraft altogether is pretty hard. Same way its hard enough to dig a well for water if a canal from a river fails and its near-to-impossible level of hard to squeeze water out of stored grains or leaves.

Another thing to consider is that, there are levels of redundancy even in sub-systems' level of redundancy. There is a primary redundancy, which can be switched on right away and start working and then there is a secondary level of redundancy that need adjustment to get switched on and start working. For example, in a family, a mother can replace most operations of a father, but an elder sister need some time to perform functions of a mother. Squeezing water out of grains or leaves or milk can be considered a secondary level of redundancy. In wartime, women can be sent to fight at extreme situations but requires deep training and mental adjustment.

So, what is the optimum level of redundancy? Before that, we must find out what is the minimum acceptable level of redundancy. In my opinion, its a factor of two for primary resiliency. We see that everywhere in nature: mother-father as engine of family, two eyes, rain water - canal water etc. Its like flying a two-engine aircraft, would you feel safe in that? The answer is, it depends on the environment in which you are flying. In clear sky, low altitude and peace time you may be confident but to be a bomber pilot in a night air raid on enemy's capital you better have a four engine plane. So, in my opinion, the maximum level of primary level redundancy in sub-system that you should be asking for is 4 and you should be happy at level 2 for all but extreme situations.

Understanding secondary level of redundancy, we may say that its those sub-systems that cannot perform for that role in normal situations but have potential to be transformed to perform at that role. For example, a toy factory in 1942's stalingrad was not intended to work as a weapons factory, but in the heat of world war 2's invasion of germans it was quickly transformed in a tanks factory and worked in that role till the end of war. It may also be considered as the role of a deputy, a deputy has potential to work as the officer but in normal situations it should not, only once the officer becomes unavailable (due to death, injury, missingness or retirement) that the deputy should start operating as the officer.

So, how much redundancy is optimum at secondary level? I think its a strict 2. Having 2 deputies is good enough. In normal situations, having one deputy is enough, its like having 2 redundant sub-systems at primary level. In extreme situations, having two deputies is optimum and enough, its like having 4 redundant sub-systems at primary level. Lets look to nature for some lessons, there are two eyes while one is enough, so there is a primary-level redundancy of 2. There are also two ears that can work somewhat like eyes, so there is a secondary-level redundancy of 1. Note that I didn't said secondary-level redundancy of 2 though there are two ears. Its because I am using cumulative in multiplication sense here, there are two ears but I am comparing them with two eyes as I have already taken in consideration primary-level redundancy of 2. Also note that the nearest physical sensory organ to eye is ear and the next nearest is too far to be considered.

So, what we learned today? Let me summarize. Other than the basic concepts and definitions, we learned that there are classes of redundancy, lowest is the economy class, then there is a crisis class and then there is a luxury class. When there is no class there is no redundancy but there can still be time-consuming repairing. So, the first line of defense is normal repairing, this solves out day to day expected problems but requires downtime and reduction in operations for the time the repairing is take place. An example for a natural system is an eye, lets suppose there is only one eye in a human being. From time to time, this eye requires repairing, for example when it gets red from over work, or when it gets a minor disease or when some dust gets in it. An example from humans is like keeping a guard, lets suppose we need guard only 8 hours a day. Even for that time we can't expect our guard to be available every day of year. He may get sick and requires downtime to get repaired. We can expect maximum 75% availability. Why 75%? Well 365 days a year, 15 dayz gazetted holidays, 350 days means 50 weeks, one off day per week, 300 days, 30 days of casual leaves, 270 working day is like 75% of 365.

Moving forward, we can have an extra guard or an extra eye. I am not saying that the new guard work in some other duty time of day than the first guard. I am saying that the new guard is available only at the duty time when the first guard is already available so we have a redundancy. This is primary-level redundancy of 2, I call it economy class of redundancy. This works great as long as there is no crisis like a war time or dust storm or frequent robberies. The combined downtime of the two sub-systems now reduce to perhaps 1/16 of the total time, that is instead of 25% off-days of the single guard, we get 25% of 25% off-days of the entire team of guards. Why 1/16, lets get that mathematically:

Suppose we have a system of two balls, one small (lets say a tennis ball) and one large (lets say a football). Obviously these two represents the two sub-systems, the two guards or the two eyes. Now, lets suppose each of these balls can be of any of the four colors. Lets take any four colors, for example RED, GREEN, BLUE, YELLOW. Ok, now lets suppose that one of these colors represents downtime and the other three represents available-to-work time. I would take YELLOW as a symbol of downtime. Now lets suppose that on any given day, the probability of downtime is same as the probability at any other day of the year.

To start the experiment, lets put a large number of balls of each type and color in an opaque bag. Lets suppose we have 1000 balls and the probability of getting each type of ball and each color of ball is same. Now lets draw two balls, one after another. What is the probability of getting both balls of YELLOW color? Since the two experiments involve different types of balls (tennis and football) the experiments are mutually exclusive. To draw one ball of any given color when total number of colors for the ball is 4, the probability is 1/4. To draw two balls of the same color, the probability is 1/4 x 1/4 in a mutually exclusive system, that is 1/16. Its equal to a downtime of 365/16 = 23 days per year. Large but manageable.

Note that we are calculating the effect on downtime by considering primary-level redundancy alone. We are not considering the effect of secondary-level redundancy. We should not depend on the secondary-level of redundancy in our calculations.

The next class of redundancy is the crisis class, specially designed to handle crisis. Its having 4 redundant sub-systems at primary-level when only one is needed. Its why there are 4 engines in a passenger aircraft and in a bomber plane. The downtime is reduced to 1/4 x 1/4 x 1/4 x 1/4 = 1/256 or 0.4% approx. Though we are considering resiliency-through-redundancy it should be noted that we not need to have 4 redundant systems to get same level of downtime. We can alternatively make our sub-systems extra strong to endure twice as much pressure as considered normal.

The next class of redundancy is the luxury level. Its to have extreme mental peace. Its having 16 redundant primary-level sub-systems when only one is needed. Such a level of redundancy is very, very rarely seen in man-made systems and never seen in natural systems. Having such a system, the downtime is reduced to 1/256 x 1/256 x 1/256 x 1/256 = 1/65536 x 1/65536 or 1 in 16 million. Such a level of redundancy is needed when we are making a starship that has to travel for lets say 30 years before reaching the nearest non-solar star alpha centauri 4.2 light years away.

So, what level of resiliency is recommended for human-made systems? I think we should consider two ways: resiliency through quality and resiliency through redundancy. I have talked enough about resiliency through redundancy and conclusion for that is, either 2 or 4. 2 is enough and another 2 on the secondary-level. I must make my point clearer.

All in all, there are four ways to get resiliency:

(1) Repairability: There should be an abundance of spare parts and repair mechanisms must be automatic and in place. Simply said, the system must be able to make parts needed for repair on the fly. At this level, a supply of twice as many spare parts should be present as currently in active use in system.

(2) Quality: The next level of defense is quality. Only those things should be taken that are of the best quality. In the quality calculations, half of the stuff made is of average quality, quarter is of worse quality and quarter is of best quality in the three categories discussed. The best quality stuff is usually twice as expensive as the average quality stuff and four times as expensive as the worse quality stuff. This can be easily seen in food, best quality stuff is tastier, larger and more colorful. Here the cost is twice than it would be if average quality stuff is taken. Consequently, average life would be double than average. Also power output would be double, its like running a car on petrol than on cng.

(3) Redundancy: Keep 2 sub-systems of every sub-system instead of one.

(4) System: Keeping an entire system redundant. Its important when for example we are going on for war, we must never engage more than one half of our forces in offense because we don't know what level of counter-offense enemy would do. We must keep an extra working space-craft for every space-craft we send in space.

Fall of The Bed Rock

Walls were high but breached,
Came down the enemy and we freezed.

There was scream, blood and arrow,
Path to victory was narrow.

One by one fell the fellows,
Left were no eagle or sparrows.

There was one odd call,
The hero fought but fall.

The sun went away to rise in east,
Giving way to chaos and beasts.

Explanation:

I wrote this small poem to express the fall of the Abbasi Khilafat by mongols and tataris. I was careful in choice of words and in sequence of events. Following are some details.

"The Bed Rock" -> At that time muslim world was divided in many empires, all of them were legally under the caliph of baghdad though he not interfere. Each one of the empires were a rock, that is shelter but there was a rock on which the other rocks rests on. This was the bed rock, the khilafat.

"Walls were high" -> Here I refer to the many empires that the mongols must cross before entering baghdad. The empires were quiet strong, especially the khwarzam.

"We freezed" -> The fall of the empires, especially the khwarzam empire was such an unbelievable shock that effectively muslims of the time were shocked and were able to do little to stop the invasion.

"Eagle" -> The brave ones. The fighters. The soldiers. Those who resisted the invasion. They were few so I used singular term.

"Sparrows" -> The peace lovers. The easy preys. Those who didn't fought. Symbol of women and children. The singers. The musicians. The dancers.

"Odd call" -> Here I refer to the event when the minister (wazir) of the khilafat called soldiers for salaries right in between of the war of baghdad. Even at the last stand, the khilafat had mustered lots of soldiers and had actually strong chance of victory, but alas the mentioned deed of the minister resulted in a huge loss. We lost not just baghdad or iraq or one khilafat but actually the golden time of muslim history, vast knowledge of maths, philosophy, science and arts (mongols drowned books of baghdad in river dajla) and much of bravery.

"The hero" -> The caliph.

"East" -> India. When Abbasi khilafat was falling a new muslim empire was building in India. In centuries to come it become one of the most glorious empires of all the times.

How to read/write or select/insert data/rows to/from datasets/datatables to Excel

using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb; //important

string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\New.xls;Extended Properties=""Excel 8.0;HDR=Yes;ImportedMixedTypes=Text;""";

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = connStr;

OleDbCommand cmd = new OleDbCommand("insert into [Sheet1$] (ID, Name) values ('1', 'abc')", conn);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Make a blank .xls file in c:\, name it New.xls. In its first row, in the first two cells, write "ID" and "Name", this becomes the column names of first and second columns respectively.

Note: You have to use the connection string as it is. Just copy paste and change file path to your excel file. A few things to note:

"Data Source" has space in between. Its not "DataSource".

FilePath has to have double slash. Its "C:\\New.xls", not "C:\xls".

There is a space in "Extended Properties". Its not "ExtendedProperties".

There is a space in "Excel 8.0". Its not "Excel8.0".

Omit "IMEX=1" entirely or you would get "use updateable query error". I don't know the default value of IMEX so omit it altogether.

Use "HDR=Yes" to use your column names. Setting column names in excel file is explained above.

After

Extended Properties=

This solution work for .xls files only. It not work for .xlsx so don't try that.

there are two quotes and at the end there are three quotes (two to close Extended Properties and one to close the entire connection string).

In the query itself, enclose the sheet name in square brackets and use a $ sign after name of the sheet (the dollar sign is inside the square bracket). Use sheet name as you use table name in sql.

All values stored in excel are strings. So I had to put 1 in single quotes even though i meant it to be a numeric value.

Sql Server Optimization

This article answer questions like:

1. Do restart of sql server service result in loss of optimization?

2. Do indexing helps in gaining performance? To what extent?

3. To what extent sql server optimize query when executed multiple times?

I have put 10 million rows in the test table to clearly see any performance gain. Upto 10,000 rows size of a table, the execution time is mostly flat.

PREPARATION

STEP 1: Create a table.

CREATE TABLE [dbo].[Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[NICNo] [varchar](50) NOT NULL,
[Addresss] [varchar](100) NOT NULL,
[Dob] [datetime] NOT NULL,
[Employed] [bit] NULL,
CONSTRAINT [PK_Person_Duplicate] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



STEP 2: Put 10 million non-unique rows in the table.

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
INSERT INTO person(Name, NICNo, [Address] , Dob)
VALUES('Person No. ' + Convert(VARCHAR, @i), Convert(VARCHAR, @i), 'Some address ' + CONVERT(VARCHAR, @i), GETDATE() - 2)

SET @i = @i + 1
END



STEP 3: Make half "Persons" unemployed and other half employed.

UPDATE TABLE Person SET Employed = 0 WHERE ID <= 5000000

UPDATE TABLE Person SET Employed = 1 WHERE ID > 5000000



STEP 4: Write Paging With Sorting Stored Procedure For Person

CREATE Procedure [dbo].[MoreEfficient_Paging_Person]
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS

DECLARE @MaxRows int
SET @MaxRows = @PageSize * @PageNumber

DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM Person)

IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )

IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.NicNo, t.Addresss, t.Dob, t.Employed
FROM (
SELECT Top (@PageSize) ID, Name, NicNo, Addresss, Dob, Employed
FROM (
SELECT TOP (@MaxRows) ID, Name, NicNo, Addresss, Dob, Employed
FROM Person
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END) DESC,
ID DESC
)
AS bar
INNER JOIN Person AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'NicNo' THEN bar.NicNo
WHEN 'Address' THEN bar.Addresss
WHEN 'Dob' THEN bar.Dob
WHEN 'Employed' THEN bar.Employed
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.NicNo, t.Addresss, t.Dob, t.Employed
FROM (
SELECT Top (@PageSize) ID, Name, NicNo, Addresss, Dob, Employed
FROM (
SELECT TOP (@MaxRows) ID, Name, NicNo, Addresss, Dob, Employed
FROM Person
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END),
ID

)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END) DESC,
ID DESC
)
AS bar
INNER JOIN Person AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'NicNo' THEN bar.NicNo
WHEN 'Address' THEN bar.Addresss
WHEN 'Dob' THEN bar.Dob
WHEN 'Employed' THEN bar.Employed
END) DESC,
bar.ID
END




TEST SEQUENCE

Query : EXEC MoreEfficient_Paging_Person 'Employed', 'ASC', 10, 100000
Basic : A table Person with 10 million rows. A column Employed of type bit.
Format : (nth execution : Execution time in seconds).

Test No. 1
Specific : Automatic index PK_Person, clustered.
System : Memory usage 1.87 GB out of 2 GB.

1: 117

Test No. 2
Specific : No index at all, not even PK_Person.
System : Memory usage 1.87 GB out of 2 GB.

1: 131 seconds

Test No. 3
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 11 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 202
2: 157
3: 135
4: 123
5: 115
6: 114
7: 112
8: 110
9: 123
10: 123
11: 123


Test No. 4
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 231
2: 137
3: 118
4: 112
5: 104
6: 112
7: 119
8: 114
9: 110
10: 117


Test No. 5
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 212
2: 134
3: 102
4: 115
5: 119
6: 119
7: 108
8: 116
9: 107
10: 120

Test No. 6
Specific : Person has only one index, PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB.
Setup : I made a new table Person_Duplicate, set its primary key (that automatically set its PK_Person_Duplicate index). Then copy all data from all

columns (except ID column) from Person to Person_Duplicate. Then deleted Person. Then renamed Person_Duplicate to Person.

1: 123
2: 87
3: 84
4: 81
5: 82
6: 103
7: 85
8: 89
9: 82
10: 84


Test No. 7
Specific : Person has two indexex, a clustered index on ID column (identity column) and a non-clustered index on Employed column.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.

1: 131
2: 82
3: 79
4: 80
5: 80
6: 79
7: 81
8: 82
9: 80
10: 81

ANALYSIS

Finding No. 1 : Comparing Test No. 1 with Test No. 2

The difference between the two tests is that first test have primary index, second don't. When the table has default index, searching took 117 seconds, when

it don't, searching took 131 seconds. Its a difference of 11%, not a huge one but may be important. Note that both tests run in identical system conditions,

1.87 GB busy RAM. The sql server service consume more than 1 GB RAM.

Conclusion => Removal of primary index don't affect 1st execution time much (increased 11%) in the first run.

Finding No. 2: Comparing Test No.s 3 to 5

3 4 5

1: 202 231 212
2: 157 137 134
3: 135 118 102
4: 123 112 115
5: 115 104 119
6: 114 112 119
7: 112 119 108
8: 110 114 116
9: 123 110 107
10: 123 117 120
11: 123

The only difference between these three tests is that after ending of each test, the sql server service is restarted.

It can be easily noted that as a test continues, from first run to tenth run, the sql server optimized the query reducing execution time. This somewhat

settled down at the 4rth run, a hyper optimization is done from 5th to 8th run, from 9th run onwards the execution time stabilizes to where it was at the

4rth run. The average of first runs is (202 + 231 + 212 = ) 645, the average of 4rth runs is (123 + 112 + 115 = ) 350, the average of 8th runs is (110 + 114

+ 116) = 340, the average of 10th runs is (123 + 117 + 120 = ) 360.

Conclusion => At 4th run, execution time is halved (reduced 46%).

Conclusion => At 8th run, execution time is hyper optimized to half (reduced 48%)

Conclusion => At 10th run, execution time stabilized to half (reduced 45%).

It can also be noted that once the sql server service is restarted, all the gains in optimization achieved at the end of previous test is lost, the execution

time of first run of all tests is approximately the same, so do every nth run. From one test to another, even though gains in optimization are lost due to

restart of sql server service, still some gains in optimization are lost, so there is a general trend downwards of nth query as test numbers proceed (look

horizontally at chart, there is a slop downwards from left to right).

Conclusion => Restarting sql server service results in lost of all previous optimizations.

Conclusion => If sql server service restart at end of each test, then nth run of each test executes in same time, with a slight downward trend forward.

Comparing these tests with test no. 1 we can find that although the first run in these tests are made when system was at a lower usage level of RAM, still

execution times are considerably high (202, 231, 212) as compare to test no. 1 (117). Since the only difference between the two series of tests (1st series =

test no.1, 2nd series = test no. 3, 4, 5) is absence of any index in the latter series, therefore the higher execution time of latter series must be due to

absence of that index. Taking averages of each series (117,215), the difference is of 47% or almost half. So, the conclusion here is:

Conclusion => Just by putting index on primary key, execution time of first run can be halved.

Finding No. 3: Comparing Test No. 1, 6, 7.

Test No.1 run with PK_Person (primary index) but under heavy system load and with a lots of queries ran before. Test No. 6 and 7 each ran with restart of sql

server service, test no. 6 has only one index PK_Person (similar to test no. 1) and test no. 7 has two indexes. These two indexes are PK_Person and

IDX_Person_Employed. Note that all the queries in this entire article order the page by Employed column. Also note that half rows in Person table have

Employed column set to 0 and other half have it set to 1.

1 6 7

1: 117 123 131
2: 87 82
3: 84 79
4: 81 80
5: 82 80
6: 103 79
7: 85 81
8: 89 82
9: 82 80
10: 84 81

The first run of test 6 takes almost same execution time (a rise of 5%) as first run of test 1. Nothing new there.

Conclusion => Restart of sql server service, don't result in any difference in execution time of first runs.

Comparing averages of 6 and 7, 90 vs 85.5, there is a reduction of 5%, nothing important.

Conclusion => Putting a non-clustered index on a column that can have only two values, and have half rows set to value 1 and rest half rows set to value 2,

and then ordering by this column, there is almost no difference (a reduction of 5%) in execution time between having that non-clustered index and not.