Is a Temporary Table Really Necessary? - PART V:

The main key is for you to test alternative ways to determine if you can replace the use of a temporary table before you settle in a create one out of habit.

As you create your bag or tricks you will find yourself using temporary tables less and less and even find yourself disgusted at your coding abilities when you actually have to use a temporary table when you truly believe there is another way out there.If you use temporary tables optimize their use.If the situation mandates a temporary table then there are several things you can do to maximize their performance.

First, just because it is a temporary table do not be tempted to put all the columns and all the rows from your permanent table into the temporary table if you do not need them.Filter the data going into your temporary table to include the minimum number of columns and rows actually needed.

Second, do not use the SELECT INTO statement to create your temp table. The SELECT INTO should be avoided at all costs in your coding due to the locking it places on system objects while it determines how to build the table. Take the time to script the temporary table out and use a separate INSERT INTO to populate the table. I will qualify this with that you can use a SELECT INTO if it includes WHERE 1=0 to create a table in the quickest way possible, but don't do this just to save a few keystrokes.

Third, watch how you use temporary tables to avoid recompiles on the stored procedure. I explain this in getter detail in my article Optimizing Stored Procedure Recompiles available on my website. Fourth, test the need for a clustered-index on your temporary table. If the data set is large a cluster-index will speed the operations against the temporary table, but you have to weigh in the performance needs of creating that index and inserting into the table with a clustered-index.

This is one of those methods that needs to be tested both ways with the largest data set you think will be placed into the temporary table before deciding on the index.

And last, I know that when the stored procedure completes and the connection ends the temporary table will be dropped but why keep it around if you are done with it. If you code creates and uses a temporary table and then goes on to do other things that do not involve that table - drop the table when you are done.

This frees up tempdb resources for other objects. I will even drop the table at the end of a stored procedure even though the connection is about to finish just to avoid any issues that may arise with unknown bugs.

Is a Temporary Table Really Necessary? - PART IV:

How can you work around using a temporary table?

Now that I shown you several situations when you consider using a temporary table, lets talk about what you can do to avoid using a temporary table if all possible.There is a nice thing in the SQL world called a derived table that can be used to replace temporary tables in most cases.

Once again I'll get on my performance soapbox and say that sometimes with very large data sets, derived tables performance is considerably less than using a temporary table with an index. But for most cases simply using a derived table on a join will cut the need for your temporary table.

You can find several articles on the use of derived table at WWW.SQLServerCentral.Com so I will not go into detail on their use in this article.

If you are using a temporary table to stage data from several different sources either replace the temporary table with a UNION or create a permanent table to mimic the temporary one, both will usually satisfy your needs with reduced overhead. If you are operating on SQL Server 2000 and are using small data sets, try using the new table data type.

This will create a temporary table like object in memory rather than on the tempdb and improve the performance of your query.

Explore the use of a correlated sub-query and see if it can replace your temporary table. Sometimes just restating where your data is coming from will replace the need for temporary tables. Any one of these ways has been discussed as possible alternative solutions to the use of a temporary table.

Is a Temporary Table Really Necessary? - PART III:

One of the tricks I use is to mimic the main reason a cursor is usually built for, looping through a result set one row at a time and performing an action based on the data in that row.

Below is a short query that displays this logic by obtaining all the user table names and executing sp_spaceused on each table.

SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME

CREATE TABLE #tTables
(numID INTEGER IDENTITY(1,1),strTableName SYSNAME)

INSERT INTO #tTables (strTableName)
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'u'

SET @lngTabCount = @@ROWCOUNTSET ,@lngLoopCount = @lngTabCount

WHILE @lngLoopCount <> 0BEGINSET
@strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)EXEC sp_spaceused @strTabNameSET @lngLoopCount = @lngLoopCount - 1
ENDDROP TABLE #tTables
GO

Cursor-like actions without cursor overhead and performance related problems.

Is a Temporary Table Really Necessary? - PART II:

