Design Help for Fast DB Access

[Rob570]

Have a VB.NET application (inventory system) built by 3rd party that uses MSDE for backend. The program is v-e-r-y s-l-o-w making requests to the database. I have been told that there are too many connections, wrong connection type, etc, etc. No stored procedures have been created. Need help to understand the optimum design (maybe a "how-to" overview) to retrieve data from maybe 3-5 tables, in order to fill a datagrid just to list the contents of the inventory. Also, the form that contains the datagrid also contains several drop-down list that can be used to filter the results. Should I be using stored procedures? What do I do with the data for the drop-down list data -- can they be stored in memory so that I don't need to get the same info every time I make another call to the DB? Really a newbie at this and need a starting point... all feedback is most appreciated. Rob S

[oykica]

Rob570 wrote: I have been told that there are too many connections MSDE has a limit on the number of concurrent batch workloads that can be processed at once. If you have a large number of clients this could be a big part of the performance problem and new code may not make much of a difference at all. Rob570 wrote: Should I be using stored procedures? Yes. Stored procedures are not only more secure but they are also much faster. Rob570 wrote: What do I do with the data for the drop-down list data -- can they be stored in memory It really depends on how often the data changes. If it doesn't change very often or the new changes are not required immediately then storing it in memory may help the performance of your application.