DataBase Loading Question

Hey guys,

I’m seeing some really bad performance hits when loading some data from a database file and I’m not sure why.

I have a window with a set of about 121 buttons (canvases actually) on it. When this window loads, each button loads it’s Icon and a couple of other text based data columns from a database file based on some settings of some other controls on the window. There’s a total of about 6700 records in the database table.

This is taking a very long time. The image data being loaded is like 12 kB PNGs. Pretty small. The buttons being loaded are a custom canvas subclass and I do call invalidate on each canvas after everything is loaded.

I’ve never tried this with a database table with this many lines before. It seemed fine when I had just a handful of records. Now with so many records, it is taking just forever to load. Is it because I am looping through each button and making a SQLSelect call on each one? Would it be faster to load the entire recordset for all the buttons and then loop through the recordset?

Thanks,

Jon

Single recordset should be faster. You can also try profiling to be sure something unexpected isn’t happening.

Is the time spent in the SQLSelect call or elsewhere? The Profiler can probably help pinpoint it. If it’s the SQL, would an index on the column used for the query help?

OK. Good idea on the profiler. I’ll check that out.

I don’t know. I was thinking about doing the indexing. When you say column used for the query which would that be? I’m selecting multiple columns and also using a combination of two other column values to determine what rows i want to select.

I’ll run a profile.

Yes, it looks like it is taking about 436 milliseconds to do that select function and 436.2 milliseconds to do the whole method were I then process the select…

So repeat this 120 times and guess what it’s a minute of time. So it all is coming clearer.

Going to try to do an index and see what that does…

OK. Wow. Creating an index on each of the columns that I’m using for the select criteria dropped the select time from 436 milliseconds to 33 milliseconds! WOW!

Is there anything special about creating an index or how often should it be created? I have these statements in the constructor of the window in question:

  MSDB.SQLExecute("CREATE INDEX SourceIndex ON RemoteCodes (SourceID)")
  MSDB.SQLExecute("CREATE INDEX ScreenIndex ON RemoteCodes (ScreenID)")

Is that fine or should I create the index whenever I write new values or what?

You create it once when you define you DB schema. The DBMS manages the index and keeps it up-to-date as data is added/removed from the table.

Is there a way to tell if an index exists? I’ve got a situation where the database and tables may already exist for a user but without the index. So then I want to create the index if it is not there.

Database.IndexSchema ought to do that.

Most awesome! Thanks, Paul.

I’ve made a less restrictive Select Statement now and am filling in all my button information from a single select statement. I’ve now taken my Window load time from over a minute down to 117 milliseconds! Ooh-Rah! I’ve got the Select statement that is selecting ALL records for the button on the page taking 32 milliseconds.

So this is most positive indeed!

On a side note, your db may have recommended procedures to periodically run either manually or automatically to optimize your indexes.

Not really since it’s a database file created by me in code. :slight_smile:

OK further stats:

With the indexes in place, I have tested the window load time using the original way of doing a select statement in the open event of each button to load the button’s info and compared that load time to the new way of loading all the button information in one record set:

Selecting for Each Button Individually Resulted in a load time for the window of about 770 milliseconds
Using one select statement for all buttons and loading the buttons from that resulted in a load time of 218 milliseconds!

So I feel like I’ve really accomplished something today. I had code that was taking over 1 minute to load a window. Now the same window is loading in less than a quarter second. COOL!!! :smiley: