SQL Anywhere ultralite - ASA vs. Ultralite performance

Author

ASA vs. Ultralite performance

Timothy Dean

2005-05-24, 8:23 pm

Ultralite C++ Component api, 9.0.2.3044

I am using an Ultralite database table with about 2000 records in it and
browsing it with a list control with the LVS_OWNERDATA style. I am using
dynamic SQL. The query executes fast, but pulling data from the result set
to fill my list is extremely slow. How does the performance of ASA on a
pocket pc compare to Ultralite in this case? Is there anything more I can
do in Ultralite to improve the performance?

Here is my select:

SELECT
Assets.assetID,Assets. assetIDBarcode,Asset
s. panelDescription,XXX
ets.columnLe
tterNumber FROM Assets LEFT JOIN Inspections ON
Inspections.assetID=Assets.assetID WHERE Assets.unitID=? AND
Assets.floorNumber=? AND Assets.inspectionTypeID=? AND Inspections.assetID
IS NULL

Thanks.

Timothy Dean

Greg Fenton

2005-05-25, 3:23 am

Timothy Dean wrote:
> The query executes fast, but pulling data from the result set
> to fill my list is extremely slow.

How do you mean "the query executes fast"? What operation are you
performing to know that the result set is finished being built?

Have you considered writing a simple app that fills a list with 2000
hard-coded values to see how "fast" a handheld is at rendering such a
beast? 2000 items in any kind of UI widget on a handheld is likely to
be very slow.

Instead of returning the entire result set, why not simply grab the
first bunch of rows (10? 20? 100?) using TOP(N) and when the user
performs a "scroll down" action, fetch the next batch?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Timothy Dean

2005-05-25, 11:23 am

I am doing the following to execute the statement:

pConnection-> PrepareStatement(ulV
alue);
m_pPreparedStatement
-> SetParameter(nParame
terNumber,parameterV
alue);
m_pPreparedStatement
-> SetParameter(nParame
terNumber,parameterV
alue);
m_pPreparedStatement
-> SetParameter(nParame
terNumber,parameterV
alue);
m_pResultSet=m_pPrep
aredStatement->ExecuteQuery();

To retrieve specific fields from the database, I first move to the
appropriate record using:

m_pResultSet-> Relative(nRelativeDi
stance);

Then pull out the values using something like this:

m_pResultSet->GetSchema();
ulValue. SetString(strFieldNa
me.getCString());
ul_column_num columnID=pResultSetS
chema-> GetColumnID(ulValue)
;
*pulvFieldValue=m_pR
esultSet->Get(columnID);

When I step through the code, executing the statement seems to take a
reasonable amount of time. But as I move to a record and retrieve a value,
it gets progresively slower as I move to the middle of the result set. Keep
in mind that I am only retrieving 10 records or so at a time (from a result
set with 2000 records) for my list box since it is using a "virtual list" or
LVS_OWNERDATA style.

Will increasing the cache size help? I will try using TOP(N) to see if that
helps. I still need to get a total count in order to set the count of the
list box. Thanks.

Timothy Dean

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4293e2bb$1@foru
ms-1-dub...
> Timothy Dean wrote:
>
> How do you mean "the query executes fast"? What operation are you
> performing to know that the result set is finished being built?
>
> Have you considered writing a simple app that fills a list with 2000
> hard-coded values to see how "fast" a handheld is at rendering such a
> beast? 2000 items in any kind of UI widget on a handheld is likely to
> be very slow.
>
> Instead of returning the entire result set, why not simply grab the
> first bunch of rows (10? 20? 100?) using TOP(N) and when the user
> performs a "scroll down" action, fetch the next batch?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-05-25, 1:23 pm

Timothy Dean wrote:
>
> Will increasing the cache size help? I will try using TOP(N) to see if that
> helps. I still need to get a total count in order to set the count of the
> list box. Thanks.

There is no "cache" for UltraLite.

Again, I think you should try just adding 2000 hard-coded strings to the
device to see how well it performs. The problem may have *nothing* to
do with UL. It could be that the OS is not designed to have that many
UI objects. No point in trying to make UL "faster" if the handheld
cannot handle even that much static data.

