Thank You! Thank You! Thank You!
That is an extremely handy tool and I was slightly disappointed to find out
Quest was considering no longer developing it.
Roger A. Simoneau
Alberta Blue Cross
(780)498-8837
does this tool has the ability to optimize queries?
I could be of assistance if they decide to push this product to the OpenSource
community…
Anyone know where the source is?
merci
Martin Gainty
Note de déni et de confidentialité
Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l’information seulement et n’aura pas n’importe
quel effet légalement obligatoire. Étant donné que les email peuvent
facilement être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.
It just grabs queries coming from the local machine. If you want to tune them,
send it to toad/sql optimizer
提寺薩。
Oops.
This is a Quest owned product Martin, it won’t be open-sourced. The crew
heard the plea for an update & Sergey updated it. He’s a very smart dude, by
the way
Hey Martin;
The only thing it does is capture queries being sent by applications as Jeff
mentioned. For example, have you ever wondered what kind of query MS Access
sends to Oracle? You can find out and it’s not pretty.
The last time I trapped such a query, I concluded that using Access is safest if
you do not identify any primary keys on the tables. With primary keys, MS
Access sends a first query to get all the key values, then sends a query for
each value. Without primary keys, MS Access sends your query “as
is” and lets the database handle how to process it.
So… imagine for a moment you had a query:
SELECT fname, lname FROM employees WHERE lname = ‘Johnson’ OR lname
= ‘Smith’
Now imagine that lname is indexed. Your query is optimized as-is.
Let’s say you told access that the primary key on the employees table is
emp_id. MS Access will perform as follows:
Modify your query, and send the first query as:
SELECT emp_id FROM employees WHERE lname = ‘Johnson’ OR lname =
‘Smith’
Let’s say the emp_id’s for those two are 1 and 2 for the example.
MS Access with then form and send the following as a second query:
SELECT fname, lname FROM employees WHERE emp_id = 1
UNION ALL
SELECT fname, lname FROM employees WHERE emp_id = 2;
It will then present the final result to you.
With SQL Monitor, you can watch in shocked amazement as MS Access sends those
queries.
Additionally, MS Access is limited in how many queries it can send in the
union. 20 if I recall correctly, could be 10, I’ll say X for now.
Anything outside that constitutes another query. So if your result set
returned less then X number of rows, MS Access sends two queries, if it returns
X + less then X, three queries and so on. As a “fun” task, imagine
how many queries MS Access forms and sends to the database if your query returns
150 rows.
But… if you don’t tell Access what the primary key on a given linked
table is, MS Access sends:
SELECT emp_id FROM employees WHERE lname = ‘Johnson’ OR lname =
‘Smith’
Go figure.
Roger S.