SQL Anywhere ultralite - How to do FindFirst on multiple columns

Author

How to do FindFirst on multiple columns

Mili

2005-04-04, 8:03 pm

Hi,

I'm using Appforge 4.0(0799) and Ultralite 9.0.1(1862) .

I need to find the record with column task_id=? and (upca=? or upce=?)
How do I do this using the Ultralite API?

Examples in the documentation only show 1 column

e.g
myTable.FindBegin
myTable.Column("upca").StringValue = txtLookup.Text
If ProductTbl.FindFirst = True Then
do something
End If

Just using sql statement "SELECT upca, description, udf1 from products where
upca = '"& theUCA & "'" takes too long approx. 1 min on a Palm SPT 1700

Help!!!
Thanks
Mili

Paul Fast

2005-04-04, 8:03 pm

I'm not sure I understand your question from the examples you've given, but
here's some information you might find useful.

FindBegin/FindFirst/etc. work on the columns that are in the index used to
open the table. If you opened your table without specifying an index then
the primary key is assumed. If the index is composite (i.e. it's composed
of more than one column), then you can choose to use only the first few
columns of the index to do your search. So, if your index is (task_id,
upca), then you could lookup with:

myTable.FindBegin
myTable.Column("task_id").IntegerValue = tid
If ProductTbl.FindFirst(1) = True Then
do something
End If

The (1) passed to FindFirst says to only use the first column in the index
(task_id). What indexes do you have on the products table? Is there one on
upca? If not, it could be why you're seeing poor performance.
Paul

"Mili" <mili@jmo-systems.com> wrote in message
news:4251520a$1@foru
ms-1-dub...
> Hi,
>
> I'm using Appforge 4.0(0799) and Ultralite 9.0.1(1862) .
>
> I need to find the record with column task_id=? and (upca=? or upce=?)
> How do I do this using the Ultralite API?
>
> Examples in the documentation only show 1 column
>
> e.g
> myTable.FindBegin
> myTable.Column("upca").StringValue = txtLookup.Text
> If ProductTbl.FindFirst = True Then
> do something
> End If
>
> Just using sql statement "SELECT upca, description, udf1 from products
> where
> upca = '"& theUCA & "'" takes too long approx. 1 min on a Palm SPT 1700
>
> Help!!!
> Thanks
> Mili
>
>

Mili

2005-04-05, 8:03 pm

Hi Paul,

Thanks for responding.

My clients will be scanning in product barcodes.
Barcodes can be 12 digit upc-a OR 6 digit upc-e.

Thus in the table I have Task_id, upca(stores the 12 digit barcode) and
upce(stores the 6digit barcode) and other columns such as counted qty and
notes. When user scans in a barcode they could be scanning a 12 digit
barcode OR a 6 digit barcode.
I have to do a lookup on the task_id AND (upca OR upce)

data example

TASK_ID UPCA UPCE COUNTEDQTY NOTES
158 012800472642 128643 12 na
158 039400055006 783003 10 some
data

I have set up primary keys to be a composite of TASK_ID, UPCA used a lookup
like this which works fine.

myTable.FindBegin
myTable.Column("task_id").IntegerValue = CInt(txtTaskID.Text)
myTable.Column("upca").StringValue = txtLookup.Text
If ProductTbl.FindFirst = True Then
display records
End If

However, when I added the UPCE column to the primary key and tried to do
this, it does not work. I get an SQLE_CANNOT_MODIFY error, which is why I'm
posting this question.

myTable.FindBegin
myTable.Column("task_id").IntegerValue = CInt(txtTaskID.Text)
myTable.Column("upca").StringValue = txtLookup.Text
myTable.Column("upce").StringValue = txtLookup.Text

If ProductTbl.FindFirst = True Then
display records
End If

I need to do something like this
myTable.Column("upca").StringValue = txtLookup.Text
OR
myTable.Column("upce").StringValue = txtLookup.Text

I even tried with taking off the UPCE column from the primary key and
indexed it.

I have indexed all the necessay columns but sql statements just take
sooooooo long that now I am forced to use the Ultralite API and I have to
redo my whole application which is costing us time, money and effort.

