Searching - Human Way and Index Way

View: 2145    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: MS Office   Fields: Other

16 point/86 review File has been tested

Download   searching-human-way-and.zip (699.00 B)

You need to Sign In to download the file searching-human-way-and.zip
If you do not have an account then Sign up for free here

Download error   Report copyright violation

It was really an art to optimize the data retrieval from database and data retrieval optimization is part of every developer's life. Indexes are one of the best ways of Optimization that SQL Servers provide. Understanding indexes some time may be tough. Here I will walk thorough how index will work on database engine with real life scenario. I hope this will simplify the understanding of indexes.

Introduction

SQL server Indexes Non-Clustered and Clustered

Real Life Scenario

Consider this real time scenario. One of your friends moved to a new place recently. He invites you for dinner.

Scenario 1: Table Scan

Consider your friend provides you only the area where he resides. Add to that, you are very new to the area. Think about how you will you go to your friends place. Here you need to scan through the full area street by street, lane by lane and think about the time taken for it.

This is something called Table Scan in SQL Server. This is also called heap in SQL Server. You are performing query on table where no index has been defined on the table.

Take the following example of Employee table:

ID EMPNO FName LName Email
1 1122 A B abc
4 4444 B C bcd
3 6666 X Y efg

If you want to get the employee information for empid 4444,then Database Engine needs to check all the rows to get the row. This will become more complex if you have more rows in it.

I have added the Execution plan and time taken to execute query in employee table which has around 160 hundreds of rows.

Time

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 36 ms.

Execution Plan

Scenario 2: Clustered Index

Now consider this, you called up your friend and asked for the address. Now you have the address. Now search scope is reduced, instead of area you started searching for street. This will definitely reduce your time.

This is something called Clustered Index in SQL Server. The index will store the data in the physical table in sorted order.

Now if you execute the query, SQL will look for data in sorted order and will return the expected rows in a much better time.

Take the same Employee table explain above. Now consider that you have created a Clustered index:

ID EMPNO FName LName Email
1 1122 A B abd
3 6666 X Y efg
4 4444 B C bcd

If you want to get the employee information for empid 3333, then the Database Engine will do Index Scan as Rows are in sorted order. Now search will be optimized.

I have added an execution plan and the execution time for the Employee table which has around 160 hundreds of rows after creating index. The same query is being executed in this case too.

Check the execution time between both cases. It will be clear how we have optimized the execution.

Time

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 16 ms.

Execution Plan

Scenario 3: Non-Clustered Index

Now consider this case. While getting the address, you asked him for some landmark. You find that his house is just straight opposite to the post office. Now your search will be easier and you just need to look for the post office and you are done. Think about the time taken for this process.

This is how Non-Clustered index will work in SQL server. When Non Clustered Index is created on table, it will store Non Clustered Column and Clustered index as key (as Landmark).

When you execute the query, it will bookmark and return the result.

Take the same Employee table explained above. Now consider that you have created Clustered index:

ID EMPNO FName LName Email
1 1122 A B abc
3 6666 X Y efg
4 4444 B C bcd

Now consider you create Non-Clustered Index on EmpNo column, the NC index will be stored as

EMPNo Key(ID)
1122 1
6666 3
4444 4

Database Engine will execute KeyLookup and based on Key will do a Non clustered seek on parent table to get the desired rows.

I have added an execution plan and the execution time for the Employee table which has around 160 hundreds of rows after creating NC index. The same query is being executed in this case too.

Check the execution time between both cases. It will be clear how we have optimized the execution.

Execution Time

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Execution Plan

Check the execution time between all 3 cases. It will be clear how we have optimized the execution from 36ms to 1ms.

I think this comparison of time will show you how the query got optimized.

You will be able to create a Non Clustered index on multiple columns. For the above sample, if you can also create an index on EmpNo, Fname columns.

Index table will have records similar to this:

EMPNo Fname Key(ID)
1122 A 1
6666 X 3
4444 B 4

This is called covered Index on Empno and Fname.

The advantage of having covered index is that if you select only first name, then there is no need to refer to the main table(ie) no bookmark is required. Also your filter can be applied on FName too.

This has some limitations like 16 key columns and a maximum index key size of 900 bytes.

Included Index

You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations(mentioned above) In the Included Index, NC Index can be created on one key column and select list can be mentioned in Included Column.

Syntax for creating Included Index is:

CREATE INDEX IndexName " 

ON TableName (List of Column) " 

INCLUDE (List of Columns); 

In the same sample, if I created NC index on EMPno and included the columns FName and LName:

EMPNo

