Application Speed Dilemma

[John]

Hi

We have a database app with two front ends to handle different areas of the
application. One front end is in access 97 and the other in vb.net. The
backend is a single access 97 database.

The access side relies on access forms to handle the data while the vb.net
app, using dataset/dataadapter, is written to access a single record at a
time in the vb.net forms. The problem is there is no complain about speed on
access side, but the vb.net side often runs too slow. For example when
moving to a single next record there is a noticeable delay. The other
problem is that the vb.net app speed is not consistent. Sometimes a vb.net
form that normally opens in 5-10 seconds opens in 20-30 seconds roughly.
General record access (next/previous records) is slower too. Sometimes it
helps to restart the server but this is not required for access side.

My questions are; what is vb.net noticeably slower than access? Why is the
app speed not consistent while the number of users is the same? And what
can I do to bring the speed difference down? Thanks.

The PCs are all pretty new 2.4/2.8/3.0 GHz dell machines with 256/512 MB
RAM. The server is a 2.4 GHz machine with 1.5 GB RAM. As the users use the
two front ends side by side they can see the difference and I am having a
difficult time selling .net to them over access.

Thanks

Regards

 

[Cor]

Hi John,

Show the way you did the connection and than of course the standard
question when it is about performance and VB.net, do you have option strict
on in all your programs (classes)

Cor

 

[Val Mazur]

Hi John,

Could be several issues:

1. First of all DAO works faster then ADO in combination with Jet. Which is
because DAO was designed to work specifically with Access, but ADO was
designed to work with any kind OLEDB provider.

2. What is location of your front end written in Access? Is it inside of the
same database? If yes, then it will work faster, because database is local
to the application

3. How do you select this one record? Could you post your code? If you use
SELECT SQL statement with WHERE clause then check if you have indexes built
in a database for the fields involved into this WHERE clause. If you do not
have indexes, then it could be very slow process, since it would require
straight loop through the table to find specific record.

3. Check network. It is possible that network causes performance issues

 

[John]

Hi Cor

I have declared a public connection in one of the modules for all
datadapters (code is given at the end). Not sure about option strict. Under
Project Properties it is off. Would turning it to on here will apply to all
classes?

Thanks

Regards

Module modMain

Public WithEvents dbConContacts As System.Data.OleDb.OleDbConnection

Public dbFile As String



Sub Connect()



dbConContacts = New System.Data.OleDb.OleDbConnection



dbfile = "F:\<db path>\db.mdb"



dbConContacts.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source="
& _

dbFile & ";Mode=Share Deny None;Extended Properties="""";ole db
services=0;Jet OLED" & _

"B:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDBatabase Password="""";J" & _

"et OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet
OLEDB:Global Partia" & _

"l Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New
Database Passwor" & _

"d="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet" & _

" OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repa" & _

"ir=False;Jet OLEDB:SFP=False"



' Testing Connection

dbConContacts.Open()

dbConContacts.Close()

End Sub



End Module

 

[John]

Hi John,


2. What is location of your front end written in Access? Is it inside of the
same database? If yes, then it will work faster, because database is local
to the application

The access front end is outside and separate from access back end.

3. How do you select this one record? Could you post your code? If you use
SELECT SQL statement with WHERE clause then check if you have indexes built
in a database for the fields involved into this WHERE clause. If you do not
have indexes, then it could be very slow process, since it would require
straight loop through the table to find specific record.

An example is here;

selNextCommand = "SELECT TOP 1 Account_Reference_No, Address, Area, Blocked,
Blocked_Date, Blocked_Depart" & _

"ment, Blocked_Reason, Client_Notes, Company,
Company_Registration_No, Country, C" & _

"ounty, Created_By, Date_Created, Date_Modified,
Directions, Elite_Client, EMail," & _

" Event_Notes, Fax, Financial_Comments, ID, Image_List,
Invoice_Address, Invoice_" & _

"Company, Invoice_Country, Invoice_County,
Invoice_Postcode, Map, Modified_By, Pa" & _

"rent, Payment_Terms, People_Client, Postcode, Source,
Source_Date, Special_Requi" & _

"rments, Staff_Grade_1, Staff_Grade_2, Staff_Grade_3,
Staff_Grade_4, Statement_Ad" & _

"dress, Status, Status_Date, Supplier_No, Tel, TempID,
Type, Uniforms_Needed, Venture_Debtor_No, Web " & _

"FROM tblClients WHERE (ID > ?) AND (Status = ?) ORDER BY
ID ASC"

selcomCompaniesNext.Parameters.Add(New
System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.Integer,
0, "ID"))

selcomCompaniesNext.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Status",
System.Data.OleDb.OleDbType.VarWChar, 50, "Status"))

selcomCompaniesNext.Parameters("Status").Value = "Current"

selcomCompaniesNext.CommandText = selNextCommand


The fields in WHERE clause are all indexed. I then simply fill the
dataadapter using the above select statement when the user presses 'Next'
for instance. Similarly for previous record.

3. Check network. It is possible that network causes performance issues

OK.

Thanks

Regards

 

[John]

PS: Sorry, I missed this in last post. I also provide the select with id of
the current record so it can get the top 1 higher than the current id (in
effect the next record by id).

 

[Cor]

Hi John,

Option strict does not allow late binding.
Late binding means that the program has to find the correct objects at
runtime, that cost time and everytime again.

You can compare it this way.
VB6 and C++ had a real speed isue.

VBNet and C# are when option strict is used at compile time have almost
exactly the same performance in runtime. (However a good written VB.net
program will be faster than a bad written C# program and visa versa). But it
needs more code and stricter code.

I did look at your code, as I think that the code it does as it is nothing,
(know that a dataadapter does open automaticly when the connection is not
open, and then when it has opened closes automaticly again). The last is
very usefull when you only do read one table in a time, otherwise it is wise
to open and close. And as told by Angel in this newsgroup it is better for a
connection to dispose it too.

When you would, you can use the code I made beneath from yours. It will be
in your programs connect.open() and connect.close(), it is all typed in
here, because I do not use it in this way, so probably not error free.

\\\\
Public class Connect
Public Shared dbConContacts as New OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;"Data Source="F:\<db
path>\db.mdb")
Public Shared Sub Open()
dbConContacts.Open()
End sub
Public Shared Sub Close
dbConContacts.Close()
dbConContacts.dispose
End Sub
End Class
////

I hope this helps?

Cor

 

[John]

Thanks. I will try that.

Regards

 

[Val Mazur]

Hi,

I do not see anything wrong except you do not need ORDER BY ID ASC clause
since you select one record. I would say it could be some problem with the
network

 

[John]

Hi

I am having problem implementing this. I am assigning dbConContacts as
connection to my data adapters. I am relying on the fill method to close the
connection. Presumably fill not dispose the connection too?! How do I get to
dispose the connection after every data adapter fill?

Thanks

Regards