SQL Server Performance Forum – Threads Archive

Compare Table Datatype and Derived Table?

Hi; I have a query in which three tables datatype are being used in order to create three small table to join in a final query. I changed them into derived table in the final query and now I have only one but big query. My question is that which way is good with respect to performance. I saw the execution plan for both of them, the first case has four plans for four queries but in the second case it has one big execution plan. The execution time for both methods is same. I am not running this query on large amount of data right now. In future it could happened. I am looking forward for helpful suggestion and comparision b/w Table Datatypes and Derived tables. Thanks Essa, M. Mughal
Software Engineer
Canada

If you are sure the execution plans are the same, then the performance will be the same also.
Table datatypes might be more useful if it becomes difficult to access the data you need from the subqueries due to the structure of the query, but other than that I see no benefit in you using them

Thanks for reply. The execution time is same but the execution plan is different for both of the methods b/c the first methods has four plans for four queries and the derived table method has one big query execution plan. I am wondering if there are four execution plans for the first method in which I used Table Datatype , it will take time in future to deal with four queries and plans. Thanks. Essa, M. Mughal
Software Engineer
Canada

Sometimes is hard or impossible to force plan you think is the best just by using hints. Example I remember from this board is:<pre id="code">Select top &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />age size> <something> from <table> where col1 > <value1> or (col1 = <value1> and col2 > <value2&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> order by col1, col2</pre id="code">You want to use index on col1 and col2 but you can’t persuade query optimizer using hints and tricks to use index properly other then using union or table variable. I know I could find much more complex and better example, but right now I can’t recall any.

Sorry, disregard my answer. Id misread your question and was sure you had said the execution PLAN was the same. I still cant really think of any reason to choose one over the other though <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

When tuning SQL Server applications, a certain degree of hands-on experimenting must occur. Index options, table design, and locking options are items that can be modified to increase performance When running a test, be sure to have SQL Server start from the same state each time. The cache (sometimes referred to as the buffer) needs to be cleared out. This prevents the data and/or execution plans from being cached, thus corrupting the next test. To clear SQL Server#%92s cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Here is example of me not reading question carefully enough. I believe in most cases you will have the same performance using either approach. I think table variables must be equivalent to worktables produced by some execution plans. However that is just my theory, I haven’t found proof for that. When a query becomes pretty complex I tend to use table variables to make it more readable. Usually I don’t experience performance degradation or improvement using that method. As satya already mentioned in this case only testing both options will give you definite answer.

]]>