I question as to whether having a drop down with 2000 items is even
usable on a handheld. Mightn't there be a better way to provide a UI to
this data?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Michael Thode

2005-05-25, 8:23 pm

Ultralite does have a cache. Its set by the cache_size connection
parameter. Increasing the cache size may help, it depends on if the cache
is the bottleneck or not. So try different cache sizes and see if they
help. You may also want to call GetPlan() to see how your query is being
executed. You may want to add an index to your table.

Is it possible to change your gui code so it doesn't need to know the row
count beforehand? Can you change the count of the list box dynamically?

Mike

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4294bc39$1@foru
ms-1-dub...
> Timothy Dean wrote:
that[color=darkred]
the[color=darkred]
>
> There is no "cache" for UltraLite.
>
> Again, I think you should try just adding 2000 hard-coded strings to the
> device to see how well it performs. The problem may have *nothing* to
> do with UL. It could be that the OS is not designed to have that many
> UI objects. No point in trying to make UL "faster" if the handheld
> cannot handle even that much static data.
>
> I question as to whether having a drop down with 2000 items is even
> usable on a handheld. Mightn't there be a better way to provide a UI to
> this data?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Timothy Dean

2005-05-25, 8:23 pm

I agree that statically adding 2000 items to a list control wouldn't work
very well. Thats why I am using a list control with the LVS_OWNERDATA style
("virtual list"). Windows will only paint the items that are currently
visible. It sends me the LVN_GETDISPINFO message for each item it needs to
paint and I supply it with the text. So each time I recieve this message, I
move to the appropriate record in the result set, pull the text from the
appropriate field, place the text into a structure that Windows hands me,
and Windows takes it from there to actually display it in the list.

My question really has nothing to do with the list control, but how to
improve the performance of extracting the data from records / fields in the
result set. The issue would be the same if I had a screen with some edit
controls, and I was navigating through a result set that contained 2000
records. If I hit the "next" button 10 times, it would take about the same
time as displaying 10 records in the list control. The size of the result
set doesn't seem to have as much effect as the number of records in the
table. If I select 200 records out of the same table that contains 2000,
the performance is similar to selecting all 2000. That indicates to me that
the performance hit really happens when you retrieve the data (with a Get)
as opposed to executing the query. I imagine the result set is not being
completly pulled into memory, but "paged". Any advice on to how to optimize
this? Would ASA be any faster? Thanks.

Timothy Dean

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4294bc39$1@foru
ms-1-dub...
> Timothy Dean wrote:
that[color=darkred]
the[color=darkred]
>
> There is no "cache" for UltraLite.
>
> Again, I think you should try just adding 2000 hard-coded strings to the
> device to see how well it performs. The problem may have *nothing* to
> do with UL. It could be that the OS is not designed to have that many
> UI objects. No point in trying to make UL "faster" if the handheld
> cannot handle even that much static data.
>
> I question as to whether having a drop down with 2000 items is even
> usable on a handheld. Mightn't there be a better way to provide a UI to
> this data?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

biela

2005-05-25, 8:23 pm

Generally UltraLite have better performance then ASA on pocket pc =
because UltraLite is for mobile devices like PDA, and ASA is for =
notebooks rather.

U=BFytkownik "Timothy Dean" <tim. dean@mobiledataforce
.com> napisa=B3 w =
wiadomo=B6ci news:4293c42d$1@foru
ms-1-dub...
> Ultralite C++ Component api, 9.0.2.3044
>=20
> I am using an Ultralite database table with about 2000 records in it =
and
> browsing it with a list control with the LVS_OWNERDATA style. I am =
using

> dynamic SQL. The query executes fast, but pulling data from the =
result set
> to fill my list is extremely slow. How does the performance of ASA on =
a
> pocket pc compare to Ultralite in this case? Is there anything more I =
can
> do in Ultralite to improve the performance?
>=20
> Here is my select:
>=20
> SELECT
> =
Assets.assetID,Assets. assetIDBarcode,Asset
s. panelDescription,XXX
ets.colum=
nLe
> tterNumber FROM Assets LEFT JOIN Inspections ON
> Inspections.assetID=3DAssets.assetID WHERE Assets.unitID=3D? AND
> Assets.floorNumber=3D? AND Assets.inspectionTypeID=3D? AND =
Inspections. assetID
> IS NULL
>=20
> Thanks.
>=20
> Timothy Dean
>=20
>

