Ultralite Performance for loading data in iphone app

[Former Member]

hi i am trying to load about 10K rows for my iphone app. iam getting huge performance problem while retrieving data. i am fetching data column by column by referring sybase tutorials. Like following code i'm trying to load 45 columns of the table.

lrs_resultSet->GetString("acc_alter_code", acc_alter_code, 21);

is this the correct way? please help me out in this. It is taking 5 secs to create an array of 10K.

[former_SQLA_member1694876]

That sounds like a lot of data to load into memory at once on a small device... If you are displaying this data, could you load it on demand as the view scrolls (or whatever) instead? Or perhaps load the remainder as a background operation after populating the view?

[Former Member]

I'm displaying only 20 rows in a tableview at once. but i have sorting functionality in my app, so on sorting i need to sort whole 10K records in array and i need to display. So i need to load all the records. But can you help me how can i write query for loading on demand?

[former_SQLA_member1694876]

Can you let the database do the sorting? It's meant to do that kind of thing 🙂 For performance you'll want to create indexes on the sort columns.

2 ways to load a page at a time: query with 'TOP n START AT m' clause, or keep the cursor open and move the cursor as required. The latter may be faster than TOP/START AT for complex queries. If neither is fast enough, I suppose you'll have to load larger "pages" of rows in the background, say 500 rows at a time. But in any case, if the database is doing the sorting you can get the first page up without delay in the app.

[Former Member]

I have 50 columns in the grid. i can sort on any column in 50 columns. So I'm not sorting in query. i will sort the array using NSSortDescriptors (IOS API).

I'm unware of how to load 500 rows at a time. or keep cursor. I checked cursor facility is not available in UltraLite? Or how can use cursors? Can you help me? Thanks a lot for your reply and your support.

[Former Member]

Do you have any working example project similar to this?

[former_SQLA_member1694876]

The TOP n START AT m syntax is used to load blocks of rows. Please see the documentation for the select statement. http://dcx.sybase.com/index.html#sa160/en/uladmin/ul-select-statement.html*d5e29815

By cursor I mean the ULResultSet object. You can retain the object and call the Next/Previous/Relative methods as required to fetch more rows. (Under the covers the result-set object maintains a database cursor for the query...) http://dcx.sybase.com/index.html#sa160/en/ulc/ulc-ulcpp-ulresultset-cla.html*d5e9921

[former_SQLA_member1694876 · Accepted solution]

Hi Sudha,

There is an optimization you can do. GetString() comes in two flavours: one which takes a column id and one which takes a column name (as in your example). The column name one is convenient, but it means UL must look up the column name for each value you fetch. For code which must run quickly/many times, it's faster to use the column id. You can get the column id by using ULResultSetSchema's GetColumnID() function - query it for each column and reuse the ids for all of your Get calls. Or you can just hard-code the column id (they start at 1, matching your query) since you know what the select statement is.

[Former Member · Accepted solution]

Iam using KVC for loading all the columns of the table. So I have created properties in a class for each column. So for matching the column name and property i have used column name flavour of GetString(). I have tried for Column id's also, I have tried to loop through all the columns and took id's and its name and tried load 10K, it was taking 2 secs more than previous method.

[former_SQLA_member1694876 · Accepted solution]

In order for the optimization to work, you must fetch (and save) the column ids only once for all the rows you are fetching...

[philippefbertrand]

It would be of great help if you posted the schema (including indexes) of the table(s) involved and the SQL query you are using.

Also please post the version and build number of UltraLite you are using.

Can you check how much free memory the phone has with the database installed? Thanks.

[Former Member]

Schema it contains 55 columns, and i'm not created any index, only primary index is creted by default.

Version number: 12.0.1.3152

I'm unaware of how to check free memory in the phone.