Make indexing work better

[gate7cy]

I have an application in VB 2008 using a database from Access 2007. I have some search functions in the application. Until recently they were working very slowly. I played around with the index of the specific tables and I saw great improvement at the start. As the application is runned the speed of the search drops. Any clues anyone? Thanks for your time

 

[Stonkie]

Any clues anyone?

No.

I'm kidding .

I have never seen performance go down from using indexes for too long! What I would suggest is to profile the application. I have tried to make performance improvements before without profiling the application and most of the time, I would waste countless hours making changes before I hit the bottleneck and made any significant performance improvement.

Compare this to using a profiler and targeting my optimization work on the areas it really mattered : I've saved a lot of time and sweat! Not to forget some optimizations have a negative effect on code clarity and measurement is obviously the key to optimizing effectively.

Now, enough with selling you to profiling! What I used at my previous job was Red Gate ANTS, but it costs a bit. You may prefer an open source alternative, or simply compare Date.Now.Ticks values every here and there in your application. This will give you a starting point as to what exact operations take so much time.

One more thing, could you just be running out of system memory after some time into the application?

 

[cjard]

played around with the index of the specific tables

So, you're not 100% sure what youre doing?

 

[gate7cy]

I created three indexes on the colunms I created my parameter queries. Theseare the columns used for searching my db.
I know about indexing but not a pro. Same for vb

 

[gate7cy]

Stonkie the performance does not drop due to useof indexes. The performance drops for unknown reasons which I am trying to get some answers through this post. The search was relatively slow and when I created some indexes it improves alot. The problem now is that performance drops as the whole application is runned. Any clues?

 

[cjard]

It's hard to comment; we don't know enough about the program you've written or how it uses the db..

 

[gate7cy]

what u need to know? the datagrid is databound. I use parameter queries. I use the queries in the dataset designer to fill, filter data in datagrids. I was told in another forum that I may have memory leaks originating from objects I am using. Them being COM or .Net objects. The person suggested in clearing them will help. I do not use objects intentionaly as I do not know how to. Now if I am using them because VB.net adds them automatically I do not know. How to I locate these objects and how do I remove them from memory at runtime. Thanks for the replies

 

[Stonkie]

Solving the problem
What I meant when I asked if you were running out of memory was pretty much if you either had a memory leak or you simply used more memory then the system has.

The easy way to tell if you're running out of memory is to look in the task manager.

I do not use objects intentionaly as I do not know how to

Believe me, you do! This one made me laugh!

To begin with, every single variable you use in you application refers to either a structure (the smallest building blocks like Integer, Rectangle, etc.) or an object (DataGridView, DataSet, DataTable, Button, etc).

Every one of those contains some information. To store that information, you need to use some system memory. Objects that are no longer referenced will be garbage collected (their memory will be freed). The problem is that sometimes, you will accidentally keep a reference to an object without knowing it! The easiest way to find them is to use a memory profiler.

Could it be that you are using code like this more than once?

gridItems.DataSource = adapterItems.getData()

I have had trouble with setting the DataSource of a DataGridView more than once. I can't remember the specifics, you would really have to use a memory profiler to know more, but DataTable objects tend to be pretty big. Look to make sure there are not too many of those.

Solving the next problem
Now, that was for the problem at hand. However, you definitely need to learn about object oriented programming as VB.NET and any other .NET language use it. So does Java and C++ and scripting languages like PHP, Javascript, Python, Ruby, etc. (most of these are just adding OOP capability over the existing specification)!

I just looked through my programming library (I'm growing a collection!) and I have nothing that covers such basics so I can't suggest anything to you. For the best ROI, look for a book made especially for VB.NET. It should get you started with garbage collection, exception handling, events and delegates, properties, anonymous classes, generics and get you acquainted with Winforms/WebForms and ADO.NET (I tried to give an exhaustive list of subjects to cover to help you choose a book, but I'm sure I missed some...).