Search Results

Search found 24705 results on 989 pages for 'tally table'.

Page 91/989 | < Previous Page | 87 88 89 90 91 92 93 94 95 96 97 98  | Next Page >

• Simple Self Join Query Bad Performance

  - by user1514042

  Could anyone advice on how do I improve the performance of the following query. Note, the problem seems to be caused by where clause. Data (table contains a huge set of rows - 500K+, the set of parameters it's called with assums the return of 2-5K records per query, which takes 8-10 minutes currently): USE [SomeDb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Data]( [x] [money] NOT NULL, [y] [money] NOT NULL, CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED ( [x] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO The Query select top 10000 s.x as sx, e.x as ex, s.y as sy, e.y as ey, e.y - s.y as y_delta, e.x - s.x as x_delta from Data s inner join Data e on e.x > s.x and e.x - s.x between xFrom and xTo --where e.y - s.y > @yDelta -- when uncommented causes a huge delay Update 1 - Execution Plan <?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0263655" StatementText="select top 100&#xD;&#xA;s.x as sx,&#xD;&#xA;e.x as ex,&#xD;&#xA;s.y as sy,&#xD;&#xA;e.y as ey,&#xD;&#xA;e.y - s.y as y_delta,&#xD;&#xA;e.x - s.x as x_delta&#xD;&#xA;from Data s &#xD;&#xA; inner join Data e&#xD;&#xA; on e.x &gt; s.x and e.x - s.x between 100 and 105&#xD;&#xA;where e.y - s.y &gt; 0.01&#xD;&#xA;" StatementType="SELECT" QueryHash="0xAAAC02AC2D78CB56" QueryPlanHash="0x747994153CB2D637" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="13" CompileCPU="13" CompileMemory="424"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52199" EstimatedPagesCached="14561" EstimatedAvailableDegreeOfParallelism="4" /> <RelOp AvgRowSize="55" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0263655"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="39" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0263555"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(100)"> <Const ConstValue="(100)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="39" EstimateCPU="151828" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0263455"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="1.80448" EstimateIO="3.76461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="15225" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[e]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.902317" EstimateIO="1.88387" EstimateRebinds="1" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0263655" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="15224" ActualExecutions="15225" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[s]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]"> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&gt;=($100.0000) AND ([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&lt;=($105.0000) AND ([SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y])&gt;(0.01)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="GE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($100.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="LE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($105.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="GT"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0.01)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>

  Read the article

• Advanced TSQL Tuning: Why Internals Knowledge Matters

  - by Paul White

  There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes.  Query tuning is not complete as soon as the query returns results quickly in the development or test environments.  In production, your query will compete for memory, CPU, locks, I/O and other resources on the server.  Today’s entry looks at some tuning considerations that are often overlooked, and shows how deep internals knowledge can help you write better TSQL. As always, we’ll need some example data.  In fact, we are going to use three tables today, each of which is structured like this: Each table has 50,000 rows made up of an INTEGER id column and a padding column containing 3,999 characters in every row.  The only difference between the three tables is in the type of the padding column: the first table uses CHAR(3999), the second uses VARCHAR(MAX), and the third uses the deprecated TEXT type.  A script to create a database with the three tables and load the sample data follows: USE master; GO IF DB_ID('SortTest') IS NOT NULL DROP DATABASE SortTest; GO CREATE DATABASE SortTest COLLATE LATIN1_GENERAL_BIN; GO ALTER DATABASE SortTest MODIFY FILE ( NAME = 'SortTest', SIZE = 3GB, MAXSIZE = 3GB ); GO ALTER DATABASE SortTest MODIFY FILE ( NAME = 'SortTest_log', SIZE = 256MB, MAXSIZE = 1GB, FILEGROWTH = 128MB ); GO ALTER DATABASE SortTest SET ALLOW_SNAPSHOT_ISOLATION OFF ; ALTER DATABASE SortTest SET AUTO_CLOSE OFF ; ALTER DATABASE SortTest SET AUTO_CREATE_STATISTICS ON ; ALTER DATABASE SortTest SET AUTO_SHRINK OFF ; ALTER DATABASE SortTest SET AUTO_UPDATE_STATISTICS ON ; ALTER DATABASE SortTest SET AUTO_UPDATE_STATISTICS_ASYNC ON ; ALTER DATABASE SortTest SET PARAMETERIZATION SIMPLE ; ALTER DATABASE SortTest SET READ_COMMITTED_SNAPSHOT OFF ; ALTER DATABASE SortTest SET MULTI_USER ; ALTER DATABASE SortTest SET RECOVERY SIMPLE ; USE SortTest; GO CREATE TABLE dbo.TestCHAR ( id INTEGER IDENTITY (1,1) NOT NULL, padding CHAR(3999) NOT NULL,   CONSTRAINT [PK dbo.TestCHAR (id)] PRIMARY KEY CLUSTERED (id), ) ; CREATE TABLE dbo.TestMAX ( id INTEGER IDENTITY (1,1) NOT NULL, padding VARCHAR(MAX) NOT NULL,   CONSTRAINT [PK dbo.TestMAX (id)] PRIMARY KEY CLUSTERED (id), ) ; CREATE TABLE dbo.TestTEXT ( id INTEGER IDENTITY (1,1) NOT NULL, padding TEXT NOT NULL,   CONSTRAINT [PK dbo.TestTEXT (id)] PRIMARY KEY CLUSTERED (id), ) ; -- ============= -- Load TestCHAR (about 3s) -- ============= INSERT INTO dbo.TestCHAR WITH (TABLOCKX) ( padding ) SELECT padding = REPLICATE(CHAR(65 + (Data.n % 26)), 3999) FROM ( SELECT TOP (50000) n = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 FROM master.sys.columns C1, master.sys.columns C2, master.sys.columns C3 ORDER BY n ASC ) AS Data ORDER BY Data.n ASC ; -- ============ -- Load TestMAX (about 3s) -- ============ INSERT INTO dbo.TestMAX WITH (TABLOCKX) ( padding ) SELECT CONVERT(VARCHAR(MAX), padding) FROM dbo.TestCHAR ORDER BY id ; -- ============= -- Load TestTEXT (about 5s) -- ============= INSERT INTO dbo.TestTEXT WITH (TABLOCKX) ( padding ) SELECT CONVERT(TEXT, padding) FROM dbo.TestCHAR ORDER BY id ; -- ========== -- Space used -- ========== -- EXECUTE sys.sp_spaceused @objname = 'dbo.TestCHAR'; EXECUTE sys.sp_spaceused @objname = 'dbo.TestMAX'; EXECUTE sys.sp_spaceused @objname = 'dbo.TestTEXT'; ; CHECKPOINT ; That takes around 15 seconds to run, and shows the space allocated to each table in its output: To illustrate the points I want to make today, the example task we are going to set ourselves is to return a random set of 150 rows from each table.  The basic shape of the test query is the same for each of the three test tables: SELECT TOP (150) T.id, T.padding FROM dbo.Test AS T ORDER BY NEWID() OPTION (MAXDOP 1) ; Test 1 – CHAR(3999) Running the template query shown above using the TestCHAR table as the target, we find that the query takes around 5 seconds to return its results.  This seems slow, considering that the table only has 50,000 rows.  Working on the assumption that generating a GUID for each row is a CPU-intensive operation, we might try enabling parallelism to see if that speeds up the response time.  Running the query again (but without the MAXDOP 1 hint) on a machine with eight logical processors, the query now takes 10 seconds to execute – twice as long as when run serially. Rather than attempting further guesses at the cause of the slowness, let’s go back to serial execution and add some monitoring.  The script below monitors STATISTICS IO output and the amount of tempdb used by the test query.  We will also run a Profiler trace to capture any warnings generated during query execution. DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TC.id, TC.padding FROM dbo.TestCHAR AS TC ORDER BY NEWID() OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; Let’s take a closer look at the statistics and query plan generated from this: Following the flow of the data from right to left, we see the expected 50,000 rows emerging from the Clustered Index Scan, with a total estimated size of around 191MB.  The Compute Scalar adds a column containing a random GUID (generated from the NEWID() function call) for each row.  With this extra column in place, the size of the data arriving at the Sort operator is estimated to be 192MB. Sort is a blocking operator – it has to examine all of the rows on its input before it can produce its first row of output (the last row received might sort first).  This characteristic means that Sort requires a memory grant – memory allocated for the query’s use by SQL Server just before execution starts.  In this case, the Sort is the only memory-consuming operator in the plan, so it has access to the full 243MB (248,696KB) of memory reserved by SQL Server for this query execution. Notice that the memory grant is significantly larger than the expected size of the data to be sorted.  SQL Server uses a number of techniques to speed up sorting, some of which sacrifice size for comparison speed.  Sorts typically require a very large number of comparisons, so this is usually a very effective optimization.  One of the drawbacks is that it is not possible to exactly predict the sort space needed, as it depends on the data itself.  SQL Server takes an educated guess based on data types, sizes, and the number of rows expected, but the algorithm is not perfect. In spite of the large memory grant, the Profiler trace shows a Sort Warning event (indicating that the sort ran out of memory), and the tempdb usage monitor shows that 195MB of tempdb space was used – all of that for system use.  The 195MB represents physical write activity on tempdb, because SQL Server strictly enforces memory grants – a query cannot ‘cheat’ and effectively gain extra memory by spilling to tempdb pages that reside in memory.  Anyway, the key point here is that it takes a while to write 195MB to disk, and this is the main reason that the query takes 5 seconds overall. If you are wondering why using parallelism made the problem worse, consider that eight threads of execution result in eight concurrent partial sorts, each receiving one eighth of the memory grant.  The eight sorts all spilled to tempdb, resulting in inefficiencies as the spilled sorts competed for disk resources.  More importantly, there are specific problems at the point where the eight partial results are combined, but I’ll cover that in a future post. CHAR(3999) Performance Summary: 5 seconds elapsed time 243MB memory grant 195MB tempdb usage 192MB estimated sort set 25,043 logical reads Sort Warning Test 2 – VARCHAR(MAX) We’ll now run exactly the same test (with the additional monitoring) on the table using a VARCHAR(MAX) padding column: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TM.id, TM.padding FROM dbo.TestMAX AS TM ORDER BY NEWID() OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; This time the query takes around 8 seconds to complete (3 seconds longer than Test 1).  Notice that the estimated row and data sizes are very slightly larger, and the overall memory grant has also increased very slightly to 245MB.  The most marked difference is in the amount of tempdb space used – this query wrote almost 391MB of sort run data to the physical tempdb file.  Don’t draw any general conclusions about VARCHAR(MAX) versus CHAR from this – I chose the length of the data specifically to expose this edge case.  In most cases, VARCHAR(MAX) performs very similarly to CHAR – I just wanted to make test 2 a bit more exciting. MAX Performance Summary: 8 seconds elapsed time 245MB memory grant 391MB tempdb usage 193MB estimated sort set 25,043 logical reads Sort warning Test 3 – TEXT The same test again, but using the deprecated TEXT data type for the padding column: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TT.id, TT.padding FROM dbo.TestTEXT AS TT ORDER BY NEWID() OPTION (MAXDOP 1, RECOMPILE) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; This time the query runs in 500ms.  If you look at the metrics we have been checking so far, it’s not hard to understand why: TEXT Performance Summary: 0.5 seconds elapsed time 9MB memory grant 5MB tempdb usage 5MB estimated sort set 207 logical reads 596 LOB logical reads Sort warning SQL Server’s memory grant algorithm still underestimates the memory needed to perform the sorting operation, but the size of the data to sort is so much smaller (5MB versus 193MB previously) that the spilled sort doesn’t matter very much.  Why is the data size so much smaller?  The query still produces the correct results – including the large amount of data held in the padding column – so what magic is being performed here? TEXT versus MAX Storage The answer lies in how columns of the TEXT data type are stored.  By default, TEXT data is stored off-row in separate LOB pages – which explains why this is the first query we have seen that records LOB logical reads in its STATISTICS IO output.  You may recall from my last post that LOB data leaves an in-row pointer to the separate storage structure holding the LOB data. SQL Server can see that the full LOB value is not required by the query plan until results are returned, so instead of passing the full LOB value down the plan from the Clustered Index Scan, it passes the small in-row structure instead.  SQL Server estimates that each row coming from the scan will be 79 bytes long – 11 bytes for row overhead, 4 bytes for the integer id column, and 64 bytes for the LOB pointer (in fact the pointer is rather smaller – usually 16 bytes – but the details of that don’t really matter right now). OK, so this query is much more efficient because it is sorting a very much smaller data set – SQL Server delays retrieving the LOB data itself until after the Sort starts producing its 150 rows.  The question that normally arises at this point is: Why doesn’t SQL Server use the same trick when the padding column is defined as VARCHAR(MAX)? The answer is connected with the fact that if the actual size of the VARCHAR(MAX) data is 8000 bytes or less, it is usually stored in-row in exactly the same way as for a VARCHAR(8000) column – MAX data only moves off-row into LOB storage when it exceeds 8000 bytes.  The default behaviour of the TEXT type is to be stored off-row by default, unless the ‘text in row’ table option is set suitably and there is room on the page.  There is an analogous (but opposite) setting to control the storage of MAX data – the ‘large value types out of row’ table option.  By enabling this option for a table, MAX data will be stored off-row (in a LOB structure) instead of in-row.  SQL Server Books Online has good coverage of both options in the topic In Row Data. The MAXOOR Table The essential difference, then, is that MAX defaults to in-row storage, and TEXT defaults to off-row (LOB) storage.  You might be thinking that we could get the same benefits seen for the TEXT data type by storing the VARCHAR(MAX) values off row – so let’s look at that option now.  This script creates a fourth table, with the VARCHAR(MAX) data stored off-row in LOB pages: CREATE TABLE dbo.TestMAXOOR ( id INTEGER IDENTITY (1,1) NOT NULL, padding VARCHAR(MAX) NOT NULL,   CONSTRAINT [PK dbo.TestMAXOOR (id)] PRIMARY KEY CLUSTERED (id), ) ; EXECUTE sys.sp_tableoption @TableNamePattern = N'dbo.TestMAXOOR', @OptionName = 'large value types out of row', @OptionValue = 'true' ; SELECT large_value_types_out_of_row FROM sys.tables WHERE [schema_id] = SCHEMA_ID(N'dbo') AND name = N'TestMAXOOR' ; INSERT INTO dbo.TestMAXOOR WITH (TABLOCKX) ( padding ) SELECT SPACE(0) FROM dbo.TestCHAR ORDER BY id ; UPDATE TM WITH (TABLOCK) SET padding.WRITE (TC.padding, NULL, NULL) FROM dbo.TestMAXOOR AS TM JOIN dbo.TestCHAR AS TC ON TC.id = TM.id ; EXECUTE sys.sp_spaceused @objname = 'dbo.TestMAXOOR' ; CHECKPOINT ; Test 4 – MAXOOR We can now re-run our test on the MAXOOR (MAX out of row) table: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) MO.id, MO.padding FROM dbo.TestMAXOOR AS MO ORDER BY NEWID() OPTION (MAXDOP 1, RECOMPILE) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; TEXT Performance Summary: 0.3 seconds elapsed time 245MB memory grant 0MB tempdb usage 193MB estimated sort set 207 logical reads 446 LOB logical reads No sort warning The query runs very quickly – slightly faster than Test 3, and without spilling the sort to tempdb (there is no sort warning in the trace, and the monitoring query shows zero tempdb usage by this query).  SQL Server is passing the in-row pointer structure down the plan and only looking up the LOB value on the output side of the sort. The Hidden Problem There is still a huge problem with this query though – it requires a 245MB memory grant.  No wonder the sort doesn’t spill to tempdb now – 245MB is about 20 times more memory than this query actually requires to sort 50,000 records containing LOB data pointers.  Notice that the estimated row and data sizes in the plan are the same as in test 2 (where the MAX data was stored in-row). The optimizer assumes that MAX data is stored in-row, regardless of the sp_tableoption setting ‘large value types out of row’.  Why?  Because this option is dynamic – changing it does not immediately force all MAX data in the table in-row or off-row, only when data is added or actually changed.  SQL Server does not keep statistics to show how much MAX or TEXT data is currently in-row, and how much is stored in LOB pages.  This is an annoying limitation, and one which I hope will be addressed in a future version of the product. So why should we worry about this?  Excessive memory grants reduce concurrency and may result in queries waiting on the RESOURCE_SEMAPHORE wait type while they wait for memory they do not need.  245MB is an awful lot of memory, especially on 32-bit versions where memory grants cannot use AWE-mapped memory.  Even on a 64-bit server with plenty of memory, do you really want a single query to consume 0.25GB of memory unnecessarily?  That’s 32,000 8KB pages that might be put to much better use. The Solution The answer is not to use the TEXT data type for the padding column.  That solution happens to have better performance characteristics for this specific query, but it still results in a spilled sort, and it is hard to recommend the use of a data type which is scheduled for removal.  I hope it is clear to you that the fundamental problem here is that SQL Server sorts the whole set arriving at a Sort operator.  Clearly, it is not efficient to sort the whole table in memory just to return 150 rows in a random order. The TEXT example was more efficient because it dramatically reduced the size of the set that needed to be sorted.  We can do the same thing by selecting 150 unique keys from the table at random (sorting by NEWID() for example) and only then retrieving the large padding column values for just the 150 rows we need.  The following script implements that idea for all four tables: SET STATISTICS IO ON ; WITH TestTable AS ( SELECT * FROM dbo.TestCHAR ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id = ANY (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestMAX ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestTEXT ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestMAXOOR ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; All four queries now return results in much less than a second, with memory grants between 6 and 12MB, and without spilling to tempdb.  The small remaining inefficiency is in reading the id column values from the clustered primary key index.  As a clustered index, it contains all the in-row data at its leaf.  The CHAR and VARCHAR(MAX) tables store the padding column in-row, so id values are separated by a 3999-character column, plus row overhead.  The TEXT and MAXOOR tables store the padding values off-row, so id values in the clustered index leaf are separated by the much-smaller off-row pointer structure.  This difference is reflected in the number of logical page reads performed by the four queries: Table 'TestCHAR' logical reads 25511 lob logical reads 000 Table 'TestMAX'. logical reads 25511 lob logical reads 000 Table 'TestTEXT' logical reads 00412 lob logical reads 597 Table 'TestMAXOOR' logical reads 00413 lob logical reads 446 We can increase the density of the id values by creating a separate nonclustered index on the id column only.  This is the same key as the clustered index, of course, but the nonclustered index will not include the rest of the in-row column data. CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestCHAR (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestMAX (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestTEXT (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestMAXOOR (id); The four queries can now use the very dense nonclustered index to quickly scan the id values, sort them by NEWID(), select the 150 ids we want, and then look up the padding data.  The logical reads with the new indexes in place are: Table 'TestCHAR' logical reads 835 lob logical reads 0 Table 'TestMAX' logical reads 835 lob logical reads 0 Table 'TestTEXT' logical reads 686 lob logical reads 597 Table 'TestMAXOOR' logical reads 686 lob logical reads 448 With the new index, all four queries use the same query plan (click to enlarge): Performance Summary: 0.3 seconds elapsed time 6MB memory grant 0MB tempdb usage 1MB sort set 835 logical reads (CHAR, MAX) 686 logical reads (TEXT, MAXOOR) 597 LOB logical reads (TEXT) 448 LOB logical reads (MAXOOR) No sort warning I’ll leave it as an exercise for the reader to work out why trying to eliminate the Key Lookup by adding the padding column to the new nonclustered indexes would be a daft idea Conclusion This post is not about tuning queries that access columns containing big strings.  It isn’t about the internal differences between TEXT and MAX data types either.  It isn’t even about the cool use of UPDATE .WRITE used in the MAXOOR table load.  No, this post is about something else: Many developers might not have tuned our starting example query at all – 5 seconds isn’t that bad, and the original query plan looks reasonable at first glance.  Perhaps the NEWID() function would have been blamed for ‘just being slow’ – who knows.  5 seconds isn’t awful – unless your users expect sub-second responses – but using 250MB of memory and writing 200MB to tempdb certainly is!  If ten sessions ran that query at the same time in production that’s 2.5GB of memory usage and 2GB hitting tempdb.  Of course, not all queries can be rewritten to avoid large memory grants and sort spills using the key-lookup technique in this post, but that’s not the point either. The point of this post is that a basic understanding of execution plans is not enough.  Tuning for logical reads and adding covering indexes is not enough.  If you want to produce high-quality, scalable TSQL that won’t get you paged as soon as it hits production, you need a deep understanding of execution plans, and as much accurate, deep knowledge about SQL Server as you can lay your hands on.  The advanced database developer has a wide range of tools to use in writing queries that perform well in a range of circumstances. By the way, the examples in this post were written for SQL Server 2008.  They will run on 2005 and demonstrate the same principles, but you won’t get the same figures I did because 2005 had a rather nasty bug in the Top N Sort operator.  Fair warning: if you do decide to run the scripts on a 2005 instance (particularly the parallel query) do it before you head out for lunch… This post is dedicated to the people of Christchurch, New Zealand. © 2011 Paul White email: @[email protected] twitter: @SQL_Kiwi

  Read the article

• Navigating MainMenu with arrow keys or controller

  - by Phil Royer

  I'm attempting to make my menu navigable with the arrow keys or via the d-pad on a controller. So Far I've had no luck. The question is: Can someone walk me through how to make my current menu or any libgdx menu keyboard accessible? I'm a bit noobish with some stuff and I come from a Javascript background. Here's an example of what I'm trying to do: http://dl.dropboxusercontent.com/u/39448/webgl/qb/qb.html For a simple menu that you can just add a few buttons to and it run out of the box use this: http://www.sadafnoor.com/blog/how-to-create-simple-menu-in-libgdx/ Or you can use my code but I use a lot of custom styles. And here's an example of my code: import aurelienribon.tweenengine.Timeline; import aurelienribon.tweenengine.Tween; import aurelienribon.tweenengine.TweenManager; import com.badlogic.gdx.Game; import com.badlogic.gdx.Gdx; import com.badlogic.gdx.Screen; import com.badlogic.gdx.graphics.GL20; import com.badlogic.gdx.graphics.Texture; import com.badlogic.gdx.graphics.g2d.Sprite; import com.badlogic.gdx.graphics.g2d.SpriteBatch; import com.badlogic.gdx.graphics.g2d.TextureAtlas; import com.badlogic.gdx.math.Vector2; import com.badlogic.gdx.scenes.scene2d.Actor; import com.badlogic.gdx.scenes.scene2d.InputEvent; import com.badlogic.gdx.scenes.scene2d.InputListener; import com.badlogic.gdx.scenes.scene2d.Stage; import com.badlogic.gdx.scenes.scene2d.ui.Skin; import com.badlogic.gdx.scenes.scene2d.ui.Table; import com.badlogic.gdx.scenes.scene2d.ui.TextButton; import com.badlogic.gdx.scenes.scene2d.utils.Align; import com.badlogic.gdx.scenes.scene2d.utils.ClickListener; import com.project.game.tween.ActorAccessor; public class MainMenu implements Screen { private SpriteBatch batch; private Sprite menuBG; private Stage stage; private TextureAtlas atlas; private Skin skin; private Table table; private TweenManager tweenManager; @Override public void render(float delta) { Gdx.gl.glClearColor(0, 0, 0, 1); Gdx.gl.glClear(GL20.GL_COLOR_BUFFER_BIT); batch.begin(); menuBG.draw(batch); batch.end(); //table.debug(); stage.act(delta); stage.draw(); //Table.drawDebug(stage); tweenManager.update(delta); } @Override public void resize(int width, int height) { menuBG.setSize(width, height); stage.setViewport(width, height, false); table.invalidateHierarchy(); } @Override public void resume() { } @Override public void show() { stage = new Stage(); Gdx.input.setInputProcessor(stage); batch = new SpriteBatch(); atlas = new TextureAtlas("ui/atlas.pack"); skin = new Skin(Gdx.files.internal("ui/menuSkin.json"), atlas); table = new Table(skin); table.setBounds(0, 0, Gdx.graphics.getWidth(), Gdx.graphics.getHeight()); // Set Background Texture menuBackgroundTexture = new Texture("images/mainMenuBackground.png"); menuBG = new Sprite(menuBackgroundTexture); menuBG.setSize(Gdx.graphics.getWidth(), Gdx.graphics.getHeight()); // Create Main Menu Buttons // Button Play TextButton buttonPlay = new TextButton("START", skin, "inactive"); buttonPlay.addListener(new ClickListener() { @Override public void clicked(InputEvent event, float x, float y) { ((Game) Gdx.app.getApplicationListener()).setScreen(new LevelMenu()); } }); buttonPlay.addListener(new InputListener() { public boolean keyDown (InputEvent event, int keycode) { System.out.println("down"); return true; } }); buttonPlay.padBottom(12); buttonPlay.padLeft(20); buttonPlay.getLabel().setAlignment(Align.left); // Button EXTRAS TextButton buttonExtras = new TextButton("EXTRAS", skin, "inactive"); buttonExtras.addListener(new ClickListener() { @Override public void clicked(InputEvent event, float x, float y) { ((Game) Gdx.app.getApplicationListener()).setScreen(new ExtrasMenu()); } }); buttonExtras.padBottom(12); buttonExtras.padLeft(20); buttonExtras.getLabel().setAlignment(Align.left); // Button Credits TextButton buttonCredits = new TextButton("CREDITS", skin, "inactive"); buttonCredits.addListener(new ClickListener() { @Override public void clicked(InputEvent event, float x, float y) { ((Game) Gdx.app.getApplicationListener()).setScreen(new Credits()); } }); buttonCredits.padBottom(12); buttonCredits.padLeft(20); buttonCredits.getLabel().setAlignment(Align.left); // Button Settings TextButton buttonSettings = new TextButton("SETTINGS", skin, "inactive"); buttonSettings.addListener(new ClickListener() { @Override public void clicked(InputEvent event, float x, float y) { ((Game) Gdx.app.getApplicationListener()).setScreen(new Settings()); } }); buttonSettings.padBottom(12); buttonSettings.padLeft(20); buttonSettings.getLabel().setAlignment(Align.left); // Button Exit TextButton buttonExit = new TextButton("EXIT", skin, "inactive"); buttonExit.addListener(new ClickListener() { @Override public void clicked(InputEvent event, float x, float y) { Gdx.app.exit(); } }); buttonExit.padBottom(12); buttonExit.padLeft(20); buttonExit.getLabel().setAlignment(Align.left); // Adding Heading-Buttons to the cue table.add().width(190); table.add().width((table.getWidth() / 10) * 3); table.add().width((table.getWidth() / 10) * 5).height(140).spaceBottom(50); table.add().width(190).row(); table.add().width(190); table.add(buttonPlay).spaceBottom(20).width(460).height(110); table.add().row(); table.add().width(190); table.add(buttonExtras).spaceBottom(20).width(460).height(110); table.add().row(); table.add().width(190); table.add(buttonCredits).spaceBottom(20).width(460).height(110); table.add().row(); table.add().width(190); table.add(buttonSettings).spaceBottom(20).width(460).height(110); table.add().row(); table.add().width(190); table.add(buttonExit).width(460).height(110); table.add().row(); stage.addActor(table); // Animation Settings tweenManager = new TweenManager(); Tween.registerAccessor(Actor.class, new ActorAccessor()); // Heading and Buttons Fade In Timeline.createSequence().beginSequence() .push(Tween.set(buttonPlay, ActorAccessor.ALPHA).target(0)) .push(Tween.set(buttonExtras, ActorAccessor.ALPHA).target(0)) .push(Tween.set(buttonCredits, ActorAccessor.ALPHA).target(0)) .push(Tween.set(buttonSettings, ActorAccessor.ALPHA).target(0)) .push(Tween.set(buttonExit, ActorAccessor.ALPHA).target(0)) .push(Tween.to(buttonPlay, ActorAccessor.ALPHA, .5f).target(1)) .push(Tween.to(buttonExtras, ActorAccessor.ALPHA, .5f).target(1)) .push(Tween.to(buttonCredits, ActorAccessor.ALPHA, .5f).target(1)) .push(Tween.to(buttonSettings, ActorAccessor.ALPHA, .5f).target(1)) .push(Tween.to(buttonExit, ActorAccessor.ALPHA, .5f).target(1)) .end().start(tweenManager); tweenManager.update(Gdx.graphics.getDeltaTime()); } public static Vector2 getStageLocation(Actor actor) { return actor.localToStageCoordinates(new Vector2(0, 0)); } @Override public void dispose() { stage.dispose(); atlas.dispose(); skin.dispose(); menuBG.getTexture().dispose(); } @Override public void hide() { dispose(); } @Override public void pause() { } }

  Read the article

• how to mount a partition inside a partition

  - by facha

  Hello, everyone I have a block device (/dev/sda5) that has been partitioned inside by a virtual machine. So, when I look inside with fdisk /dev/sda5, I see: sda5p1 sda5p2 and so on. Is it possible to mount them on my host system? Thanks in advance.

  Read the article

• SQL Server Backup File Significantly Smaller After Table Recreation

  - by userx

  We run automated weekly backups of our SQL Server. The database in question is configured for Simple Recovery. We backup using Full, not differential. Recently, we had to re-create one of our tables with data in it (making 2 varchar fields a couple characters longer). This required running a script which created a new table, copied the data over, and then dropped the old. This worked correctly. Oddly though, our weekly backup files now SHRANK by over 75%! The tables don't have large indexes. All data was copied over correctly (and verified). I've verified that we are doing full and not incremental backups. The new files restore just fine. I can't seem to figure out why the backup files would have shrank so much? I've also noticed that they get about 10 MB larger every week, even though less than that amount of data is being added. I'm guessing that I'm simply not understanding something. Any insight would be appreciated.

  Read the article

• Excel 2003 Freezes When Worksheet with PivotTable Selected

  - by Max

  All of the sudden, my Excel 2003 began an odd behavior today. Whenever I click on a worksheet tab that has a PivotTable on it, I become unable to click on any other tabs or on the menu with the options to minimize, maximize, and size at the top left of the worksheet window. I am left unable to click on the other tabs until I double-click inside a cell in the PivotTable worksheet and get a blinking curor as if to type. Then, I can navigate to other tabs normally. I can't think of any major changes I have made to my computer in the last day that would have caused this. I did instiall PC Tools antivirus over a week ago, and since that time have noticed my computer behaving in odd ways, but excel has been just fine until now. Does anyone have any thoughts on what might cause this? Thanks so much.

  Read the article

• Pivot tables: How can I total the subtotal?

  - by Mike

  Person A needs £115, Person D £234 and Person G £789, but how do I SUM that and get it to show on the same ROW as the subtotal? The Rows are subscription names. The Value field holds the Cost per subscription. the Columns holds the name of the person who receives the subscription. I have GROUPED on YEAR & MONTH, and have a subtotal that shows me how much each person will need to pay each month for all their subscriptions, but I need a figure showing me the total of all the subscriptions per month. I've tried adding calculated fields, but I want to SUM the subtotals so I'm struggling to see the field I need to use. I've tried Grand Totals but that SUMS all rows and I really only want SUM the Subtotal Total Row. I need a nice neat report that my managers won't go white at when looking at it...to many numbers = fear and confusion. Anyway it got messy, so I've come for help. Cheers Mike.

  Read the article

• My pivot chart has the wrong Y axis values but correct data point values

  - by Mark Harnett

  I created a pivot chart based on some raw data for the x axis (dates) and 4 calculated fields for the Y values. The values on resulting lines are correct (see the data label at the end of the line) but the Y axis is off by about 100, but not off by any consistent amount. I have played with auto axis on and off, turn log scale on and off. All to no avail. Does anybody have any thoughts? Image link

  Read the article

• Excel 2007 Pivot Tables: Refreshing upsets my manual formatting

  - by Mike

  How can I stop a refresh on Pivot Tables from ruining my manually configured columns widths? I'd like to be able to make the columns fixed. The columns will always be the same ones, but the data obviously changes and new data goes in to those columns. Any pointers, guidance, or workarounds are much appreciated. Mike.

  Read the article

• Partition 500gb of database stored in SQL Server

  - by Devendra

  Hello I have 500GB of database stored in SQL Server. Because it has too big a database, it takes time to update and insert data. Now the case is that I want to partition the data. So which partitioning techniques should I use? Or is there any other technique which will improve my performance?

  Read the article

• Can a Mediawiki table be dynamically created using other Mediawiki pages?

  - by Ashimema

  OK, So I've got created a page on my wiki which contains just a single table listing various details about servers and customers. You can follow links for each customer name in the table to find additional details about said customer. What I want to know is; Can the information in the customers page (page B) be used to dynamically update the table (Page A). Is this something that the Semantic MediaWiki extension can accomplished? Running Mediawiki 1.16.2

  Read the article

• How Do I Restrict Views of a Custom List by Group in Sharepoint 2007?

  - by Crash893

  I'm pretty new to Sharepoint and what I would like to do is create a huge master list of all our employees and then make different "views" on that person depending on the persons group For example: A new employee might have Salary info Security info Personnel info Contract info I would like to have all that in one row (per employee) but then when someone from the hr group logs in they can only see Personnel and Salary or something like that. If that is not an option is there a way to link tables across different lists?

  Read the article

• restrict views of custome list by group in sharepoint

  - by Crash893

  Im pretty new to sharepoint and what i would like to do is create a huge master list of all our employees and then make diffrent "views" on that person depending on the persons group for example a new employe might have sallery info security info personel info contract info i would like to have all that in on row (per employee) but then when someone from the hr group logs in they can only see personel and salery or something like that If that is not an option is there a way to link tables across different lists?

  Read the article

• Excel 2007 Pivot Tables: Overlapping issue hampers my summary sheet

  - by Mike

  I've created a Workbook that has 5 Pivot Tables (PT). I want to make a summary sheet that holds all these PT's, but when they expand the 'not allowed to overlap issue' causes me updating problems - they don't update/expand effectively. Therefore, can't be printed off easily. The sheet would basically help my users give their bosses a simple quick overview of the larger worksheet - this way they would be more inclined to fill it in (give a little too get a little philosophy). I had thought about using the Camera Tool, but I'm not sure how you could make it dynamic, or whether it can be dynamic with a PT? Any advice, links or step-by-steps are greatly appreciated. Thanks Mike.

  Read the article

• Is the "One Description Table to rule them all" approch good?

  - by DavRob60

  Long ago, I worked (as a client) with a software which use a centralized table for it's codified element. Here, as far as I remember, how the table look like : Table_Name (PK) Field_Name (PK) Code (PK) Sort_Order Description So, instead of creating a table every time they need a codified field, they where just adding row in this table with the new Table_Name and Field_Name. I'm sometime tempted to use this pattern in some database I design, but I have resisted to this as from now, I think there's something wrong with this, but I cannot put the finger on it. It is just because you land with some of the structure logic within the Data or something else?

  Read the article

• mysqldump skip one table

  - by danneth

  I'm running a cronjob to backup our system using mysqldump. The database contains 90 or so tables. One of these tables is HUGE and every once in a while causes the dump to fail. From the manual I see that you can specify specific tables to dump shell> mysqldump [options] db_name [tbl_name ...] This got me thinking. What if I have two jobs, one for dumping the huge table, and one for all the others. To accomplish this it would be nice if I could to something like shell> mysqldump -u backupuser -p database huge_table > db_huge_table.sql shell> mysqldump -u backupuser -p database --skip huge_table > db_rest.sql Unfortenately I'm not seeing such and option. I could of course explicitly state the 90 tables, but that just seems like a mess. Another option would be a script of some sort, but before checking that route I'll try this resource. MySQL is 5.1.61 on CentOS 6.2

  Read the article

• Multi-Document TOC showing in wrong order

  - by Jeremy DeStefano

  I had a large document that was having formatting issues, so I split it into 2 files. Chapters 1-7 are in the main doc with the TOC and a second doc has chapters 8-12. I have the following: {TOC \O "1-3" \H \Z \U} {RD \f "MCDPS Training Manual Part2.docx"} The TOC is created and has entries from both documents, however its showing the entries from Chapter 8-11 first and then Chapter 1-7. I've read that it should list them based on page numbers, but its not. Chapter 8 starts at page 121, yet its listing it first. How can I get it to show the TOC from the main doc first and then the RD?

  Read the article

• Word: MAC 2011, TOC on too many pages

  - by Mark

  I have a Word: MAC 2011 document where the bottom of the first 40 pages or so say "TOC: Page x". This notation appears to be in the Footer, as it is gray until I click on it (then the rest of the text goes gray instead). There is no TOC that I can see in the document, so I'm presuming someone tried to create one and messed things up. After the first 40 pages or so, all the other bottom of the page notations appear to be correct. (i.e. Chapter One, Chapter Two, etc.) How can I get those first 40 pages to be part of Chapter One rather than TOC?

  Read the article

• Heavy write to Galera cluster - table locked, cluster practically unusable

  - by Joe

  I set up Galera Cluster on 3 nodes. It works perfectly for reading data. I have done simple application to make some test on the cluster. Unfortunately I have to say that the Cluster fails totally when I try to do some writing. Maybe it can be configured differently or I do sth wrong? I have a simple stored procedure: CREATE PROCEDURE testproc(IN p_idWorker INTEGER) BEGIN DECLARE t_id INT DEFAULT -1; DECLARE t_counter INT ; UPDATE test SET idWorker = p_idWorker WHERE counter = 0 AND idWorker IS NULL limit 1; SELECT id FROM test WHERE idWorker = p_idWorker LIMIT 1 INTO t_id; SELECT ABS(MAX(counter)/MIN(counter)) FROM TEST INTO t_counter; SELECT COUNT(*) FROM test WHERE counter = 0 INTO t_counter; IF t_id >= 0 THEN UPDATE test SET counter = counter + 1 WHERE id = t_id; UPDATE test SET idWorker = NULL WHERE id = t_id; SELECT t_counter AS res; ELSE SELECT 'end' AS res; END IF; END $$ Now my simple C# application creates for example 3 MySQL clients in separate threads and each one executes the procedure every 100ms until there is no record where column 'counter' = 0. Unfortunately - after about 10 seconds sth is going bad. On servers there is process 'query_end' that never ends. After that - you cannot make update on the test table, MySQL returns: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction . You cant even restart mysql. What you can do is to restart server, sometimes whole cluster. Is Galera Cluster so unreliable when you do massive concucurrent writing/updates? Hard to believe.

  Read the article

• Help with routing table

  - by user68752

  I have tried to find the answer to my question but not really found a clean and easy solution. I have a box (Ubuntu headless 10.04.1 server, with one Ethernet port) on LAN behind a router (running m0n0wall), that I have successfully installed a PPTP device (ppp0) on, this is working flawlessly (following this link) The thing is I want this box to route all it's internet traffic through the VPN tunnel (ppp0 device) but also being able to access the local LAN on 192.168.1.* subnet. I've succeeded a bit with this, but my problem right now is that I have port forwards (e.g. SSH) done in the m0n0wall pointing to this specific box which forces me to do "add routes" to all boxes that want to access this machine through this specific port. For instance a machine with ip xyz.xyz.xyz.xyz needs to have a static route setup in the routing table on the box to be able to access the box. This is the result of route -n xxx.xxx.137.2 192.168.1.1 255.255.255.255 UGH 0 0 0 eth0 xxx.xxx.137.2 0.0.0.0 255.255.255.255 UH 0 0 0 ppp0 192.168.1.0 0.0.0.0 255.255.255.0 U 0 0 0 eth0 yyy.yyy.0.0 192.168.1.1 255.255.0.0 UG 0 0 0 eth0 0.0.0.0 0.0.0.0 0.0.0.0 U 0 0 0 ppp0 Where xxx is the IPs provided from VPN server. yyy.yyy.0.0 is a net that i want to have access to the box, without this I can't access the box from outside the LAN (via port-forwards done in router software, m0n0wall) is there away round this ugly solution?

  Read the article

• In Excel, how to group data by date, and then do operations on the data?

  - by Bicou

  Hi, I have Excel 2003. My data is like this: 01/10/2010 0.99 02/10/2010 1.49 02/10/2010 0.99 02/10/2010 0.99 02/10/2010 0.99 03/10/2010 1.49 03/10/2010 1.49 03/10/2010 0.99 etc. In fact it is a list of sales every day. I want to have something like this: 01/10/2010 0.99 02/10/2010 4.46 03/10/2010 3.97 I want to group by date, and sum the column B. I'd like to see the evolution of the sales over time, and display a nice graph about that. I have managed to create pivot tables that almost do the job: they list the number of 0.99 and 1.49 each day, but I can't find a way to simply sum everything and group by date. Thanks for reading.

  Read the article

• How to get the value of a field in PHP?

  - by user272899

  I need to get the value of a field; I think I am along the right lines but not quite sure this is the proper code. The "Delete Movie" button is where I am trying to get the value of that row like so: value="'.$row['id'].'" Can you help? <?php //connect to database mysql_connect($mysql_hostname,$mysql_user,$mysql_password); @mysql_select_db($mysql_database) or die("<b>Unable to connect to specified database</b>"); //query databae $query = "select * from movielist"; $result=mysql_query($query) or die('Error, insert query failed'); $row=0; $numrows=mysql_num_rows($result); echo "<table border=1>"; echo "<tr> <td>ID</td> <td>Type</td> <td>Title</td> <td>Description</td> <td>Imdb URL</td> <td>Year</td> <td>Genre</td> <td>Actions</td> </tr>"; while($row<$numrows) { $id=mysql_result($result,$row,"id"); $type=mysql_result($result,$row,"type"); $title=mysql_result($result,$row,"title"); $description=mysql_result($result,$row,"description"); $imdburl=mysql_result($result,$row,"imdburl"); $year=mysql_result($result,$row,"year"); $genre=mysql_result($result,$row,"genre"); ?> <tr> <td><?php echo $id; ?></td> <td><?php echo $type; ?></td> <td><?php echo $title; ?></td> <td><?php echo $description; ?></td> <td><?php echo $imdburl; ?></td> <td><?php echo $year; ?></td> <td><?php echo $genre; ?></td> <td> <!-- Delete Movie Button --> <form style="display: inline;" action="delete/" method="post" onsubmit="return movie_delete()"> <input type="hidden" name="moviedeleteid" value="'.$row['id'].'"> <button type="submit" class="tooltip table-button ui-state-default ui-corner-all" title="Delete trunk"><span class="ui-icon ui-icon-trash"></span></button> </form> </td> </tr> <?php $row++; } echo "</table>"; ?>

  Read the article

• Internet Explorer background-color hover problem

  - by danilo

  I have a strange Problem with table formating using IE 7. My table looks like this: In IE, when using border-collapse, the borders don't get displayed correctly. That's why I used this fix: .table-vmlist td { border-top: 1px solid black; } td.col-vm-status, tr.row-details td { border-left: 1px solid black; } td.col-vm-rdp, tr.row-details td { border-right: 1px solid black; } .table-vmlist { border-bottom: 1px solid black;} When hovering over the row, it gets highlighted with CSS: .table-vmlist tr.row-vm { background-color: #A4C3EF; } .table-vmlist tr.row-vm:hover { background-color: #91BAEF; } Now, in IE 7, when moving the mouse from the top to the bottom of the list, every row gets highlighted correctly and no problems happen. But if I move my mouse pointer from the bottom of the list to the top, every second row seems to loose the border. Can someone explain what the problem is, and how to solve it? This is my markup: <tr class="row-vm"> <td class="col-vm-status status-1"><img title="Host Down" alt="Down" src="/Technik/vm-management/img/hoststatus_1.png"></td> <td class="col-vm-name">V1-VM-1</td> <td class="col-vm-stati"> <img title="Ping" alt="Ping status" src="/Technik/vm-management/img/servicestatus_3.png"> <img title="CPU" alt="CPU status" src="/Technik/vm-management/img/servicestatus_3.png"> <img title="RAM" alt="RAM status" src="/Technik/vm-management/img/servicestatus_3.png"> <img title="C:\ Diskspace" alt="Disk space status" src="/Technik/vm-management/img/servicestatus_3.png"> </td> <td class="col-vm-owner">kus</td> <td class="col-vm-purpose">Citrix Testserver</td> <td class="col-vm-ip">-</td> <td class="col-vm-uptime">-</td> <td class="col-vm-rdp">&nbsp;</td> </tr> And the CSS: /* VM-Tabelle formatieren */ .table-vmlist { border-collapse: collapse; } .table-vmlist tr { border: 1px solid black; } .table-vmlist tr.row-header { border: none; } .table-vmlist tr.row-vm { background-color: #A4C3EF; } .table-vmlist tr.row-vm:hover { background-color: #91BAEF; } .table-vmlist th { text-align: left; } .table-vmlist td { } .table-vmlist th, table td { padding: 2px 0px; } /* Spaltenbreite der VM-Tabelle festlegen */ .table-vmlist #col-status { width: 25px; } .table-vmlist #col-stati { width: 90px; } .table-vmlist #col-owner { width: 90px; } .table-vmlist #col-ip { width: 100px; } .table-vmlist #col-uptime { width: 70px; } .table-vmlist #col-rdp { width: 25px; } .table-vmlist tr.row-details td { padding: 0px 10px; } /* Kein Rahmen um verlinkte Bilder */ a img { border-width: 0px; } /* Für Einschaltknopf Hand-Cursor anstatt normalen Pfeil anzeigen */ td.status-1 img { cursor: pointer; } img.ajax-loader { margin-left: 2px; } IE fix: .table-vmlist td { border-top: 1px solid black; } td.col-vm-status, tr.row-details td { border-left: 1px solid black; } td.col-vm-rdp, tr.row-details td { border-right: 1px solid black; } .table-vmlist { border-bottom: 1px solid black;}

  Read the article

• Fixed Table Header And Scrolling Body

  - by user2881191

  can Anyone please help me with the example that is on site 'http://mkoryak.github.io/floatThead/examples/inner-scroll/' How Can i Apply the Jquery to the below example. please provide Me the Demo in JSFiddle. I Want to Make My Table Header Fixed With Scrolling (both horizontal and vertical) Body. If Possible I also need to make the last column Fixed as the Header. Please Help Me Reageding the Issue. I Have Been Stuck For a Week. Below Is My test Code For which I need to apply the style as in the above link or any other better approach. Css : #table-container{ background-color :white; overflow:auto; position:absolute; top : 0px; bottom : 18px; width : 100%; z-index: 1; } Jsp : <div id="table-container"> <table id="maintable" style="width: 100%"> <thead> <tr> <th>Col 1</th> <th>Col 2</th> <th>Col 3</th> <th>Col 4</th> <th>Col 5</th> <th>Col 6</th> <th>Col 7</th> <th>Col 8</th> <th>Col 9</th> </tr> </thead> <tbody> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> <tr> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> <td>info</td> </tr> </tbody> </table> </div>

  Read the article

• iPhone: image in table cell - wrong position & not scaled correctly in iPhone 3.0

  - by Karsten Silz

  Hi, I have a custom table cell in an iPhone 3.x app to show basketball player data, including a picture. Here's my code from the table controller: UIImageView *theImage = (UIImageView *) [cell viewWithTag:0]; theImage.image = [manager getSmallImageForPlayer: data.number]; theImage.frame = CGRectMake(14, 0, 30, 44); theImage.clipsToBounds = YES; theImage.contentMode = UIViewContentModeScaleAspectFit; theImage.autoresizingMask = UIViewAutoresizingFlexibleHeight && UIViewAutoresizingFlexibleWidth; theImage.autoresizesSubviews = YES; Here's what it looks like in the iPhone SDK 3.0 simulator: Here's what it looks like in the iPhone SDK 3.1.3 simulator: So I have two problems: In both versions, the image view starts at position (0,0) in the table cell, even though I set the frame to start at (14,0). In the 3.0 simulator (and 3.0 device), the image is not shrunk down correctly, even though I set the image view content mode to "UIViewContentModeScaleAspectFit". But the same code works fine in the 3.1.3 simulator / device. Who can help me with either of these two problems?

  Read the article

< Previous Page | 87 88 89 90 91 92 93 94 95 96 97 98  | Next Page >