Duplicating ClientDataSets on a slow connection

I am using SQLConnection, SQLTable, DataSetProvider, ClientDataSet, DataSource method of displaying contents of a MySQL database on forms.  

In some cases the same table needs to be viewed simultaneously (but displaying different Rows) using different DataSource's.  The only way this seems to work for me is to use the same SQLConnection, but duplicate the SQLTable, DataSetProvider, ClientDataSet, DataSource components for each of the "Views".

The MySQL database is at a remote location - when activating these components it can take ten minutes to login due to the size of the tables that need to be made available for viewing.  Once loaded speed of scrolling and searching is perfectly fine.

Is there anything I can do to speed up the loading process?
LVL 31
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you populate the TClientDataset with a SQLquery instead of a SQLtable?  That might limit the rows being sent from the server.
moorhouselondonAuthor Commented:
I see that a DataSource can connect into a SQLQuery, which in turn connects directly to the SQLConnection, but this then prompts this question:  

If I have a DbGrid populated in this way (showing a subset of the table), and I want to Insert a record into the underlying Table, how will I be able to ascertain what the last record in the table is (say it's an Invoice table) and I want to generate the next Invoice No?  

Presumably I have to do this using the full SQLTable, Provider, ClientDataSet route, then refresh the Query to display that record in the Grid?

Excuse my ignorance on this, I picked up lots of bad habits coding using the BDE, and having moved away from the BDE am now trying to clean up my act.
* if working disconnected from the main database, you could use GUIDs as a key field.

* add the record to the server and requery

* use a two column identity, with one column being a unique client name
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

moorhouselondonAuthor Commented:
I have just chanced upon this article (whilst looking for something else - as is often the case).  Haven't had chance to wade through it yet... bear with me for a couple of days whilst I do.  In the meantime, any thoughts on Jensen's approach would be appreciated.

moorhouselondonAuthor Commented:
At the moment I have

SQLConn1 <- SQLTable1 <- SQLProv1 <- CDS1 <- DS1
SQLConn1 <- SQLTable2 <- SQLProv2 <- CDS2 <- DS2
(where SQLTable1 and SQLTable2 point to the same SQLConn1)

Am I right in thinking that using Jensen's technique SQLTable2, SQLProv2 are redundant.  CDS2 is plonked onto a datamodule, then defined without any parameters, then programmatically I would say

CDS2.CloneCursor(CDS1, True);

to duplicate the definition of CDS1?

So my Datamodule will contain the following components:-

SQLConn1 <- SQLTable1 <- SQLProv1 <- CDS1 <- DS1
CDS2 <- DS2

Is this correct?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moorhouselondonAuthor Commented:
Looks like it works, but crashes when I shut-down, presumably because I need to somehow free up the Clone operation.  Tomorrow will look at that...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.