New Query (I usually name my temporary table after the stored procedure that created it so I can troubleshoot any problems in tempdb from the use of temporary tables faster.)

CREATE TABLE #tquery2a (multiplecolumns DATATYPES)

CREATE TABLE #tquery2b(mulitplecolumns DATATYPES)



INSERT INTO #tquery2a
SELECT columns
FROM dbo.table2
WHERE table2.strPhoneNumber = '5555555555'


INSERT INTO #tquery2b
SELECT columns
FROM dbo.table3
WHERE table3.strPhoneNumberCalled = '1234561234'


SELECT table1.numCustID, #tquery2a.strPhoneNumber, #tquery2b.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN #tquery2a #tquery2a ON table1.numBillID = #tquery2a.numBillID
INNER JOIN #tquery2b #tquery2b ON #tquery2a.numBillDtlID = #tquery2b.numBillDtlID
WHERE table1.numCustID = '5555'
ORDER BY #tquery2b.dtmCalled DESC


Believe it or not this method works, especially with the ORDER BY statement and its performance is vastly better than the original query.Reporting off an OLTP designed databases is not always the easiest thing to do.


The database is just built to maximize reports that executives want. Using temporary tables to stage the results from numerous SELECT statements, aggregate those results before displaying them is sometimes the only way to can get reports out of an OLTP database.


Working in a call center application you are usually asked to produce reports that summarize what the call center reps are doing on a time filtered basis. Working your way through all the tables to gather the data and then summarizing it in multiple ways can only be accomplished with the use of temporary tables.


Before any comes up with this argument: I know I work in a multi-billion company but that doesn't mean that executives are willing to listen to our arguments that they need a data warehouse or a simple reporting database if it means they have to spend money to get one when they can just as easy piggy-back off of the OLTP database and blame me if the queries are too slow and cause a performance headache for the servers. Sorry, that was for the theoretical guys out there who have magically gotten everything they wanted no matter the cost or the size of the companies they worked for.


The last argument for the use of a temporary table is to replace a cursor. I am not fond of cursors and advocate doing anything possible to replace the cursor (performance of your solution needs to be tested against the performance of the cursor though).

Is a Temporary Table Really Necessary? - PART I:

Why would you use a temporary table?

There are several reasons that I use temporary tables in my work; to hold the results of a called stored procedure, to reduce the number of rows for joins, to aggregate data from different sources, or to replace cursors.As your query become more complex you will find yourself repeating blocks of code within a query or between different queries.

This reuse of code makes the case for a creating a stored procedure with that code and calling that stored procedure. This may make for a large amount of stored procedures in your database, but it does greatly reduce the maintenance when the functionality needs to be changed and you have to change code to meet that functionality, only one query to change now and not multiple queries that have to researched and changed. I use this technique quite often and it often forces me to use a temporary table to hold the results of those stored procedures since Transact-SQL does not allow the results of a stored procedure to be used as a table.

This is probably the number one reason in my code for the use of temporary tables. I quite often find myself having to join a 10 million plus row table to a 100 million plus row table to a 20 million plus row table and then ordering the results so only the most recent activity displays first.

Even with proper indexes and using WHERE clauses to filter and force the use of an index, the performance of the query is unacceptable (since the application I work on is used by call centers, acceptable performance for a query is measured in seconds) and often the sorting produces huge performance losses as well as huge tempdb activity. I have quite often found that using corresponding temporary tables for each of the permanent tables to hold data from filtered by the WHERE clauses before I join and sort the data will increase performance on the query to such a large degree that I can actually place it into production without worrying about its performance or the impact on the tempdb database.

Below is a very simple query to show how I do this.

Original Query to find details on a particular customer's phone call

SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2 ON table1.numBillID = table2.numBillID
INNER JOIN dbo.table3 table3 ON table2.numBillDtlID = table3.numBillDtlID
WHERE table1.numCustID = '5555'
AND table2.strPhoneNumber = '5555555555'
AND table3.strPhoneNumberCalled = '1234561234'
ORDER BY table3.dtmCalled DESC

