Hi. I'm running ADS API within a VFP application.

In the following SQL, indexes exist on unitid, datetime, l.id, d.id, inseq, oparid and parmid.

Within the ADA, the following SQL runs within about 1:07. I've tried other permutations of the statement, but this gave me the fastest results. It returns 655767 records.

SELECT l.unitid , l.id AS [logid], datetime, opid, tothrs, 
       p.groupid, p.inseq, p.name, d.id AS [dataid], d.value, d.alarm
FROM log l
LEFT JOIN parm p ON p.unitid = l.unitid
LEFT JOIN logdata d ON d.logid = l.id AND d.parmid = p.oparid
WHERE l.unitid = 2 AND l.datetime between '2011-01-01 00:00' and '2013-01-01 23:59'
ORDER BY l.unitid, l.datetime, p.inseq

But using ADS API from within VFP and the following Statements, the exact same query takes 3:18. Why the significant difference?

result = AdsCreateSQLStatement(inConnection, @locHandle)
result = AdsExecuteSQLDirect(locHandle, locSQL, @ioHandle)

Thanks.

asked 21 Oct '14, 08:23

gverge393's gravatar image

gverge393
126111118
accept rate: 100%

edited 21 Oct '14, 09:55

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185


I have some questions so this answer may not necessarily be applicable. If not, please comment and potentially edit the question (leave a comment if you edit otherwise it won't notify)

First, is ADA = Advantage Data Architect?

Second, when you are timing, is it just the execution of AdsExecuteSQLDirect or are you including filling an object / grid?

I'm assuming that ADA is Architect (ARC). Typically the difference in type is because with ARC it executes the statement and grabs enough rows to fill the visible grid then returns while the actual execution may still be occurring at the server. Other applications may actually end up reading all rows before returning.

You can simulate returning all rows in ARC by executing the query then, once it returns, scroll the data table to the very bottom (or CTRL-END) and see how long that returns as well.

I'm not that familiar with coding in VFP. But this KB article may be helpful? 090909-2202

link

answered 21 Oct '14, 09:54

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
accept rate: 25%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×172
×39
×24

Asked: 21 Oct '14, 08:23

Seen: 1,610 times

Last updated: 21 Oct '14, 09:55

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.