recordset question 1

[lovejaeeun]

Hi, I have a question.

Im making an ASP page that would allow a user to change the primary key of a database table. I have a form that takes in two inputs (the old PK, the new PK).

Right now Im just openning two recordsets.

1) SELECT * FROM mytable WHERE pk = 'oldPK'
2) SELECT * FROM mytable WHERE pk = 'newPK'

I do a EOF check on the first recordset to make sure it exists. And I do the same for the second except I check to make sure it DOESNT exist.

I was wondering if there was a better way to do this? I want the least amount of work for the server. I was thinking I could just open a single recordset:

SELECT PrimaryKey FROM mytable

And iterate through each record to check if the newPK or oldPK exist? Is this faster than openning two recordsets like above?


Where can I learn about writing efficient code that gets the most work done while using the least amount of server resoures?

 

[onpnt]

great topic!

It really all depends on the database you are using though. if it is SQL Server then you can get the @@ROWCOUNT

check out rac2's reply at the bottom of this thread
thread333-808233



___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[lovejaeeun]

onpnt,

Thanks for your reply but I am not sure how it relates to my question.

My question is what is the most efficient way to check if I can change the primary key. The two conditions that must be met are:
1) the oldPK must exist
2) the newPK must NOT exist

If I am just doing a check (before doing any UPDATES on the database), how would @@rowcount work for me?

I am using MS Access 2000. But if the answer is different for SQL2000, I wouldn't mind learning about that too.

Thanks!

 

[onpnt]

what database you are using matters the most in this type of instance. A desktop DB like Access does not lend itself to the abilities of a database server.

In your case, seeing as Access is the situation, then what you are doing is about the most efficient process I can see at this moment.

If it was MS SQL/MySQL/Oracle/DB2 and on... you would have he abilities to simply do the update on the table and return a value to the recordset indicating if the update actually occurred. in noting @@ROWCOUNT, this value gives you a int of the number of rows that were affected by the sql statement. so if you returned this value form a stored procedure on SQL2K, then you could validate > 0 to see if both, the value was updated and if the where clause existed.

In something as small as this instance, a fast select after the update of the new primary id would be useful also.
if you did something like that, then you just do a if on the rs being EOF and if not write the rs(0) out. that being the new id.


___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[lovejaeeun]

onpnt,

Great feedback, thanks. I was wondering if I could get your efficiency advice on another piece of code. It involves two tables with a one-to-many relationship with a mandatory parent and optional child.

Family (parent) |-------------------| Picture (child)

Basically my ASP page lists out all the families and their info (family name, address, phone, email, etc). If the family also has at least one picture on file, a small camera icon appears next to their name in the list.

This is what I'm doing now, and I would like to know if you have a more efficient way of doing things. (There are about 1,200 records in the table right now, and there will be another 1,200 records added each year.)

I make 2 recordsets:
1) SELECT * FROM tblFamily
2) SELECT filename DISTINCT FROM tblPicture

To make the list I just have a nested loop. The first loop prints out the family info and the second loop iterates through the entire 2nd recordset to see if the family has a picture. Then it moves on.

This seems like a lot of iterations because at least 95% of the families have pictures. So the first loop will go through 1,200 loops and for each of those loops it will do another 1,200 * .95 loops. There has to be a better way?

PS - is efficiency just learned through experience? I would love it if there were just a book that says: "if you have to so XXX, then YYY is the most efficient way"

Thanks!

 

[onpnt]

Just join the tables and then it will get rid of the extra loop.

so there must be some kind of identification (relational key) between the tables and on that it would simply be something to the tone of

SELECT * FROM tblFamily
INNER JOIN filename ON tblFamily.ID = tblPicture

At this point it seems SQL is the place you can optimize things. That is most commonly the 80% deficiency in some ASP development. Basically not using the power of all the tools around the pages!

I wrote a few FAQ’s on using the DBMS for getting the best statement to get all the info in one statement
Development practices when using SQL/Interfaces faq333-4896
How can other tools help me develop ASP faq333-4770


I have some bookmarks I'll post back in a bit on optimization of ASP's

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[onpnt]

derrr.

that should be
SELECT * FROM tblFamily
INNER JOIN tblPicture ON tblFamily.ID = tblPicture.ID

see what happens when you do ten things at once

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[onpnt]

here is 3 good links I had bookmarked on my laptop, more to post later. sorry..not much here as I'm on the road

http://www.4guysfromrolla.com/webtech/110898-1.shtml

http://docs.sun.com/source/817-2514-10/Ch3_ManageASP23.html

http://www.learnasp.com/learn/speedtablesall.asp

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[lovejaeeun]

onpnt,

wonderful tip again, however I have some questions.

