Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Sep 19, 2018 12:01 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 21 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Indexes in RealSQLdatabase
PostPosted: Sat Oct 08, 2005 10:45 am 
Offline

Joined: Sat Oct 08, 2005 10:20 am
Posts: 8
My app were working perfectly with the the realdatabase in RB 5.5.5
I have made the migration to RealSQLdatabase on RB 2005.
The first impression was a slowness without indexes.
The new database editor is not easy for the management of indexes. I can't edit them. The message is "Unable to retrieve data".
Is it normal?
Other problem. It's impossible to modify or drop a column. I fond the reason on SQLite site. Realbasic documentation is very insufficient for a good migration!


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Oct 08, 2005 2:54 pm 
Offline

Joined: Sat Oct 08, 2005 2:15 pm
Posts: 75
>The first impression was a slowness without indexes.
Be sure to use ‘Begin Transaction’ and ‘End Transaction’.

Like anything else new, I think you have to spend a little time messing around with it to get the fill.
SQLite is very fast and will scale big.

There is a decent little manager and browser at:
http://sqlitebrowser.sourceforge.net/

It allows you to drop columns - it’s free.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sun Oct 09, 2005 1:46 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
I thought the browser only did Primary Indices not true indices but I could be wrong, it's been a month since I've opened 2005, why don't you just code them, it's rather simple code.

dbFile.SQLExecute("Create Index Index_Name ON Table_Name(Column_Name, Column_Name ... )")


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 10, 2005 1:34 am 
Offline

Joined: Sat Oct 08, 2005 10:20 am
Posts: 8
What is the good place for "begin transaction"? How do you do that in RB? Is it useful before simple sqlselect, or only with insert and update?
Is it true that "End transaction" is implicit with commit? Thanks!


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 10, 2005 10:50 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
http://www.sqlite.org will answer all your questions. however when you Begin transaction you are saying, in lay terms, I'm going to use the Database engine now, and when you end transaction you say ok I'm done for now. I use it everytime I want to access the database, before the Select statement, the end just when I'm done with the engine. It is not equivilent to a Commit statement. You can still roll back after an End Transaction, but you can't after a commit transaction

again it's all explained on the sqlite website
http://www.sqlite.org/lang_transaction.html


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 12, 2005 9:40 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
cognitron wrote:
What is the good place for "begin transaction"?


Any time you are going to do a lot of INSERTs or UPDATEs, basically, because transactions make database updates about 10 times faster in SQLite. Be aware that the REAL SQL Database does implicit transactions. That means that a new transaction is started for you if you don't start it explicitly.

cognitron wrote:
How do you do that in RB?


db.SQLExecute "BEGIN TRANSACTION"

cognitron wrote:
Is it useful before simple sqlselect, or only with insert and update?


It isn't so useful before SELECTs. It won't speed anything up.

cognitron wrote:
Is it true that "End transaction" is implicit with commit? Thanks!


Yes.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 12, 2005 9:41 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
Jym wrote:
I thought the browser only did Primary Indices


I believe you can create any index you want by clicking the Index button.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 12, 2005 2:11 pm 
Offline

Joined: Sat Oct 08, 2005 10:20 am
Posts: 8
Thank you for your help!
SQLite browser is a little wonder, that RB should buy!
With begin and end transaction the speed is perfect for me!


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 12, 2005 3:24 pm 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
Except that the REAL SQL Database does transactions for you, so you shouldn't have to do the BEGIN TRANSACTION part of things. You do need to commit, however.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 13, 2005 4:21 am 
Offline

Joined: Fri Sep 30, 2005 8:28 am
Posts: 34
Location: Dunfermline, UK
Will Leshner wrote:
Except that the REAL SQL Database does transactions for you, so you shouldn't have to do the BEGIN TRANSACTION part of things. You do need to commit, however.


That's not quite the full picture.

By default SQLite (and most other reasonable databases) will automatically wrap a transaction around each sql statement that updates the database, whether that be an insert, update, delete or whatever.

That means if you don't specifically start a transaction, every insert/update/delete will incur a bit of overhead for the begin and commit.

If however you control the transaction by beginning it yourself, it'll stay open until you decide to commit or roll it back, and if you fail to do so, the updates will be rolled back (say, if you lost connection).

So if you need more than one update to occur, and particularly if you'd like them to be "do all, or do none" you should begin and commit your own transactions. It'll incur much less overhead and therefore be quite a bit quicker too (as evidenced by a previous post in this topic).

Sorry for being pedantic, just thought it warranted a little clarification.

_________________
Ian M. Jones
IMiJ Ltd
http://www.imij.co.uk
http://www.ianmjones.com
http://twitter.com/ianmjones


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 13, 2005 1:12 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Well, I have to disagree with a lot of what you have to say because you are making it sound like End Transaction and commit are the same thing. I agree controlling the transactions yourself gets rid of the overhead of the Begin Transaction and End Transaction for each change you make, but commit is nothing more than a transaction (or many transactions) and should be inside of a Begin ... End transaction so if you are committing numerous changes to a database file you don't get the aformentioned overhead on each change.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 13, 2005 1:42 pm 
Offline

Joined: Fri Sep 30, 2005 8:28 am
Posts: 34
Location: Dunfermline, UK
Jym,

I'm sorry if I got it wrong and you know more about SQLite than I do (not hard, haven't used it yet), but I was talking in general terms with regards transactions.

But it seems to me that "end transaction" and "commit transaction" are the same thing in SQLite from reading http://www.sqlite.org/lang_transaction.html. Especially as nested transactions are not supported.

Could you explain more as to how "end transaction" and "commit transaction" differ in SQLite?

_________________
Ian M. Jones
IMiJ Ltd
http://www.imij.co.uk
http://www.ianmjones.com
http://twitter.com/ianmjones


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 13, 2005 2:08 pm 
Offline

Joined: Sat Oct 08, 2005 10:20 am
Posts: 8
It's easy to prove the effectiveness of "begin transaction". I have a large procedure with a large usage or sqlselect
Without transaction : 14 sec
With a single "begin transaction" : 6 sec


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 13, 2005 2:28 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Hmmm I'm reading that page now, and it looks like the End Transaction and Commit Transaction have become the same thing in v 3 (+), and I can't verify it, because of computer issues I'm having right now ... long story.

If it is like it was in v1 (+), and v 2 (+), then an end transaction didn't commit the database file only the 'memory' database, until a commit was run, then it committed the actual database file so a rollback was possible after an End Transaction, because the file itself didn't get changed until a Commit transaction was made.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 20, 2005 9:45 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
imij wrote:
By default SQLite (and most other reasonable databases) will automatically wrap a transaction around each sql statement that updates the database, whether that be an insert, update, delete or whatever.


That is the default for SQLite, but not for the REAL SQL Database. The REAL SQL Database detects that you are not in a transaction and opens one for you whenever you do something that will write to the database. So if you don't explicitly do a "BEGIN" before a bunch of INSERTs, the REAL SQL Database issues the BEGIN for you.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 21 posts ]  Go to page 1, 2  Next

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