HELP

[xk120]

Can anyone explain why the cursor created by a remote data view is 275M in size
when the table that results from a query of that view is only 6.1M. Next how can I
close this 275M cursor after the query builds the table without closing the project? [sig][/sig]

 

[gcole]

You could try purging the table before you build the query. The Purge command cleans out empty space from deleted records. [sig][/sig]

 

[TomasDill]

gcole, the purging command is PACK [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[dgrewe]

xk120, can you post the SQL code here. [sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[xk120]

Here is the SQL select statement that builds the view:

SELECT *;
FROM DSSCOM.TRANSACTION_DETAIL Transaction_detail;
WHERE Transaction_detail.CATEGORY_ID = 'X1';
AND Transaction_detail.INV_PROD_FAM_CD <> 'D1';
OR (Transaction_detail.CATEGORY_ID = 'C1')

(This statement creates a 275m tmpfile)

Here is the Query SQL statement that build the Table:

SELECT Ship_to_customers_a.name1, Ship_to_customers_a.customer_group,;
Transaction_detail_a.product_id, Transaction_detail_a.config_code,;
Calendar_a.mth_name, Transaction_detail_a.order_no,;
Transaction_detail_a.order_int_line_no,;
Transaction_detail_a.sched_line_nbr, Transaction_detail_a.seq_nbr,;
Transaction_detail_a.category_id,;
Transaction_detail_a.inv_item_group,;
Transaction_detail_a.inv_prod_fam_cd,;
Transaction_detail_a.qty_backlog_scheduled AS qty_sched,;
Transaction_detail_a.qty_backlog_allocated AS qty_alloc,;
Transaction_detail_a.qty_backlog_shipped AS qty_ship,;
Transaction_detail_a.qty_backlog AS qty_bklg,;
Transaction_detail_a.ext_cost_backlog,;
Transaction_detail_a.ext_price_backlog,;
Transaction_detail_a.req_arrival_dttm,;
Transaction_detail_a.req_ship_dttm,;
Transaction_detail_a.sched_ship_dttm,;
Transaction_detail_a.sched_ship_orig,;
Transaction_detail_a.hold_cd_count,;
Transaction_detail_a.hold_cd_text_values,;
Transaction_detail_a.unit_price, Transaction_detail_a.customer_po,;
Transaction_detail_a.trans_type,;
Transaction_detail_a.sold_to_cust_id,;
Transaction_detail_a.ship_to_cust_id,;
Transaction_detail_a.dss_last_update,;
Transaction_detail_a.date_booked,;
Transaction_detail_a.date_added_sched_line;
FROM issbu!ship_to_customers Ship_to_customers_a INNER JOIN issbu!transaction_detail Transaction_detail_a;
INNER JOIN issbu!calendar Calendar_a ;
ON Transaction_detail_a.fiscal_period = Calendar_a.fiscal_period ;
ON Ship_to_customers_a.cust_id = Transaction_detail_a.ship_to_cust_id;
WHERE Transaction_detail_a.trans_type = &quot;BACKLOG&quot;;
AND Calendar_a.begin_dt <= DATE();
AND Calendar_a.end_dt >= DATE();
ORDER BY Transaction_detail_a.inv_item_group,;
Transaction_detail_a.inv_prod_fam_cd,;
Transaction_detail_a.product_id,;
Transaction_detail_a.sched_ship_dttm;
INTO TABLE d:\project\issbu\tables\bklg_shipto_trans_daily_issbu.dbf

This statement creates a 6.1m table [sig][/sig]

 

[MikeFreeland]

Under the Project Menu, try &quot;Clean Up Project&quot;. I've had numerous types of files reduced tremendously with this operation. It packs the project itself and a load of other things which I can't tell. Can't hurt to do, and it may be useful.

How big is your source file? If your source file contains a load of deleted records which make it into the view, it may be useful to set deleted on, or include a &quot;for not deleted()&quot; in your SQL code to help reduce bloat. [sig][/sig]

 

[TomasDill]

Following are just a stupid questions/suggestions. Maybe one of them will give you answer. I ask them because I have no other ideas instead of something weird in VFP or something missed by you in the data queries though first query returns MUCH more data.

1. How you check that tmp file is exactly that file for remote view? I ask this because this file might not belong to view, it just might be something internal to VFP.
2. You meant you use 'remote view'. Which connection you use? SQL Server? ORACLE? ODBC to VFP database? Maybe this temporary file created by something other, for example, ODBC driver for VFP database?
3. How many fields you have in the first query? Are they different than for second query?
4. Did you tried to switch off optimization?
5. Did you checked just number of records? You queries look MUCH different, so I guess they return MUCH different data.
6. If you use connection to SQL Server, what is 'TRANSACTION_DETAIL' there? Is it view on SQL Server? If it is view, can you send SQL SELECT statement for that view on SQL Server?
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]