Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 3 of 3 total |
slow query |
Mon, Sep 3 2007 10:06 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |