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.
14 September, 2010
04 September, 2010
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.
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.
03 September, 2010
Improvements In Last Post
Create Procedure [dbo].[MoreEfficient_Paging_Student]
@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 student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Improvements:
(1) The last page in previous post was showing as much records as the page size. For example if there are 10 rows in table and page size is 3 and we are requesting 4rth page (pages in all these three posts are 1-based indexed), then its returning 8th, 9th and 10th row. It should be returning only the 10th row. This is corrected in this post by putting following lines:
DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
In scenario above, @Count is 10, @MaxRows is 12, @PageSize is 3, @PageNumber is 4. The above lines do this:
@PageSize = 10 - ( (4-1) * 3)
@PageSize = 10 - (3 * 3)
@PageSize = 10 - 9
@PageSize = 1
Therefore only one row is shown in the 4rth page.
(2) Another problem in previous post was that if we input a page number greater than the maximum number of pages in the table, it still return the last few rows (as much rows as the page size). This get corrected automatically in above lines:
For page 5, (row numbers 13th, 14th, 15th), no rows exist (since table has 10 rows), so there must be an error. Using above code:
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF( (15 - 10) > 0) --condition is true
@PageSize = 10 - ( (5 - 1) * 3)
@PageSize = 10 - (4 * 3)
@PageSize = -12
That is, a negative number. When this is used in "top" statement in sql, it result in error because a "top" variable can't be negative.
(3) Efficiency (both in terms of memory and speed). The previous post was using two temporary tables, first of them might be as large as the entire table if page number is the last page number, second table was a only as large as the page size so no issue there. This post don't use any temporary table, instead the entire query run as a whole (though its quiet complex and long).
@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 student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Improvements:
(1) The last page in previous post was showing as much records as the page size. For example if there are 10 rows in table and page size is 3 and we are requesting 4rth page (pages in all these three posts are 1-based indexed), then its returning 8th, 9th and 10th row. It should be returning only the 10th row. This is corrected in this post by putting following lines:
DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
In scenario above, @Count is 10, @MaxRows is 12, @PageSize is 3, @PageNumber is 4. The above lines do this:
@PageSize = 10 - ( (4-1) * 3)
@PageSize = 10 - (3 * 3)
@PageSize = 10 - 9
@PageSize = 1
Therefore only one row is shown in the 4rth page.
(2) Another problem in previous post was that if we input a page number greater than the maximum number of pages in the table, it still return the last few rows (as much rows as the page size). This get corrected automatically in above lines:
For page 5, (row numbers 13th, 14th, 15th), no rows exist (since table has 10 rows), so there must be an error. Using above code:
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF( (15 - 10) > 0) --condition is true
@PageSize = 10 - ( (5 - 1) * 3)
@PageSize = 10 - (4 * 3)
@PageSize = -12
That is, a negative number. When this is used in "top" statement in sql, it result in error because a "top" variable can't be negative.
(3) Efficiency (both in terms of memory and speed). The previous post was using two temporary tables, first of them might be as large as the entire table if page number is the last page number, second table was a only as large as the page size so no issue there. This post don't use any temporary table, instead the entire query run as a whole (though its quiet complex and long).
Efficient Method of Getting Middle Rows From Sql Server 2008, 2005 OR Efficient Method of Paging With Sort In Sql Server 2008, 2005
Step 1: Create a table with 3 fields: ID int, Name varchar(50), Marks float.
Step 2: Put some rows in this table.
Step 3:
Alter Procedure Efficient_Paging_Student
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
DECLARE @MaxRows INT
SET @MaxRows = @PageSize * @PageNumber
SET ROWCOUNT @MaxRows
SELECT ID, Name, Marks
INTO #Temp
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
SET ROWCOUNT @PageSize
SELECT ID, Name, Marks
INTO #Foo
FROM #Temp
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
SET ROWCOUNT 0
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Step 4: Test
Efficient_Paging_Student 'ID', 'DESC', 4, 2
Efficient_Paging_Student 'Name', 'ASC', 4, 2
Explanation:
Using this example we can get any page (set of rows) having any number and any page size. For example if page number is 2 and page size is 4 then we get row numbers 5, 6, 7, 8. Note that row number here do not means id. Its not a fixed field. Its row number 5th, 6th, 7th and 8th AFTER sorting is done according to desired column name.
To make it efficient, we have to limit the rows returned in each query. Using "top" keyword don't work here because top keyword don't take a variable. However, ROWNUMBER can be set to any variable:
SET ROWNUMBER @VAR
This works.
The whole procedure is divided into 3 select statements, first of them returned a different number of rows than the last two. We can't combine the last two because of this error:
Msg 1033, Level 15, State 1, Procedure Efficient_Paging_Student, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
It means that we can't apply "order by" clause to derived tables. Therefore we have to store that table value (coming from the second select statement) in a separate hash table called #foo. Its not an issue because the size of this table is same as size of page at front end (web page, windows form etc) which is usually a very small size (5 to 100 rows).
The actual load of this method is in the first select statement, its because here the number of rows selected is equal to the product of page size and page number. It means that if number of rows can be evenly divided by page size (for example 100 rows and 5 rows each table) then for the last page the query select the entire table. I don't know anyway to avoid this because the sorting requires us to go to that level, that is each row have to be accessed once no matter what (unless we are using views but thats a different story).
Once the first select statement is executed, the rest of the procedure runs in flash because the second and third select statements deal with only as much rows as the size of page. Since size of page is always very little (discussed above) therefore its not an issue at all.
At last we have to do the final sorting. Remember that "order by" clause sort by default in ascending order so I didn't wrote "asc" keyword anywhere in any query. To find out whether to use ascending order or descending order I had to use an if statement.
An important part here is selecting of column name. See:
(SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END)
Look that each case statement returns a column name but not in quotes !!!. It is 100% valid sql code for sql 2005 and above. Whatever column name is returns is fit in above query. Following two statements parse to same query if @SortColumn is equal to 'Name':
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY Name DESC,
bar.ID
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
Its a very important thing to remember and greatly simplify the code. For prior versions of sql server, one have to use separate if/else or case statements to get this behavior, would be very messy. Note that the select case statement above have to be written in the stored procedure, there is no way to make a separate function for that. A function cannot return name of column.
Note that RowCount is specific to current thread. Value of RowCount set to some value in one thread don't affect its value in another thread. This can be tested by opening to "query windows" in management studio:
1st window:
type and execute: SET ROWCOUNT 1
keep this window open
2nd window:
type and execute: select * from student
RESULT: All rows of student table are returned. If there are 10 rows then all 10 rows are returned.
Go back to the first window (it should be open already). Type and execute "select * from student". Only 1 row should be returned. Its because in this thread (a query window is thread, so do a procedure, a function etc) the rowcount is set to 1. Now set rowcount to 5 and execute "select * from student". Now 5 rows should be returned.
Note that a thread is specific to a user as well as a function. It means two users simultaneously executing a stored procedure are still in two different threads.
Step 2: Put some rows in this table.
Step 3:
Alter Procedure Efficient_Paging_Student
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
DECLARE @MaxRows INT
SET @MaxRows = @PageSize * @PageNumber
SET ROWCOUNT @MaxRows
SELECT ID, Name, Marks
INTO #Temp
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
SET ROWCOUNT @PageSize
SELECT ID, Name, Marks
INTO #Foo
FROM #Temp
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
SET ROWCOUNT 0
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Step 4: Test
Efficient_Paging_Student 'ID', 'DESC', 4, 2
Efficient_Paging_Student 'Name', 'ASC', 4, 2
Explanation:
Using this example we can get any page (set of rows) having any number and any page size. For example if page number is 2 and page size is 4 then we get row numbers 5, 6, 7, 8. Note that row number here do not means id. Its not a fixed field. Its row number 5th, 6th, 7th and 8th AFTER sorting is done according to desired column name.
To make it efficient, we have to limit the rows returned in each query. Using "top" keyword don't work here because top keyword don't take a variable. However, ROWNUMBER can be set to any variable:
SET ROWNUMBER @VAR
This works.
The whole procedure is divided into 3 select statements, first of them returned a different number of rows than the last two. We can't combine the last two because of this error:
Msg 1033, Level 15, State 1, Procedure Efficient_Paging_Student, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
It means that we can't apply "order by" clause to derived tables. Therefore we have to store that table value (coming from the second select statement) in a separate hash table called #foo. Its not an issue because the size of this table is same as size of page at front end (web page, windows form etc) which is usually a very small size (5 to 100 rows).
The actual load of this method is in the first select statement, its because here the number of rows selected is equal to the product of page size and page number. It means that if number of rows can be evenly divided by page size (for example 100 rows and 5 rows each table) then for the last page the query select the entire table. I don't know anyway to avoid this because the sorting requires us to go to that level, that is each row have to be accessed once no matter what (unless we are using views but thats a different story).
Once the first select statement is executed, the rest of the procedure runs in flash because the second and third select statements deal with only as much rows as the size of page. Since size of page is always very little (discussed above) therefore its not an issue at all.
At last we have to do the final sorting. Remember that "order by" clause sort by default in ascending order so I didn't wrote "asc" keyword anywhere in any query. To find out whether to use ascending order or descending order I had to use an if statement.
An important part here is selecting of column name. See:
(SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END)
Look that each case statement returns a column name but not in quotes !!!. It is 100% valid sql code for sql 2005 and above. Whatever column name is returns is fit in above query. Following two statements parse to same query if @SortColumn is equal to 'Name':
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY Name DESC,
bar.ID
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student 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 'Marks' THEN bar.Marks
END) DESC,
bar.ID
Its a very important thing to remember and greatly simplify the code. For prior versions of sql server, one have to use separate if/else or case statements to get this behavior, would be very messy. Note that the select case statement above have to be written in the stored procedure, there is no way to make a separate function for that. A function cannot return name of column.
Note that RowCount is specific to current thread. Value of RowCount set to some value in one thread don't affect its value in another thread. This can be tested by opening to "query windows" in management studio:
1st window:
type and execute: SET ROWCOUNT 1
keep this window open
2nd window:
type and execute: select * from student
RESULT: All rows of student table are returned. If there are 10 rows then all 10 rows are returned.
Go back to the first window (it should be open already). Type and execute "select * from student". Only 1 row should be returned. Its because in this thread (a query window is thread, so do a procedure, a function etc) the rowcount is set to 1. Now set rowcount to 5 and execute "select * from student". Now 5 rows should be returned.
Note that a thread is specific to a user as well as a function. It means two users simultaneously executing a stored procedure are still in two different threads.
Paging And Sorting In Sql Server OR Getting Middle Rows In Sql Server
CREATE PROCEDURE Student_Paging
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
BEGIN
SELECT
ROW_NUMBER() OVER (ORDER BY (
SELECT CASE @SortColumn
WHEN 'Id' THEN Id
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END
)
) AS ROWNUMBER,
*
INTO
#Student_Page
FROM
student
IF(@SortDirection = 'ASC')
BEGIN
SELECT *
FROM #Student_Page
WHERE ROWNUMBER BETWEEN ((@PageNumber-1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY ROWNUMBER
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT *
FROM #Student_Page
WHERE ROWNUMBER BETWEEN ((@PageNumber-1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY ROWNUMBER DESC
END
END
GO
Subscribe to:
Posts (Atom)