 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
HanslH
Guest
|
 Posted: Mon Jan 29, 2007 5:35 am Post subject: (newbie)adstables->tibdatabase transition, can anything be h |
 |
|
3 Years ago imoved a 60 table program from clipper dbfcdx to
delphi4,tadstable.
I now bought Delphi Turbo and had someone so kind to explain
firebird/interbase basics to me.
I create an interbase database, import all tables and create indexes
but I'm afraid this is just about 1% of the work. I now have to
rethink my program because I'm using this kind of construction a lot:
findkey([somevalue])
while (fieldbyname('somefield').asstring = somevalue) and not eof do
begin
someaction;
next;
end;
I might go from tadstable (findkey) to tibtable (locate) which works
fine
I am afraid that after a lot of work i find myself with a much slower
program when working with large tables. Also everything I read/hear
suggests I should use tables but queries.
But I can't understand how to solve some things by queries:
For example I have a dialog that lets a user search a table on a
keyfield. He types some characters in a tedit and the first
(findnearest) hit and following say 20 are put in a listview. The
user can hit <enter> if the first is the one he was looking for or
navigate to the right one in the listview.
How do I do something like this using queries as in queries as I
understand you ask for a subset of data based on some conditions in
stead of the findnearest and 19 next ones. |
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
| Posted: Mon Jan 29, 2007 7:18 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
HanslH wrote:
| Quote: |
For example I have a dialog that lets a user search a table on a
keyfield. He types some characters in a tedit and the first
(findnearest) hit and following say 20 are put in a listview.
|
Known as "incremental search".
| Quote: | The
user can hit <enter> if the first is the one he was looking for or
navigate to the right one in the listview.
How do I do something like this using queries as in queries as I
understand you ask for a subset of data based on some conditions in
stead of the findnearest and 19 next ones.
|
The only real difference you need to make this efficient for an SQL database
is to place a "Search" button next to the TEdit. Don't search on every
keystroke, only when he pushes the button. Then you can can format a query
with a Where clause.
Generally you would use a LIKE operator in the query, e.g. to find names
starting with "Smi" the where clause would be
"where lastname like 'Smi%'"
the '% is a wild card.
Note that the like operator case-sensitive, so 'smi%' will not match the
name "Smith". You can use Upper() to make it case-insensitive but then this
would not be able to take advantage of an index on that field. There are
other ways to deal with this as well if that is an issue.
You should also try to ue parameterized queries as much as possible - makes
less work in code and can provide better performance. E.g. the query
component for the above search would be set to "select <whatever fields>
from tablea where name like :name%". Then when the user presses Search, you
just assign the parameter and execute:
MyQuery.ParamByName('name').AsString := Edit1.Text;
MyQuery.Open; // or ExecQuery, depending on the type of query component
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr. |
|
| Back to top |
|
|
HanslH
Guest
|
| Posted: Mon Jan 29, 2007 9:11 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
On Sun, 28 Jan 2007 20:18:48 -0500, "Wayne Niddery [TeamB]"
<wniddery (AT) chaffaci (DOT) on.ca> wrote:
| Quote: |
The only real difference you need to make this efficient for an SQL database
is to place a "Search" button next to the TEdit. Don't search on every
keystroke, only when he pushes the button. Then you can can format a query
with a Where clause.
Generally you would use a LIKE operator in the query, e.g. to find names
starting with "Smi" the where clause would be
"where lastname like 'Smi%'"
the '% is a wild card.
Note that the like operator case-sensitive, so 'smi%' will not match the
name "Smith". You can use Upper() to make it case-insensitive but then this
would not be able to take advantage of an index on that field. There are
other ways to deal with this as well if that is an issue.
You should also try to ue parameterized queries as much as possible - makes
less work in code and can provide better performance. E.g. the query
component for the above search would be set to "select <whatever fields
from tablea where name like :name%". Then when the user presses Search, you
just assign the parameter and execute:
MyQuery.ParamByName('name').AsString := Edit1.Text;
MyQuery.Open; // or ExecQuery, depending on the type of query component
|
So 'incremental search' is the name of the first bit of database
programming I did in 1990 in dbase iv...
I'm not sure how reacting to a search button and reacting to a typed
character should make a difference. I always try to make my program as
user friendly as possible which means getting things done fast by
using the keyboard if they want (That doesn't include hitting tab for
the right button to get focus and then hitting enter  )
I alway used uppercase indices for this kind of work, never regretted
I can't do case sensitive searching.
When using the 'smi%' construction my listbox will not fill. Also
users cannot scroll through this listbox using pageup/dn. Definetely
not a price i'm (my users are) not going to pay for anything
I can't believe everybody is just happily droppig the use of tables
I'm both a programmer and a user of the software in a veterinairy
practice. I never experience performance issues (heard about them from
other practices using the software) Also data corruptions aren't an
issue since i never leave tables open for writing a long time and
don't use data aware controls. Maybe I should rethink the transition. |
|
| Back to top |
|
|
Woody (TMW)
Guest
|
| Posted: Mon Jan 29, 2007 11:41 pm Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
"HanslH" <no (AT) no (DOT) no> wrote in message
news:as8rr2tbl1c8lhpqiuuckgjrvmn7aoio5u (AT) 4ax (DOT) com...
| Quote: | So 'incremental search' is the name of the first bit of database
programming I did in 1990 in dbase iv...
I'm not sure how reacting to a search button and reacting to a typed
character should make a difference. I always try to make my program as
user friendly as possible which means getting things done fast by
using the keyboard if they want (That doesn't include hitting tab for
the right button to get focus and then hitting enter )
|
I use a timer that gets reset everytime the user presses a key in the edit
box. If they pause for 2 or 3 seconds, the query kicks off with the current
entry. This way, there is no intervention needed by the user such as
clicking a button.
| Quote: |
I alway used uppercase indices for this kind of work, never regretted
I can't do case sensitive searching.
When using the 'smi%' construction my listbox will not fill. Also
users cannot scroll through this listbox using pageup/dn. Definetely
not a price i'm (my users are) not going to pay for anything
I can't believe everybody is just happily droppig the use of tables
I'm both a programmer and a user of the software in a veterinairy
practice. I never experience performance issues (heard about them from
other practices using the software) Also data corruptions aren't an
issue since i never leave tables open for writing a long time and
don't use data aware controls. Maybe I should rethink the transition.
|
Tables contain a lot of overhead and aren't suited to client/server
databases. They are fine for stand-alone, desktop applications but when
switching to client/server architecture, you should be aware that a
different mode of thinking is needed. Like it or not, that's just the way it
is. Learning SQL is a must in order to streamline queries so that there is
no difference in speed (or as little as possible anyway).
You don't have to become a SQL guru, just learn the basics. SQL is a
powerful tool that a standard table component just can't match.
HTH
Woody (TMW) |
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Tue Jan 30, 2007 12:03 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
HanslH wrote:
| Quote: |
I'm not sure how reacting to a search button and reacting to a typed
character should make a difference.
|
If searching on each keystroke is going to produce noticeable hesitation
that makes them wait, they will quickly find that typing a few characters
and requesting a search *once* will be much faster.
| Quote: | I always try to make my program as
user friendly as possible which means getting things done fast by
using the keyboard if they want (That doesn't include hitting tab for
the right button to get focus and then hitting enter )
|
The Search button can be made a default button so it fires by pressing
Enter. At worst you can make a short cut so they can use, e.g. Alt-F (for
"find").
| Quote: | I alway used uppercase indices for this kind of work, never regretted
I can't do case sensitive searching.
|
Then no problem.
| Quote: | When using the 'smi%' construction my listbox will not fill.
|
If it was filling before it should fill now - probably just something not
quite right in the query and its not fetching matching records. If you have
uppercase indexes, be sure to uppercase whatever the user enters before
assigning it as a parameter.
| Quote: | Also
users cannot scroll through this listbox using pageup/dn. Definetely
not a price i'm (my users are) not going to pay for anything
|
They can after performing a search. The advantage is there'll be a lot less
to scroll through. If the user knows the person they're looking for has the
last name Smith, then by searching on that, they only have to scroll through
Smiths in the listbox.
| Quote: | I can't believe everybody is just happily droppig the use of tables
I'm both a programmer and a user of the software in a veterinairy
practice. I never experience performance issues (heard about them from
other practices using the software) Also data corruptions aren't an
issue since i never leave tables open for writing a long time and
don't use data aware controls. Maybe I should rethink the transition.
|
I've been through this kind of transition several times with many different
kinds of industry and users. There is essentially no difference among them
and the following points are always true:
1) Users, as a whole, don't welcome any significant change to the way they
do things and have to be convinced it is really better.
2) Displaying a screen with the entire contents of a table they can scroll
through is *extremely* common and long-time users generally can't imagine
anything better if they haven't seen/used anything else.
3) SQL databases *generally* are not going to return a large number of
records as instantaneously as a desktop database like Paradox/Dbase/Access,
but that is *exactly* why they need to be used differently - while it may
seem like a contradiction, as long as it is coded well, that apparently
slower SQL database will be *more* efficient, especially when there are more
than a few users working concurrently.
4) What really is the difference between the following use:
4a) Pressing 'S' then PageDn 5 or so times to get to the right page
containing the desired person
4b) Pressing 'SMIT' and Enter to get exactly the same "page" just as quickly
5) The notion that a user *needs* to be able to scroll through a *large*
list of all names *instead* of searching for something more specific is a
myth - see #1 and #2. If they are doing it because there is no way to search
(e.g. because they need to find a matching SSN/SIN number or invoice number
or, maybe, a pet name or license number, then it is just a matter of
providing the ability to search by these alternate methods. 5a) One *huge*
advantage of SQL is that you can perform searches on *any combination* of
fields whether there are indexes or not - the SQL database will use whatever
indexes it can automatically rather than you having to have a matching index
and having to select just that one. So you can search by, e.g. Last name
starting with SMI and the first 3 digits of a phone number. If only last
name is indexed, the database will use that first then from there manually
search the phone number field for you.
6) No matter how well you design it there will be a *few* users that will
never be satisfied and will want the old system back. But, after a little
time using it *most* users will get used to it and will range in opinion
from neutral to happy (unless of course the interface is poor or performance
actually sucks).
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist." - Ayn Rand |
|
| Back to top |
|
|
Loren Szendre
Guest
|
| Posted: Tue Jan 30, 2007 12:19 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
HanslH
| Quote: |
I'm not sure how reacting to a search button and reacting to a typed
character should make a difference. I always try to make my program as
user friendly as possible which means getting things done fast by
using the keyboard if they want (That doesn't include hitting tab for
the right button to get focus and then hitting enter )
I alway used uppercase indices for this kind of work, never regretted
I can't do case sensitive searching.
When using the 'smi%' construction my listbox will not fill. Also
users cannot scroll through this listbox using pageup/dn. Definetely
not a price i'm (my users are) not going to pay for anything
I can't believe everybody is just happily droppig the use of tables
I'm both a programmer and a user of the software in a veterinairy
practice. I never experience performance issues (heard about them from
other practices using the software) Also data corruptions aren't an
issue since i never leave tables open for writing a long time and
don't use data aware controls. Maybe I should rethink the transition.
|
Yes, people have been HAPPILY dropping tables for a long time! It is a
paradigm shift, but one worth making. There are lots of ways to work
within the c/s framework -- one of which is to go to an n-tiered design.
You can cache your lookup table datasets in TClientDataSets that are
only filled upon first request. For larger things, like looking up a
customer when you have hundreds of thousands, you do NOT put them in a
combo box. You do what Wayne suggested.
I made the transition from Paradox to InterBase in the late 90's. I had
to rethink everything, but I have been TTable (& TIBTable) free for over
7 years now! It's been so long since my last puff on findkey and
setrange that I don't even feel the pangs anymore.
Good luck,
Loren |
|
| Back to top |
|
|
HanslH
Guest
|
| Posted: Tue Jan 30, 2007 1:02 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
On Mon, 29 Jan 2007 13:03:16 -0500, "Wayne Niddery [TeamB]"
<wniddery (AT) chaffaci (DOT) on.ca> wrote:
...
Thanks for taking such an effort to advice on an issue you must have
been advising already for so many years. I will rethink my rethinking,
I sure want to get ahead in the world and learn new things. It's a
pity using tables in client server is so slow otherwise I could
gradually adapt the application to query/SQL.. |
|
| Back to top |
|
|
HanslH
Guest
|
| Posted: Tue Jan 30, 2007 2:58 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
On Mon, 29 Jan 2007 11:41:28 -0600, "Woody \(TMW\)"
<woody-tmw (AT) suddenlink (DOT) net> wrote:
| Quote: | I use a timer that gets reset everytime the user presses a key in the edit
box. If they pause for 2 or 3 seconds, the query kicks off with the current
entry. This way, there is no intervention needed by the user such as
clicking a button.
|
Okay, but there is a pause where there was none. Almost everthing in
my program is instantanious now.
Since veterinairy practices (in holland) seldom grow to > 5 user and
there are no problems to solve now the need transition still is not
100% evident to me.
It's just that the whole client sever idea where all data is centrally
administered is just very appealing for a control freak like me. |
|
| Back to top |
|
|
Woody (TMW)
Guest
|
| Posted: Wed Jan 31, 2007 12:15 am Post subject: Re: (newbie)adstables->tibdatabase transition, can anything |
|
|
"HanslH" <no (AT) no (DOT) no> wrote in message
news:3insr2p3k125r5tuuahu4i6q0luo66oeg1 (AT) 4ax (DOT) com...
| Quote: | On Mon, 29 Jan 2007 11:41:28 -0600, "Woody \(TMW\)"
woody-tmw (AT) suddenlink (DOT) net> wrote:
I use a timer that gets reset everytime the user presses a key in the
edit
box. If they pause for 2 or 3 seconds, the query kicks off with the
current
entry. This way, there is no intervention needed by the user such as
clicking a button.
Okay, but there is a pause where there was none. Almost everthing in
my program is instantanious now.
|
That's because a table brings all the data over locally to search through
and a query only gets what you need when you need it. You can even limit the
query to kick off only when X amount of letters has been typed. Everyone's
needs are different and some ideas work for some but not all. Sometimes it
just takes a little tweaking.
| Quote: | Since veterinairy practices (in holland) seldom grow to > 5 user and
there are no problems to solve now the need transition still is not
100% evident to me.
It's just that the whole client sever idea where all data is centrally
administered is just very appealing for a control freak like me.
|
It's not apparent because you haven't done it yet. :)
I built programs that used Access and Paradox databases for a long, long
time. When Interbase went open source, I switched over and was in the same
boat as you. Things seemed much slower. As I learned more and more, I
started replacing the old way of doing things with the new ways using SQL.
Amazing how much easier, faster and friendlier things get after awhile.
Don't give up. Make a copy of the current program and start playing with it
separately. Come back and post the bottlenecks you run into and we'll help
you evolve it or rewrite it, whichever is needed.
HTH
Woody (TMW) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
