Fast display of more the 20.000 lines tables

[donkjam]

I have to do a call center application. the people there have to enter name of cities (in spain more than 20.000) or name of streets. during input a windows is open over the main input window showing the possible choices. the possible choices are depending from the inputted characters (i.e if the person inputted "C" only the cities starting with "C" are showed. when the person input an "A" only the town starting with "CA" are showed and so on). actually this is done with a Delphi two-layer application and it is working almost in milliseconds. We have to replicate that in .NET an three layer application. <BR>what is the best way? ado.NET disconnected recordset? memory database? some more options?? <BR>thanksSome of those answers will depend on other variables. Are you building an asp.net app? A windows forms app? Are the clients on the same network? What is the network like? What server will the app be running on?<BR><BR>I don't know if you can get it down to milliseconds. it depends on how you build your app. You can use disconnected datareader. If your database is MS SQL Server 7+ then you can use a slim trim SQL Datareader. The datareaders are forward-only "firehose" interfaces. If you are trimming it down to the 26 letters of the alphabet then displaying only cities starting with that letter, it's an average of 769 records per letter. you can also page the records (more of a usability thing than anything else). The datagrids and datareaders are, in my opinion, the best way to go.<BR><BR>For even more performance you can cache the results and dispose of the cached results only after a stagnant period. That only works if the datasource is not updated on a constant basis. If the datasource changes then your cached results are old.<BR><BR>You might not ever reach milliseconds, but if you do it right you can come darn close.