i am rakesh giri ,a software developer from india,
i am facing some problem and want help.
i hope you will help me
i have a website named 'bizlis.com'which i have built a website in asp.net 2.0 with sql 2005 database.it is a database search engine which searches buyers and sellers all around the world....
my mssql database consist of 267 tables of 267 countries(table names-dbo.india,dbo.australia etc) which contains suppliers/buyers of that particular country/table.which has fields like address,email,website,buyer or seller(cateory) etc. each country table has all the details of buyer or supplier of that country
when user enters url 'bizlis.com'.my home page opens .loading time of home page is fast.homepage has .. user has to select country from dropdown box,he selects category from dropdown(buyer/seller)he then selects a criteria from dropdown(exact phase search,anyword search or all word search) after that he enter product name in the textbox which he wants to search ...after that searching is done ..the problem is that my searching for the first page takes very long time it takes 8-10 seconds (try searching for steel sellers from india as it has maximu records)..after first page is loaded in 9-10 sec ..then 50 records are shown .it has navigation button on click of next navigation button data comes faster as compared to loading(starting first page-which brings data for the first time ) page.yhis is because cahing is applied...
hence now my biggest problem is that how to make my first datapage fast(starting first page-which brings data for the first time)
some of my friends told me to apply pre caching
i can do it in this way
by loading data into cache by country dropdown (when user selects country before pressing on submit button). in this way the table of the country which is selected by user will get loaded.
but in the case of country dropdown cache,user might have to wait for some time after selecting coutry as data will be loaded and he might not be able to select other dropdown such as (all,any,exact search or enter the product name in the text box)
my friends suggested me :pre-caching on the DB tier by using perhaps using Ajax to submit a query before the user hits the submit button.
my main problem is that to avoid waiting for user when data is being loaded .as i want the cache data to be loaded as fast as as possible ..so that user donot have to wait for clicking on next action as data is being loaded in cache
i think this thing can help me .
hence can you help me by giving sugesstion/codes about what to do and how to avoid user waiting after dropdown selection as caching is done in backend and user does not face any difficulty in acessing the website (does not have to wait while home page is being loaded(when all tables are cached or does not have to wait after country dropdown is selected)