performance - MS Access 2003 + linked tables to SQL Server 2005 + Windows Authentication = slow -

-

Our MS Access application with linked tables in SQL Server 2005 is slow when Windows authentication is used from Windows API client .

We are successfully using SQL Server authentication, but now we want to go into Windows authentication for better security control.

Setup:

  • Database Server: Windows 2003 Server, SQL Server 2005 SP2
  • Client: Windows XP SP3, SQL Server ODBC Driver v2000.85.1132 .00
  • MS Access Application: MS Access 2003
  • Connection String:
    Driver = SQL Server; Server = [server name]; Timeout = 300 connect; Trusted connection = true; APP = Microsoft Office 2003; WSID = [server name]; Database = [DB name]
  • Only the TCP / IP network protocol is enabled on the server.

It is such that these conditions:

  • Application on DB server, SQL Server authentication
  • App on DB server, Windows authentication
  • Windows XP client, app on SQL Server authentication
  • SQL Server Management Studio on client, Windows certification - I have 15 questions in SQL MS There was a small test with running. It went fast and did not cause any logon / logoff event in the security event log on the server.

    • I have analyzed the slowerness by using SQL Server Profiler and Event Log on the server < / Li> Li> User's identity is verified (security on the server) Visible in the event log, there is a logon / logoff event). It takes hundreds of milliseconds.

  • The query runs on SQL Server
  • Results are returned to access

    • This is for every query something happens Form + 10 questions while updating new records (updating sub-forms, loading values ​​for combo etc.) This is performed very slowly.

    Of course, for every query it is not necessary to establish a new connection on the SQL Server, and the connection that re-use can solve the problem. I am looking for information on ensuring that Access / ODBC does proper connection pooling. I found these MS KB articles:


    I have tried to call the SQLSetEnvAttr function with the main form of the Access application, but this has not improved the results.

    Any help is greatly appreciated.

I have the first question: are you running a domain controller? It may sound like a crazy question, but I just want to make sure. Although less and less common, I have seen organizations run Windows Network with workgroups and "pass-through" authentication. The features you describe are similar to those seen on the network in this fashion. You can.

Assume that you have the proper domain set up, you should have a problem in the named pipes network. Named Pipes is the default protocol if you are using Windows authentication. If you have the time, but if you just want to fix your performance problem, then I do not have a bad idea to go to the bottom of this, so I will force the TCP / IP protocol in your connection string: < / P> 

 APP = Microsoft Office 2003; WSID = [server name]; Database = [Name of DB]   Driver = SQL Server; Server = TCP: [server name]; Timeout = 300 connect;

In addition to TCP NOTE: The prefix I got from this syntax TCP / IP is the default protocol for SQL Server authentication. You can also switch protocols by deactivating named Pipes support on the server, but this is a Trouble is more and other unexpected problems can be caused.


Comments

Post a Comment

Popular posts from this blog

c# - ListView onScroll event -

-
I am programming a simple C # application, and I need a Scallow event on the list view. So I inherited the original ListView class created in ListviewEx witch. I found out how to locate the scroll message from WinAPI and I modified the WndProc method. Now I have this WndProc: protected override zero WndProc (Ref message message) {base.WndProc (Ref I); If (m.msg == WM_VSCROLL) {onScroll (This, New EventArgs ()); }} But the problem is, I do not know how to know about scrolling information. This data should be in wParam, but there is a LOWORD macro ++ like in C in C # and I switch to find the criteria like SB_ below, SB_ENDSCROLL, SB_PAGEUP etc. there any way Need to change the LOWORD macro from C #? Or any other way to find the necessary parameters about scrolling? You can define the WParam constants as the following: Private const int WM_HSCROLL = 0x114; Private Constant WM_VSCROLL = 0x115; Private contact int SBHOZZ = 0; Private Consultant SB_VERT = 1; Private Con...
Read more

PHP - get image from byte array -

-
I am trying to create a back-end for my mobile application. I am sending the content of an image in the body of the HTTP request (output stream) as a byte array, I want to read this stream of bytes in PHP scripts and take back an image. Can anyone tell me how can I do this? Thank you. You can obtain the request body $ body = Reading from file_get_contents ('php: // input'); What you do with that data Depending on you, you can write the data in a file as you mentioned that this is image data, you can drop the data into the object can do. Another option is to use and load the image.
Read more

Linux Terminal Problem with Non-Canonical Terminal I/O app -

-
I have a small app written in C to run on Linux. Part of the app accepts user-input from keyboard , And it uses non-canonical terminal mode so that it can respond to each keystroke. The section of the code that accepts input is a simple task that is called repeatedly in the loop: char get_input () {char C = 0; Int res = reading (input_tamille, & amp; c, 1); If (RSS == 0) 0 returns; If (res == -1) {/ * snip error handling * /} return c; } It reads a letter from the terminal if there is no input within a given time limit, (c_cc [VTIME] value in the vocabulary structure), read () 0 And get_input () is called again It all works very well, I have recently discovered that if you run this app in a terminal window, and then the app Close the terminal window without ending, the app will not exit Received, but CPU intensive launches into an infinite loop, where the read () returns 0 without waiting continued. So how can I get out of the apple if it runs from the terminal win...
Read more