ADO question.

[SBTBILL]

I use ADO pass througs a lot and am wondering if anyone has some suggestions about optimizing. Don't want to switch to the VFP functions particularly now that I'll probably have to port the code to ACCESS or something over the next few years.

Example

lcconnstr="DRIVER={SQL SERVER};SERVER=GODZILA;UID=SALESMAN;PWD=MOTHRAATEGODZILA!!"
lcsql="SELECT ecommerce.dbo.orders.ordertypeid,"
lcsql=lcsql+" Ecommerce.dbrders.order_id as sono,"
lcsql=lcsql+" Ecommerce.dbrders.Order_Date,"
lcsql=lcsql+" Ecommerce.dbrders.ship_country,"
lcsql=lcsql+" Ecommerce.dbrder_Status.Shipping_Status_ID,"
lcsql=lcsql+" Ecommerce.dbrder_line_items.item_number, "
lcsql=lcsql+" Ecommerce.dbrder_line_items.quantity, "
lcsql=lcsql+" Ecommerce.dbrder_line_items.cost_subtotal, "
lcsql=lcsql+" Ecommerce.dbrder_line_items.qty_shipped, "
lcsql=lcsql+" Ecommerce.dbrder_line_items.pv_value, "
lcsql=lcsql+" ecommerce.dbo.ordertype.name as ordtype "
lcsql=lcsql+" FROM ecommerce.dbo.order_line_items inner join "
lcsql=lcsql+" ecommerce.dbo.orders on "
lcsql=lcsql+" ecommerce.dbo.order_line_items.order_id=ecommerce.dbo.orders.order_id "
lcsql=lcsql+" INNER JOIN "
lcsql=lcsql+" Ecommerce.dbrder_Status ON Ecommerce.dbrders.Order_ID = Ecommerce.dbrder_Status.Order_ID "
lcsql=lcsql+" left outer join "
lcsql=lcsql+" ecommerce.dbo.ordertype on "
lcsql=lcsql+" ecommerce.dbo.orders.ordertypeid = ecommerce.dbo.ordertype.id "
lcsql=lcsql+" where "+mcriteria
lcsql=lcsql+" ORDER BY Ecommerce.dbrders.Order_Date"
oconnection=CREATEOBJECT("ADODB.Connection")
oconnection.OPEN(lcconnstr)
rs=oconnection.execute(lcsql)

Then I read the record set into a DBF that I do all the work on or use as the basis of the report.

Bill Couture
http:\\

http://www.sbtbill.com

 

[bborissov]

Porting from VFP to ACCESS?
That is....like porting from VFP 9 to FP2.x

You could build a COM object with VFP and then use it as a Data layer. All data handling could be done from here. You could use CursorAdapter instead of get an ADO recordset and the transform it to VFP Table/Cursor.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Bill,

I use ADO pass througs a lot and am wondering if anyone has some suggestions about optimizing.

Optimising a query written in ADO is no different from optimising any other kind of query. Ultimately, the back end just sees a SQL statement. So your strategy should be to optimise the argument that you pass to the Execute() method.

Exactly, how you do that depends on the back end. But, in general, the usual guidelines apply. In particular, make sure the fields in your join and ORDER BY clause have suitable indexes. If the back end is VFP or SQL Server, the usual Rushmore optimisations will apply.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk