On 05/11/2011 03:38 PM, Werner F. Bruhin wrote:

> I am trying out Ubuntu and not that FB is a lot slower on it compared to
> Win 7. I am running this on the same hardware by dual booting and
> actually running FB 2.5 in a Win 7 VM under Ubuntu is just about as fast
> as in native Win 7.
>
> I am pretty sure it has to do with my setup on Ubuntu - I am very NOOB
> on this.
>
> I installed FB 2.5-super using Synaptic and didn't tune anything.
>
> I am using Python/wxPython/sqlalchemy and the slow performance I see
> when I do bulk stuff, i.e. reading from a table check in another table
> if row exists and then either insert or update.
>
> I am aware that this is a very little information, but for the moment I
> am just looking for tips on what one should look at first before I dig
> in further down into the issue.
>
> Werner
>
> BTW, the db is just copied over from Windows - is this a no no? I.e.
> should I have to backup/restore instead?

Here some more information:

I do e.g. this:
start = time.time()

utils.CreateSingleLangTableSA(engine,
'sub_region_lm',
'sub_region_ls',
1,
'subregionid')

end = time.time()

On Ubuntu it takes: 95.6 sec
On Windows 7 VM as guest on Ubunto 10.10 it takes: 12.1 sec
On Windows 7 native on same machine (Intel i5 with 4 GB) it takes: 18.6 sec

The native setup has a few other things running which are probably the
cause for the slower time.

"CreateSingleLangTableSA" is doing something along these lines:
- select itemkey from sub_region_lm where fk_langid = 1 # english rows
- select all_columns from sub_region_lm where fk_langid = 2 # german rows
- if german row is not found do:
update or insert into ..... sub_region_ls # using english row data
else
update or insert into ..... sub_region_ls # using german row data

I checked that the "select" statements above used the index by doing one
manually using flamerobin.

All of this is running on a Sony notebook with a SATA internal disk
setup for dual boot.

I also checked the information on firebird.conf but nothing jumps at me
which might help - but that might not mean much:-[

Werner



Following is the actual Python code if that is of use (Python 2.6 and
SQLAlchemy 0.6 on all three setups)

def CreateSingleLangTableSA(engine, MultiLang, SingleLang, NewLangId,
ItemKey):
"""Create/Refresh single language table with language records for
NewLangId.
Using SA engine directly
MultiLang = table containing all languages
SingleLang = table containing single language (language used by user)
NewLangId = primary key of new language to use
ItemKey = string containing field name for primary key
"""
# get all english records for multi lang table
selML = 'select %s from %s where fk_langid=%i' % (ItemKey,
MultiLang, 1)
conn = engine.connect()

dbItemsMultiLang = conn.execute(selML)
fields, fieldCount = GetDbColNames(engine, MultiLang)

qMarks = '?,'
for x in range(fieldCount):
qMarks += '?,'

qMarks = qMarks.rstrip(',')

selMLdata = "select %s from %s" % (fields, MultiLang)
updSLdata = "update or insert into %s (%s) values (%s)" %
(SingleLang, fields, qMarks)

for item in dbItemsMultiLang:
where = ' where %s=%i and %s=%i' % (ItemKey, item[0],
'fk_langid', NewLangId)
dbItemNewLang = conn.execute(selMLdata + where).fetchone()

if dbItemNewLang == None:
# put english into single lang table
where = ' where %s=%i and %s=%i' % (ItemKey, item[0],
'fk_langid', 1)
dbItemEnglish = conn.execute(selMLdata + where).fetchone()
conn.execute(updSLdata, dbItemEnglish.values())

else:
# put NewLangID info into single lang table
conn.execute(updSLdata, dbItemNewLang.values())

conn.close()