SQL Anywhere ultralite - Select statement efficiency 9.0.2.3193

Author

Select statement efficiency 9.0.2.3193

rr12

2005-10-27, 7:41 am

I am using UL.NET and VB.NET.

I have a DataGrid whose DataSource is set to a DataTable. I then use a
ULDataAdapter to populate the DataTable using the Fill method.

For this grid, I have a set of fixed filters that I want to be able to
apply. Currently, I have a ComboBox which contains the filters. For each
item, I have a corresponding SQL select statement stored in a ULCommand
array. When the user wants to filter, I call Prepare on that ULCommand and
then set the SelectCommand equal to that ULCommand. I then call Fill and I
get the correct data.

The code looks like this...

' For adding items to the combo box
Private Sub AddSQLComboItem(ByVa
l sComboText As String, ByVal sSQL As
String)
Dim iIdx As Integer
iIdx = cboFilter.Items.Add(sComboText)
ReDim Preserve CurVals.ulcmdFilter(iIdx)
CurVals.ulcmdFilter(iIdx) = New ULCommand(sSQL, ulc)
End Sub

' When user wants to filter
Private Sub LoadPatData()
CurVals.tblPats.Rows.Clear()
CurVals. ulcmdFilter(cboFilte
r.SelectedIndex).Prepare()
CurVals.ulaPats.SelectCommand = CurVals. ulcmdFilter(cboFilte
r.SelectedIndex)
CurVals.ulaPats.Fill(CurVals.tblPats)
End Sub

Is this the best way to accomplish this?

rr12

Philippe Bertrand

2005-10-27, 7:41 am

It depends on the size of your result set and the number or ULCommand
objects you are keeping...

For small result sets, it is probably ok. You don't need to prepare the
ULCommand, the first time you execute, it will be prepared. One issue is
that a prepared ULCommand may allocate temp tables, etc and if you have too
many you may get an error.

For large result sets, see the Large DataGrid sample on codeXchange.

For large number of filters, keep an array of SQL statements and use only
one ULCommand.

Make sure you close or dispose of the result set when done with it (don't
rely on garbage collection) to free resources as soon as possible.

--
- Philippe Bertrand
iAnywhere Solutions, Inc.

Please include in your subject line what UltraLite API are you using
(I.E. UltraLite.NET, Native UltraLite for Java, Embedded SQL, C++,
(pure) Static Java UltraLite, etc.). Please include version and BUILD
numbers in your post.

rr12

2005-10-27, 7:41 am

Ok. Thanks. What would you consider "large" when you say "large result
sets?"

"Philippe Bertrand" <my.name@ianywhere.com> wrote in message
news:434d5368$1@foru
ms-1-dub...
> It depends on the size of your result set and the number or ULCommand
> objects you are keeping...
>
> For small result sets, it is probably ok. You don't need to prepare the
> ULCommand, the first time you execute, it will be prepared. One issue is
> that a prepared ULCommand may allocate temp tables, etc and if you have
> too many you may get an error.
>
> For large result sets, see the Large DataGrid sample on codeXchange.
>
> For large number of filters, keep an array of SQL statements and use only
> one ULCommand.
>
> Make sure you close or dispose of the result set when done with it (don't
> rely on garbage collection) to free resources as soon as possible.
>
> --
> - Philippe Bertrand
> iAnywhere Solutions, Inc.
>
> Please include in your subject line what UltraLite API are you using
> (I.E. UltraLite.NET, Native UltraLite for Java, Embedded SQL, C++,
> (pure) Static Java UltraLite, etc.). Please include version and BUILD
> numbers in your post.
>
>

Philippe Bertrand

2005-10-27, 7:41 am

Thousands of rows? The issue is the DataAdapter.Fill() makes a copy of
*all* the rows but UltraLite is a local database so it can fetch rows as you
need them without interactive delay.

The gain in using the Large DataGrid example is in the reduction of memory
usage.

Philippe

"rr12" <abc@123.com> wrote in message news:434d72be$1@foru
ms-2-dub...
> Ok. Thanks. What would you consider "large" when you say "large result
> sets?"
>
> "Philippe Bertrand" <my.name@ianywhere.com> wrote in message
> news:434d5368$1@foru
ms-1-dub...
>
>

Sponsored Links