Question

database sql,with vb coding,first time running not fast

Visual Basic 6, SQL database

we have an accounting package written in visual basic 6.0 and sql database,the problem is when we open the database the first time,it takes lot of time,but when we open it after, it will be very fast and remain fast ,even when we quit the application and restart it.

This will be because the first time you connect the database has to be opened and the information that you are looking for loaded from disk into memory. This is called caching the data. SQL caches as much data as possible into it's buffer. The more often the data is needed the longer it will stay in the buffer. Let used data is eventually flushed from the buffer as more recently used data is cached.

Also make sure that your database does not have the auto-close option enabled. With this option enabled when all users leave for the data, SQL will close the file and flush the cache which removes all the cached data from memory writting it back to disk.

You may need to look at your indexs on the tables which you are using, as incorrectly indexed tables can force SQL Server to load more data than is actually needed into memory which then takes longer than expected.