Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a Application which was developed in VS2005 and Database as SQL Server 2000.
 
If i have database in Executing machine, it logins fastly. But when i have database in server it take nearly 1 minute for Login(My startup form is Login)
 
I am new to this Concept can anyone have suggestion for this Problem?
 
MyConnection String is:
 
dim con as sqlconnection
con=new sqlconnection("server=XXXX;database=Test;uid=sa;pwd=123;Connection Timeout=100;Trusted_Connection=False")
Posted 2-Aug-12 0:52am
Comments
shijuse at 2-Aug-12 7:24am
   
Using the sql query execution plan you can optimize your query..or may be this is a network issue..

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

First, your Login form should NOT be your start up form. Make you main application form your startup form and the Login form gets called if the main form determines that the user is not logged in.
 
Second, without seeing your login code (besides what you already posted), it's pretty much impossible to tell you what you're doing wrong.
 
Third, you're using the "sa" account to connect to the server?? That's a VERY BAD practice. I realize that this may just be a test server, just don't get into the habbit of doing that.
  Permalink  
Comments
Member 8089069 at 3-Aug-12 4:26am
   
I was changed my startup form to normal form but still the form take long time
 
My Login Code was
 
con.Open()
cmd = New SqlCommand("select UserName,Password from Login where UserName COLLATE Latin1_General_CS_AS='" + txtUserName.Text + "' and Password COLLATE Latin1_General_CS_AS='" + txtPassword.Text + "' and Status='Active'", con)
da = New SqlDataAdapter(cmd)
da.Fill(dt)
cmd.Dispose()
If (dt.Rows.Count = 1) Then
Dim theNetworkInterfaces() As System.Net.NetworkInformation.NetworkInterface = System.Net.NetworkInformation.NetworkInterface.GetAllNetworkInterfaces()
For Each currentInterface As System.Net.NetworkInformation.NetworkInterface In theNetworkInterfaces
macaddress = currentInterface.GetPhysicalAddress().ToString
Exit For
Next
If String.Compare(macaddress, "") = 0 Then
MessageBox.Show("Internal Error: Unable to Login", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
Else
cmd = New SqlCommand("delete from temp_table where PhysicalAddress='" + macaddress + "'", con)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into temp_table values('" + dt.Rows(0)(0).ToString + "','" + macaddress.ToString + "','" + Date.Now.ToString + "')", con)
cmd.ExecuteNonQuery()
con.Close()
Dim fobj As New frmMain
Me.Visible = False
fobj.Show()
End If
Else
lblError.Visible = True
con.Close()
End If
 
i am trying to change my server. thanks for your reply
Dave Kreskowiak at 3-Aug-12 8:06am
   
OK, what's with te COLLATE modifiers?? You usually just set that at the database level and don't have to put it in the SQL query strings.
 
What's with the MAC address stuff?? There are problems with what you're doing, namely, machines now-a-days have multiple network cards and MAC addresses, not just one. Laptops can switch between a hardwired and wireless NIC just by popping out the cable, thereby changing the MAC address in use.
 
There's nothing obvious in your code that suggests what the problem is, so I'm guessing that your database tables are not indexed properly to allow faster lookups, or the SQL server is misconfigured somehow screwing with remote connections.
 
Also, you're storing passwords in clear text. That's a HUGE no-no in a production system.
Member 8089069 at 4-Aug-12 0:33am
   
Thanks for your Guidelines, Finally through your guideline i was solved my problem

yes i was changed the collate modifier to database.
 
ok i will go through the MAC concept.
 
After indexing Login table, now it logins fastly in remote connection. I need a Clarification, is indexing must for all tables?
Dave Kreskowiak at 4-Aug-12 10:45am
   
No. As a rule of thumb, you only need it on columns where you need fast record lookup functionality by searching on those columns. Indexs also slow down database write as the index must be updated, so there's a price to pay to get faster lookups.
Member 8089069 at 6-Aug-12 5:25am
   
ok thanks for your support
shanmugamvasu at 22-Dec-12 3:20am
   
Again i am facing the same problem .. Please help me
 
Table :
create table Login(UserName varchar(50) primary key,Password varchar(30),Status varchar(15),Limit varchar(200))
create index Login_index1 on Login(UserName,Password,Status)
 
This login table has one two rows .. Even it takes nearly more than a 20 seconds to login(ie : It takes 20 seconds to all operation insert,reterive).
I don't know, why it happens..
 
My Code:
 
con.Open()
cmd = New SqlCommand("select UserName from Login where UserName='" + txtUserName.Text + "'", con)
pwd = cmd.ExecuteScalar.ToString()
con.Close()
 
Any Suggestion would be heplful.
Dave Kreskowiak at 22-Dec-12 11:10am
   
SELECT UserName from Login where UserName = 'something'?? Really?? Do you know what's that SQL statement is doing??
 
There isn't enough information here to diagnose what's going on. You have to step through this code in the debugger, line-by-line, and see which one is taking forever to execute. Beyond that, you'd use a profiler to find out where the app is taking a lot of time. It could also be that your code is taking forever to execute something AFTER you check the login credentials against the database.
shanmugamvasu at 8-Jan-13 2:33am
   
Thanks for your reply. Now i have some information, When we open the sqlconnection for fist time it takes long time. After connection was opened all queries are executed fastely.
 
My Code is:
 
con.Open()
MessageBox.Show("Con Opened")
cmd = New SqlCommand("select Limit from Login where username='" + txtUsername.Text + "'", con)
da = New SqlDataAdapter(cmd)
da.Fill(dt)
da.Dispose()
cmd.Dispose()
con.Close()
 
The Messagebox Con Opened came after long time. Please Guide me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 2 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100