|Mich Talebzadeh, Site expert|
Trouble truncating tables? Group permission getting you down? Procedures putting your sanity to the test?
Never fear, a Sybase expert is here! Go straight to the source with the chairman of the Sybase Future Forum, Mich Talebzadeh, who specializes in creating database architectures for large global trading systems involving heterogeneous databases.
In this tip, Talebzadeh gets back to the basics, describing the differences between the drop and truncate table commands, as well as giving advice on which pre-compiled procedure is quicker.
What's the difference between a stored procedure and a view, if both are pre-compiled objects? Which of the two is faster? Doesn't compiling make things quicker?
Mich Talebzadeh: On the face of it, one might think that a view and a stored procedure were equivalent to one another. After all, a view returns a result set, and a stored procedure can also return one. But that's where the similarity ends.
A view is really nothing more than a virtual table. It looks like a table, behaves like one, but only exists in a compiled form. In effect, the table is in substantiated when the view is referenced. You can refer to this virtual table (the view) in any query where you could refer to a "real" table. There are some restrictions on this, especially with regards to updating via the view.
A view cannot take parameters. However, it can be used as a security mechanism without giving permission to the underlying tables. More importantly, you can use the view to BCP out a subset of data from the underlying table or tables.
A view is a very handy mechanism for insulating the complexity of the database. A view can be constructed which hides the details of normalization. For example, you can create a view that allows the data consumer to see information while hiding the technical details of the normalizations that may have been applied to the underlying data tables. Thus, the consumers can be presented with a view of the data which hides the complexities of the JOIN operations required of the underlying data tables, instead presenting a "flatter" view of the data that is easier to understand.
When a query containing a view is processed, the view definition is incorporated into the query plan and this is how the virtual table is materialized. Therefore, explicitly coding the view or referring to it is equivalent, performance-wise. If you have a complex set of JOINs and you use that set often, it may be better to code them as a view, ensuring that all uses of the data perform the JOINs in the same way.
It is the queries that are compiled into an execution plan, and then the plan is cached. If another query comes along that can use the same (cached) query plan, and nothing significant has changed since the plan was compiled, the plan will be re-used.
This simply avoids the cost of compilation of the query plan. "Compiling" a plan doesn't make the query run faster - every query has to be "compiled" before it can run. It's just that circumstances may allow the compilation effort to be avoided on subsequent executions. Compilation can be a major process, as the optimizer which is responsible for creating the plan will try very hard to come up with the most efficient plan possible. Really complicated queries may convince the optimizer that it is worth it to spend more time trying to find the best plan, so compilation time goes up.
It is true that using more stored procedures will likely result in a more efficient use of the cache and that may explain why, in general, a stored procedure execution seems to be faster than using a view returning the identical result set.
What is the difference between the drop and truncate table commands?
Talebzadeh: The drop table command drops the table, including the table definition and the associated objects (rules, indexes, constraints, triggers, primary key and so on). Obviously, once a table is dropped all the data rows contained in the table are also removed.
A truncate table command removes all rows from a table. The table structure and all the indexes continue to exist until you issue a drop table command (as discussed above). The rules, defaults and constraints that are bound to the columns remain bound, and triggers remain in effect. The truncate table command also de-allocates the distribution pages for all indexes.
The truncate table is equivalent to, but faster than, a delete command without a where clause. Delete removes rows one at a time and logs each deleted row as a transaction; truncate table de-allocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes. Only the table owner can truncate a table.
I can truncate a table only if I'm the owner of the table. So how can this ownership be changed to give a group permission to truncate a table?
Talebzadeh: Because the deleted rows are not logged individually, truncate table cannot fire a trigger. Since it is a non-recoverable command, then only the owner of the table can truncate the table. Normal users cannot truncate the table.
If you have an application which requires a table to be truncated after certain operations by a user other than you, you may want to consider using a temporary table in tempdb (the so-called #table) for this purpose. If the table is a staging table in the database and has to be there all the time, then delete must be used. The problem with deleting all records is that it can cause the transaction log of database to fill up. For this reason, you may consider looping through records in a batch size, of say, 10,000 records, at a time and commit, afterwards, as the following example shows:
set rowcount 10000
while exists (select 1 from table_a)
delete from table_a
if @@error != 0 or @@transtate = 3
print "ERROR : delete of table_a failed "
if @@trancount > 0 rollback tran
You mentioned creating a temp table. How do I call another stored procedure's temp table on execution?
Talebzadeh:The temp table (# table) is created by the first stored procedure and needs to exist before the second stored procedure calling it can be created. As a rule, all #tables referenced in a stored procedure must exist at the time of the creation of the stored procedure. Otherwise, the stored procedure will not be created. This is irrespective of which stored procedure creates the #table initially.
Here is the easiest way for you to create these objects in sequence during the same session:
Don't forget that the #table must exist before either the first or second procedure can be created. It's also a good idea to ensure that the first procedure exists before the second procedure is created; otherwise, you will get a warning.
This was first published in January 2006