Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread slow query
Mon, Sep 3 2007 10:06 PMPermanent Link

silven
We are having serious performance issues.

1> Dbiasm in client mode over a lan
We are using query component to run the  following query
over a lan

        select SpecID, SpecIDVersion from specs
        where (SpecIDVersion = 99)

The specs table has 15k and there are 11k with a specidversion = 99
The query takes 14 seconds
The table has an index on specidversion
The engine has all buffer sizes set to 512K
What can be done to increase performance.

2> Dbiasm in C/S mode
The query takes 3.5 seconds
Unfortunately a new problem is created

     FOR p := 0 TO dispParamSL.Count-1 DO
        BEGIN
         ParamsFlagsTransTable.SetKey;
         ParamsFlagsTransTableDBFieldName.AsString := fName;
         IF ParamsFlagsTransTable.GoToKey THEN
                showmessage('Hello World');
        END;

The above code which was working in LAN mode slows down to an unacceptable level in C/S mode.

The following sequel while an improvement is still not good enough

     q := TDBISAMQuery.Create(nil);
     Q.SessionName := 'REMOTE';
     q.DatabaseName := 'HASDB';         
     q.requestlive := false;
     q.readonly := true;


     FOR p := 0 TO dispParamSL.Count-1 DO
        BEGIN
     q.SQL.Clear ;
     q.SQL.Add('select username from instparams ' );
     q.SQL.Add('where dbfieldname = ' + quotedstr(fname) );
     try
        q.ExecSQL;
     except
      on e: exception do
      begin
        showmessage(e.Message);
        exit;
      end;
   END;

It appears that navigation under client only mode is faster than
sql under client/server leaving us without a viable solution

Thanks,
Silven
Tue, Sep 4 2007 3:40 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

silven


Which version of DBISAM and if it has them can you post the query plans

Roy Lambert
Tue, Sep 4 2007 9:27 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Silven,

<< The specs table has 15k and there are 11k with a specidversion = 99
The query takes 14 seconds
The table has an index on specidversion
The engine has all buffer sizes set to 512K
What can be done to increase performance. >>

As Roy indicated, please post the query execution plan for this query:

http://www.elevatesoft.com/dbisam4d7_executing_sql_queries.htm

(see Retrieving Query Information section).

<< The above code which was working in LAN mode slows down to an
unacceptable level in C/S mode. >>

You should consider moving any looping code like this, especially if there
is a fairly high iteration count, into a server-side procedure:

http://www.elevatesoft.com/dbisam4d7_customizing_engine.htm

(see the Server Procedures section)

<< It appears that navigation under client only mode is faster than sql
under client/server leaving us without a viable solution >>

Yes, navigation under local sessions is usually faster than C/S, depending
upon how the navigation is occurring.  In your case, you're using random
GotoKey navigation, which means that the remote session can't perform any
read-ahead optimization in order to help improve the navigation and reduce
the number of request/response cycles to the database server.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image