SQL Server Performance Forum – Threads Archive

First Connect MS Access 2000 – NT Authentifikation

Hallo! I managing a small SQL Server 7.0/MS Access-Application on an Win NT 4.0 Server. About 10 tables, biggest table has ca. 12000 rows. I would like to say: There habe to be no performance problem (it’s really small). But: Wimdows 2000-Client (where I developed the application): fast connection, first connection too.
Windows XP as well.
Wimdows MT 4.0: First view to a table costs 1 minute waiting. I used to identify every user on SQL Server the Windows NT-Authentification. I suppose that there are some bad checks that spend a lot of time. Some suggestions? Thanks spider2618

Ensure there is no issue with the network between client and server.
Check event viewer log for any information or warning about the Hardware and application, SQL error log too. Ensure to schedule dbcc checks and other maintenance tasks on the database in order to get optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hello Satya! No, there is nothing with the network between client and server. I tested: Open Access-Application with shift-key pressed. In the database window i made a double click on any table. Half a minute of waiting and I see the contents. Now I double clicked another table. The content was shown as fast as possible: Just clicked and voila! The connect strings for all tables in the mysysobjects-table of MS Access are: DSN=MyEnterprice_Prod;APP=MicrosoftÂ® Access;WSID=myPC;DATABASE=myDBname;Trusted_Connection=Yes I will say: I do not really understand the WinNT-Authentification-Mode of SQL-Server and/or itÂ´s cooperation with MS Access. Satya, any idea? Thanks
Spider2618

Few tips:
– Restrict the amount of data that you request from the server. Do not ask for more data than you need. Use queries to select only the fields and rows that you need. -Use list and combo boxes wisely. On a form, each list box, combo box, subform, and control containing a total requires a separate query. Against local data, performance may be adequate. Against remote data, however, long delays may occur when you open a form since each query must be sent to the server and a response returned before the form can open. -Use the Find command only on smaller recordsets. The Microsoft Jet database engine optimizes the Find command to work well against local recordsets of almost any size, and against remote recordsets of reasonable size. However, when you are working with large remote recordsets (thousands of records or more), you should instead use a filter or query and be careful to use restrictions that your server can process. -Make sure that queries are sent to the server for processing. The most important factor in query performance against remote data is ensuring that your server executes as much of the query as possible. The Microsoft Jet database engine attempts to send the entire query to your server, but evaluates locally any query clauses and expressions that are not generally supported by servers or by your particular server. -Servers differ in some areas of supported functionality. When you attach a remote table, the Microsoft Jet database engine queries the ODBC driver for its capabilities. If the required functionality is supported by the driver and the server, the Microsoft Jet database engine sends the operation to the server for processing. Windows authentication is a security model to authenticate the application users, and nothing to do with the performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

One suggestion is to use exactly this type of behaviour to your advantage. When opening the Access database, add VBA code to the Form_Open event of your startup form so that a small recordset is fetched from the server, using a DAO.Recordset object. This gives you the first ODBC connection, and the next data will be fetched much quicker. Also not sure if using a DSN can be bad for performance. If you manually link from within Access, you can only go through DSNs, but in VBA you can also set the ODBC connection string for your linked tables (using the DAO.TableDef object) with no DSN at all. Just include the database provider type, server name, network library, database name, login name, Trusted_Connection setting, and you can forget about the DSN. This can also be done from the Form_Open event of your startup form. We store the connection parameters in a local table, and build the connection string from that. An example of what we’re using is – with a SQL login and TCP/IP: ODBC;Server=servername;driver={SQL Server};Database=database_name;Network=DBMSSOCN<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=SQL_password;UID=SQL_login; And with an NT login: ODBC;Server=servername;driver={SQL Server};Database=database_name;Network=DBMSSOCN;Trusted_Connection=Yes<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=;UID=; Regarding performance, using NT logins should not make a big difference to using SQL Server logins.

Thanks adriaan! I used your trusted connection code. YouÂ´re alright. There is no difference in performance. But is easier to distribute the application. spider2618

I correct my posting above: ItÂ´s a Windows 2k server machine with SQL Server 7.0.# spider2618

]]>