Implement Search Functionality in C# Windows application

See more:

SQL

C#4.0

entity-framework

Hi,

I wants to implement search feature like Micosoft OneNote Search Functionality.

Our Apllication is an Accounting Application which includes Creating Bill, Credits,PayBills etc,

The Search Box is available in the Home Window.

If the user starts type in the Search Box it should search all the basic transation table and show the result in a tree like structure just similar to the Microsoft OneNote Search Function.

Now I tried in the following way,

In the Editvalue changed property of the Devexpress TextEdit Box, I called a StroedProcedure to search and return the results using Entity Framework.

It works as similar to Microsoft OneNote, but the problem i faced here is to display the results it takes Long time.


The Number of rows returned by the stored procedure may vary based on the entries in the table, In our db currently am getting more than 20,000 rows if i type a single character, when the search term is getting more accurate the response time also improves.

How can I optimize the performance in this scenario?
Please provide me suggestions..


My Stored procedure like as follows..

select 2 as pageNumber,null as Parent,'' as Child, 'BatchBill' as PageName,NEWID() as UniqueId,'' as EntryDate,'' as Code,'' as VendorName,'' as Invoice,'' as Reference,'' as Amount , '' as Memo from tbl1 where column1 like '%searchterm%' or column2 like '%searchterm%'

UNION

select 3 as pageNumber,null as Parent,'' as Child, 'Bill' as PageName,NEWID() as UniqueId,'' as EntryDate,'' as Code,'' as VendorName,'' as Invoice,'' as Reference,'' as Amount , '' as Memo from tbl3 where column1 like '%searchterm%' or column2 like '%searchterm%'

UNION

select 4 as pageNumber,null as Parent,'' as Child, 'Credit' as PageName,NEWID() as UniqueId,'' as EntryDate,'' as Code,'' as VendorName,'' as Invoice,'' as Reference,'' as Amount , '' as Memo from tbl2 where column1 like '%searchterm%' or column2 like '%searchterm%'

UNION

select 5 as pageNumber,null as Parent,'' as Child, 'Deposit' as PageName,NEWID() as UniqueId,'' as EntryDate,'' as Code,'' as VendorName,'' as Invoice,'' as Reference,'' as Amount , '' as Memo from tbl3 where column1 like '%searchterm%' or column2 like '%searchterm%' 

Thanks
Magesh