Taking more time for Login in a Vb.NET exe (Windows Application)

Question

4.00/5 (1 vote)

See more:

, +

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 1-Aug-12 23:52pm

Shanmugam Vasu

Add a Solution

Comments

shijuse 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Accepted Answer · 2012-08-02T02:20:00

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.

Posted 2-Aug-12 2:20am

Dave Kreskowiak

Comments

Shanmugam Vasu 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 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.

Shanmugam Vasu 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 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.

Shanmugam Vasu 6-Aug-12 5:25am

ok thanks for your support

Shanmugam Vasu 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 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.

Shanmugam Vasu 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