Question

KbmMemTable too slow

Asked by: Jaymol

Hi.

I've got an app that needs to load data from a dataset into a KbmMemTable, which is then streamed over a tcp connection and lots of other stuff done with it. The application is working fine, except for performance. There's a couple of issues, such as encryption and decryption, that I'm currently working on as they slow the process down, but loading the data from a dataset into a KbmMemTable is simply too slow.

I've included code below (dfm & pas) for a delphi test app, and an sql script that will create the test table I've used in my testing.

It's a messy table, on purpose, but it's representative of the type of data I'll be looking at importing regularly into a memory table.

The initial query to get all data from the test table takes anywhere up to 0.5 seconds. That's obviously fine. The problem is that KbmMemTable.LoadDataSet takes about 3.5 seconds with the test data, which is about a third of the size of the real tables. It takes 12-13 seconds for the real data to be imported, and this simply is too slow.

Any suggestions would be greatly appreciated, but this is a very old and long-standing system, so suggestions that relate to the database are no good to me. I need to get a dataset into a memory stream as quickly as possible.

The demo app will expect the test table to exist on a local instance of MASTER, so change that if required.

Thanks in advance.

John.

// Start of DFM --------------------------------------------------------------------------------------------------
object Form1: TForm1
  Left = 312
  Top = 103
  BorderIcons = [biSystemMenu]
  BorderStyle = bsSingle
  Caption = 'Form1'
  ClientHeight = 377
  ClientWidth = 505
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 16
    Top = 16
    Width = 161
    Height = 25
    Caption = 'Test '#39'LoadFromDataSet'#39
    TabOrder = 0
    OnClick = Button1Click
  end
  object ListBox1: TListBox
    Left = 192
    Top = 16
    Width = 297
    Height = 345
    ItemHeight = 13
    TabOrder = 1
  end
end
// Start of PAS --------------------------------------------------------------------------------------------------
unit kbmst01;
 
interface
 
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls,
  ADODB, DB, KbmMemTable, ActiveX;
 
type
  TForm1 = class(TForm)
    Button1: TButton;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    procedure UpdateStatus(Value: String);
    { Private declarations }
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.DFM}
 
procedure TForm1.Button1Click(Sender: TObject);
var
	sQuery,
  sAlias: String;
  adoDataSet: TADODataSet;
  kbmMemTable: TkbmMemTable;
begin
 
	sQuery := 'SELECT * FROM DUMMY_TABLE';
 
  try
    CoInitialize(nil);
 
    adoDataSet := TADODataSet.Create(Self);
    adoDataSet.ConnectionString :=
    	'Provider=SQLOLEDB.1;' +
      'Integrated Security=SSPI;' +
      'Persist Security Info=False;' +
      'Initial Catalog=MASTER;' +
      'Data Source=localhost';
 
    UpdateStatus('PreQuery');
    adoDataSet.CursorType := ctOpenForwardOnly;
    adoDataSet.CommandText := sQuery;
    adoDataSet.Open;
    UpdateStatus('PostQuery');
 
    kbmMemTable := TkbmMemTable.Create(Self);
 
    UpdateStatus('PreLoad');
    kbmMemTable.LoadFromDataSet(adoDataSet, [mtcpoStructure]);
    UpdateStatus('PostLoad');
    UpdateStatus(Format('RecordCount (%d)', [kbmMemTable.RecordCount]));
 
    FreeAndNil(adoDataSet);
    FreeAndNil(kbmMemTable);
 
    CoUninitialize;
 
  except
  	on E:Exception do
    begin
    	CoUninitialize;
    	raise Exception.Create(E.Message);
    end;
  end;
end;
 
procedure TForm1.UpdateStatus(Value: String);
begin
	ListBox1.Items.BeginUpdate;
	ListBox1.Items.Add(Format('[%s] %s', [FormatDateTime('hh:mm:ss:zzz', Now), Value]));
  ListBox1.TopIndex := ListBox1.Items.Count - 1;
	ListBox1.Items.EndUpdate;
end;
 
end.
// Start of SQL --------------------------------------------------------------------------------------------------
SET NOCOUNT ON
 
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE [NAME] = 'DUMMY_TABLE' AND TYPE = 'U')
	DROP TABLE DUMMY_TABLE
 
DECLARE @CREATE_TABLE VARCHAR(8000),
	@COUNTER INT
 
SET @CREATE_TABLE = 'CREATE TABLE DUMMY_TABLE (RECORD_ID INT,'
SET @COUNTER = 0
 