Key(ID)

1122

A

B

 

1

6666

X

Y

 

3

4444

B

C

 

4

Note: I created the Inner Table for logical understanding but in SQL Server it will be stored on leaf nodes.

Make all other columns that cover the query included nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

text, ntext, and image data types are not allowed in the Included Index.

Points of Interest

Bad Indexes

Now consider this while giving the landmark, your friend has provided some wrong information. Say he gave too many landmarks like opposite to Post office, beside the coffee shop, or he gives direction the other way like instead of opposite to post office he says behind the post office. This will definitely confuse and become more complex in the address search.

The same may happen in SQL Server too. If you create Index on column which is not at all used in any filter condition or joins, or create NC index without Clustered Index, it will lead to performance degradation.

You also need to consider the Transaction on tables. Index may end up degrading your transactions.

Searching - Human Way and Index Way

Searching - Human Way and Index Way Posted on 12-05-2014  It was really an art to optimize the data retrieval from database and data retrieval optimization is part of every developer's life. Indexes are one of the best ways of Optimization that SQL Servers provide. Understanding indexes some time may be tough. Here I will walk thorough how index will work on database engine with real life scenario. I hope this will simplify the understanding of indexes. 0.18604651162791/10 2145

Comment:

To comment you must be logged in members.

Files with category

  • Document Library - Enable New Document, Edit Document Features

    View: 2064    Download: 0   Comment: 0   Author: none  

    Document Library - Enable New Document, Edit Document Features

    Category: MS Office
    Fields: Other

    0/5 review
    In this article we can explore the underlying aspects involved in enabling the New Document feature in a Document Library.

  • Generating Excel (XML Spreadsheet) in C#

    View: 2605    Download: 2   Comment: 0   Author: none  

    Generating Excel (XML Spreadsheet) in C#

    Category: MS Office
    Fields: Other

    0.5625/8 review
    Recently, I was working on a web based report in ASP.NET. The report had to be generated in MS Excel format and the data was too much. To give you an idea, I had to generate more than 300 worksheets and the file size was more than 5 MB. I tried the...

  • Searching - Human Way and Index Way

    View: 2145    Download: 0   Comment: 0   Author: none  

    Searching - Human Way and Index Way

    Category: MS Office
    Fields: Other

    0.093023255813953/86 review
    It was really an art to optimize the data retrieval from database and data retrieval optimization is part of every developer's life. Indexes are one of the best ways of Optimization that SQL Servers provide. Understanding indexes some time may be...

  • Creating Pivot Tables and Charts Using ActiveX

    View: 2767    Download: 0   Comment: 0   Author: none  

    Creating Pivot Tables and Charts Using ActiveX

    Category: MS Office
    Fields: none

    1.125/4 review
    Most of us (developers) use the “Generate Excel” feature in our applications. This allows us to generate an Excel copy on any of our reports. Most of us are also familiar with “Pivot Table” and “Pivot Charts”. This feature from MS Excel allows the...

  • Automating MS Excel Documents

    View: 2022    Download: 0   Comment: 0   Author: none  

    Automating MS Excel Documents

    Category: MS Office
    Fields: Other

    0/3 review
    I have been working on automating MS Office applications since some time. I have seen that it is very easy to find information on automation of MS Word but it is difficult to find information on automation of PowerPoint or Excel. So I decided to...

  • Embedding PowerPoint presentation player into a WPF application

    View: 1177    Download: 0   Comment: 0   Author: none  

    Embedding PowerPoint presentation player into a WPF application

    Category: MS Office
    Fields: Other

    0/2 review
    On one occasion I came across an interesting and, I can even say, challenging task of building a customized player of PowerPoint presentations. This task emerged as part of a project which my teammates and I developed at Reliable Systems. Our...

  • Parallel Processing MS Access VBA Application

    View: 1147    Download: 1   Comment: 0   Author: none  

    Parallel Processing MS Access VBA Application

    Category: MS Office
    Fields: Other

    0/1 review
    I got a challenging assignment to provide alarm capability on top of an existing application, written in VBA Access. This required a few issues to be dealt with and resolved. I will, in this article, describe the steps I took to provide parallel...

  • Microsoft Office XML formats, defective by design?

    View: 3781    Download: 0   Comment: 0   Author: none  

    Microsoft Office XML formats, defective by design?

    Category: MS Office
    Fields: Other

    0/549 review
    Microsoft is trying to push new file formats that are using Zip and XML. Are those new file formats any good for Office developers? In other words, should anyone feel safe to make direct access to file parts, and start getting free of running...

 

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd