We have about 100000 records in one table containing 11 fields and about 20000 records in another tables containing 9 fields.

Our server hardware is not so bad, (CPU 3.2 Ghz Quad, RAM 4 GB) and we have 5 clients.

I've set index on some fields in each tables and use filter to filter records in the table, when user tries to run the application.
It's so slow when user runs the program for the first time, after first time the filter command works good, but it's annoying first time.
Any suggestion?

Masoud


You haven't given very much for us to go on. My guess would be that the first run is simply having to load / open all the tables / indices and that takes time. Since you've posted in the c/s newsgroup I assume you're using it and that would mean that the server process has access to the stuff when the next user starts up.

You say filter so I assume you're not using SQL. One thing you could do is use the filter as the WHERE clause and run a query in DBSys to see if the plan gives you any indication of performance problems.

I'm also assuming that its a filter and not using the on filter event.

Roy Lambert

Roy Lambert wrote:

You haven't given very much for us to go on. My guess would be that the first run is simply having to load / open all the tables / indices and that takes time. Since you've posted in the c/s newsgroup I assume you're using it and that would mean that the server process has access to the stuff when the next user starts up.

You say filter so I assume you're not using SQL. One thing you could do is use the filter as the WHERE clause and run a query in DBSys to see if the plan gives you any indication of performance problems.

I'm also assuming that its a filter and not using the on filter event.

@Roy
Thanks for your fast reply.
Sorry, I've forgot to mention I'm using C/S version (without source) of DBISAM.
You're right, we have this long delay just first time .
I already use Table's Filter property as WHERE of Query, and still takes so long.
Also, tried to "Set an Expression Filter..." on dbsys on the server, it takes same time as my program on server does.

Yes, I'm not using the OnFilter event.

Masoud

>I already use Table's Filter property as WHERE of Query, and still takes so long.

If you do that in DBSys and tick generate plan can you post the plan here.

Roy Lambert

@Roy
Thanks again for your time.


>If you do that in DBSys and tick generate plan can you post the plan here.

Can you please explain what should I do in DBsys?
Did you mean using "Action \ Set an Expression Filter..." ?
If so, then what should I post here ?

btw, as I mentioned before, our main problem here is on the clients not the server.
Should I use your instructions on the clients or the server?

On 5/31/2016 12:44 PM, Masoud wrote:
> Can you please explain what should I do in DBsys?
> Did you mean using "Action \ Set an Expression Filter..." ?

Roy suggested you run a remote sql query with the plan and the filter
condition specified as WHERE clause. Plan is easiest way to see which
indexes are used and what the cost of query and exec time is

Steps:
1. start dbsys
2. file->options and on Remote tab configure the IP and port for your
DBSRVR server. Hit OK when done
3. File->New SQL Query
4. Select "Remote (client/server)" option and authetnicate
5. In query window tick the "generate plan" checkbox
6. Enter your SQL now in query window and include the fitler expression
you find slow as WHERE clause (i.e. select * from mytable WHERE <your
filter clause>)
7. Run SQL
8. Switch to Plan tab of the query that will appear
9. select all of plan text and paste into reply to this message

Raul

Raul


The only bit I'd disagree with is using the remote option. I don't care if the query is run local or remote as long as Masoud makes sure he's using the right database

Roy Lambert

@Raul

Thanks for your instructions, I didn't know that.

btw, As I mentioned before, I don't have much problem when access the database from the server locally.
When clients attempt to open the aforementioned tables, it takes so long just when opening those tables just the first time.
Is it because of using Table's filter ? Will it get faster if I use TQuery instead?

I'm on vacation now, I will post here the plan when I get back.

Thanks for your helps.

On 6/1/2016 4:33 AM, Roy Lambert wrote:
> The only bit I'd disagree with is using the remote option. I don't care if the query is run local or remote as long as Masoud makes sure he's using the right database

The way i look at it is that this represents what end user app does more
closely.

Query plan results in terms of indexes and costs should not be any
different but query timings using remote would be more close to actual.

Raul

Here is a plan generated on one of the clients:



================================================================================
SQL statement (Executed with 4.42 Build 1)
================================================================================

SELECT * FROM PersonsRecords WHERE FollowUpUser LIKE '%ali%'

Tables Involved
---------------

PersonsRecords (PersonsRecords) table opened shared, has 20737 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

FollowUpUser LIKE '%ali%'

is UN-OPTIMIZED and will be applied to each candidate row in the PersonsRecords
table (PersonsRecords) as the result set is generated

================================================================================
>>>>> 8066 rows affected in 2.184 seconds
================================================================================



I couldn't understand what is "UN-OPTIMIZED" warning for. And how to solve it.

btw, I'm using TDBISAMTable.Open just first time to open a table. and before opening, I check TDBISAMTable.Active property to check whether it is already open or not.
But as I looked in the source of Dbsys, it uses TDBISAMQuery.ExecSQL .
Am I misusing those functions?