Hi Gar & Dove,

I need to explain a little more about the problem. I select records from one Table (SELECT * from OwnrAddr where somecondition...). The empty shell table contains all fields from all our tables (~200 fields). So, I'm afraid, I can not create an empty table from my Selected table. I can (and should) add indexes after appending. I'll do and see, how it improves the situation.
There is another problem. First, we append the records, and then we assign recnum=recno() in replace all command. I was thinking, that may be instead of

APPEND FROM and then REPLACE ALL better use
SCAN .. ENDSCAN and inside do SCATTER MEMVAR and INSERT INTO TargetTable FROM MEMVAR. But this technique could have some problems too.

Anyway, this is not my code, so I may leave this problem to my colleague to decide, which technique is the best.


>>Hi everybody,
>>
>>We're having this problem: our SQL produces ~1000000 records. SQL works fast. Then we need to place this records into Database with predefined structure, indexes, etc. So, firstly it prepares a shell Database and a table and then uses Append from command (runs DeNull program in between). This operation takes already ~15 min. and it's not finished yet. What can you suggest to optimize it?
>>
>>Thanks in advance.
>
>Hi Nadya
>
>Try this without building the indexes first. Appending to an indexed table takes longer than to one that is not indexed.
>
>If this proves to be the problem, try indexing afterwords. If this does not save you any time, try seeing if you can eliminate some indexes.
>
>If populating the table still takes forever, even without prebuilt indexes:
>
>I note that you are placing it into an empty structure. This suggests a simple solution. In your SQL statement, make sure you have all field names and types Null/Nonull , data conversions exactly the way you need them -- so tht your SQL results essentially need nothing done. Look up the CASE statement in SQL to find out how to get rid of NULLs and do other types of conditional conversion. Make sure your where clause extracts only the data you need so that you do not end have to do a conditional select against this query result.
>
>Now create empty database, and make it the default. But do not create the empty table.
>
>To create your table:
>
>SELECT * FROM QueryReSult INTO TABLE TargetTableName DATABASE shell_database_you_just_created.
>
>This should go pretty fast.
>
>Now index the table. Warning: As I said above, this may take much longer than building the table. On a large recordset like this, you may want to see if you can keep your indexes to a minimum.
>
>Let me know if this helps.