Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/Code)
 Better Admin_moderators.asp
 New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  08:47:17  Show Profile  Visit HuwR's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
work mule:

1) transactions don't work in mysql.
2)
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Why put it in a transaction ?
Do you generally have problems with queries failing?

Even if this code failed, all that would happen is that either you would end up with no moderators, or only some of them, it doesn't appear critical that you should transaction it.

if it failed while adding 200 moderators, would you want it to roll it back, or leave you with half of them inserted?
<font color=blue>'Resistance is futile'</font id=blue>

Edited by - Huwr on 21 December 2000 08:48:39

I just tried this against Access using Jet4, and SQL server, worked on both even with transaction left in

Edited by - Huwr on 21 December 2000 09:09:52
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 21 December 2000 :  10:06:18  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Why put it in a transaction ?
Do you generally have problems with queries failing?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

It's more a case of work load... Queries failing? Hum, no when I have 10 users on the board, but for myself I'm use to work with BIG systems (CN, Bombardier, IBM, Banks, gouvernements sites...), so yes we need Transaction... Try to have 200 concurents user, you will see, queries failing.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Even if this code failed, all that would happen is that either you would end up with no moderators, or only some of them, it doesn't appear critical that you should transaction it.
if it failed while adding 200 moderators, would you want it to roll it back, or leave you with half of them inserted?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

To thing... Without Transaction you will have to do it one by one, because if you have a moderator on-line it could corrupt you database. Let say is correcting someone mail... What will hapen if he's not a moderator (for a short while) when he saving the message... Or moving the tread to a new categorie?

With transaction you could do someting like this: delete all Moderator, Insert new moderator with one single statement : insert ... WHERE Id In [1,2,5...]...

Then if the transaction didn't work, the program could pop a message to the admin, (the SQL server would roolback at the sametime), then send the admin BACK to the modification, and he could resubmit it.


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  10:29:39  Show Profile  Visit HuwR's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
To thing... Without Transaction you will have to do it one by one, because if you have a moderator on-line it could corrupt you database. Let say is correcting someone mail... What will hapen if he's not a moderator (for a short while) when he saving the message... Or moving the tread to a new categorie?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I don't see this would corrupt anything, it either wouldn't post the changes (because not moderator) or he wouldn't see any moderator icons, changing his status halfway through a post won't make any difference.

Your code IS doing the inserts one by one.

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 21 December 2000 :  10:57:31  Show Profile
Okay....

How about using the batch method? That way everything is batched together and sent to the server once.

For every checkbox that is checked, an insert is made, so how I understand this, with the batch method it would be combined into one call to the database, whereas without using the batch method, it would have to send the data to the database for every insert (checked checkbox).

What I can't find an answer to is if transactions also batch everything and sends it once too?



Edited by - work mule on 21 December 2000 10:59:25
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 21 December 2000 :  11:04:27  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I don't see this would corrupt anything, it either wouldn't post the changes (because not moderator) or he wouldn't see any moderator icons, changing his status halfway through a post won't make any difference.

Your code IS doing the inserts one by one.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Hum, I don't know who you implemented the security, so maybe it won't cause problem, but here is many thing that could create problem... Maybe not it THIS case, maybe not with mederator, but when you write one multiple tables in the forum. I might (if I have time) take a look into the forum to see where there it may cause problem.

Yes is one by one... But on one call, it a litle bit faster. With trafic we have to think of this. Because this site is good, but when you have many users at the same times, there is this that slow it very mush.

Working for site with 1000's of user by hours on it, I saw so many problem. And because I did some stress testing on many site, I can say it will cause problem.

I might do some stress testing. Don't forgot, maybe it will not be the DB that will cause trouble, but the ASP page itself, ADO, or juste the connection... For myself SQL server isn't on the same server... So the connection could be lost.


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  11:12:42  Show Profile  Visit HuwR's Homepage
A transaction in SQL flags the SQL code with an ID so that it can roll them back, it doesn't batch them and then send it all at once. each execute is sending an insert to the DB.

If you are worried about SQL failures, surely it is better to send them one at a time, less chance of failure because small insert, you could trap the errors and record a log so you know which ones failed. If you put them in a transaction they will all fail.

All that batching is doing is sending all the inserts togther rather than one at a time.

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  11:22:08  Show Profile  Visit HuwR's Homepage
using UpdateBatch would have an adverse effect on Access DB's

