how to retrive data as fast as possible?

Question

0.00/5 (No votes)

See more:

, +

Hello frends,

I am developing a search engine for searching a sentences. I have a single data table which contains over 1 millian records and it has two columns id and sentence.

Now i want to retrive these sentences by filtering the search criteria.

Till now i have develop an appliction by selecting filter result from db and bindingon grid view but it take too many seconds

I wants this process fast. Any idea about this

Thanks in advance

Posted 22-Oct-13 6:35am

ExpertITM

Add a Solution

Comments

Richard C Bishop 22-Oct-13 12:37pm

Can you use a stored procedure to do all the heavy lifting and then just return the dataset you want and bind that to the gridview?

ExpertITM 22-Oct-13 12:40pm

Yes i use sp for select data and bind grid

Wombaticus 22-Oct-13 12:45pm

I'd be tempted to build an index table - parse each sentence in turn and store each word along with its Sentence_ID in this. You'll then be able to retrieve results very quickly, as well as order them by the number of matches etc. Of course you'll need to re-index each time the sentences table is updated.
In fact... if you stored the word's posution in the sentence as well, you could even do away with your sentence table altogether :)

Paulo Augusto Kunzel 22-Oct-13 12:48pm

There are many things to consider, like how many time the data is read, updated, deleted.
Also, is this search going to get information that starts with the same value as the sentence or can the user type "one" ad it will return sentences like "done" and "someone"?
What are you bringing from the database?

ExpertITM 22-Oct-13 12:53pm

The sentences like paragraph, and user enter some sentance if paragraph and get filter of contain sentence.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nhilesh B · Answer 1 · 2013-10-22T06:48:00

Solution 1

Hi,

Using JQuery with AJAX you can load data as required, alternatively you can persist data searched frequently on client side instead of requiring it again.

I am not sure if your UI permits, you can use a very nice data table implementation, found at http://flexigrid.info/[^] my personal favorite.

Others found at http://www.jquery4u.com/plugins/30-amazing-jquery-tables/[^]

Regards,
NBaua

Posted 22-Oct-13 6:48am

Nhilesh B

Comments

ExpertITM 22-Oct-13 12:55pm

Hi,
I try with ajax call for retriving data by paging like when scroll down than it retrive more records but it take more time and i am doing another sql connection.

Nhilesh B 22-Oct-13 13:09pm

Hmmm, In that case have you checked options on indexing your SQL tables and using views for querying.
More help: http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server

Philippe Mori · Answer 2 · 2013-10-22T12:59:00

Solution 2

Searching Text database or Full text search on Google will probably be a good start.

Here is one link:

http://en.wikipedia.org/wiki/Full_text_search[^]

Posted 22-Oct-13 12:59pm

Philippe Mori

Patel Nutan · Answer 3 · 2013-10-23T08:10:00

Solution 3

Hi,

If there is no frequent insert, update & delete operation in your table then you can create non clustered index on sentence field using below syntax. It will give you 100% performance gain.

CREATE NONCLUSTERED INDEX ON [dbo].[TableName] ([FieldName])

Regards.

Posted 23-Oct-13 8:10am

Patel Nutan

S. M. Ahasan Habib · Answer 4 · 2013-10-23T15:07:00

FullText search in SQLServer is the best option for you for text search inside one or more columns.

Just configure your table columns for capable to use full text search feature.

then you write sp and use full text search query inside that stored procedure.

write ajax function from your application, with this ajax function you execute that sp and fetch the value and render it by client side.

In details regarding fulltext search in sqlserver
http://technet.microsoft.com/en-us/library/ms142571.aspx[^]

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/[^]