Heuristic Optimization of SQL Queries

-
In general, relational algebra expressions and query trees could be equivalent; that is, they may correspond to the same query. But, the performance or cost of query may vary depending on the query technique that we apply. An optimization technique helps reduce the query execution time as well as the cost by re-formatting the query. We applied heuristic optimization in our queries and could reduce the execution time to a greater extent and thus reduced the cost quite a bit.
Figure 1: Initial Query
If we write this query in SQL we get:
Select Name from E, J, P where E.EID=J.EID and PCode=Code and P.Name='GreenLife' and E.BDate>'31-12-1967'
Which is basically the cross product of E, J and P

Outline of a Heuristic Algebraic Optimization Algorithm:

  1. Break up any select operations with conjunctive conditions into a cascade of select operations.

  2. Move each select operation as far down the query tree as is permitted by the attributes involved in the select condition.

  3. Rearrange the leaf nodes of the tree so that the leaf node relations with the most restrictive select operations are executed first in the query tree representation.

  4. Combine a Cartesian product operation with a subsequent select operation in the tree into a join operation.

  5. Break down and move lists of projection attributes down the tree as far as possible by creating new project operations as needed.

  6. Identify sub trees that represent groups of operations that can be executed by a single algorithm.

Searching on Non-Indexed Field


[Before Heuristic Optimization]:


SET STATISTICS IO ON


SET STATISTICS TIME ON


DECLARE @StartTime datetime,@EndTime datetime


SELECT @StartTime=GETDATE()


SELECT tblProvince.Province_Name, tblCities.City_Name, tblDistricts.District_Name, tblCommunities.Community_Name, tblCategories.Category_Name, tblSubCategories.SubCategory_Name, tblProperties.Price, tblProperties.Fireplace, tblProperties.CentralAC, tblProperties.Pool, tblProperties.GreenSpace, tblProperties.WaterFront, tblProperties.BasementDeveloped, tblProperties.StateArea, tblProperties.BedRooms, tblProperties.BathRooms, tblProperties.BusService, tblProperties.Shopping, tblProperties.School, tblProperties.Clinic, tblProperties.Description, tblProperties.RealtorID, tblProperties.Comments


FROM tblProperties,tblProvince,tblCommunities,tblDistricts,tblCities,tblCategories, tblSubCategories where (tblProperties.Community_ID = tblCommunities.ID) and (tblProperties.District_ID = tblDistricts.ID) and (tblProperties.City_ID = tblCities.ID) and (tblProperties.Province_ID = tblCities.Province_ID) and (tblProperties.SubCategory_ID = tblSubCategories.ID) and (tblProperties.Category_ID = tblCategories.ID)


and Province_Name='AB' and City_Name='City1' and District_Name='NW' and Community_Name='Community1' and Category_Name='Category2' and subcategory_name='SubCategory3'


SELECT @EndTime=GETDATE()


SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]


Execution Time: CPU time = 94 ms, Elapsed time = 282 ms.


Searching on Indexed Field [IDs]:


[Before Heuristic Optimization]:


SET STATISTICS IO ON


SET STATISTICS TIME ON


DECLARE @StartTime datetime,@EndTime datetime


SELECT @StartTime=GETDATE()


SELECT tblProvince.Province_Name, tblCities.City_Name, tblDistricts.District_Name, tblCommunities.Community_Name, tblCategories.Category_Name, tblSubCategories.SubCategory_Name, tblProperties.Price, tblProperties.Fireplace, tblProperties.CentralAC, tblProperties.Pool, tblProperties.GreenSpace, tblProperties.WaterFront, tblProperties.BasementDeveloped, tblProperties.StateArea, tblProperties.BedRooms, tblProperties.BathRooms, tblProperties.BusService, tblProperties.Shopping, tblProperties.School, tblProperties.Clinic, tblProperties.Description, tblProperties.RealtorID, tblProperties.Comments


FROM tblProperties,tblProvince,tblCommunities,tblDistricts,tblCities,tblCategories, tblSubCategories where (tblProperties.Community_ID = tblCommunities.ID) and (tblProperties.District_ID = tblDistricts.ID) and (tblProperties.City_ID = tblCities.ID) and (tblProperties.Province_ID = tblCities.Province_ID) and (tblProperties.SubCategory_ID = tblSubCategories.ID) and (tblProperties.Category_ID = tblCategories.ID)


