I have been doing some tests and find it very interesting at the results of time it takes to do a query on shared database.
Using Win 7 Pro (64) on both computers
1. I have an application that opened the same database on two computers.
2. Database is on a shared folder on computer 1.
3. I open data builder on Computer 2. Same database as my application opened.
4. All the access is Readonly - Non-Exclusive.
Table in the database has 4897 records that users at times like to open to search records from. Its Parts Inventory Table.
I use the following Select
SELECT PARTNO, DESCRIPT, FACTPARTNO, QTY, PRICE, CATCODE, MAKE, BARCODE, UNIT, VENDOR, QTYOUT, CURRENTPARTS FROM PARTS WHERE CURRENTPARTS = True
I open Data Builder and run this query:
Run 1: 830ms
Run 2: 8.001 Sec
Run 3: 8.043 Sec
Run 4: 7.948 Sec
Run 5: 7.650 Sec
Run 6: 7.980 Sec
Remember this database is opened in my application on two computers and I am accessing it via Databuilder as well. So 3 places its open.
I close the file in DataBuilder and close the data builder as well.
Open Databuilder and run the same query again.
This time the first one is 7.106 secs and the rest close to 8 secs.
I close the file and databuilder again.
Open databuilder and run the Query and I get 889ms the first time and then it drops to around 8 secs.
I tried this several times and get inconsistent results. At times the very first time you run the query its below 1 sec and the rest jump up to 8 secs. That a big jump.
I believe there is a bug in VistaDB. Its possible the difference in time of 1 or 2 seconds that can happen but a whole 7 to 8 seconds.
I have the same complaint from my client who uses 4 computers and reports the same issue.
So that we can better understand the scenario, are the two running copies of the application actively querying the database--and perhaps that same table--or just sitting idle with an open database connection during the testing from Data Builder?
Could you try the test using SharedReadOnly mode in Data Builder? (You should be able to leave the application using NonExclusiveReadOnly for this test.) This mode is blocked in the file handle against overlapping with write-access file handles (ReadWrite connection modes), so the engine knows that no modifications are possible and it does not need to further guard the file with locking.
What you may be seeing with the first-operation-is-fast-then-all-else-are-slow behavior you're describing is an interaction between VistaDB and the network where once there are multiple connections to a database (which means multiple handles opening the same network file) then VistaDB has to start requesting shared file locks as it works which are not particularly fast to create or release over a network connection.
On the second point consider that you don't *have* to make a windows service - start by making a console application or a standard WinForms application that can host a network endpoint to answer your questions. Once you have a console application working, creating a windows service wrapper for it is pretty straightforward. Alternately, you might even tell your client to leave your application running on the computer where the data file lives and have all the others just connect to your application.
We have seriously considered making a windows service version of VistaDB - it's still on our roadmap - but customer feedback has pushed us to work on other areas first which is why it hasn't been completed.
If you'd like to send an email to support describing the language, .NET version, and other aspects of your application we can probably give you some more detailed guidance for how you might make a network service for it along with some options to get it done.
I have been looking at creating a Win service and I am stuck. Never programmed a Win service before. Can I get help on this or a website or a book I should look at.
Sunil, I think that has more to do with the operating system than VistaDB. Windows manages the connections to the files and doesn't always drop them right away. This can slow down connections to any files on a shared folder, not just VistaDB. A possible solution is to create a service that connects to VistaDB, and then connect to the service from your applications. I think you will get much better performance that way.
I hope that helps.