I work with VS.NET 2005 (c#), still use Vanatec.Open.Access version 4.3.21.866 and connect with SQLServer2000/2005 instances.
When I start my program from a deployment machine, the first connection when the first ObjectScope is created takes between 10 and 30 seconds whereas on my developermachine this is only an issue of milliseconds. Here is the beginning of the trace-files of both machines.
Developer machine:
--------------------------
14:15:54.068 pmf.start Log Level: all
14:15:54.271 driver.con.connect SSL=off;DOMAIN=;TDS=8.0;PACKETSIZE=0;NAMEDPIPE=false;SERVERTYPE=1;INSTANCE=;LOGFILE=;LANGUAGE=;MAXSTATEMENTS=500;SENDSTRINGPARAMETERSASUNICODE=true;APPNAME=jTDS;USER=sa;PREPARESQL=1;LOBBUFFER=32768;CHARSET=;TCPNODELAY=true;PROGNAME=jTDS;DATABASENAME=GreyhoundsDevCM;MACADDRESS=000000000000;PORTNUMBER=1433;XAEMULATION=true;LOGINTIMEOUT=0;SERVERNAME=192.168.10.53;LASTUPDATECOUNT=true;LOCK_TIMEOUT=5000;
14:15:54.271 driver.con.connect SSL=off;DOMAIN=;TDS=8.0;PACKETSIZE=0;NAMEDPIPE=false;SERVERTYPE=1;INSTANCE=;LOGFILE=;LANGUAGE=;MAXSTATEMENTS=500;SENDSTRINGPARAMETERSASUNICODE=true;APPNAME=jTDS;USER=sa;PREPARESQL=1;LOBBUFFER=32768;CHARSET=;TCPNODELAY=true;PROGNAME=jTDS;DATABASENAME=GreyhoundsDevCM;MACADDRESS=000000000000;PORTNUMBER=1433;XAEMULATION=true;LOGINTIMEOUT=0;SERVERNAME=192.168.10.53;LASTUPDATECOUNT=true;LOCK_TIMEOUT=5000;
14:15:54.334 driver.con.isolation READ_UNCOMMITTED(1)
14:15:54.334 driver.con.autoCommit false
...
Deployment machine:
----------------------------
14:23:44.720 pmf.start Log Level: all
14:23:44.798 driver.con.connect SSL=off;DOMAIN=;TDS=8.0;PACKETSIZE=0;NAMEDPIPE=false;SERVERTYPE=1;INSTANCE=;LOGFILE=;LANGUAGE=;MAXSTATEMENTS=500;SENDSTRINGPARAMETERSASUNICODE=true;APPNAME=jTDS;USER=sa;PREPARESQL=1;LOBBUFFER=32768;CHARSET=;TCPNODELAY=true;PROGNAME=jTDS;DATABASENAME=GreyhoundsDevCM;MACADDRESS=000000000000;PORTNUMBER=1433;XAEMULATION=true;LOGINTIMEOUT=0;SERVERNAME=192.168.10.53;LASTUPDATECOUNT=true;LOCK_TIMEOUT=5000;
14:23:49.298 driver.con.connect SSL=off;DOMAIN=;TDS=8.0;PACKETSIZE=0;NAMEDPIPE=false;SERVERTYPE=1;INSTANCE=;LOGFILE=;LANGUAGE=;MAXSTATEMENTS=500;SENDSTRINGPARAMETERSASUNICODE=true;APPNAME=jTDS;USER=sa;PREPARESQL=1;LOBBUFFER=32768;CHARSET=;TCPNODELAY=true;PROGNAME=jTDS;DATABASENAME=GreyhoundsDevCM;MACADDRESS=000000000000;PORTNUMBER=1433;XAEMULATION=true;LOGINTIMEOUT=0;SERVERNAME=192.168.10.53;LASTUPDATECOUNT=true;LOCK_TIMEOUT=5000;
14:23:53.798 driver.con.isolation READ_UNCOMMITTED(1)
14:23:53.798 driver.con.autoCommit false
....
---> so the 2 "driver.con.connect"-lines took close to 5seconds(!) here, but there there is no exception or something like that and the program works fine afterwards. ???
The configuration file is loaded dynamically:
-------------------------------------------------------
public
static IObjectScope GetNewObjectScope(string alternateUsername, string alternatePassword) {
string myconfig =
"<openaccess>" +
"<references>" +
// refer the DAL assembly with app.config with backendconfig section
"<reference assemblyname='Greyhounds_BusinessObjects' configrequired='TRUE'/>" +
"</references>" +
"<connections>" +
"<connection id=\"" + _DB_Servername + alternateUsername + "Con\">" +
"<databasename>" + _DB_Databasename + "</databasename>" +
"<servername>" + _DB_Servername + "</servername>" +
"<integratedSecurity>false</integratedSecurity>" +
"<backendconfigurationname>sqlConfiguration</backendconfigurationname>" +
"</connection>" +
"</connections>" +
"<backendconfigurations>" +
"<backendconfiguration id=\"sqlConfiguration\" backend=\"mssql\" driver=\"" + (GlobalData.SqlServer == SqlServerVersion.SqlServer2005 ? /*"genericADO2"*/"ntds" : "ntds") + "\">" +
"<mappingname>mssqlMapping</mappingname>" +
"<isolationLevel>READ_UNCOMMITTED</isolationLevel>" +
"<logging.logEvents>all</logging.logEvents>" +
"<logging.logEventsToTrace>True</logging.logEventsToTrace>" +
"<logDownloader.eventText>True</logDownloader.eventText>" +
"<option.Multithreaded>True</option.Multithreaded>" +
"<conTimeout>1200</conTimeout>" +
"<retryCount>3</retryCount>" +
"<retryIntervalMs>500</retryIntervalMs>" +
"<waitForConOnStartup>False</waitForConOnStartup>" +
"</backendconfiguration>" +
"</backendconfigurations>" +
"</openaccess>";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(myconfig);
return OpenAccess.Database.Get(_DB_Servername + alternateUsername + "Con", xmlDoc.DocumentElement).GetObjectScope(alternateUsername, alternatePassword);
}
Hope you can help me to get rid of that serious start-up-problem!
6 Answers, 1 is accepted
that looks strange. The difference between the connection from different machines should not be noticeable in such a way. We would like to find out why this happens :
(a) Is your development machine also the database server?
(b) Is that only upon the first request to the database or does it also happen when the application is started a second, third, ... time after the slow first time?
(c) Are you starting the application in the deployment machine in a debugger? Did you specify the symbol path in the same way as on your development machine? I have seen great differences in speed depending on found symbols and the amount of symbol information loaded.
(d) Are there more exceptions when running on the deployment machine?
(e) Are the machines identical (CPU speed, RAM, network connectivity, patches)?
As the time is spent in the connect operation for the Microsoft driver, I would guess the problem is in the network access to the server.
All the best,
Thomas
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
one more thing: please also check the DNS configuration of the deployment machine! Maybe the hostname resolution takes unexpectedly long.
All the best,
Thomas
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Rank 1
You were absolutely right with your DNS-assumption. The firewall of my development machine was configured in a way that the name-resolution did not work. I changed that and the app-start-up with my development machine as db-server was fast from the beginning.
The problem is now that our "real" db-server is in a different subnet than our client-machines. I talked with our sys-admin today and he told my that the only way to get a connection to the db-server is via IP. We also do not have a DNS-server in our network what might be a solution for the problem, although he does not want to set up one.
What I do not understand now is why the name-resolution must work for the first open-access connection? For an sql-connection or oledb-connection you can always use the IP and that works fine. What is happening inside the ObjectScope that the name-resolution has to work?
For completness:
a) With my development-machine as db-server the app.-start-up was slow because my firewall blocked the name-resolution (Remotedesktop was not in the list of exceptions) and the subnet of our "real" db-server is totally blocked by a cisco pix firewall and a connection is only possible via IP (no DNS-server in our network).
b) Even when I start the application a second or third time, the first access to the db is always slow.
c) I did not run the program in a debugger on the deployment machine. I wrote a trace-listener which logs all events to a textfile.
d) There are no more exceptions when the program is running. New connections are fast from the beginning.
e) Client machines are not at all identicall. Different CPUs, RAM, operating systems XP/Vista/Windows server 2003....
greets,
Bernhard
there is nothing in the code intentionally preventing you from using IP addresses directly. It just happens that we pass the server name string to the ADO driver.
Kind regards,
Thomas
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Rank 1
Hi Thomas!
Well, then I do not understand why an ADO.NET based application I wrote 3 years ago has no slowdown when it starts up. There I also pass the IP-address for my sql-connection string?
But there is a workaround for that issue: I edited the hosts-file on all the clients where my application has to run and made an entry for our real db-server there. I still have to use the IP for the Open Access <servername> - property but the program start-up is fast now.
(C:\WINDOWS\system32\drivers\etc\hosts)
INFO: Turn off DNS client service for Windows Vista when using the hosts file - tremendous slowdowns are possible otherwise.
greets
Berni
I'm glad we figured this out but I really doubt that there is anything different in ADO and OpenAccess when it comes to DNS name resolution. Both can be used with IP addresses directly. And putting the server name into various hosts files should be unnecessary when using an IP address and should also not be done; after all, what is DNS there for?
All the best,
Thomas
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.