WHILE @COUNTER < 100
BEGIN
	SET @COUNTER = @COUNTER + 1
	SET @CREATE_TABLE = @CREATE_TABLE + 'FIELD' + CAST(@COUNTER AS VARCHAR) + ' VARCHAR(36) DEFAULT NEWID()'
	IF @COUNTER < 100
		SET @CREATE_TABLE = @CREATE_TABLE + ','
	ELSE
		SET @CREATE_TABLE = @CREATE_TABLE + ')'
END
 
EXEC(@CREATE_TABLE)
 
SET @COUNTER = 0
 
WHILE @COUNTER < 2000
BEGIN
	SET @COUNTER = @COUNTER + 1
	INSERT INTO DUMMY_TABLE (RECORD_ID) VALUES (@COUNTER)
END
 
SELECT * FROM DUMMY_TABLE
 
SET NOCOUNT OFF

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-06-15 at 07:01:56ID24492167
Tags: delphi kbmmemtable loadfromdataset performance
Topic: Delphi Programming
Participating Experts: 2
Points: 0
Comments: 24

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: Geert_GruwezPosted on 2009-06-15 at 14:36:12ID: 24633272

performance ?
um, with this code ?
probably not ... why ?
you seem to be wanting feedback about everything your code does
this is ok in test, but throw it away if you want speed

the updatestatus procedure will just kill your performance !
so get rid of that for starters

second you aren't using the fastest components on earth ... ADO ?
consider testing it with SDAC from www.devart.com

and while you test that, did you notice they have a virtual memory table ?
http://www.devart.com/vtable/

in my opinion try and minimize the screen routines
instead writing to a log file is a lot faster (though this also slows down with a virusscanner)

when updating data try disablecontrols / enablecontrols

kbmMemTable := TkbmMemTable.Create(Self);
try 
  UpdateStatus('PreLoad');
  kbmMemTable.BeginUpdate; // or DisableControls
  try
    kbmMemTable.LoadFromDataSet(adoDataSet, [mtcpoStructure]);
  finally
    kbmMemTable.EndUpdate; // or EnableControls
  end;
  UpdateStatus('PostLoad');
  // Below line also is very slow
  UpdateStatus(Format('RecordCount (%d)', [kbmMemTable.RecordCount]));
finally
  FreeAndNil(kbmMemTable);
end;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all Open in new window

by: JaymolPosted on 2009-06-16 at 01:15:20ID: 24635803

"the updatestatus procedure will just kill your performance !"

No it won't. You never ran the code or you'd have seen that it simply shows a pre and post update, not during, so that does not slow anything down. It is simply there as a timestamp to show how long each individual process took in total. Also, ADO is not the issue. Again, running the application would have proved that.

I truly appreciate you taking the time to comment, but will wait for other relevant comments.

Thanks.

by: JaymolPosted on 2009-06-16 at 01:25:35ID: 24635865

"and while you test that, did you notice they have a virtual memory table ?"

Thanks - that's a great suggestion, and it's my fault for not mentioning - I'm using Delphi 5. They don't support their components for Delphi 5.

Thanks again.

by: Geert_GruwezPosted on 2009-06-16 at 01:30:27ID: 24635897

it may not kill performance,
but will most certainly slow it down

Delphi 5 ... ooo I removed that ...

how many records do you want to copy to memory ?

around 20000 should still be ok to use a TClientDataset
as of 50000 it really gets slow ...

by: JaymolPosted on 2009-06-16 at 01:51:23ID: 24636025

The performance that I am concerned with is on the "LoadFromDataSet" line. The update status method simply gives me a timestamp within that block of code, so I'm not concerned about how I could make them faster, as they are only for this test application.

LoadFromDataSet is taking too long, which is why I'm asking the question. I don't want the above code to be improved. I just want to know if there's a faster method of doing that 1 thing.

As far as TClientDataset goes, I did consider using that originally, but I don't want to have to install Midas on all the target machines. This is basically a no-go.

Thanks.

by: Geert_GruwezPosted on 2009-06-16 at 01:52:49ID: 24636038

if you use the TClientDataset for in memory applications you don't need Midas (I think)

by: JaymolPosted on 2009-06-16 at 01:57:31ID: 24636070

I thought you did - I'll investigate that.

Thanks.

by: JaymolPosted on 2009-06-16 at 02:30:52ID: 24636226

Thanks for that advice. I've got a test app that's running twice as fast as the original one now, so I know that the TClientDataSet method is definitely quicker.

All I need to do now is test it on a machine that definitely doesn't have any Midas componentents on it, just to be sure.

by: Geert_GruwezPosted on 2009-06-16 at 02:37:06ID: 24636247

i'm not all the experienced with TClientDataset either
i can only be of limited help

