Bad Queries

[GregTomkins]

IMO, approaches of trying to find problems based on XREF or looking at source can be non-productive because there are lots of cases where CAN-DO or USE-INDEX causes whole index reads ... or some other heinous thing ... which don't actually matter, because the tables involved never have more than a handful of records in them. Or, the problem is in code that hardly ever runs, or runs in a non-interactive way where the difference between a 1-second operation and a 1-minute operation is often irrelevant.

Of course, we should code properly regardless, but in the real world, with 1000's of .P's and 10's of programmers over 10's of years, it's more useful (IMHO) to concentrate on statistics and real-world measurements, eg. logging of the performance characteristics of your application using VST's and such.

Eg. in our case, and I expect this is typical, about 5% of our code handles 95% of our users' activity. So, we concentrate on identifying that 5% and fixing them. At least, that's the theory

 

[Cringer]

Thanks Greg. That's a good point. At the same time (IMO) developer testing of code should go deeper than just a few button clicks to check it does what you expect, and should contain at least some analysis of the queries to check that they aren't doing anything surprising behind the scenes. It shouldn't add much time in most cases. That's what I'm hoping to convey in my talk.

 

[TomBascom]

I agree. I even have a few presentations on exactly that topic

Mostly.

But there is a lot to be said for forming good habits and paying attention to obvious issues.

Sure, most CAN-DO() usages are of trivial significance. And many instances of WHOLE-INDEX are not causing an actual problem. But that doesn't mean you should just code whatever crazy WHERE clause works with your development database. A few very simple checks and some basic standards right up front can save you from a world of hurt chasing red-herrings later on.

 

[TheMadDBA]

Even when developers see the table and index activity... quite a few of them are missing the perspective of what is good or bad. Usually a lack of understanding of the data and the application. Sometimes even not understanding how fast a application function should run.

Reading 100,000 records may or may not be a bad thing, depending on what you are doing. I have had some success in getting them to compare the tablestats to the records actually returned/processed but even then it is hit or miss from developer to developer.

But at least if they are looking they can catch some of the truly awful things and I try and make sure they attach the monitoring output files to the task. So if something slips by or they aren't doing performance checks we can deal with that afterwards by more training or manager pressure.

 

[Rob Fitzpatrick]

Another topic that has been discussed a lot lately, but it's still critically important, is index selection and bracketing. Developers need to know these rules cold and in some cases they don't. (I could have phrased that differently but I'm feeling generous today. )

The docs and examples could be better, certainly, and some rules aren't even in the docs, but you have to know them to write good queries.

 

[TomBascom]

"The rules" are very obscure beyond the first one.

But any developer who cannot demonstrate mastery of "the index with the most equality matches on leading components wins" should certainly be looking for work in a new career.

 

[Rob Fitzpatrick]

Off-topic:
I'd like to see PSC rewrite the Triggers & Indexes manual. And permit TABLE-SCAN for dynamic queries.

 

[TheMadDBA]

How about a nice cost based optimizer with server side partition scans and joins?

 

[Rob Fitzpatrick]

I'm asking for improvements, not miracles.

 

[TheMadDBA]

Many times they are one and the same...

 

[Cringer]

And permit TABLE-SCAN for dynamic queries.

Too right!

 

[Cringer]

That is very true Greg. Especially important to catch as many as you can during development and testing.

James. There is a new release of SessionTrace that supports conditionally turning on tracing based on a config file with options for filtering by user and program and only output a file based on reaching a specified elapsed time or total activity.

I think an option for specific tables and indexes would be nice too.

Keith, have you managed to document all the different options at all? I can see some of them mentioned on your website but not all.

 

[TheMadDBA]

Let me know which ones are missing or you have questions about. I will try and carve out the time to correct it.

Thanks

 

[Cringer]

Not any particular ones just wondering if I've missed any niceties along the way.
So far got JSON and HTML output,
Config file stuff
Custom Debug stuff
Session vs DB usage stats
Use profiler or not

Think that's it.

 

[TheMadDBA]

I think that is pretty much it for now.

The recent additions were the batch config file options and the custom debugging info.

 

[Cringer]

Smashing. Thanks for that. The custom debugging is a really interesting addition. I like it. Was playing around with it last night. Mocked up some code that garbled user input in certain circumstances and resulted in some horrendous queries. Worked a treat.

 

[TheMadDBA]

Glad to hear it

For interactive testing I don't know if the debugging info will add much value. But when you are using the conditional config options it can be really useful to get the query prepare and any relevant information from background jobs or appservers.

It would be interesting to integrate it into your appserver connect/disconnect procedures for those async appserver calls. Set the thresholds high enough to only output on long running/excessive activity and find out what is falling through the cracks. Without profiling the overhead should be very minimal.. at least minimal enough for dev/test environments.

 

[Cringer]

Yeah that's a thought. Certainly some mileage there. Just got to find time to have a play with it.

 

[ForEachInvoiceDelete]

Pretty off topic but did anyone else who used the _Lock table have to rewrite bits of code for 11.5? They changed how the _lock table was sequenced and it screwed all my optimisation/Debug tools.

Gits.

 

[Cringer]

Yes there's been some discussion along those lines, although the general rule is that you shouldn't be querying _lock anyway. I'll try and find a thread.