Feeds:
Posts
Comments

Archive for the ‘SQL’ Category


Have been hearing this OLAP cube build issue repetetively, hence thought of posting it others might foind it helpful 

1.    On the Server verify latest SQL Server 2005 SP is installed and all DB / APP / AS SP levels are same
 
2.    Add MOSS 2007 SSP Admin account to which Production Project Server Instance(http://servername/pwa) is associated as Admin for Analysis Server
 
3.    Note Install or verify (if they are already there) these programs in the order in which they are presented.
 
Note :: Even if they are installed, verify the “latest versions” (Very Important) of the below mentioned components, alternatively latest versions can be downloaded from
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
 
       Microsoft Dot Net Framework 2.0 or above
       Microsoft Core XML Services (MSXML) 6.0
       Microsoft SQL Server Native Client
       Microsoft SQL Server 2005 Management Objects Collection
       Microsoft SQL Server 2005 Backward Compatibility Components – Note : you need to install only DSO component
       Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

Once Done Build the Cube 🙂

Read Full Post »


 

Many a times we use #temp tables in our SQL Query, the question is should we use #temp table or @table variable, and the long and short of it is
”It DEPENDS”, Wow confused, rather than making it more complex, let me simplify it,
so the points to be considered

When to choose @Table variable and when #Temp table  

  • If You have less data or simple data, possibly under 10000 rows use @variable
  • If you don’t want the data to be have transactional logging or locking or any kind of rollback then use @Table variable
  • If you don’t need to create index in data rows then use @Table variable, though there are workarounds to it, use a constraint

Obviously @variables will be faster because  

  • Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
  • Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them
  • And Of course you will not need to DROP #TEPM TABLE every time you create one :-), less manageability

Drawbacks of @Table Variable

  • Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
  • Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
  • The table definition cannot be changed after the initial DECLARE statement.
  • Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
  • CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
  • You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and asp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure

Myth :: Memory Consumption

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)

-=================================== TEMP TABLE EXAMPLE=============================

SELECT
        MSP_EpmTask_UserView.TaskDuration, 
        CASE WHEN MSP_EpmTask_UserView.TaskDuration < 10 THEN 1
             WHEN MSP_EpmTask_UserView.TaskDuration > 10 AND MSP_EpmTask_UserView.TaskDuration <= 25 THEN 2
             WHEN MSP_EpmTask_UserView.TaskDuration > 25 THEN 3             
        END AS DurRangeCount
INTO #TEMP
FROM  MSP_EpmProject_UserView INNER JOIN MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
WHERE (MSP_EpmTask_UserView.[Replan Task] = 1) AND (MSP_EpmTask_UserView.TaskPercentCompleted = 100)AND (MSP_EpmProject_UserView.ProjectCondition = ‘Active’)

–Select COUNT(DurRangeCount),DurRangeCount FROM #TEMP GROUP BY DurRangeCount

ALTER TABLE #TEMP ADD DurRange VARCHAR(20)
UPDATE #TEMP SET DurRange = ‘0 – 10’ WHERE DurRangeCount=1
UPDATE #TEMP SET DurRange = ’11 – 25′ WHERE DurRangeCount=2
UPDATE #TEMP SET DurRange = ‘ > 25’ WHERE DurRangeCount=3

Select COUNT(DurRangeCount) AS NoOfProject,DurRange FROM #TEMP GROUP BY DurRangeCount,DurRange

DROP TABLE #TEMP

===================================================================================

-================= SAME QUERY REWRITTEN USING @TABLE VARIABLE===================================

DECLARE @TABLE1 TABLE (DurRange Varchar(20),TaskDuration int,DurRangeCount int)

INSERT INTO @TABLE1(TaskDuration,DurRangeCount)
SELECT    MSP_EpmTask_UserView.TaskDuration, 
        CASE WHEN MSP_EpmTask_UserView.TaskDuration < 10 THEN 1
             WHEN MSP_EpmTask_UserView.TaskDuration > 10 AND MSP_EpmTask_UserView.TaskDuration <= 25 THEN 2
             WHEN MSP_EpmTask_UserView.TaskDuration > 25 THEN 3             
        END AS DurRangeCount
FROM  MSP_EpmProject_UserView INNER JOIN MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
WHERE (MSP_EpmTask_UserView.[Replan Task] = 1) AND (MSP_EpmTask_UserView.TaskPercentCompleted = 100)AND (MSP_EpmProject_UserView.ProjectCondition = ‘Active’)

–ALTER TABLE @TABLE ADD DurRange VARCHAR(20)
UPDATE @TABLE1 SET DurRange = ‘0 – 10’ WHERE DurRangeCount=1
UPDATE @TABLE1 SET DurRange = ’11 – 25′ WHERE DurRangeCount=2
UPDATE @TABLE1 SET DurRange = ‘ > 25’ WHERE DurRangeCount=3

Select COUNT(DurRangeCount) AS NoOfProject,DurRange FROM @TABLE1 GROUP BY DurRangeCount,DurRange

===============================================================================

 

MSDN LINK ::  INF: Frequently Asked Questions – SQL Server 2000 – Table Variables

Read Full Post »

« Newer Posts