Michael Thode

2005-05-25, 8:23 pm

The only way to know if UL or ASA is faster is to try it. ASA has far more
sophisticated optimizations and does much better on complex queries.
However on simple queries Ultralite is faster due to its simplicity.

Mike

"Timothy Dean" <tim. dean@mobiledataforce
.com> wrote in message
news:4294d3e6$1@foru
ms-2-dub...
> I agree that statically adding 2000 items to a list control wouldn't work
> very well. Thats why I am using a list control with the LVS_OWNERDATA
style

> ("virtual list"). Windows will only paint the items that are currently
> visible. It sends me the LVN_GETDISPINFO message for each item it needs
to
> paint and I supply it with the text. So each time I recieve this message,
I
> move to the appropriate record in the result set, pull the text from the
> appropriate field, place the text into a structure that Windows hands me,
> and Windows takes it from there to actually display it in the list.
>
> My question really has nothing to do with the list control, but how to
> improve the performance of extracting the data from records / fields in
the
> result set. The issue would be the same if I had a screen with some edit
> controls, and I was navigating through a result set that contained 2000
> records. If I hit the "next" button 10 times, it would take about the
same
> time as displaying 10 records in the list control. The size of the result
> set doesn't seem to have as much effect as the number of records in the
> table. If I select 200 records out of the same table that contains 2000,
> the performance is similar to selecting all 2000. That indicates to me
that
> the performance hit really happens when you retrieve the data (with a Get)
> as opposed to executing the query. I imagine the result set is not being
> completly pulled into memory, but "paged". Any advice on to how to
optimize
> this? Would ASA be any faster? Thanks.
>
> Timothy Dean
>
>
> "Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
> news:4294bc39$1@foru
ms-1-dub...
if[color=darkred]
> that
> the
>
>

Tim McClements

2005-05-25, 8:23 pm

I gather you are saving your current cursor position and doing relative
fetches in response to the listview getdispinfo messages (as opposed to
always doing absolute fetches), right? You may find it interesting to log
the getdispinfo messages -- in the past I've found that they jump around a
bit, which means lots of extra moves for the UL cursor. Depending on what
you see from the getdispinfo messages, you may find doing a bit of your own
caching of rows very greatly improves performance of the UI.

You could also make the UL Get call sequence faster by caching the column
ids instead of getting them again on each fetch.

- Tim

"Timothy Dean" <tim. dean@mobiledataforce
.com> wrote in message
news:4294d3e6$1@foru
ms-2-dub...
>I agree that statically adding 2000 items to a list control wouldn't work
> very well. Thats why I am using a list control with the LVS_OWNERDATA
> style
> ("virtual list"). Windows will only paint the items that are currently
> visible. It sends me the LVN_GETDISPINFO message for each item it needs
> to
> paint and I supply it with the text. So each time I recieve this message,
> I
> move to the appropriate record in the result set, pull the text from the
> appropriate field, place the text into a structure that Windows hands me,
> and Windows takes it from there to actually display it in the list.
>
> My question really has nothing to do with the list control, but how to
> improve the performance of extracting the data from records / fields in
> the
> result set. The issue would be the same if I had a screen with some edit
> controls, and I was navigating through a result set that contained 2000
> records. If I hit the "next" button 10 times, it would take about the
> same
> time as displaying 10 records in the list control. The size of the result
> set doesn't seem to have as much effect as the number of records in the
> table. If I select 200 records out of the same table that contains 2000,
> the performance is similar to selecting all 2000. That indicates to me
> that
> the performance hit really happens when you retrieve the data (with a Get)
> as opposed to executing the query. I imagine the result set is not being
> completly pulled into memory, but "paged". Any advice on to how to
> optimize
> this? Would ASA be any faster? Thanks.
>
> Timothy Dean

Timothy Dean

2005-05-25, 8:23 pm

Yes, I am saving the current cursor position on each move so I can move
relative. Would moving absolute be any faster?

