SQLite Questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Webbiz - 09 Jul 2009 03:50 GMT

Just so I get these things clear in my mind.

Q. Can you get data out of a TABLE without having to create a
recordset?

Q. What if you just wanted to access a single cell in the Table?

Thanks.

Webbiz

Reply to this Message

Schmidt - 09 Jul 2009 23:39 GMT

> Just so I get these things clear in my mind.
>
> Q. Can you get data out of a TABLE without having to create a
> recordset?

No.
Selecting a small recordset, that only delivers a very small
result-matrix is not all that expensive - it is done usually
in a range below 1msec (depending somewhat on the
indexing, as long as there are where-clauses involved).

What you should not do, is to perform thousands of Rs-
Selects in a tight loop - then the overhead (which is of
course there) would sum up. Better to do one single
(larger) select - and working with the Rs-Find-methods
then (or copying that larger Rs-Content into better suiting,
even faster searchable container-structures as e.g. arrays
or dictionaries, collections, etc.).

> Q. What if you just wanted to access a single cell in the Table?

To "address" a single cell in a table, you have to restrict
to the "Column-Coordinate" (easily done by specifiying the
columname in the Select-String) ... and you have to specify
the "Row-Coordinate" by specifying a Where-Clause-condition
which is matching with exactly one Row (ID ... for example).

Olaf

Reply to this Message

Webbiz - 10 Jul 2009 02:09 GMT

>> Just so I get these things clear in my mind.
>>
[quoted text clipped - 13 lines]
>even faster searchable container-structures as e.g. arrays
>or dictionaries, collections, etc.).

Okay. Then that does it. Anytime I need data from a table, I will no
longer forget that I must create a recordset. And yes, I'll do
whatever it takes to not do a bunch of 'select' queries, such as in a
loop. Better to grab more data at once and then search through it then
to grab zillions of small pieces of data through queries.

Got it!

>> Q. What if you just wanted to access a single cell in the Table?
>To "address" a single cell in a table, you have to restrict
>to the "Column-Coordinate" (easily done by specifiying the
>columname in the Select-String) ... and you have to specify
>the "Row-Coordinate" by specifying a Where-Clause-condition
>which is matching with exactly one Row (ID ... for example).

Well, that second question was more geared towards the possibility of
just getting one or two pieces of information from the table without
creating a recordset. Since you've made it clear that this cannot be
done, this second question no longer applies.

I've been testing the speed of my code to determine whether going to
an array is better than accessing data via .ValueMatrix.

In other words, I'll create an array using .GetRows() and run it
through my loop. Then I forego .GetRows and go directly to the
recordset using .ValueMatrix. So far, I can't really detect a
difference in speed, but my loops have been relatively small, like
about 400 iterations on a 6 column recordset.

Thanks Olaf. :-)

Webbiz

>Olaf

Reply to this Message

VB Forum / Database Access / July 2009

SQLite Questions