I need to display a list of EVERY family ONCE(even if they don't have a photo, or even if they have 10 photos). If they do have a photo, i will put a small camera icon next to their name.

I figure that I will need to use a LEFT JOIN instead of an INNER JOIN because I want the family info even if they do not have a photo.

But if I use a LEFT JOIN, what if the family has 3 photos?
Wouldn't that mean the family would be listed 3 times? Is there a way to sneak a DISTINCT in the SQL statement anywhere?

PS - ill be sure to check your FAQs. im sure they will be full of great info.

 

[onpnt]

I think at that point testing to see which method is going to
be the most efficient on the server/database to the script portions

if the table struture looks something like thi
tblFamily
Family_Ident
Family_Name
Family_Address
Family_Phone
Family_Email

tblPicture
Picture_Ident
Family_Key
Family_Picture

and your primary to foriegn is the Family_Ident -> Family_Key

the best way to group a query on the 2 tables is what you stated as

SELECT 
tblFamily.Family_Name, 
tblFamily.Family_Address, 
tblFamily.Family_Phone, 
tblFamily.Family_Email, 
tblPicture.Family_Picture
FROM tblFamily 
INNER JOIN tblPicture ON tblFamily.Ident = tblPicture.Family_Key
ORDER BY tblFamily.Family_Name;

that would return something as

Family_Name	Family_Address	Family_Phone	Family_Email	Family_Picture
myName		9999		999-999-9999	email@email.com	/path/pic.gif
myName		9999		999-999-9999	email@email.com	/path/pic2.gif
myName		9999		999-999-9999	email@email.com	/path/pic3.gif
myName		1111		111-111-1111	email@email.com	/path/pic.gif
myName		1111		111-111-1111	email@email.com	/path/pic2.gif

So the first test would be to grab that resulting data and see how efficient it is
to get what you need out of it. logic form I would think up something like
this for a JOIN on the tables and run through while having a nested loop to grab
all the relational pictures and print them while only printing the primary data once.

SQL = "SELECT " & _
		"tblFamily.Ident, " & _
		"tblFamily.Family_Name, " & _
		"tblFamily.Family_Address, " & _
		"tblFamily.Family_Phone, " & _
		"tblFamily.Family_Email, " & _
		"tblPicture.Family_Key, " & _
		"tblPicture.Family_Picture " & _
		"FROM tblFamily " & _
		"INNER JOIN tblPicture ON tblFamily.Ident = tblPicture.Family_Key " & _
		"ORDER BY tblFamily.Ident;"

RS.Open(SQL),conn,3,3

Dim IdentHold
Dim IdentCompare

If NOT RS.EOF Then
	IdentHold = RS("Ident")
	IdentCompare = RS("Family_Key")
End If

Do While NOT RS.EOF
	IdentCompare = RS("Family_Key")
 	IdentHold = RS("Ident")
 	
    Response.Write RS("Family_Name") & "<br>"
    
    Do While NOT RS.EOF AND cInt(IdentHold) = cInt(IdentCompare)
    	Response.Write RS("Family_Picture") & "<br>"
    	RS.MoveNext
    	If NOT RS.EOF Then
    		IdentCompare = RS("Family_Key")
    	End If
    Loop
Loop

In your case I would tend to say (and what I would go with) is the two recordset's.
That due to maintainability in all. I curious to test the efficiency of both though.

the things you would want to take into careful consideration here are
1) the time to hit the database with the type of query (SQL Statement)
2) the looping structure time
3) the needs for multiple defined variables etc. for teh actual looping structures
4) the time needs for opening the multiple to single object (recordset)

Let's get a bit donw to the real world though for a minute also.
In a application that is this small in data read, there is going to be little
difference in the methods. Now if you get to a larger data source, say 100,000+
then I would go with the JOIN and looping structure above more so in my initial tests.
The reasoning for that is mainly due to your statements here

This seems like a lot of iterations because at least 95% of the families have pictures.
So the first loop will go through 1,200 loops and for each of those loops it will
do another 1,200 * .95 loops. There has to be a better way?

In the structure above that is not going to happen. What will happen is it will in reality
loop through one recordset only one time. The conditioning jsut gets a bit more indepth to
catch where and where not to perform the tasks needed.

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[lovejaeeun]

onpnt,

Thanks again for a wonderful post. Gotta give a star for that. As my last question (i promise!), how am I supposed to test efficiency?

Is there some application that I am supposed to run along-side my code? Or am I just supposed to time (with a stop-watch?) how long it takes for the page to load?

I don't have any control over the server because it is hosted elsewhere, so I would not be able to install any 3rd party apps or make any changes to the server's settings.

Thanks again!

 

[onpnt]

Tarwn has a example of testing the performance of some scripts in this thread
thread333-792152

That should give you the method of seeing how long the pages are loading.

you'll need to break it down one by one to optimize the code after getting your results.




___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]