Pernah membuat website yang ada fitur search-nya? Dengan fitur ini pembaca website akan mengetikkan sembarang keyword yang kemudian harus dicari dalam database website kita. Pencarian model ini biasanya ditujukan terhadap semua resource dalam website tersebut. Jika website kita menggunakan database untuk menyediakan datanya maka pencarian tersebut mesti dilakukan terhadap semua tabel dalam website tersebut. Secara bahasa database, pencarian tersebut akan dilakukan terhadap kolom-kolom table yang searchable, termasuk content.
Query dalam database biasanya akan efektif kalau where-condition-nya dilakukan terhadap kolom-kolom yang di-index. Melakukan query terhadap kolom-kolom yang tidak di-index, apalagi jika kolom tersebut berisi data yang cukup besar tentu saja tidak akan efektif. Nah, bayangkan jika kita harus membuat query terhadap kolom-kolom tersebut dan melakukannya untuk 5 atau lebih tabel. Query-nya akan berjalan lama dan hasilnya belum tentu efektif. Jadi, definitely menggunakan cara ini bukan solusi yang viable untuk keperluan searching.
Solusi yang tepat adalah dengan memanfaatkan fitur Full Text Indexing yang di-bundle bersama dengan SQL Server 2005 dan 2008. Dengan menggunakan fitur ini, kita memang masih melakukan query ke database, namun bedanya query kita tidak secara spesifik menyebutkan untuk melakukan query di kolom ini di mana posisi kata-kata yang dicari ada di tengah-tengah... dan seterusnya. Dengan fitur ini query kita menjadi lebih deklaratif lagi. Kita cukup bilang, lakukan query terhadap kolom a, b, c dimana kolom-kolom tersebut mengandung kata-kata x.
Dan dari sisi performa, cara ini pun jauh lebih efektif. Karena SQL Server secara internally melalui background process (service) melakukan indexing terhadap kolom-kolom yang kita specify dan meletakkan hasil indexing-nya dalam sebuah katalog. Melalui katalog yang telah ter-index inilah query kita akan ditujukan, sehingga performanya bisa jauh lebih baik.
OK, untuk keperluan demo saya mempunyai schema database berikut ini (digambar dengan Enterprise Architect 7.5 complimentary license from Sparx System).
Kita akan focus ke dua tabel yang diberi kotak warna merah dalam gambar di atas, yaitu tabel News dan Page. Struktur detail kedua tabel tersebut tampak seperti dalam gambar berikut ini.
Perhatikan bahwa dalam kedua tabel tersebut terdapat beberapa kolom yang "indexable", yaitu: Title, Content, dan Keywords. Kolom Description bisa di-index tetapi ketiga kolom yang saya sebutkan pertama sudah cukup mewakili content dari tabel-tabel tersebut.
Langkah pertama untuk bisa menggunakan fitur ini adalah meng-enable-kan nya secara eksplisit. By default fitur ini tidak di enable pada sebuah database. Untuk meng-enable-kannya buka SQL Server Management Studio, klik kanan pada database yang hendak di-enable-kan lalu pilih properties, pilih tab Files. Berikan tanda checkmark pada checkbox yang telah disediakan seperti terlihat dalam gambar berikut ini.
Setelah itu ketikkan perintah berikut ini dalam SQL Server Management studio untuk meng-enable indexing pada kolom-kolom yang kita definisikan di atas.
CREATE FULLTEXT CATALOG CompanyProfileCatalog
GO
exec sp_fulltext_database 'enable'
GO
CREATE FULLTEXT INDEX ON [News]
(
[Title], [Content], [Keywords]
)
KEY INDEX PK_News ON CompanyProfileCatalog
WITH CHANGE_TRACKING AUTO
GO
CREATE FULLTEXT INDEX ON [Page]
(
[Title], [Content], [Keywords]
)
KEY INDEX PK_Page ON CompanyProfileCatalog
WITH CHANGE_TRACKING AUTO
GO
OK, dengan script di atas, tabel News dan Page kita sudah siap untuk di-index secara otomatis oleh SQL Server 2005/2008. Nah, sekarang bagaimana cara meng-query hasil indexing tersebut dalam aplikasi kita?
Gampang! Untuk mempersingkat posting berikut ini saya sajikan sebuah service class yang berfungsi melakukan query terhadap kedua tabel ini, dan hasilnya di-encapsulate dalam sebuah IPagedList.
// Copyright (C) 2009 by Agus Suhanto [agus.suhanto@mvps.org]
// For more information please visit http://suhanto.com
//
#region
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.EntityClient;
using System.Data.SqlClient;
using CompanyProfile.Models.Entities;
using CompanyProfile.Models.Helpers;
#endregion
namespace CompanyProfile.Models.Services.Impl
{
public class SqlFullIndexSearchService : ISearchService
{
private IExcerptStrategy excerptStrategy;
public SqlFullIndexSearchService(IExcerptStrategy excerptStrategy)
{
this.excerptStrategy = excerptStrategy;
}
private const string searchCommand = @"
SELECT KEY_TBL.RANK, FT_TBL.Title, FT_TBL.Name, FT_TBL.Content, 'news' AS GroupName
FROM [News] AS FT_TBL
INNER JOIN
FREETEXTTABLE([News], ([Title], [Content]),
@searchTerm) AS KEY_TBL
ON FT_TBL.NewsID = KEY_TBL.[KEY]
WHERE FT_TBL.CultureId = @cultureId
UNION ALL
SELECT KEY_TBL.RANK, FT_TBL.Title, FT_TBL.Name, FT_TBL.Content, FT_GROUP.Name AS GroupName
FROM [Page] AS FT_TBL
INNER JOIN [Group] AS FT_GROUP ON FT_TBL.GroupId = FT_GROUP.GroupId
INNER JOIN
FREETEXTTABLE([Page], ([Title], [Content]),
@searchTerm) AS KEY_TBL
ON FT_TBL.PageId = KEY_TBL.[KEY]
WHERE FT_TBL.CultureId = @cultureId
ORDER BY KEY_TBL.RANK DESC";
#region ISearchService Members
public Setting Setting { get; set; }
public IPagedList<SearchResult> Search(string cultureId, string searchTerm, int pageIndex, int pageSize)
{
IList<SearchResult> results = new List<SearchResult>();
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connection = new SqlConnection(ParseEntityConnectionString(
ConfigurationManager.ConnectionStrings["CompanyEntities"].ConnectionString));
connection.Open();
command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = searchCommand;
command.Parameters.Add(new SqlParameter("@cultureId", cultureId));
command.Parameters.Add(new SqlParameter("@searchTerm", searchTerm));
reader = command.ExecuteReader();
while (reader.Read())
{
var result = new SearchResult
{
Title = reader["Title"].ToString(),
Excerpt = excerptStrategy.GetExcerpt(reader["Content"].ToString()),
Name = reader["Name"].ToString(),
GroupName = reader["GroupName"].ToString()
};
results.Add(result);
}
}
finally
{
if (reader != null) reader.Dispose();
if (command != null) command.Dispose();
if (connection != null) connection.Dispose();
}
return results.ToPagedList(pageIndex, pageSize);
}
#endregion
private static string ParseEntityConnectionString(string connectionString)
{
var builder = new EntityConnectionStringBuilder(connectionString);
return builder.ProviderConnectionString;
}
}
}
OK that's it! Semoga bermanfaat!