Thank you.
Mili

"Paul Fast" <NOSPAM_paulf@sybase.com> wrote in message
news:425171d1@forums
-1-dub...
> I'm not sure I understand your question from the examples you've given,
but
> here's some information you might find useful.
>
> FindBegin/FindFirst/etc. work on the columns that are in the index used to
> open the table. If you opened your table without specifying an index then
> the primary key is assumed. If the index is composite (i.e. it's composed
> of more than one column), then you can choose to use only the first few
> columns of the index to do your search. So, if your index is (task_id,
> upca), then you could lookup with:
>
> myTable.FindBegin
> myTable.Column("task_id").IntegerValue = tid
> If ProductTbl.FindFirst(1) = True Then
> do something
> End If
>
> The (1) passed to FindFirst says to only use the first column in the index
> (task_id). What indexes do you have on the products table? Is there one
on
> upca? If not, it could be why you're seeing poor performance.
> Paul
>
> "Mili" <mili@jmo-systems.com> wrote in message
> news:4251520a$1@foru
ms-1-dub...
>
>

Paul Fast

2005-04-09, 8:24 pm

I'd like to make a couple points:
1. SQL should not be fundamentally slower than the Table API. Under the
covers, SQL will be parsed and a plan will be created for a statement that
fundamentally gets data from tables. The table API goes directly to those
tables, leaving you to implement the "plan" part in your code. You're
really only saving on the parsing which should not be a large percentage of
time. The table API can be faster if your application has some knowledge
about the data that UltraLite's optimizer does not.
2. Your application can use both the table API and SQL. If there's just
one query for which you'd like to implement the plan yourself in your code,
you don't need to change the rest of your application to use the Table API
too.

For coming up with a table-based algorithm, do you know anything about your
data? Are there likely to be a lot of rows with the same task_id? Like 5?
50? 100? Or, are there likely to be a lot of rows with the same UPCA or
UPCE? You could just do an index lookup on one and then a linear search for
the others. So:
*** uncompiled/untested code & logic follows ... ***
Given this schema:
Table: Tasks
Primary Key: task_id, UPCA
-----
Dim t1 As ULTable
Dim cUpca As ULColumn
Dim cUpce As ULColumn

Set t1 = Conn.GetTable( "Tasks" )

t1.Open

t1.FindBegin
t1.Column("task_id").IntegerValue = CInt(txtTaskID.Text)
' It is more efficient to do this outside the loop ...
Set cUpca = t1.Column("UPCA")
Set cUpce = t1.Column("UPCE")
While t1.FindFirst(1) = True Then
' Found a matching task ID, now look for UPCA or UPCE
If cUpca.StringValue = txtLookup.Text Then
' Do whatever with the row and exit the loop
Else
If cUpce.StringValue = txtLookup.Text Then
' Do whatever with the row and exit the loop
End If
End If
Wend

If you think in general you'll have fewer duplicate UPCA or UPCE values, you
could create an index on each of these columns and do a Find on them and if
you find a match, do a linear seach until you find a matching task_id.

One final idea: could you take the length of txtLookup.Text and if it's 12,
do a Lookup on (task_id,upca) and if it's 6, do a lookup on (task_id, upce)?
There's no point in executing a query involving column upca if your data
isn't 12 digits.

Hope that helps.
Paul

