Posted in General

Sql Server 2005 & DotLucene FullText Performance comparision

I wanted to compare Sql Server and DotLucene FullText, i didn’t find which was faster, so i started writing some code to do check the performances. DotLucene is very much loved by the community. Only difference between Sql Server & DotLucene is that, people think we can index documents using DotLucene but not with SQL Server. Well DotLucene actually needs Text to be passed for indexing, if you are doing that, then why not store trhe same text in SQL Server’s Text field and let SQL Serer do the indexing. Anyway these were my initial performance measures…

10 times each query

LuceneFullText:

"Test" took : 00:00:00.5781250
"Dotlucene" took : 00:00:00.2031250

SqlServerFullText:

"Test" took : 00:00:00.0156250
"Dotlucene" took : 00:00:00.0156250

100 times each query

LuceneFullText:

"Test" took : 00:00:02.5156250
"Dotlucene" took : 00:00:02.1093750

SqlServerFullText:

"Test" took : 00:00:00.1875000
"Dotlucene" took : 00:00:00.1718750

1000 times each query

LuceneFullText:

"Test" took : 00:00:21.3437500
"Dotlucene" took : 00:00:20.6250000

SqlServerFullText:

"Test" took : 00:00:01.5625000
"Dotlucene" took : 00:00:01.5312500

With 12 records this was the performance

with 12 rows

10 times each query

LuceneFullText:

"Test" took : 00:00:00.5937500
"Dotlucene" took : 00:00:00.2187500

SqlServerFullText:

"Test" took : 00:00:00.0468750
"Dotlucene" took : 00:00:00.0312500

100 times each query

LuceneFullText:

"Test" took : 00:00:02.3906250
"Dotlucene" took : 00:00:02.0312500

SqlServerFullText:

"Test" took : 00:00:00.1875000
"Dotlucene" took : 00:00:00.2031250

1000 times each query

LuceneFullText:

"Test" took : 00:00:20.5625000
"Dotlucene" took : 00:00:20.2500000

SqlServerFullText:

"Test" took : 00:00:01.9218750
"Dotlucene" took : 00:00:02.0468750
I will set up a bigger test soon. Even if you think about it, SQL Server must be faster as its a database that uses memory and DotLucene uses file system (slower). I suspeccted that SQL Server might be faster, again my gut feel Oracle must be faster than SQL Server, we will think about thiss later. I have used same machine with DotLucenec 1.9 and SQL Server 2005, with same number of processes running on machine at the time of execution using .NET 2.0 framework. The following queries were used { "Test", "Dotlucene" }. The following was the code that was used in the test...

 The main method... int times = 1000; string[] queries = { "Test", "Dotlucene" }; using (LuceneFullText l = new LuceneFullText(@"C:Documents and SettingsNaliniDesktopLuceneIndexerLuceneIndexerTestIndex", times, queries)) {        l.Start(); }              using (SqlServerFullText s = new SqlServerFullText(@"Data Source=(local);Initial Catalog=Junk;Trusted_Connection=True", times, queries)) {        s.Start(); }   public abstract class FullTextSearch     {         protected string[] _Queries;         protected int _Times;          ///          /// Initializes a new instance of the  class.         ///          /// 
The times.         ///
The queries.         public FullTextSearch(int times, string[] queries)         {             _Times = times;             _Queries = queries;         }          ///          /// Searches the specified q.         ///          ///
The q.         protected abstract void Search(string q);          ///          /// Starts this instance.         ///          public void Start()         {             Console.WriteLine("{0}:n", GetType().Name);             for (int j = 0; j The index folder.         ///
The times.         ///
The queries.         public LuceneFullText(string indexFolder, int times, string[] queries) : base(times, queries)         {             _Searcher = new IndexSearcher(indexFolder);         }          ///          /// Searches the specified q.         ///          ///
The q.         protected override void Search(string q)         {             Query query = QueryParser.Parse(q, "ContentText", new StandardAnalyzer());             Hits hits = _Searcher.Search(query);         }          #region IDisposable Members         ///          /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.         ///          public void Dispose()         {             _Searcher.Close();         }         #endregion     }   public class SqlServerFullText : FullTextSearch, IDisposable     {                  private SqlConnection _Connection;         private SqlCommand _Command;         private string sql = "SELECT * FROM Content WHERE CONTAINS (*, '{0}')";         ///          /// Initializes a new instance of the  class.         ///          ///
The connection string.         ///
The times.         ///
The queries.         public SqlServerFullText(string connectionString, int times, string[] queries) : base(times, queries)         {             _Connection = new SqlConnection(connectionString);             _Command = new SqlCommand(sql, _Connection);             _Command.Parameters.Add("@Query", SqlDbType.VarChar, 100);             _Connection.Open();                      }          ///          /// Searches the specified q.         ///          ///
The q.         protected override void Search(string q)         {             //Query query = QueryParser.Parse(q, "ContentText", new StandardAnalyzer());             //Hits hits = _Searcher.Search(query);             _Command.Parameters[0].Value = q;             _Command.CommandText = string.Format(sql, q);             SqlDataReader dr =_Command.ExecuteReader();             dr.Close();         }          #region IDisposable Members         ///          /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.         ///          void IDisposable.Dispose()         {             _Connection.Close();             _Connection.Dispose();         }         #endregion     }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s