Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Preview 1

Status
Not open for further replies.

zubby

Programmer
Feb 16, 2001
4
US
Is there a way to see the exact SQL the database will use before performing an update? For example, can you control if the database will issue an update or a delete and insert?
 
Why this matters to you? Or I am just a moron that I didn't understand your question.
 
When using ADO, and writing an update statement, EXAMPLE: Update a_table Set a_column = :a_value Where a_keycol = 'XYZ'. Before the update statement is executed, can I see the update statement that the DATABASE is going to perform, NOT the update statement that I wrote?
 
The only thing you can do is, if you are assembling the update query into a string, you can display the string and see what it looks like. Rest assured, the database is going to execute the query that you have constructed and nothing else.
 
zubby -

Look in your database documentation for "Show Plan". This should tell you what the database engine's execution plan will be for the SQL you specify. MS SQLServer 2000 has a graphical display of this in the Query Analyzer.

This will allow you to tune your SQL to avoid expensive an table-scan (read all rows in a table) on a large table. Tables with fewer than 50 rows are usually OK with a table scan (not that expensive in I/O cost).

What you want is to design your query and your database so that about 80% or more of all queries use an index on the larger tables. This may involve changing your SQL, adding indexes, or restructuring your tables and primary keys.

Chip H.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top