"Mili" <mili@jmo-systems.com> wrote in message news:42528955@forums
-1-dub...
> Hi Paul,
>
> Thanks for responding.
>
> My clients will be scanning in product barcodes.
> Barcodes can be 12 digit upc-a OR 6 digit upc-e.
>
> Thus in the table I have Task_id, upca(stores the 12 digit barcode) and
> upce(stores the 6digit barcode) and other columns such as counted qty and
> notes. When user scans in a barcode they could be scanning a 12 digit
> barcode OR a 6 digit barcode.
> I have to do a lookup on the task_id AND (upca OR upce)
>
> data example
>
> TASK_ID UPCA UPCE COUNTEDQTY NOTES
> 158 012800472642 128643 12 na
> 158 039400055006 783003 10
> some
> data
>
> I have set up primary keys to be a composite of TASK_ID, UPCA used a
> lookup
> like this which works fine.
>
> myTable.FindBegin
> myTable.Column("task_id").IntegerValue = CInt(txtTaskID.Text)
> myTable.Column("upca").StringValue = txtLookup.Text
> If ProductTbl.FindFirst = True Then
> display records
> End If
>
> However, when I added the UPCE column to the primary key and tried to do
> this, it does not work. I get an SQLE_CANNOT_MODIFY error, which is why
> I'm
> posting this question.
>
> myTable.FindBegin
> myTable.Column("task_id").IntegerValue = CInt(txtTaskID.Text)
> myTable.Column("upca").StringValue = txtLookup.Text
> myTable.Column("upce").StringValue = txtLookup.Text
>
> If ProductTbl.FindFirst = True Then
> display records
> End If
>
> I need to do something like this
> myTable.Column("upca").StringValue = txtLookup.Text
> OR
> myTable.Column("upce").StringValue = txtLookup.Text
>
> I even tried with taking off the UPCE column from the primary key and
> indexed it.
>
> I have indexed all the necessay columns but sql statements just take
> sooooooo long that now I am forced to use the Ultralite API and I have to
> redo my whole application which is costing us time, money and effort.
>
> Thank you.
> Mili
>
> "Paul Fast" <NOSPAM_paulf@sybase.com> wrote in message
> news:425171d1@forums
-1-dub...
> but
> on
>
>

Paul Fast

2005-04-19, 8:23 pm

What is the plan UL's using for the query you have below? You can find this
by doing the following:

' WARNING: untested code ....
Dim p As ULPreparedStatement
Dim the_plan As String
Set p = Conn.PrepareStatement( "SELECT upcid, description, udf1 FROM
products where upcid = ?")
the_plan = p.Plan

Perhaps you could post the plan. There's no way there should be that much
of a difference between getting the results in SQL and using the table API.

Paul

"Mili" <mili@jmo-systems.com> wrote in message
news:425ebf4e$1@foru
ms-2-dub...
> Hi Paul,
>
> I did a simple test application while waiting for a reply or help
> just to compare the time it takes the SQL query and the time it takes the
> API to lookup on one column i.e. the primary key.
> Table has these columns with 11000 rows
> UPCID char(20) PK
> description char(50)
> udf1 char(15)
>
> In the application I put in the upcid and click a button that does the
> lookup.
> I display the lookup Start time and End time on labels just before I do
> the
> lookup and after it finds the record.
>
> SELECT upcid, description, udf1 from products where upcid =?
>
> Result -> SQL takes approx. 1 min. API takes a second or so.
>
> Thus,
>
> Regarding point 1: SQL should not be fundamentally slower than the Table
> API.
> Perhaps you and your colleagues can investigate this and provide an
> explanation.
>
> Regarding point 2. Your application can use both the table API and SQL.
> If
> there's just
> code,
>
> The bulk of my application is looking for a particular record and updating
> it in this one table.
> If the SQL query is slow in performance I have no choice but try to use
> the
> API which means modifying most of my app.
>
>
> Yes, approximate 3000 records in the table with 100 or more same task_ids
> and each of these task can have the same upca.
> Data depends on the way my clients do their product setup and product
> profile selection.
>
> One final idea: could you take the length of txtLookup.Text and if it's
> 12,
> upce)?
>
> Yes, I could do this, but the thing is the SQL query is slow and even if I
> did just lookup on one column as I did in my test application
> it does not help at all.
>
> Despite all this, I have already changed my application and table
> structure
> and performance is better.
>
> Thank you.
> Mili
>
>
> "Paul Fast" <NOSPAM_paulf@sybase.com> wrote in message
> news:4256b7f0$1@foru
ms-1-dub...
> of
> code,
> your
> 5?
> for
> you
> if
> 12,
> upce)?
> news:42528955@forums
-1-dub...
> and
> to
> one
> upce=?)
> products
> 1700
>
>

Sponsored Links