The UpdateBatch method of an ADO recordset can be used to send multiple recordset updates to the server in one call and can significantly reduce process times for some recordset updates. However, this is not the case with Jet databases. When the UpdateBatch method is used with a Jet database (.mdb), Jet still processes each instruction individually therefore, process times using the UpdateBatch method will generally have an opposite adverse affect of actually increasing process times by more than 100 percent.



<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 21 December 2000 :  11:23:12  Show Profile
Cool! Thanks for the information! I'm definitely learning a lot of stuff through Snitz (this forum, members, and the forum code) - Thanks!! <img src=icon_smile.gif border=0 align=middle>

So...

I haven't changed the file yet. Any recommendations? Should I make two versions? <img src=icon_smile_wink.gif border=0 align=middle> Or code one of these in and then comment out the alternate version and put comments in the code?

1 - Transaction Method - the way it is now?
2 - The Batch Method - is this compatible with mySQL?
3 - or....if need be neither of the above and multiple inserts

Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 21 December 2000 :  11:31:10  Show Profile
Man, I just can't win here!! So how about checking for the db type and using a select statement for the update portion?

SQL Server - Transactions or batch
mySQL & Access - multiple inserts

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
using UpdateBatch would have an adverse effect on Access DB's
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I tested this with Access, but ya know, since it was only a test site, I only have a couple of topics and members so I wouldn't have noticed anything.

Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  11:34:22  Show Profile  Visit HuwR's Homepage
Just do a check for dbType and code accordingly, thatway it will work with everything, I will check on mySQl see what it supports in the way of transactions or batches.

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 21 December 2000 :  12:08:10  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
If you are worried about SQL failures, surely it is better to send them one at a time, less chance of failure because small insert, you could trap the errors and record a log so you know which ones failed. If you put them in a transaction they will all fail.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I don't talk doing it by batch...

Ok, it's a mater of opinion. But, SQL isn't the only point of failures. For multiple table doing it, even by loging error, it may cause some coruption, let say you have to update 3 differents tables, and they ALL have to be updated, you update the 1st one, the ASP fails... You have only one updated, you don't have any log (the ASP fails). So... BIG problem.

Ok, you create small insert... 2-3 might be ok. But imaging having to do 50-100 for 100 users at the SAME time... You see my point here. Even 2-3 with many user (100+) might cause problem.

Create to many process on you server, lost time not just because creating all this but you lose CPU time just with process swaping. You might NEVER have problem with you site, but it's why I might not be able to use snitz... Requirement for site with million on hits by months.

Even the ASP cause problem, too much mode switching (<% %>). It take time. All good book on ASP tels about uptimising your code. Then there is the HTML... Yes IE is very forgiving.... Netscape isn't as much, but is forgiving. But with well form HTML will display much faster on both browser.

It might not be very easy to see, because you have only fraction of second, but, with many users all thoses fractions become seconds and maybe minutes. I yes I saw this often.


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 21 December 2000 :  12:11:32  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
tilttek, I cannot put it into my snitz forums addons :( the asp is screwed up in some way I cannot fix. I will have to rewrite that entire script.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I just saw you answer... Hum, what is the problem? I could look at it this week-end (30 min max, but usualy I can find problem very fast).


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 21 December 2000 :  12:31:05  Show Profile  Visit HuwR's Homepage
I wasn't suggesting that you shouldn't use transactions, you just need to be aware that for your code to be compatible with other Snitz copies, you have to think about Access and MySQL.

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

frankie
Junior Member

Vatican City
304 Posts

Posted - 21 December 2000 :  12:49:38  Show Profile  Visit frankie's Homepage  Send frankie an AOL message  Send frankie an ICQ Message  Send frankie a Yahoo! Message
Guys,

This may sound stupid, but how do you grant

a member a moderator or an admin status

without doing so in the database table?!!<img src=icon_smile_blush.gif border=0 align=middle>

ô¿~
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 21 December 2000 :  12:56:35  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I wasn't suggesting that you shouldn't use transactions, you just need to be aware that for your code to be compatible with other Snitz copies, you have to think about Access and MySQL.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Exactly, so what I did suggest at first (for user using ASP 3) is to add ASP side transactions... Not SQL side!



Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000 Version 3.4.07