I changed my function calls to "get" based on columnID directly instead of
by name, similar to what you were saying about caching the columnIDs. That
made a big difference! Since I was previously retrieving by column name
each time, I had to first get the result set schema, and then get the
columnID, and then get the value. Retrieval is now much faster which allows
the list control operate properly. Thanks.

Timothy Dean

"Tim McClements" < mcclemenXnospam@syba
se.com> wrote in message
news:4294f921$1@foru
ms-1-dub...
> I gather you are saving your current cursor position and doing relative
> fetches in response to the listview getdispinfo messages (as opposed to
> always doing absolute fetches), right? You may find it interesting to log
> the getdispinfo messages -- in the past I've found that they jump around a
> bit, which means lots of extra moves for the UL cursor. Depending on what
> you see from the getdispinfo messages, you may find doing a bit of your
own
> caching of rows very greatly improves performance of the UI.
>
> You could also make the UL Get call sequence faster by caching the column
> ids instead of getting them again on each fetch.
>
> - Tim
>
> "Timothy Dean" <tim. dean@mobiledataforce
.com> wrote in message
> news:4294d3e6$1@foru
ms-2-dub...
message,[color=darkr
ed]
me,[color=darkred]
edit[color=darkred]
result[color=darkred
]
2000,[color=darkred]

Get)[color=darkred]
being[color=darkred]

>
>

Michael Thode

2005-05-26, 11:23 am

Could you quantify the performance improvement you saw? How many times
faster was it? Information like this is extremely valuable to us as it help
us decide where to spend time optimizing the code. How many columns total
where in the tables being referenced?

Thanks

- Mike

"Timothy Dean" <tim. dean@mobiledataforce
.com> wrote in message
news:4295193a$1@foru
ms-2-dub...
> Yes, I am saving the current cursor position on each move so I can move
> relative. Would moving absolute be any faster?
>
> I changed my function calls to "get" based on columnID directly instead of
> by name, similar to what you were saying about caching the columnIDs.
That
> made a big difference! Since I was previously retrieving by column name
> each time, I had to first get the result set schema, and then get the
> columnID, and then get the value. Retrieval is now much faster which
allows
> the list control operate properly. Thanks.
>
> Timothy Dean
>
> "Tim McClements" < mcclemenXnospam@syba
se.com> wrote in message
> news:4294f921$1@foru
ms-1-dub...
log[color=darkred]
a[color=darkred]
what[color=darkred]
> own
column[color=darkred
]
work[color=darkred]
currently[color=dark
red]
needs[color=darkred]

> message,
the[color=darkred]
> me,
in[color=darkred]
> edit
2000[color=darkred]
> result
the[color=darkred]
> 2000,
me[color=darkred]
> Get)
> being
>
>

Timothy Dean

2005-05-26, 8:23 pm

I have a list control with 3 columns in it displaying 10 records at a time.
It originally took about 5 seconds to retrieve the data (10 records, 3
fields each from a table with 2000 total records) and paint the control. It
now takes less than 1 second. Dragging the scroll bar wasn't even possible
before. It is now (almost) as fast as actually adding items to the control
directly. Another thing I didn't mention is that I was doing a
GetRecordCount each time as well because it would crash if there wasn't a
result set with a valid schema. I was also able to remove that when
accessing the columns directly by columnID.

Timothy Dean

"Michael Thode" < mthode_no_spam@sybas
e.com> wrote in message
news:4295ec6b$1@foru
ms-2-dub...
> Could you quantify the performance improvement you saw? How many times
> faster was it? Information like this is extremely valuable to us as it
help
> us decide where to spend time optimizing the code. How many columns total
> where in the tables being referenced?
>
> Thanks
>
> - Mike
>
> "Timothy Dean" <tim. dean@mobiledataforce
.com> wrote in message
> news:4295193a$1@foru
ms-2-dub...
of[color=darkred]
> That
> allows
relative[color=darkr
ed]
to[color=darkred]
> log
around[color=darkred
]
> a
> what
your[color=darkred]
> column
> work
LVS_OWNERDATA[color=
darkred]
> currently
> needs
> the
hands[color=darkred]

to[color=darkred]
> in
> 2000
the[color=darkred]
> the
> me
a[color=darkred]
>
>

Sponsored Links