How to make Search fast

Question

hey..i got a microsoft access db with a table called items which has more than 100,000 records.So in my application when i search for some item it takes some time to give me the result in the data grid.

Basically what i do is add each item object to arraylist and then return it to print in the datagrid.im new to c# and i wanna knw a bettr method to do my search wher it takes less time ..thanks in advance..

Best regards,
Hussain

Kirtan Patel · Answer

Friend, Create Indexes in Database that will make Search Faster. also Use Common DataSet ..globally instead of Filling Different DataSets or Executing DataReaders on Every Search.. That Will reduce Search time as We Dont Need to Consult Database Every time ..

Hussain Multhazim · Answer

hey thanx a lot..its pretty fast nw :)

Kirtan Patel · Answer

Its Better to Create Index on Smaller Field,. if you are Searching database from Primary Key ( Code ) then Create Index on Code Field . else if you directly passing Crushed Data In Search Query then Create Index on that Field. since its Text Data it will take more memory to create index. Create Index as I told you the process how to create both type of Indexes ..

Hussain Multhazim · Answer

Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} Table - Item ---------------------- Code ItemDetails DateofPurchase PurchaseFrom Cost SellingPrice PriceSold DateOfSale BillNo Remarks Category CrushedData -------------------------- thats my table design.basically whenever i add a record, CrushedData field will contain all values for that particular row in that table.e.g. Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} Item Code Description DateofPurchase PurchaseFrom Cost SellingPrice PriceSold DateOfSale BillNo Remarks Category CrushedData ABE 341 Embroidery Work 14.11.99 Andrew 850 1500 1400 04.01.00 919 Embroidery ABE 341,Embroidery Work,Andrew,, Embroidery,919,04.01.00 So when i search for a record i write a like query for CrushedData field. So if i hav index this field which method is best..single-field index or multiple fild index ?? and hw to do it ???

Kirtan Patel · Answer

Creating a Single-Field Index loadTOCNode(3, 'moreinformation'); To create a single-field index, follow these steps: Start Microsoft Access, and then open the database that you are working with. Open a table in Design view. Click the field that you want to create an index for. Under Field Properties , click the General tab, click in the Indexed property box, click the arrow, and then click Yes (Duplicates OK) or Yes (No Duplicates) . Creating Multiple-Field Index loadTOCNode(3, 'moreinformation'); If you will often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often set criteria for LastName and FirstName fields in the same query, you should create a multiple-field index on both fields. When you sort a table by a multiple-field index, Microsoft Access sorts first by the first field that is defined for the index. If there are records with duplicate values in the first field, Microsoft Access sorts next by the second field defined for the index, and so on. You can include up to 10 fields in a multiple-field index. To create a multiple-field index, follow these steps: Open the table in Design view. On the View menu, click Indexes . In the Indexes dialog box, type a name for the index in the first blank row of the Index Name column. You can name the index after one of the index fields or use another name. Click in the Field Name column, click the arrow, and then select the first field for the index. Click in the next row in the Field Name column, click the arrow, and then select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields that you want to include in this index.

Hussain Multhazim · Answer

thanks for da reply...hw to create indexes..can u giv me an example ??