(This query does not match the schema or an existing query at Verizon. It has been created to show a particular problem with a hypothetical telecommunications database.)

Sybase Interview Question's - SET V:

1. Explain Stored Procedures on performance?Query optimizer generates a query plan for a st. proc based on the parameters parsed in the first time it is executed. To make st.prc to perform well, first the quires used in the procedure are to be checked. Also since it is compiled and stored in memory, procedure may become inefficient over time. Further execution used only if it is gain otherwise again the st. proc is recompiled.For example when it was first compiled, it may be fast as the table size is small and the table scan is better choice. But when the table grows into huge, index selection may be a better option than the table scans for that query. But the procedure will still do only table scan as it is pre-compiled, so as a general rule, if the table size changes by 20%, we have to run update statistics on the table and recompile the stored procedure. For stored proc in which, the parameter has the strong influence over the result set, we have to make it with recompile option, so that it compiles every time it is accessed.
2. What is sp_dboption?It is used to enable or disable db option for your database
3. Explain Denormalization techniques?Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently
4. Which is better Stored procedure or Trigger?1.If you intend to set or modify some column values in the proc/trig, a trig is the right place to do it. A BEFORE trigger can directly modify the row buffer before it is updated/inserted. A procedure would have to issue a UPDATE, thus a performance penalty.2. The trigger has all the row data automatically available to it. For the procedure to have the same information, you'll either have to pass all values to the proc via parms, or the proc will need to re-read the data, a performance penalty.3. If your application issues a single update, delete, or insert that modifies multiple rows, a FOR EACH ROW trigger will be fired for each row. It could be difficult to provide the SP the information that allows it to identify the set of rows that were modified.
5. I made two tables A B id1 age id1 age 1 Asked me to find out all the id1's which are in table A and select * from A where not exists(select id1 from B) do not exist in table b
6. How did you know the index was the cause of the performance degration?
7. Compare left and Right Outer Join?For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL
8. What is Join and Its Types?Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
9. what are different global variables ?@@rowcount-returns no of rows processed by preceeding command.@@error- returns the error code for last or current query.@@transtate- returns the current transaction state. @@tranchained-returns the current transcation mode.@@server- returns the server name.@@version-returns the version of the sql server and OS used in a system.@@spid- current process id.@@identity-returns the lastly incremented identity value.@@nestlevel-No of level in stored procedures/trigger.@@sqlstatus-status of previous fetch statement in cursor.
10. what is temporary tables and its types?Sub query: A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.

Sybase Interview QuestionS SET-IV:

1. what is In & OUT in stored procedure?The output keyword may be specified in the sp creation statement if the parameters can bepassed both in and out of the sp,when passing out you must specifiy output keyword inexecution statement
2. Tell me the limitation of the Trigger?any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger
3. can I update view?yes,but view had created using only one table than update is possiable
4. what is difference between constraints and rules?rules deals with constant and one rule can bound one column,constraints can bound many column and it can be compare with column in another table
5. How will you bind a rule?sp_bindrule
6. Explain about @@sqlstatus?It returns the status of the pervious fetch statement in a cursor
7. What are the steps involved in creating cursor?Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors
8. What is Transaction Log?It is a database level system table called syslogs,which contains a sequential list of all modification to every objects in the database.
9. Compare Rollback transaction and Rollback Trigger?
10. How will bcp out the Identity column?-E
11. What is Demand lock and Dead lock?A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.Demand Lock:A demand lock prevents any more shared locks from being set on a data resource (table or data page). Any new shared lock request has to wait for the demand lock request to finish.
12. How to get the last month’s last Day?select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))
13. What is patindex()?returns the starting postion of the specified ,else 0
14. How do I get the Duplicate rows from a table?select * from table name group by column1 having count(*)>1
15. What is command permission and object permission?OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults)