Masoud,

<< Here is a plan generated on one of the clients: >>

Are you *only* seeing a 2 second delay (that is, the same as execution time of this query) ?  If not, then something else is taking the time.  The fact that the same queries/filters run fast on the server itself indicate to me that the issue is client-side.  Are you using any 3rd party grids or other type of data-aware controls in the client application ?

Tim Young
Elevate Software
www.elevatesoft.com

@Tim Young:

Thanks for your email.
Yes, I'm using a third party grid from TMS.
I have not much problem on the server as I mentioned before. Our only problem is on ALL clients which takes so longer than 2 seconds (about 30 seconds) when I try to open aforementioned tables.

In addition, the tables are encrypted. Can it be a reason for this long delay?

Thanks for your helps and your time.

Masoud

<<
Masoud wrote:
Yes, I'm using a third party grid from TMS.
I have not much problem on the server as I mentioned before. Our only problem is on ALL clients which takes so longer than 2 seconds (about 30 seconds) when I try to open aforementioned tables.
In addition, the tables are encrypted. Can it be a reason for this long delay?
>>

DBSYS uses the exact same dbisam components as your own delphi app (dbsys is in fact a regular delphi app).

So whatever performance you see in DBSYS when running a remote query is generally what you should expect in your own application on client side.

if there is a big discrepancy then you need to look at how your app uses the data.

3rd party data bound components like grids or combo boxes etc are often the source of extra queries or traffic so optimizing them is usually the first step.

Raul

@Raul

Thanks for your post.
I'm gonna use regular grid to see if it gets better or not.

Please let me know should I use another way instead of TDBISAMTable.Open ? because the main problem is when the program tries to open tables.

I appreciate all your helps .

<<Masoud wrote:
Thanks for your post.
I'm gonna use regular grid to see if it gets better or not.

Please let me know should I use another way instead of TDBISAMTable.Open ? because the main problem is when the program tries to open tables.
>>

It really depends on your usage and data.

In simple terms TDBISAMTable allows you to have access to the table and set ranges/filters to do subsets. However you have to be careful about getting the whole table transferred client side if it's large (something to look out for with bound data controls and make sure they page data properly).

DBISAMQuery allows you to execute sql on server side so you can fully limit amount of data your client retrieves and of course sql allows you to join multiple tables in resultsets etc,


Raul

Masoud


I've been trying (and failing) to get my machine to upgrade from W7 to W10 for the past couple of days but here's my input

1.
UNOPTIMISED means that the query or filter can't use an index to speed things up but has to go through testing each row in turn.

2.
I don't know if DBISAM's LIKE works in the same way as ElevateDB's or if FollowUpUser is a large field or a small one but if its a large memo field then you may find that for a simple "find this string anywhere in the field" operation like your's 0 <> POS('ali',FollowUpUser) is faster

3.
I think the big problem will be the TMS grid. If you have PageMode (I think it was) set to False then it will load the entire dataset ie it will drag 8066 records across the LAN. This will take time, buffering will probably speed this up for future iterations. DBISAM itself is smart enough to only send enough for the visible rows of a grid but if the grid asks for them all it will send them. The is another problem that I used to have - the TMS grid used Move rather than RecordCount to determine the number of records. There is an event which you can use to override this behaviour. If you don't it will slow things down.

If you're using the features in the grid to sort / filter the data then you probably are using it in PageMode = False

4.
Encryption will add some overhead but nowhere near enough to cause a jump from 2 - 30 seconds. This has almost certainly got to be a transport problem

Roy Lambert

ps its a long time since I removed all TMS products from my apps so I may have teh property name wrong, or the effect of true/false reversed

First of all, I would appreciate Raul and Roy for great helps.

@Roy:

The problem was exactly for TMS grid, as you mentioned I believe it does not show rows on demand and loads the whole table's records entirely, instead.
Now I have replaced that with regular TDBGrid and the major problem is solved !

About UN-OPTIMISED warning, I already set index on that field, what else should I do ?

Thanks again for all your helps !

Masoud

>The problem was exactly for TMS grid, as you mentioned I believe it does not show rows on demand and loads the whole table's records entirely, instead.
>Now I have replaced that with regular TDBGrid and the major problem is solved !

You'll find its similar with most of the enhanced grids - to use the features you have to load all of the records :{

>About UN-OPTIMISED warning, I already set index on that field, what else should I do ?

I don't know if DBISAM can use an index to speed up LIKEs (I think ElevateDB can). Looking at the execution plan I'd guess it can't so unless you need the index for something else I'd get rid of it. You can try my idea with POS and see if that's any faster, the other thing to try depends on wether full text indexing in DBISAM supports wildcards at both ends of a word. I don't have a modern version so can't say. if it does its worth trying a full text index and using TEXTSEARCH.

Roy Lambert

@Roy

Thanks for your instructions.
I will give it a try.

Masoud,

<< About UN-OPTIMISED warning, I already set index on that field, what else should I do ? >>

There's nothing else you can do with DBISAM, and the index won't help for LIKE queries that use a leading wildcard in the search condition.  As Roy indicates, ElevateDB can "kind of" optimize this by directly scanning the index, but DBISAM cannot do this due to the way that its index keys are built.

So, you've done all you can to optimize this query for DBISAM.

I'm glad that you found the source of your issue.

Tim Young
Elevate Software
www.elevatesoft.com