and tblProperties.Province_Id=1 and tblProperties.City_Id=1 and tblProperties.District_Id=1 and tblProperties.Community_Id=1 and tblProperties.Category_Id=1 and tblProperties.subcategory_Id=1


SELECT @EndTime=GETDATE()


SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]


Execution Time: CPU time = 15 ms, Elapsed time = 236 ms.



Searching on Non-Indexed Field


[After Heuristic Optimization]:


SET STATISTICS IO ON


SET STATISTICS TIME ON


DECLARE @StartTime datetime,@EndTime datetime


SELECT @StartTime=GETDATE()


SELECT tblProperties.ID, tblProvince.Province_Name, tblCities.City_Name, tblDistricts.District_Name, tblCommunities.Community_Name, tblCategories.Category_Name, tblSubCategories.SubCategory_Name, tblProperties.Price, tblProperties.Fireplace, tblProperties.CentralAC, tblProperties.Pool, tblProperties.GreenSpace, tblProperties.WaterFront, tblProperties.BasementDeveloped, tblProperties.StateArea, tblProperties.BedRooms, tblProperties.BathRooms, tblProperties.BusService, tblProperties.Shopping, tblProperties.School, tblProperties.Clinic, tblProperties.Description, tblProperties.RealtorID, tblProperties.Comments


FROM (((((tblProperties LEFT JOIN tblProvince ON tblProperties.Province_ID=tblProvince.ID) LEFT JOIN tblCities ON (tblProperties.Province_ID=tblCities.Province_ID) AND (tblProperties.City_ID=tblCities.ID)) LEFT JOIN tblDistricts ON (tblProperties.City_ID=tblDistricts.City_ID) AND (tblProperties.Province_ID=tblDistricts.Province_ID) AND (tblProperties.District_ID=tblDistricts.ID)) LEFT JOIN tblCommunities ON (tblProperties.District_ID=tblCommunities.District_ID) AND (tblProperties.City_ID=tblCommunities.City_ID) AND (tblProperties.Province_ID=tblCommunities.Province_ID) AND (tblProperties.Community_ID=tblCommunities.ID)) LEFT JOIN tblCategories ON (tblProperties.PropertyType_ID=tblCategories.PropertyTypeID) AND (tblProperties.Category_ID=tblCategories.ID)) LEFT JOIN tblSubCategories ON (tblProperties.Category_ID=tblSubCategories.CategoryID) AND (tblProperties.PropertyType_ID=tblSubCategories.PropertyTypeID) AND (tblProperties.SubCategory_ID=tblSubCategories.ID) WHERE Province_Name='AB' and City_Name='City1' and District_Name='NW' and Community_Name='Community1' and Category_Name='Category2' and subcategory_name='SubCategory3'


SELECT @EndTime=GETDATE()


SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]


CPU time = 47 ms, Elapsed time = 209 ms.



Searching on Indexed Field [IDs]:


[After Heuristic Optimization]:



SET STATISTICS IO ON


SET STATISTICS TIME ON


DECLARE @StartTime datetime,@EndTime datetime


SELECT @StartTime=GETDATE()


SELECT tblProperties.ID, tblProvince.Province_Name, tblCities.City_Name, tblDistricts.District_Name, tblCommunities.Community_Name, tblCategories.Category_Name, tblSubCategories.SubCategory_Name, tblProperties.Price, tblProperties.Fireplace, tblProperties.CentralAC, tblProperties.Pool, tblProperties.GreenSpace, tblProperties.WaterFront, tblProperties.BasementDeveloped, tblProperties.StateArea, tblProperties.BedRooms, tblProperties.BathRooms, tblProperties.BusService, tblProperties.Shopping, tblProperties.School, tblProperties.Clinic, tblProperties.Description, tblProperties.RealtorID, tblProperties.Comments


