Search optmization in ADO.NET... (Please answer even if you are not very confident...)

[pra net]

I am building an application in VB.BET and ADO.NET. The application requires searching only. The searches are on wide varity of criteria and huge tables. The output resultsets may also be very big or small depending on the query. The backend being used is Microsoft Access. The search fields are usually indexed. Before switching over to ADO.NET, I was programming in VB6 and used SEEK which proved to be very fast. But I find no similar thing in ADO.NET. So given that No add/update is required, only searching is required, 1. What is the best alternative to SEEK in ADO.NET? 2. Which data mining technique should suit better? - Dataset or datareader. 3. How can I optimize queries on Memo fields? 4. Tips for faster searching and best practices. Thanks a lot in advance for those who reply. Even small hints would be very appreciated. Please do not hesitate to answer even if you are not very confident or you can help me with even one of the questions mentioned above.

[kubben]

Well, I will say I am in the not very confident catagory since it has been a long long time since I have used Access. I do a long of work in SQL with ado.net so it should be pretty similar. I believe in ado.net you will have to use a command object. You pass in a select statement. Since you are doing searching I would always use the datareader, it is faster then a dataset. Hope that helps. Ben

[pra net]

Thanks a lot Ben. So that confirms one of the questions I posted. Anyone, any suggestions about the other? Thanks,

[kubben]

I am not that familar with memo fields in access. So I don't know how to best handle them. As far as best practices. All tables should have primary keys. These primary keys in general should be used to link tables together. After you are pretty much done with the app, it is a good idea to go back over your queries and see if the under performing ones need additional indexs on those tables to speed them up. Sometimes it is hard to tell if there are only a few rows in the tables. You should be the most concerned with the tables that will get larger over time. It is never a bad idea to think through archiving old data. If you have a production system where after three months the data is pretty useless, them move it off to someplace else or flat out delete it. Hope that helps. Ben

[pra net]

My database is already having appropriate indexes and primary keys wherever suitable. The old application was built in VB6. I need to port my VB6 code to VB.NET. My biggest concern still remains. The classic ADO had the SEEK method that could utilize the indexes and get very fast results. I tried various ADO.NET methods, but nothing gets even near to the speed I was getting earlier. My application has bogged down and has raised a big concern. I was wondering if there is any way in ADO.NET to utilize the indexes in database? Please help.

[kubben]

Normally you would do this is the query itself. This is where I am not very certain what you can do in access. In sql you can just write the query. Select column1, column2, etc from table where column1 = 'fred' If column1 is index that query is very fast. I am just not sure if you can do this. If you can it would be with a sqlCommand object. You would set the commandtext to the query and then call the executereader to return the result set. Again my issues is I have never tried to do this with Access. I think it will work since access has a very similar sql engine, but I don't know exactly how. Hope that helps. Ben