The ADO connection database usually has three methods: System DSN Connection, DSN-Less Connection, and OLE DB Connection, this is very familiar, their usage is as follows: (Note: The difference between the three methods is which keyword - DSN, DRIVER, DATA SOURCE, Provider.uid, PWD is the ODBC tag, User ID, password is OLEDB tag. It is specifically pointed that Data Source represents data sources in the ODBC tag, equivalent to DSN, in the OLEDB tag Server name or database name.)
'System DSN ConnectionSet cnn = Server.CreateObject cnn.Open ( "ADODB.Connection") "DSN = your_dsn; UID = user_name; PWD = password;"' or labeled with OLEDB cnn.Open "Data Source = your_dsn; User ID = User_name; Password = password; "
'DSN-Less Connection' Take SQL Server as an Example SET CNN = Server.createObject ("AdoDb.Connection") cnn.open "Driver = {SQL Server}; server = server_name; uid = user_name; pwd = pwd; database = public "
'OLE DB Connection' to SQL Server Example Set cnn = Server.CreateObject ( "ADODB.Connection") cnn.Open "provider = sqloledb; data source = server_name; initial catalog = pubs; user id = user_name; password = pwd; "
Below, we discuss their respective performance.
Essentially, SYSTEM DSN and DSN-Less Connection are connected through ODBC and databases, and they are not particularly different (this is true). Many people say that DSN-Less Connection is better than System DSN Connection, I am not opposed to this. (Is it some contradiction before and after, just said that they have no difference, now ...) I have tested these two connectors, but failed. Because my test data is not ruled, I can't explain the problem (perhaps the performance of the hypothesis can be compared, but it is dying). So I got a conclusion: there is no conclusion! Later, I saw an article on the Internet SYSTEM DSN or DSN-Less Connection? It was an answer.
Conclusion is (this is original):
These tests showed that DSN-less connection were slightly faster than System DSN Connections.The increase in performance was nothing monumental; the greatest performance boost was mere 13% faster with 64 concurrent requests.For one, two, or four concurrent requests, there was Virtually No Performance Improvement.in Fact, No NOTICEABLE IMPROVEMENT IS Seen In A DSN Until Theree Are 10 or More Concurrent Connections. why? Because System DSN reads the registry when connecting.
Now only Ole DB has not said (typing is really tired). OLE DB is more efficient than ODBC.
Don't test it at all, this conclusion is obvious. If you still have some doubts, it is recommended to see the connection pool to introduce the illustration of MDAC Framework. It can be seen from the figure that the ODBC connection is ADO -> OLE DB -> ODBC Provider -> ODBC -> DRIVER -> Database; OLE DB is ADO -> OLE DB -> DB Provider -> Database. Which one is more direct? Of course it is OLE DB!
The OLE DB connection database is fast than ODBC, and the search data is faster than ODBC. So, I suggest that every person in the Internet: use Ole DB!
Set cnn = Server.CreateObject ( "ADODB.Connection") 'Connection string for SQL Servercnn.Open "Provider = SQLOLEDB; Data Source = srvName; Initial Catalog = DBname; User ID = user_id; Password = yourPassword;"
'for accesscnn.open "provider = microsoft.jet.Oledb.4.0; data source = db_path"
Connecting the database is so easy!
Discussion on Access Database Connection
If you are using an Access database, remember not to use the DSN system connection. The reason is that their speed is very slow because they have to pass the ODBC first, and then the ODBC is turned on through Jet Drivers.
When you use non-DSN (DSN-LESS) Connection ASP, Jet Drivers can be directly accessed, so that the performance of each aspect is greatly improved, especially highlighting, the original version of MDAC, which is very slow on the Access DSN. (Multiplication Mode Transformation) will have a significant performance improvement.
If you are using the newsgroup, you should hear the following advice: "Do not use non-DSN connection". This rule comes from the actual experience of users who have long used DSN. As for specific reasons and explains, just as a story saying ... reasons are too old, so that we can't say it. Ha ha.
For my personal experience, there are often some web applications in my work. At the same time, the database of these sites is used by 20 to 30 users, still running well. The working status of the database depends on the user's access to the database. If this limit is not limited, more users will not use it. If you have enough resources, you can first sql; but if you don't, please try ACCESS. If necessary, you can expand it to a SQL database in the future, just do a little change to your code. Every day I use SQL database and access, so I have the foundation for each evaluation. Fair say that they have the strengths. There is a little story here:
Once, after we upgrade the server to MDAC 2.1, a series of ACCESS-driven sites start an error: "Too Many Client Tasks". When studying this situation, I found an article in Microsoft's site, which said that the DSN system using Access is very "horrible". Unfortunately, I can't find this article now. Otherwise I can provide a link to everyone.
After experimenting, various programs are still inactive, I finally changed some of the largest sites from DSN to non-DSN connections. Between the income, the speed is improved, and the error message is not. Although new MDAC still has a more headache problem, at least the current Access database site has 10 times the previous speed at the top of the same visit. The increase in speeds makes the user more quickly, so that the number of users who simultaneously access will not be too high, which avoids the occurrence of excess user access to Access unable to process. From the total amount of Access database running on the same server, ODBC Access looks like it is increasing than previous loads. I also tried this change on the drive that was not upgraded to MDAC, and the speed was also improved. So from now on, all of my Access designs are non-DSN. Although most of my design does not use Access in work.
Change your DSN to non-DSN. Do this for yourself. If you find that it is not used, it will not harm you anything. The more users visited at the same time, the easier you can detect speed differences.
Here is a normal example of a non-DSN connection, assuming that this method can directly access the Access drive, DataConn.open "DBQ =" & Server.mAppath ("../ Database / Database.mdb") & "; driver = {Microsoft Access driver (* .mdb)}; "
In addition, there is another way to access Jet more directly. I have to say that both methods are much better than DSN performance. But I still discovered a little difference between the two. This method of betterness (just a little bit), use Access97 as an example, directly connected to Jet,
DataConn.open "Data Source =" & Server.mappath ("../_ Database / Database.mdb") & "; provider = microsoft.jet.Oledb.3.51;"
There are also examples of Access 2000:
DataConn.open "Data Source =" & Server.Mappath ("../_ Database / Database.mdb") & "; provider = microsoft.jet.Oledb.4.0;"