FROM (((((tblProperties LEFT JOIN tblProvince ON tblProperties.Province_ID=tblProvince.ID) LEFT JOIN tblCities ON (tblProperties.Province_ID=tblCities.Province_ID) AND (tblProperties.City_ID=tblCities.ID)) LEFT JOIN tblDistricts ON (tblProperties.City_ID=tblDistricts.City_ID) AND (tblProperties.Province_ID=tblDistricts.Province_ID) AND (tblProperties.District_ID=tblDistricts.ID)) LEFT JOIN tblCommunities ON (tblProperties.District_ID=tblCommunities.District_ID) AND (tblProperties.City_ID=tblCommunities.City_ID) AND (tblProperties.Province_ID=tblCommunities.Province_ID) AND (tblProperties.Community_ID=tblCommunities.ID)) LEFT JOIN tblCategories ON (tblProperties.PropertyType_ID=tblCategories.PropertyTypeID) AND (tblProperties.Category_ID=tblCategories.ID)) LEFT JOIN tblSubCategories ON (tblProperties.Category_ID=tblSubCategories.CategoryID) AND (tblProperties.PropertyType_ID=tblSubCategories.PropertyTypeID) AND (tblProperties.SubCategory_ID=tblSubCategories.ID) WHERE tblProperties.Province_Id=1 and tblProperties.City_Id=1 and tblProperties.District_Id=1 and tblProperties.Community_Id=1 and tblProperties.Category_Id=1 and tblProperties.subcategory_Id=1


SELECT @EndTime=GETDATE()


SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]


CPU time = 15 ms, Elapsed time = 113 ms.



So, we found that the searching technique utilizing Indexed Fields for property searches and after doing the heuristic query optimization takes minimal time [CPU time = 15 ms, Elapsed time = 113 ms.] to produce the query output.


Comments

Post a Comment

Popular posts from this blog

How to Create SSRS Report on SharePoint List

-
Image
There is fairly an easy way to create a SSRS report on SharePoint List. By using an XML data source, we will get access to the SharePoint List. Then, create your own CAML query to manipulate SharePoint List Data. Here is the step by step example of creating SQL Reporting Services report on SharePoint List.   1. Create a Report Server Project in Visual Studio   2. Add a Data Source with following info types:   - Name : Should be the Data Source Name   - Type : XML   - Connection String : Should be the SharePoint Web Service for SharePoint List     [Example: http://myintranet/ts/FazlulsWorld/_vti_bin/lists.asmx ]   - Credentials: Windows Authentication or choose appropriate radio button   3. Add a New Report:   - Select Shared Data Source that you just created   - Paste the CAML Query into the Query String. You may have to find out the GUID for the SharePoint List and View on which you are re...
Read more

Database Testing With DBUnit

-
Image
DBUnit is an Open Source Database Testing Framework which was created by Manuel Laflamme. DBUnit is a Database Unit Testing Framework to Test Database Driven Classes, Procedures or Functions. NUnit, MVC .NET/Visual Studio testing platforms can be used to build a standard unit test suite. Developer should create a dataset, defined within an XML file, and then extends one of a number of parent classes to inherit pre-defined functionality in order to use the framework. An IDbConnection implementation to connect to the database, then DbUnit.NET can be used to test the data access code. Following Operations can be performed using DBUnit: UPDATE TRUNCATE INSERT REFRESH DELETE CLEAN_INSERT DELETE_ALL NONE Other Operations: Composite Operation Transaction Operation Identity Insert Operation DBUnit Support Databases: OracleDatabase MsSQL MySQL IbmDB2 IbmInformix H2 HypersonicSQL PostgreSQL SybaseSQL InterBase OpenBase8 SapDB/MaxDB Derby FrontBase HOW TO WRITE A DBUnit TEST CASE ? To create a ...
Read more

SAP RFC Integration through BizTalk

-
Image
It has been a long while since I published some technical write-ups into my blog. Today, I will try to elaborate on Integration mechanism with SAP though BizTalk middleware. As you are aware, SAP is a self-contained and modular system. So, communicating with SAP is a complex process. You have to have basic understanding of SAP Modules and Data Query/Retrieval process. But, communicating to SAP through RFC function calls has made it even easier. You can communicate with SAP RFCs in three different ways: 1.     Access SAP RFCs using WCF-SAP Adapter and SAP system credentials 2.     Access SAF RFCs through exposed RFCs as ABAP web services 3.     Access SAF RFCs through SAP Process Orchestrator, which is basically the SAP integration middleware Today, I will concentrate on No. 2, which is to communicate with SAP through RFC Web Services via ABAP web services. To do that, you need to expose SAP RFCs as consumable web services. Please follow THIS to expose RFCs as ...
Read more