Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Oct 20, 2018 9:38 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Advice re - huge MySQL databases
PostPosted: Sat Mar 10, 2007 8:37 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
I've created a program which converts blogs (Wordpress & b2evolution) to Drupal (content management system). Most users that have used it have had normal size Wordpress databases, between 1000 - 2000 posts (rows) and it works as intended. My test databases are only 500 rows each.

I've been notified by a user with an 80 mb database, I'm assuming 10,000 + rows (lets just say huge) that the program hangs. They are accessing the database remotely (MySQL database on a remote server).

I've revisited my code and the guts of it are like this:-

recordset = db.sqlselect(qry_get_all_rows) // no filtering
count = rst.recordcount
progressbar.Maximum = count
progressbar.Value = 0
While Not rst.EOF
Process each and every record (inserting a row into Drupal db)
wend

My question is; is the above code snippet acceptable for large databases (100,000+ rows). If not, what would be the most efficient method to transfer 100,000 rows of data to another database (it's not a straight copy, some processing of data has to take place - eg. convert dates/time to unix date/time etc).

Any advice appreciated.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Mar 10, 2007 9:53 am 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Though it's hard to say without code why it's hanging, I'm guessing you have a rst.MoveNext somewhere, though you haven't shown it, and rst is actually recordSet they are 2 different things.

If it works for 20 it should work 20 million. Dumby up your database to 80 meg and give it a whirl and see when it hangs. You already have 500 records, just repeat them over and over again. They don't have to be unique for testing.

What happens if you get rid of the ProgressBar, I don't use them but 100,000 may be a bit much for it ? <just an idea>


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Mar 10, 2007 5:34 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Jym wrote:
Though it's hard to say without code why it's hanging, I'm guessing you have a rst.MoveNext somewhere, though you haven't shown it, and rst is actually recordSet they are 2 different things.

If it works for 20 it should work 20 million. Dumby up your database to 80 meg and give it a whirl and see when it hangs. You already have 500 records, just repeat them over and over again. They don't have to be unique for testing.

What happens if you get rid of the ProgressBar, I don't use them but 100,000 may be a bit much for it ? <just an idea>


Thanks for your reply. Recordset and rst are in fact the same thing (I failed to change 'rst' to 'recordset' for the purposes of explanation). Yes there is a .movenext in there but not shown. To clarify the pseudo code is:
recordset = db.sqlselect(qry_get_all_rows) // no filtering
count = recordset.recordcount
progressbar.Maximum = count
progressbar.Value = 0
While Not recordset.EOF
Process each and every record (inserting a row into Drupal db)
Show user progress of processing (ie Progressbar)
recordset.movenext
wend


A question that comes to my mind is that when creating a recordset, and in that recordset there are 100,000+ records, are those records transferred down the line at that point and any methods on the recordset (eg MoveNext) are handled locally.

I'm also thinking of changing the way that the 'RecordCount' is obtained. Retrieve it via an aggregate SQL query. My understanding is that at least the processing for that is done on the server and only the result passed back down the line. In this way I could still display some sort of progress to the user. (Did I just answer my own question in relation to the recordset in the previous paragraph)

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Mar 10, 2007 7:33 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
I think I may have found a solution to my problem. I need to restrict the number of rows returned, lets say 100 and know that there maybe more to retrieve.

It's the LIMIT keyword which is supported by MySql and PostreSQL but apparently is non-standard. This has a starting position and a 'count of rows' to retrieve.

I'm thinking I can use an SQL aggregate function (Count) that passes back the number of rows (say 100,000), that's stored to a variable.

The Recordset created would be based on this sql select statement:-
SELECT * FROM tablename ORDER BY primarykey LIMIT x, y;

x = starting row
y = number of rows to return.

Knowing the total record count and the number of rows to return for each recordset will enable me to implement some sort of progress to the user.

I still have the same question and is probably classed as Database 101 stuff - does the server do the bulk of the processing and only pass back what's requested.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Mar 10, 2007 8:36 pm 
Offline

Joined: Wed Feb 15, 2006 1:30 pm
Posts: 3108
Location: U.S.A (often), Creswell, often Dallas, Atlanta, Spokane, Pago Pago.
Quote:
need to restrict the number of rows returned, lets say 100 and know that there maybe more to retrieve.


Thanks for posting -- this is very helpful.

Michael


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group