by: JaymolPosted on 2009-06-16 at 02:50:45ID: 24636315

I've just sub-classed it and created my own version that has a LoadFromDataSet method in it. Just tried 2 methods of copying data - using a manual copy, row by row, and using a TDataSetProvider to copy the data directly, and the latter is definitely faster.

I'll test it on my PC at home tonight and let you know how I get on.

Thanks.

by: JaymolPosted on 2009-06-16 at 03:02:16ID: 24636387

Actually, I take it all back. I've just rerun the tests, with both ClientDataSet and KbmMemTable, and the KbmMemTable is about 10% faster.

Guess I might be stuck with the fact that loading data from a dataset to a memory table is going to be a bit slow :-(

by: Geert_GruwezPosted on 2009-06-16 at 04:30:24ID: 24636910

loool, how many records are you processing ?

by: JaymolPosted on 2009-06-16 at 04:33:17ID: 24636926

That's only with 5000 records.

by: aikimarkPosted on 2009-06-17 at 13:40:07ID: 24652363

You might consider contacting kbmMemTable support
support@components4developers.com

Or consider their forum/newsgroup support options:
http://www.components4programmers.com/support/index.htm

=========================
If you are using a TClientDataset, you can get some performance boost by setting the Capacity property before you begin loading data. This might minimize the number of memory allocation and memory copy operations during your load process.

by: JaymolPosted on 2009-06-23 at 02:45:23ID: 24690318

Thanks for the comments aikimark. I'm going to see if kbm support can help.

I've unfortunately had to scrap the idea of using a TClientDataset, as I wanted to create a new class that inherits from it, and add a few extra properties to it. When I do this, it breaks DateTime fields for some reason. Whenever I apply data to TMyClientDataSet (for example), all the DateTime values come out as massive negative numbers, therefore causing issues when converting to a date time and finally show as "00/00/0000", without a time value as well!

I'd appreciate mods leaving this post open for now - I WILL update it when I get further news. I'd rather put any comments from the kbmMem people on here for future reading, rather than just leave it as it is.

Thanks guys.

by: JaymolPosted on 2009-06-25 at 03:27:20ID: 24709854

After getting in touch with KbmMemTable support, it appears that this is not a performance issue with the KbmMemTable, but with getting data from ADO instead.

Unfortunately, this is simply how fast it is, end of.

by: Geert_GruwezPosted on 2009-06-25 at 03:29:35ID: 24709869

one of the advantages of using ADO is: it's provided by Delphi in the standard.
I have yet to find the next advantage.

It is indeed a slow component

by: JaymolPosted on 2009-06-25 at 06:11:40ID: 24710987

That's exactly why I'm using it, and will continue to do so (unfortunately). I'm obvisouly slightly constrained by the fact that this is an existing project.

by: aikimarkPosted on 2009-06-25 at 06:28:31ID: 24711146

Jaymol

If the performance isn't a deal-breaker, you might post a new question about the datetime column problem with TClientDataset.

by: JaymolPosted on 2009-06-26 at 04:32:12ID: 24719945

Hi aikimark - I plan to do that exact thing, once I get these points back ;-), just out of sheer curiosity!

by: Geert_GruwezPosted on 2009-06-26 at 05:24:45ID: 24720222

don't try and get back poinx too much
some experts don't like it when they try to help out and the question gets closed like this
and will object to closing the question like this in the future

by: aikimarkPosted on 2009-06-26 at 06:39:55ID: 24720797

@Jaymol

Before this thread is closed, I turned my attention to the ADO-related data retrieval.

* Have you tried using a datatable object instead of a datatable object?
The dataset object can contain multiple datatable objects, so there is some overhead.

* How about using an TADOQuery object and passing its recordset collection to kbmMemTable?
reference: http://edn.embarcadero.com/article/27790

by: JaymolPosted on 2009-06-28 at 12:04:04ID: 24732060

@Geert
"some experts don't like it when they try to help out and the question gets closed like this".

If there was an answer in here, then I would gladly accept it, but there hasn't been. I'm more concerned about giving relevant information to future users who find this page via a search engine than I am the point scoring system on the site.

@aikimark
"some experts don't like it when they try to help out and the question gets closed like this".

Thanks for the suggestion, but it's one I've already tried and it made negligible difference to the test case.

by: JaymolPosted on 2009-06-28 at 12:05:13ID: 24732063

Sorry aikimark - that was a comment on your quote..

"How about using an TADOQuery object and passing its recordset collection to kbmMemTable?"

I should learn to proof read a bit more :)

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

KbmMemTable too slow

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

delphi kbmmemtable loadfromdataset performance

Delphi Programming

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press