optimization wish

[Ludek]

I relatively often need to open a TMSQuery and I know, it will be empty. The user appends then new records to it. Is it a way to open it WITHOUT sending aquery to sql server, just to make it empty, active and editable? I could make my product a lot faster.
I can't use TVirtualTable, as I need to send the appended records to database. And the same query can under other circumstances contain data right after opening.

Some method like "openempty" would make me very happy. And it should not be hard to realize it, I think...

Thanks, Ludek.

// edit: such queries DO have persistent fields

[Dimon]

If you need only to insert records in a table you can use INSERT statement without table opening.
But if you need to have an open dataset you can not do it without sending a query to SQL Server. You can use a simple query that will not fetch data from server, like this:
SELECT * FROM tablename WHERE 0=1

[Ludek]

I can't use INSERT, it is the user, who appends new records in a dbgrid. so i need an open query, as i need to have an editable tdbgrid.

I currently have a query with "0=1" and I will elimate it because of the poor efficiency - I have many of them.

is it really impossible to do? is it somehow because of oledb or why is it so? i don't use fetchall=false, i have no server side cursors, I have cachedupdates set to true (life cycle is empty query-> user fills grid -> applyupdates -> empty query etc.), really no special functionality, i just need to have an (sometimes empty, sometimes filled) editable dbgrid. and i need it fast...

I was thinking about dynamically switching TMSQuery/TVirtualTable, but it would make my source very hard to read. And the tvirtualtable does not have cached updates, onupdaterecord etc...

why do i need it: it is simple master/detail relationship. there are many details to one master. and i don't want detail recordset to send any queries to server on a call masterdataset.append - details are queries with complicated joins, subqueries etc. and their opening costs too much server time.

[Dimon]

You can not open dataset without sending a query to SQL Server.

If you use master/detail relationship try to use LocalMasterDetail mode by setting the TMSQuery.Options.LocalMasterDetail property to True. You can find more detailed information about this property in SDAC Help.

[Ludek]

Yes, I can do it, using your TVirtualTable component. But: I need sometimes empty query for appends, sometimes opened query for modifications, always in the same form, with the same "onvalidate" events etc.
Just a hybrid component, that behaves sametimes like a TMSQuery and sometimes like TVirtualTable+TMSUpdateSQL (limited to inserts, no updates or deletions).

I think, Local Master-Detail is bad idea - some customers have over 10 millions records in the detail dataset and I need only a few of them, for some master records. In most cases customers just append new data, only sometimes edit them. so i can't use local filtering. Or, at least, I don't know, how could I efficiently use local filtering, if the master and detail table have millions of records.

[Dimon]

If you don't want to use dynamically switching TMSQuery/TVirtualTable then you have just one way: use "SELECT * FROM tablename WHERE 0=1" query. Really this query is executed very quickly and returns empty dataset without any data.