why is SQL server 2000 is significantly slower OUTSIDE private network
-
hi,
I will be very happy if anyone can help me on that one, since the Point of Sale application I have built in VB is nearing its end, and only now, after using the SQL server 2000 from outside (i.e., not from within my private network), so I realize that the application works so slowwww. For example, I have a VB control (list view) which lists primary keys of some table. Every time I click on an item, it takes a couple of seconds ( 3 seconds!) for the VB application to fetch the field values corresponding to the primary key the vb list view control item is associated with and display them in vb text boxes or labels. This happens only with a connection NOT within the private network (WORKGROUP). In the private workgroup, it's a different story -- it takes a fraction of a second to do what I have just described above with respect to displaying values given a primary key of some table.
To be fair, the remote location in which the VB application fetches datra from the SQL server has a slower dsl connection, but at the same breath I will say that from this same location fetching data from another sql server (which is not under my control) is substantially faster than fetching data from my sql server 2000.
So just to make sure I was clear and not too verbose, it seems that there is something wrong with the configuration of the sql server and/or the using end when it comes to use of SQL server outside of the private network. It would be a great help for me, and a point of relief, if someone among the experts of this SQL oriented group can point to reasons that may cause the remote data fetching to be so slow.
thank you in advance
avi
-
The best way to find that out is to use networkmonitor and profiler to see what your client is doing and which part is slowing down your queries (resolving the server, sending the data or executing the query). But my first guess would be name resolution, allways connect to your server using the full dns name and make sure all your clients can resolve it, if your clients really need to connect based on ip adress make sure that your can do a reverse lookup of it.
-
Try setting up an alias using cliconfg, and putting an entry in the hosts file
-
Could you have a router or other piece of hardware equipment that is giving a priorty boost to some packet types over others?
-
Thank you all for the diverse potential solutions. I'm very keen to find the solution for the problem, as I'm in apoint of stagnation when it comes to lallowing others to use my Point of Sale application, and I promise to post the final answer once discovered.
My gut feeling is that the problem lies in anything that has to do with authentication or resolution, though I’m not so familiar with the subjects or the techniques to check or improve the configuration related thereto. I do intend to use the SQL Profiler ,which is totally new to me, as I have never worked with it before (thank you Leenders for calling my attention – I’m sure I’ll have questions to pose to you in the next coming days)
Before I do anything with respect to WSquared’s suggestion, I think I need to understand a few things. In your reply you mentioned the word cliconfg, which, I believe, relate to Client Configuration or to Client Network Utility. If that is the case, then I should point out that in my initial post I meant that client computers connect to an SQL server through a VB code (included at the end of this reply). Those client computers do not have SQL server, and my apology if I mislead you to think otherwise. If your suggestion is still applicable, even when client computers fetche data from the sql server programmatically with help of MDAC components, then I would be happy to learn how that suggestion can be implemented and tested, andI would appreciate it if you could provide more details as to how to accomplish your suggestion, since I’m hardly familiar with the individual topics related to client configuration, how they work together with other files or components, if any, and how the VB code can ‘cooperate’ with the the configuration you suggested.
As far as HenryTR’s suggestion is concerned, I don’t rule out the possibility that the problem lies with the workings of the router. After all, I mentioned before that in a private network neighborhood the speed of data fetching is flawless, so it’s reasonable to think that the configuration related to connection to outside might be the source of the problem, let alone the router plays a big role in the connection to outside. However, I only managed to understand the concept behind that suggestion, not the details or the interpretation of it to practical steps – I get lost when I come across tems like ‘priority boost’ or ‘packet types’. It seems like you have a deep insight into the matter, and I would appreciate it if you pushed through in that direction, taking into consideration that I’m a layperson.
Just so you all have a better picture, here is the code that connects to the server, along with a few lines that fetch data with the help of a recordset denoted as rs.
dim conn As ADODB.Connection, rs As ADODB.Recordset
set conn = new ADODB.Connection
ConnectionString = "Driver={SQL Server};" & _
"Server=192.168.0.2;" & _
"Address=192.168.0.2,1433;" & _
"Network=DBMSSOCN;" & _
"Connection Timeout=30;" & _
"DatabaseMYDBNAME;" & _
"User ID=sa;" & _
"Pwd=mypassword"
conn.Open ConnectionString
Set rs = conn.Execute("select * from sometable")
Debug.print rs(“somefieldname”)
Once again, I thank all of you for the time you took to help me solve my problem, and I'm looking forward to hearing more from you
Avi
-
Hi Avi,
For the client configuration side, enter "Setting Up Client Configuration Entries" in the search panel in BOL, once you have set up your alias, you can put an entry in the hosts file, this will allow a bypass of the DNS server, which may be the point at which the process is slowing down, it certainly was the case on our network
-
hi WSquared ,
thanks for the prompt response. Please correct me if I'm wrong. Generally speaking, the only place I can go ahead and apply the configuration you suggested is on a client computer that HAS sql server installed, right? In my case, the client computers do not have sql running -- they connect to SQL by means of ADO component that is part of the MDAC component, and the process is done with the VB code I included in my previous post:
dim conn As ADODB.Connection, rs As ADODB.Recordset
set conn = new ADODB.Connection
ConnectionString = "Driver={SQL Server};" & _
"Server=192.168.0.2;" & _
"Address=192.168.0.2,1433;" & _
"Network=DBMSSOCN;" & _
"Connection Timeout=30;" & _
"Database=MYDBNAME;" & _
"User ID=sa;" & _
"Pwd=mypassword"
conn.Open ConnectionString
Set rs = conn.Execute("select * from sometable")
Debug.print rs(“somefieldname”)
That snippet of code is the only way by which I nget to fetch data. I don't have the sql running on the client computer to the extent that I can create a remote instance of whatever is in the sql server.
In light of those facts I just mentioned above, do you still think that what you have suggested is applicable in my case. In other words, do you think that my sql server usage setup is similar to the one on your network where, as I understand it, all the client computers, apart from the main server, had sql server installed. I would appreciate your answering to this question, since if your company's case is similar to mine, then I think I'm missing something.
Thanks
Avi
-
Hi Avi,
As far as I'm aware, cliconfg.exe is part of windows, and it resides in the winnt\system32 directory.
The hosts file is located in \WINNT\system32\drivers\etc
-
Thank you all -- it's good to come across people like you.
Unfortunately, the configuration with cliconfig.exe is not related to the problesm in my machine --as far as I can tell -- since the use of cliconfig.exe implies that the client machines have SQL installed therein, which is obviously not the case over here. But it could be that I dont see WSquared's idea in the right way because I still need to learn about SQL in generall. In any rate, I appreciate your help WSquared. Please let me know if you see something that I can not see with respect to implementing your potential solution.
As far as the still-existing slowness problem is concerned, I think that one of the reasons for the existence of such problem was the fact that I enabled the TCP/IP protocol and Named Pipes protocols on the Server Network Utility, whereas the desired configuration is TCP/IP protoco only. In light of this observation, I disabled the Named Pipe Protocol, which has contributed somewhat to the performance as far as overall execution time is concerned -- in one way or another (which I can not explain) it helped a bit.
So as of this moment of writing these lines, the problem still exits in full strength. Just to give you an idea: on a private network some vb code using vb controls and adodb.connection object. etc. to fetch data and display it in a list view takes 1 second and change( less than one millisecond). The same machine using the same vb code but over a remote connection takes more than 30 seconds!!
At first I feared that the stark differences can be accounted for by the fact that I did not use indexes (e.g., tables or views indexs), but as I delved into the matter with the SQL Profiler, helping myself with the suggestion of Coen Leenders , I noticed that the Audit Logout duration in remote connection is way out of what one would expect according to the results in the private network. For example, everytime an execution of sql statement is completed, the Audit Logout process(event) follows. The remote connection manifetsed consistently higher durations per Audit Logout over the private network neighborhood -- at one time it reached 12257 millisecons! (12 seconds) compard to 313 milliseconds in private netwrok.
So the problem is now narrowed down to the so-called Audit Logout event, something that I'm hardly familiar with. I hope I can get here some help in figuring out the reasons for the existence in Audit Logout events' duration between private network connection and remote connection, and what needs to be done to improve the performance.
Please remember that the only difference between private and remote connection is the IP address. The connection string is the same connection string except for the IP address. In private netwrok it is 192.168.0.2 in remote is something like this 24.191.235.111.
any assitace will be much appreciated
Avi
-
Hi Avi,
If you install MDAC on the client computer, then cliconfg.exe will be installed as part of this.
You then have to create an alias for your server, call it what ever you like eg. MyPOSServer, but enter the same name in the Server Alias and Server Name boxes
Ensure that you have clicked on TCPIP
In the hosts file
which is located in C:\Winnt\System32\Drivers\etc
add an entry that looks like the following
24.191.235.111 MyPOSServer
You can then refer to your server by name in the connection string, this should bypass the DNS server
-
Hello WSquared,
Thanks you for clarifying what I otherwise thought to be irrelevant to my case. However, having followed you instructions, I don't see any improvement. It seems as if the connection is not robust when my application excutes sql statement (e.g., select filedname from some table). I tend to think so, because when I debug the program (by using a debugging mode and break points) I notice that the duration per line of execution such as 'set myrecordset=myconnection.execute("select * from mytable") ' is relatively long. At times the execution of such line will end with a time out error.
I hope there are other ideas in your mind as far as solving this annoying problem.
thanks
Avi
-
WSquared - YOU ROCK!!!! I had a similar situation to the one that started this thread, but your solution resolved the issue. I upgraded a 6.5 database to 7.0 and then to 2000 tonight, everything was great except the performance of one screen in the application that ran A LOT slower in 2000. Your suggestion took care of the issue and kept me from having to work all night - thank you so much.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply