Speed up data retrieval time

Hi Faisal
Can you give me some tips on 'How to speed up data retrieval time if there are 2-3 lakh records in a table?'.

Thanking you,
Prasad

You can retrieve hundreds of thousands of records if you want to but the point is will you be able to show them to the user? I mean what about scrolling the scroll bar forever to see them? don't think this is a good idea.

You may be better off retrieving few records at a time e.g. 10, 20; and showing them to the user and then display a 'previous'/'next' button to show others.

Hi Faisal
Thank you for your reply.
Sorry. I didn't write in detail in my previous email.
What I wanted to ask you is How to retrieve say 50-100 records from a table that contains more than 3 lakhs records so that data retrieval time is less?

Thanks & Regards,
Prasad

I assume that you are asking this question in reference to caching records in memory. Now what you can do is to cache like first 100-200 records which are most often accessed by the user. For example, when you search on Google it displays only 10 records at a time while giving you a paging option to see others. And I bet 80-90% users don't pass 3rd or 4th pages ( 30th - 40th record ). Same applies to your case, not many people are going to be clicking 'next' to view those hundreds of thousands of records.

So cache first 100-200 records in memory for a short period of time and if the user insists on seeing the rest, fetch those records there and then. Shouldn't cost you much.

Hi Faisal
Thank you for reply.
I will ge greatful to you if you could send me answer for one more question.
I am working on a search engine where user enters a keyword to find a list of matching of albums and movies. The table I am using to store albums and movies data contains more than 5 lakhs records in sequential format.
My question is 'Will SELECT ... LIKE %keyword% query take more time to retreive records from albums and movies data table?'. I am using MS SQL Server.

Thanks & Regards,
Prasad

Prasad,

Yes that'd be expensive. Since you are using SQL Server there is another better and more efficient way to do that, use Full-Text search capability of SQL Server.

Full-Text service is installed separately to SQL Server service. You start SQL Server using following command:

net start mssqlserver

And Full-Text service ( provided it is installed, by default it is on SQL Server 2000 ):

net start mssearch

Next you'll have to create catalogs, indexes, then use those indexes to search a string. Your SQL command will then change to SELECT ... WHERE CONTAINS(%keyword%).

Hope you got the idea, now you know what you should be doing.