INSERT INTO query

[AKS44]

I am working on a user registration page. It was initally designed with a Do While Loop and I have been encountering latency issues because it is looping through 35,000+ records in SQL before entering a new record.

I am attempting to change it to an INSERT INTO query. The DB has a Name, Email, Password, Salt, ID_Code, HTMLformat, and Active field. Where I am running into an issue is how to get the following code

If blnEncryptPasswords Then .Fields("Salt") = strSaltValue
.Fields("ID_Code") = strUserCode
.Fields("HTMLformat") = blnHTMLformat
.Fields("Active") = False

into my insert statement?

'Insert Statement
strSQL = "INSERT INTO " & strDBTable & " ([Name],,[Password]) VALUES " & _
"('" & strUserName & "', " & _
"'" & strEmail & "' " & _
"'" & strPassword & "' " & _
")"

 

[jbenson001]

I am not sure what the question is. But I have one. Why is the code looping through 35,000 + rows?

Jim

 

[AKS44]

It is set up to Loop through all the records in the recordset to check that the user id and the email address are not already in the database
If there is no user code or it is already in the database make a new one and serch the recordset from the begining again
If the e-mail address is already in the database then this is an update so exit loop

 

[vongrunt]

Loop?

What happened with good ol' WHERE clause?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

To resolve that issue, I would just query the DB to find out if the user exists.

Select count(*)
from table
where userid = newuserid

then do the same for the emailaddress

If the count = 0 then insert the new info, otherwise create a new one and try again.

 

[AKS44]

I am doing an inital query

strSQL = "SELECT " & strDbTable & "Members.* FROM " & strDbTable & "Members WHERE(Email = '"&strEmail&"');"

rsCommon.Open strSQL, adoCon

Do While NOT rsCommon.EOF
blnEmailExists = true
rsCommon.MoveNext
Loop
rsCommon.Close

If the email doesn't already exsist then enter the email into the database

If blnEmailExists = False Then

'Encrypt password
If blnEncryptPasswords Then

'generate a salt value
strSaltValue = hexValue(Len(strPassword))

'Concatenate salt value to the password
strPassword = strPassword & strSaltValue

'Encrypt the password
strPassword = HashEncode(strPassword)
End If


This is where i am doing the INSERT INTO....

 

[vongrunt]

Why not simply:

strSQL = "SELECT " & strDbTable & "Members.* FROM " & strDbTable & "Members WHERE(Email = '"&strEmail&"');"
rsCommon.Open strSQL, adoCon
blnEmailExists = not rsCommon.Eof
rsCommon.Close

... but I don't think that's the only problem. That simple DO WHILE loop couldn't crawl over 35,000 records.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[AKS44]

vongrunt, your right, that's not the only issue. Below is the original code. All I am wanting to do is improve the performance of this code. This page is the registration page for new user accounts, the existing db has 35,000+ records in it already. When a new user signs up it takes about 50 seconds to run this page before it returns any information, i.e. user is already registered, or user has successfully registerd, or what ever.

Original Code.

'Initalise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT " & strDbTable & "Members.* FROM " & strDbTable & "Members;"

With rsCommon

'Set the cursor type property of the record set to Dynamic so we can navigate through the record set
.CursorType = 2

'Set the Lock Type for the records so that the record set is only locked when it is updated
.LockType = 3

'Query the database
.Open strSQL, adoCon


'Calculate a code for the user
strUserCode = hexValue(20)

'Loop through all the records in the recordset to check that the user id and the email address are not already in the database
Do While NOT .EOF

'If there is no user code or it is already in the database make a new one and serch the recordset from the begining again
If strUserCode = .fields("ID_Code") Then

'Calculate a code for the user
strUserCode = hexValue(20)

'Move to the first record to make sure the new user code is not in the database
.MoveFirst
End If

'If the e-mail address is already in the database then this is an update so exit loop
If strEmail = .fields("Email") Then

'Set the blnEmailExists variable to true
blnEmailExists = true

'Exit the for loop
Exit Do
End If

'Move to the next record in the recordset
.MoveNext
Loop


I assumed that it would perform better if i did an INSERT INTO, that is the partal code i supplied in my previous post. Sorry for any confusion in this post. This is the first time i have posted to a forum.

Thank you for you help with this issue.

 

[vongrunt]

I don't understand this part:

'If there is no user code or it is already in the database make a new one and serch the recordset from the begining again
If strUserCode = .fields("ID_Code") Then
             
	'Calculate a code for the user
	strUserCode = hexValue(20)
            
   'Move to the first record to make sure the new user code is not in the database
   .MoveFirst
End If

strUserCode is already initialied to hexValue(20), there is no need to do that again. And if ID_code exists, wouldn't .MoveFirst go top and cause infinite loop?

I guess e-mail is used as unique key to identify user. And ID_Code is also unique; new user cannot have existing ID_Code, right?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

I need some clarification. Are you checking if the UserName exists first, then checking if the EMailAddress Exists? Are you saying that both need to be unique to the table?

 

[SQLSister]

your first problem is that you can't insert more than one record without looping if you use the values clause of the insert statement. Look in BOL for how to use a select clause instead.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[AKS44]

vongrunt,

That is correct the ID_Code needs to be unique. It is used in the email for varification, it addes the verification code hexValue(20) to the url and sets Active=1. The email address also needs to be unique, no duplicates for either field.

 

[AKS44]

my revised code resulted in a almost insentanious reponse.


'Insert Statement
strSQL = "INSERT INTO " & strDBTable & "Members ([Name],,[Password],[Salt],[ID_Code],[HTMLformat],[Active]) VALUES " & _
"('" & strUserName & "','" & strEmail & "','" & strPassword & "','" & strSaltValue & "','" & strUserCode & "',1,0)"




'Write to database
adoCon.Execute(strSQL)

 

[vongrunt]

> my revised code resulted in a almost insentanious reponse.

As it is supposed to work. And for checks whether entered value (ID_Code?) exists or not, simply use trivial SELECT query with WHERE clause and test for RS.Eof (no looping).


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)