Using SYS(3054)

[jjjt]

Hi,

I have some existing VFP7 code that features a lot of SQL queries and I want to identify any queries in that code that are not Rushmore optimised. I have figured out how to use SYS(3054) to return info on an individual query. However, with my current understanding, I am thinking that I am going to have to enter a SYS(3054) statement in before every SQL query and store the results after each query has executed.

Given the amount of code, this is going to be a time consuming process, so I was wondering if there was a quicker way of achieving the same result, namely a list of all queries that are not Rushmore optimised. An ideal solution would also indicate where in the code the non-optimised query appears.

Any help is much appreciated.

Jake

 

[Mike Lewis]

Jake,

I don't know of any way of getting single list of all non-optimised queries. However, I wonder if that's really what you want.

If your aim is to get rid of the slow parts in your app, your first step should be to figure out where the slow parts are. You can do that with the coverage profiling tool. If you run your app against that tool, then search the results for SELECT statements, you will be able to see how long each one is taking.

The coverage profiler is far from ideal, but (unless someone can come up with a better idea) it should get you started.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mike Gagnon]

jjjt

You may also want to use the SET COVERAGE TO, to create a coverage log that would indicate where your application "bogs" down. Take a look
[ignore]

http://msdn.microsoft.com/library/d...elp/html/newcoverage_profiler_application.asp

[/ignore]

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jjjt]

Mikes,

Mike L is quite right, we have been tackling parts of our app that are running slowly. What is happening is that users experience the system running consistently slowly at a certain point and report the problem. That gives us a good idea of more or less where the problem lies in the code. We have then been using the coverage profiler to pin point the exact lines of code that are causing the problem. What we have found is that often the problem is coming from a query that is non-optimised. Changing the query sorts the problem out.

What we are finding is that testing is not always picking up the slow queries. This is then compounded by the fact that users are not always reporting the speed issues for some time, putting it down to hardware or network traffic, etc. Therefore, rather than waiting for the next speed problem to arise, we were hoping to be a bit more pro-active and identify problem queries in advance.

It would be nice to be able to go with 'If it ain't broke, don't fix it', but then if a program grinds to a halt in the woods and there's nobody there...

Jake

 

[Mike Lewis]

Jake,

I guess another thing you could do is to look for all the queries that have a WHERE, ORDER BY or GROUP BY clause (OK, maybe that's all of 'em), and check the conditions against a list of your indexes. If a field does not have a corrsponding index tag, that would suggest you should look a bit closer.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jjjt]

Mike,

I was thinking along similar lines. Thanks for your input. On the subject of thanks, I'm fairly new to forums and I'm unsure about etiquette. I realise that you can give stars for useful feedback. When I have a question and I am searching to see if it has already been asked, I often get several results returned, all looking like they might be possible candidates. If a post has a star against it, that suggests that the question has a solution. For that reason, I would only give a star if my question was resolved.

However, there are instances, as with this question, where we have not managed (so far) to come up with a solution, but the discussion has been useful and the input appreciated. My question (finally) is which is more annoying:

1) Not to be thanked for your input?

2) To receive email notification of a response, only to log on and find that it just says "Thanks for your input"?

Jake