Board index » delphi » Speeding up a "filter" in DBExpress

2004-07-01 02:44:41 PM
delphi136
Hi There,
I' m using DBexpress, interbase and D6. The application is running on a
W2000 server with XP clients.
I have the following problem. I have one table with all my customer
information of about 800 records. In this table I try to select all
customers that match a certain phrase in their name. I' m using the "filter"
option to do this. The problem with the way DBEXPRESS works is that all
records first get copied to the client and then the filter gets active. This
takes (very) much time.
Is there way to speed this up !? As I am not very familiar with indexes, can
anyone tell me either an index on the name helps in this case?
I' m familiar with the SELECT statement, but that is not applicatable here
as I really need to filter the name of the customer.
Thanks in advance
Erik
 

Use SELECT with WHERE:
SELECT * FROM SomeTable WHERE CustomerName LIKE 'ABC%'
this will select only rows where CustomerName starts with ABC. If you have
index on CustomerName column, query will run faster.
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"ErikSw" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

Thanks for the quick response !,
But isn't this statement casesensitive !?, and if it is is there a way to
disable that ?
Erik
"Viatcheslav V. Vassiliev" <XXXX@XXXXX.COM>schreef in bericht

"ErikSw" <XXXX@XXXXX.COM>writes
SELECT * FROM CUSTOMERS WHERE UPPERCASE(NAME) LIKE UPPERCASE(:ACustName)
is quite handy because you can easily add the '%' to the parameter on the
client side to give 'Starts with', 'contains' or 'Ends with' functionality.
On the question of indexes - an index will speed up this query enormously,
but you should not create indexes unless necessary. You need to weigh up a
few factors.
a) is this kind of search vital to you and/or very common on your database
?
b) is the search intolerably slow without it ?
c) are there other processes performing bulk inserts and updates on your
database which *must not* be impacted ?
An index will speed up relevant queries, but will slow down inserts, updates
and deletes.
Having said that - indexes are usually good.
Hope that Helps
H
 

ErikSw writes:
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
news server: info.borland.com/newsgroups/guide.html