Koen,
The SSMS (or whatever it’s called) has some really good tools to analyze the queries and suggest improvements. I haven’t worked with it much in the past 10 years but I worked on a rewrite project with MSSQL for a while 10+ years ago and found the query analyzer an excellent tool to analyze the queries. If I remember correctly you can grab the queries coming in from the ODBC layer in the IDE and then just copy/paste into the analyzer and fire it off.
Arnor, yes SSMS has some good tools. You can also just use the EXPLAIN keyword when copy-and-pasting commands.
This is so useful i added support for it to the new SQL drivers. Set
customers{prop:explain} = true
Before the SET or whatever and it’ll do the EXPLAIN for you and send the server output to debugview.
Hi Bruce,
Thank you for reminding me of EXPLAIN - I thought there was a keyword for it, but it’s been over 10 years and I’ve slept since then Very cool that you have implemented that in your SQL drivers! I recall some processes and reports that were taking a long time in this MSSQL project and it didn’t make sense why they were so slow until I ran the query through the analyzer and realized that there was a missing index.
Hi Arnor and Bruce,
Thanks for the ideas. I will try those tools next week.
Not specifically mentioned yet, but One BIG advantage of stored procedures I found in PostgreSQL is using INSTEAD triggers. This allows a Clarion app to simply call insert/update/delete for a view record and then have PG write to tables within the view as needed. This eliminates the need in Clarion to think in terms of the multiple table record buffers for a view.