Sorry for taking so long on this, but I had to go out and do some gardening, which I hate, or stay at the computer with death as a possible outcome. I think I'd not enjoy that a lot either.

Anyway, back to the issue at hand.

Ah, would that life were that simple. Unfortunately, the view may start out simple, but at it's worst, it would look like this (paramterized);

Select * from ctracts where contractno = ?cgContractNo and ;
contractrcd = ?cgContractorCd and ;
ctractid in (Select ctractid from ctgrtees where guaranteeno = ?cgGuaranteeNo) ;
and ?cgTextDesc $ Upper(descrip)

In fact, the sql is not really parameterized in what I'm doing. The sql statement is created from information entered by the user into 4 fields on a filter screen. I hate to think how slow navigation in the system would be if I set that up as a filter rather than an sql statement.

Then it has to be indexed so that the user can set the order of records. That takes no time at all.

I realize the idea is still the same, and I can create the view ahead of time like that, but I was creating it each time the filter information was changed by the user. The bottom line though, is that one way or the other, it still has to be requery()'d, and that's where the snag is, not in the creating. Creating the view only takes a very small fraction of a second. But the "requery()"! It takes .44 seconds in the command window in it's simplest form (see below), and 8.99 seconds in the exe, also in it's simplest form.

If it were just a question of 'create on the fly' or not, then there would be no issue. The problem is in the requery() time required.

So; even using your code, I still have the requery() statement, and I still have the same time issue (I have tested this with the simplest query pre-created, allowing the datasession in the blank form to do the work).

All considerations of pre-create, or on the fly aside for the moment, my question is still why does it take approximately 20 times as long from the exe compared with doing it from the command window.

Is it simply that the runtime is horribly slow compared with the IDE? I've never run into this problem before, so I suspect that's not the issue. But should I be looking for something odd about the data maybe? Something that the runtime doesn't like that the IDE simply adores?

Any clue would help.

Alan


>Then you need to create 1 parameterized view:
>
>Create Sql View v_contracts as select * from ctracts where somefield = ?cSomeValue order by fieldx
>
>Then when the filter criteria changes:
>
>local cSomeValue
>cSomeValue = "X"
>requery('v_contracts')
>
>When you first open the view, do so with the nodata option:
>
>use v_Contracts nodata
>
>Or, if you put the view in the DE of the form, set the NoDataOnLoad property to True.
>
>
>>I take your point. The specs call for constant filtering. When the app starts up, the data must be filtered, and the user can change the filter on demand. There is only one dbc shared on the network. Rather than setting filters, I thought I'd try recreating the view's select statement according the the filtering information. Doing it that way requires recreating it on each filter request. There are also a couple of other related views that are requeried depending on the information int the main view, but they are not recreated each time. This may well be the wrong way to go about it, but I figured it would be a worthwhile experiment.
>>
>>I'll probably go back and do it by setting filters on the view rather than this way, but for the moment, it still doesn't answer my question about why the same code in the exe takes 8.5 seconds longer than doing it from the command window.
>>
>>As a matter of fact, if I create the view and add it to the database, then let the datasession open it nodataonload, the requery after creating view indices still takes over 8 seconds to pull the data down. So either way, it causes the exe to start up extremely slowly for no good reason that I can see.
>>
>>Alan
>>
>>>I do not understand why you would need to create this view every time you start the app. Are you creating a user DBC on the local drive for all our views? And using a shared DBC on the server? Once you create the view, you should not need to recreate every time, and you would then eliminate any need to bpack the DBC. IMHO, this is all bad practice to do under runtime.
>>>
>>>>I finally give up. I have an application that, when it starts up, creates an sql view, and it opens the view reeeeaaal slow.
>>>>
>>>>It's a simple view - "Create Sql View v_contracts as select * from ctracts". I don't see how it could be any simpler. The data lives on the network. Also I should mention that I pack the database before exiting the application, so that when it starts up again, there are no deleted records in the database itself.
>>>>
>>>>If, from the command window, I type,
>>>>
>>>>

>>>>Set safety off
>>>>open database contracts
>>>>set database to contracts
>>>>create sql view v_contracts as select * from ctracts
>>>>set safety on
>>>>use v_contracts nodata
>>>>requery('v_contracts')
>>>>

>>>>
>>>>that last requery line pulls down the 10,000 records in .44 seconds.
>>>>
>>>>Now, if I create a completely empty form, and put those very same lines in the beforeopentables() event of the datasession, compile it into an exe and run it, that last requery line pulls the same 10,000 records down in 8.99 seconds.
>>>>
>>>>The code in the actual application has more to it, but for the purpose of testing, I did the above and kept it as simple as I could.
>>>>
>>>>Can anybody give me a clue what I should be looking for as to why an exe might take so much longer than the command window to get the same records from the same table on the same network using the same code?
>>>>
>>>>Alan