Does the remote data tab affect SQL db connections?

[jennyflower]

Hi, I was just wondering this because I ran a test to an sql database with the default settings for sqlsetprop on my connection, I then swapped the values around ie if there was a rue value I gave it a false value, and I still got the same time for the query to run.

So basically do the settings in the remote data tab and sqlsetprop affect the connection in any way what-so-ever?

If anyone knows in more detail than the help guide how each option does that would also be cool.

 

[Mike Lewis]

Hi Jenny,

You need to be more specific.

First, are you talking about the Remote Data tab in Tools / Options? If so, which setting did you change from True to False?

In general, the Remote Data tab in Tools / Options define the default values for new remote views or connections. If a view or connection already exists in your DBC, it will retain the settings that were in force when you created it.

You say that you changed a setting, but that didn't affect the query time. There are many reasons for that, and many factors involved.

Perhaps you could give some more information about exactly what you are trying to do.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[jennyflower]

The settings I used intitally were:

asynchronous: False
batchmode: True
connecttimeout: 15
displogin: 3
dispwarnings: False
idletimeout: 0
packetsize: 4096
querytimeout: 0
transactions: 1
waittime: 100

The settings I used next were:

asynchronous: True
batchmode: False
connecttimeout: 30
displogin: 1
dispwarnings: True
idletimeout: 15
packetsize: 8192
querytimeout: 15
transactions: 2
waittime: 50

These are the only settings I changed within vfp and also with the database configuration settings. As there was no change in the time for the query I thought they didn't have any affect on what I as doing. I always use sqldisconnect aswell.

Thanks for any help you can give.

 

[Mike Lewis]

None of the settings you listed are likely to affect the time it takes to run the query.

As I mentioned before, it would help if you could be more specific about what the problem is. Is your goal to speed up your queries? If so, there are better ways to go about it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[jennyflower]

Yes, I definitely need to speed up the queries. I have a test query which brings 10 fields each from two tables which are joined. The tables have 86000 and 260000 records, 200 and 150 columns and 17 and 22 indexes respectively. The query I run generates 517 results and takes on average 45 seconds.

The query includes a distinct clause, a join, 7 where clauses and an order by.

I am hoping to try and get this figure down to at least 15 though.

Thanks

 

[Mike Lewis]

In that case, I'd suggest you forget about tweaking the settings in Tools / Options (at least, for the time being), and conentrate on optimising the query.

The first thing you need to do is to let us know which back end you are using. How you go about speeding up a query depends very much on the back end in question.

You also need to determine whether the delay is being caused by the back end or by VFP. Has the back end got a console or command window that lets you run the query directly (for example, SQL Server 2000 has the Query Analyzer)? If so, run the query there and compare the time with running it from VFP. That will at least tell you where you need to focus your attention.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[jennyflower]

The back end I am using is PostgreSQL (free for me!). I have run the query in a query window which does not go through odbc or anything like that and it still takes 45 seconds to run. So I guess my attention must have to be on the query and database side of things

thanks

 

[Mike Lewis]

Jenny,

There's a Postgress forum here on Tek Tips. The folk there might be able to help you.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[jennyflower]

Well thanks for the help, at least now I know it wasn't VFP.