VFP caching SQL query in SQLEXEC

[insania]

Hi,

Is there any way I can stop VFP from caching SQL queries with SQLEXEC? I am trying to time how long it takes an SQL query connecting to a PostgreSQL database via ODBC takes and really dont want to have to restart my pc every time I change a setting. Are there any general speed issues when using VFP with an SQL backend via ODBC?

Thanks, any help is appreciated

 

[bborissov]

Most likely not VFP caching your query, but PostgreSQL. And this is normal behavior of all RDBMS. VFP can cache only native queries, but not these you send to remote DB.

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

 

[insania]

Thanks but I am sure its VFP. After each time running the same query I restarted the postgres database, but the runtime figures I was getting suggested VFP was caching the data, so I restarted VFP after running the query each time aswell. My figures jumped 6 fold, which definitely showed that VFP was caching the database query somehow.

Thanks anyways

 

[Mike Lewis]

Insania,

What exactly do you mean by caching?

When you call SQLEXEC(), VFP sends a query to the back end. It receives back zero, one or many result sets. The query itself can't be cached. It's just a short character string.

Perhaps you mean that the result set is cached in some way. But I don't see how that could be possible. The whole point of SQLEXEC() is that it retrieves the latest data from the server. It would only make sense to cache that data if VFP had some way knowing when it was out of date. The only way to do that would be to fetch a new result set - which would defeat the idea of caching.

I'd guess that the big increase in execution time you saw when you restarted VFP was caused by something other than the query. One possibility is that, after restarting, you issued SQLCONNECT() again, to re-establish the connection to the back end. SQLCONNECT() has quite a lot of overhead, so maybe that's why you saw a jump in execution time.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[insania]

Thanks, Mike I shall have a look at this soon.

Would issuing an sqldisconnect at the end of the code force a re-establishment to the database?

So I could restart the database between tests and as VFP has disconnected no data should be cached anywhere and it should take the same time for each test? Does this sound logical?

insania

 

[Mike Lewis]

Insania,

Would issuing an sqldisconnect at the end of the code force a re-establishment to the database?

No. You would have to explicitly call SQLCONNECT() to re-establish the connection.

So I could restart the database between tests and as VFP has disconnected no data should be cached anywhere and it should take the same time for each test?

I'm not sure about "restarting the database". Is that something you do within Postgress?

You might like to consider the following:

- Call SQLCONNECT().

- Start timing.

- Send the query and get the results back.

- Stop timing.

- Start timing again.

- Send the query again and get the results back.

- Stop timing again.

I would expect the first and second timings to be more-or-less the same. If the secone one is very much shorter than the first, it would be because Postgress is doing some sort of caching, not VFP.

I hope this helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[insania]

Thanks Mike, I shall definitely give that a go. If I were to do two tests like this where I followed the above method for one then did:
-start timing
-call sqlconnect()
-send query
-stop timing
-start timing
-send query
-stop query

would it show if sqlconnect is the reason why its taking so long?

And yes you can restart postgres as it runs like a service, i think mssql dos this also but im not sure

Thanks anyways

 

[Olaf Doschke]

Insania,

if you do:

-start timing
-call sqlconnect()
-send query
-stop timing

How would you know which portion of time the connect takes and what portion the query takes?

You may also time the connection itself, and then have every figure:

-start timing
-call sqlconnect()
-stop timing
-display time taken

-start timing
-send query
-stop timing
-display time taken
...

Bye, Olaf.

 

[Mike Lewis]

Insania,

would it show if sqlconnect is the reason why its taking so long?

It wouldn't specifically show that. But if you found that the first timing was high in comparison to the second, it's likely that SQLCONNECT() is the reason. In general, there's a fair amount of overhead with SQLCONNECT() (which is one reason that some programmers like to create the connection at the start of the process and keep it open throughout).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[insania]

Thanks to everyone, But it seems it may have been a network problem or my computer was ready was for a holiday. I havent touched the script since before christmas but now its decided to run in just over second when it took 12 before! Slightly confused but I hope to figure it out soon!

Thanks again