Search Results

Search found 6031 results on 242 pages for 'paul white nz'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 15/242 | < Previous Page | 11 12 13 14 15 16 17 18 19 20 21 22  | Next Page >

  • Heaps of Trouble?

    - by Paul White NZ
    If you’re not already a regular reader of Brad Schulz’s blog, you’re missing out on some great material.  In his latest entry, he is tasked with optimizing a query run against tables that have no indexes at all.  The problem is, predictably, that performance is not very good.  The catch is that we are not allowed to create any indexes (or even new statistics) as part of our optimization efforts. In this post, I’m going to look at the problem from a slightly different angle, and present an alternative solution to the one Brad found.  Inevitably, there’s going to be some overlap between our entries, and while you don’t necessarily need to read Brad’s post before this one, I do strongly recommend that you read it at some stage; he covers some important points that I won’t cover again here. The Example We’ll use data from the AdventureWorks database, copied to temporary unindexed tables.  A script to create these structures is shown below: CREATE TABLE #Custs ( CustomerID INTEGER NOT NULL, TerritoryID INTEGER NULL, CustomerType NCHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, ); GO CREATE TABLE #Prods ( ProductMainID INTEGER NOT NULL, ProductSubID INTEGER NOT NULL, ProductSubSubID INTEGER NOT NULL, Name NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, ); GO CREATE TABLE #OrdHeader ( SalesOrderID INTEGER NOT NULL, OrderDate DATETIME NOT NULL, SalesOrderNumber NVARCHAR(25) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, CustomerID INTEGER NOT NULL, ); GO CREATE TABLE #OrdDetail ( SalesOrderID INTEGER NOT NULL, OrderQty SMALLINT NOT NULL, LineTotal NUMERIC(38,6) NOT NULL, ProductMainID INTEGER NOT NULL, ProductSubID INTEGER NOT NULL, ProductSubSubID INTEGER NOT NULL, ); GO INSERT #Custs ( CustomerID, TerritoryID, CustomerType ) SELECT C.CustomerID, C.TerritoryID, C.CustomerType FROM AdventureWorks.Sales.Customer C WITH (TABLOCK); GO INSERT #Prods ( ProductMainID, ProductSubID, ProductSubSubID, Name ) SELECT P.ProductID, P.ProductID, P.ProductID, P.Name FROM AdventureWorks.Production.Product P WITH (TABLOCK); GO INSERT #OrdHeader ( SalesOrderID, OrderDate, SalesOrderNumber, CustomerID ) SELECT H.SalesOrderID, H.OrderDate, H.SalesOrderNumber, H.CustomerID FROM AdventureWorks.Sales.SalesOrderHeader H WITH (TABLOCK); GO INSERT #OrdDetail ( SalesOrderID, OrderQty, LineTotal, ProductMainID, ProductSubID, ProductSubSubID ) SELECT D.SalesOrderID, D.OrderQty, D.LineTotal, D.ProductID, D.ProductID, D.ProductID FROM AdventureWorks.Sales.SalesOrderDetail D WITH (TABLOCK); The query itself is a simple join of the four tables: SELECT P.ProductMainID AS PID, P.Name, D.OrderQty, H.SalesOrderNumber, H.OrderDate, C.TerritoryID FROM #Prods P JOIN #OrdDetail D ON P.ProductMainID = D.ProductMainID AND P.ProductSubID = D.ProductSubID AND P.ProductSubSubID = D.ProductSubSubID JOIN #OrdHeader H ON D.SalesOrderID = H.SalesOrderID JOIN #Custs C ON H.CustomerID = C.CustomerID ORDER BY P.ProductMainID ASC OPTION (RECOMPILE, MAXDOP 1); Remember that these tables have no indexes at all, and only the single-column sampled statistics SQL Server automatically creates (assuming default settings).  The estimated query plan produced for the test query looks like this (click to enlarge): The Problem The problem here is one of cardinality estimation – the number of rows SQL Server expects to find at each step of the plan.  The lack of indexes and useful statistical information means that SQL Server does not have the information it needs to make a good estimate.  Every join in the plan shown above estimates that it will produce just a single row as output.  Brad covers the factors that lead to the low estimates in his post. In reality, the join between the #Prods and #OrdDetail tables will produce 121,317 rows.  It should not surprise you that this has rather dire consequences for the remainder of the query plan.  In particular, it makes a nonsense of the optimizer’s decision to use Nested Loops to join to the two remaining tables.  Instead of scanning the #OrdHeader and #Custs tables once (as it expected), it has to perform 121,317 full scans of each.  The query takes somewhere in the region of twenty minutes to run to completion on my development machine. A Solution At this point, you may be thinking the same thing I was: if we really are stuck with no indexes, the best we can do is to use hash joins everywhere. We can force the exclusive use of hash joins in several ways, the two most common being join and query hints.  A join hint means writing the query using the INNER HASH JOIN syntax; using a query hint involves adding OPTION (HASH JOIN) at the bottom of the query.  The difference is that using join hints also forces the order of the join, whereas the query hint gives the optimizer freedom to reorder the joins at its discretion. Adding the OPTION (HASH JOIN) hint results in this estimated plan: That produces the correct output in around seven seconds, which is quite an improvement!  As a purely practical matter, and given the rigid rules of the environment we find ourselves in, we might leave things there.  (We can improve the hashing solution a bit – I’ll come back to that later on). Faster Nested Loops It might surprise you to hear that we can beat the performance of the hash join solution shown above using nested loops joins exclusively, and without breaking the rules we have been set. The key to this part is to realize that a condition like (A = B) can be expressed as (A <= B) AND (A >= B).  Armed with this tremendous new insight, we can rewrite the join predicates like so: SELECT P.ProductMainID AS PID, P.Name, D.OrderQty, H.SalesOrderNumber, H.OrderDate, C.TerritoryID FROM #OrdDetail D JOIN #OrdHeader H ON D.SalesOrderID >= H.SalesOrderID AND D.SalesOrderID <= H.SalesOrderID JOIN #Custs C ON H.CustomerID >= C.CustomerID AND H.CustomerID <= C.CustomerID JOIN #Prods P ON P.ProductMainID >= D.ProductMainID AND P.ProductMainID <= D.ProductMainID AND P.ProductSubID = D.ProductSubID AND P.ProductSubSubID = D.ProductSubSubID ORDER BY D.ProductMainID OPTION (RECOMPILE, LOOP JOIN, MAXDOP 1, FORCE ORDER); I’ve also added LOOP JOIN and FORCE ORDER query hints to ensure that only nested loops joins are used, and that the tables are joined in the order they appear.  The new estimated execution plan is: This new query runs in under 2 seconds. Why Is It Faster? The main reason for the improvement is the appearance of the eager Index Spools, which are also known as index-on-the-fly spools.  If you read my Inside The Optimiser series you might be interested to know that the rule responsible is called JoinToIndexOnTheFly. An eager index spool consumes all rows from the table it sits above, and builds a index suitable for the join to seek on.  Taking the index spool above the #Custs table as an example, it reads all the CustomerID and TerritoryID values with a single scan of the table, and builds an index keyed on CustomerID.  The term ‘eager’ means that the spool consumes all of its input rows when it starts up.  The index is built in a work table in tempdb, has no associated statistics, and only exists until the query finishes executing. The result is that each unindexed table is only scanned once, and just for the columns necessary to build the temporary index.  From that point on, every execution of the inner side of the join is answered by a seek on the temporary index – not the base table. A second optimization is that the sort on ProductMainID (required by the ORDER BY clause) is performed early, on just the rows coming from the #OrdDetail table.  The optimizer has a good estimate for the number of rows it needs to sort at that stage – it is just the cardinality of the table itself.  The accuracy of the estimate there is important because it helps determine the memory grant given to the sort operation.  Nested loops join preserves the order of rows on its outer input, so sorting early is safe.  (Hash joins do not preserve order in this way, of course). The extra lazy spool on the #Prods branch is a further optimization that avoids executing the seek on the temporary index if the value being joined (the ‘outer reference’) hasn’t changed from the last row received on the outer input.  It takes advantage of the fact that rows are still sorted on ProductMainID, so if duplicates exist, they will arrive at the join operator one after the other. The optimizer is quite conservative about introducing index spools into a plan, because creating and dropping a temporary index is a relatively expensive operation.  It’s presence in a plan is often an indication that a useful index is missing. I want to stress that I rewrote the query in this way primarily as an educational exercise – I can’t imagine having to do something so horrible to a production system. Improving the Hash Join I promised I would return to the solution that uses hash joins.  You might be puzzled that SQL Server can create three new indexes (and perform all those nested loops iterations) faster than it can perform three hash joins.  The answer, again, is down to the poor information available to the optimizer.  Let’s look at the hash join plan again: Two of the hash joins have single-row estimates on their build inputs.  SQL Server fixes the amount of memory available for the hash table based on this cardinality estimate, so at run time the hash join very quickly runs out of memory. This results in the join spilling hash buckets to disk, and any rows from the probe input that hash to the spilled buckets also get written to disk.  The join process then continues, and may again run out of memory.  This is a recursive process, which may eventually result in SQL Server resorting to a bailout join algorithm, which is guaranteed to complete eventually, but may be very slow.  The data sizes in the example tables are not large enough to force a hash bailout, but it does result in multiple levels of hash recursion.  You can see this for yourself by tracing the Hash Warning event using the Profiler tool. The final sort in the plan also suffers from a similar problem: it receives very little memory and has to perform multiple sort passes, saving intermediate runs to disk (the Sort Warnings Profiler event can be used to confirm this).  Notice also that because hash joins don’t preserve sort order, the sort cannot be pushed down the plan toward the #OrdDetail table, as in the nested loops plan. Ok, so now we understand the problems, what can we do to fix it?  We can address the hash spilling by forcing a different order for the joins: SELECT P.ProductMainID AS PID, P.Name, D.OrderQty, H.SalesOrderNumber, H.OrderDate, C.TerritoryID FROM #Prods P JOIN #Custs C JOIN #OrdHeader H ON H.CustomerID = C.CustomerID JOIN #OrdDetail D ON D.SalesOrderID = H.SalesOrderID ON P.ProductMainID = D.ProductMainID AND P.ProductSubID = D.ProductSubID AND P.ProductSubSubID = D.ProductSubSubID ORDER BY D.ProductMainID OPTION (MAXDOP 1, HASH JOIN, FORCE ORDER); With this plan, each of the inputs to the hash joins has a good estimate, and no hash recursion occurs.  The final sort still suffers from the one-row estimate problem, and we get a single-pass sort warning as it writes rows to disk.  Even so, the query runs to completion in three or four seconds.  That’s around half the time of the previous hashing solution, but still not as fast as the nested loops trickery. Final Thoughts SQL Server’s optimizer makes cost-based decisions, so it is vital to provide it with accurate information.  We can’t really blame the performance problems highlighted here on anything other than the decision to use completely unindexed tables, and not to allow the creation of additional statistics. I should probably stress that the nested loops solution shown above is not one I would normally contemplate in the real world.  It’s there primarily for its educational and entertainment value.  I might perhaps use it to demonstrate to the sceptical that SQL Server itself is crying out for an index. Be sure to read Brad’s original post for more details.  My grateful thanks to him for granting permission to reuse some of his material. Paul White Email: [email protected] Twitter: @PaulWhiteNZ

    Read the article

  • I enabled and setup glBlendFunc, but my texture has a white outline. What am I doing wrong?

    - by vinzBad
    You can see most of my source code in this question: Instead of the specified Texture, black circles on a green background are getting rendered. Why? Now I have the problem, that my texture has a white outline on its transparent parts. After googling and setting up glBlendFunc, the outline just got "softer". This is how it looks like: This is how I now setup OpenGL: public static void SetupGL() { GL.Enable(EnableCap.Blend); GL.BlendFunc(BlendingFactorSrc.SrcAlpha, BlendingFactorDest.OneMinusSrcAlpha); GL.Enable(EnableCap.Texture2D); GL.Hint(HintTarget.PerspectiveCorrectionHint, HintMode.Nicest); }

    Read the article

  • Need your opinion: which domain should I use for the blog?

    Reader new to my blog might have wondered why blog lives in a .NZ domain: to make a long story short is because when I started my blog I was soo excited about my relocation to New Zealand that I registered a .NZ domain. Then I came back to Italy, and started to work in Milano. Last autumn I had to chance to go back to New Zealand again, but at the same time the offer to work for an European Institution arrived. And then my .net.nz domain didn't make sense any more. And because it seems like I've...Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

    Read the article

  • Numbering grouped data in Excel

    - by Jeff
    I have an Excel spreadsheet (2010) with data similar to this: Dogs Brown Nice Dogs White Nice Dogs White Moody Cats Black Nice Cats Black Mean Cats White Nice Cats White Mean I want to group these animals but I only care about species and color. I don't care about disposition. I want to assign group numbers to the set as shown here. 1 Dogs Brown Nice 2 Dogs White Nice 2 Dogs White Moody 3 Cats Black Nice 3 Cats Black Mean 4 Cats White Nice 4 Cats White Mean I was able to select all the species and colors, then from the data tab select 'advanced', then 'unique records only'. This collapsed the data so that I could number the visible rows. Then when I 'cleared' the filter I could easily just fill the blank areas under the numbers with the number above. The problem is that my real data has far too many rows for this to be practical. Also, the trick about entering 1 in the first cell, 2 in the cell below, selecting both then dragging the corner down to 'auto-number' doesn't seem to work when you're viewing filtered rows. Any way to do this?

    Read the article

  • Why is my iPhone SDK 3.2 iPad code showing a white screen?

    - by Anthony Glyadchenko
    I'm trying to get a UISplitViewController working with an iPad app. I have the table view controller linked up under the Master pane and a plain UIView under the Detail view. I also have [window addSubview:splitView.view]; in my code. For some reason I just get a white screen even though the table view controller code is properly coded and linked under my nib. Any help would be great! Thanks! Here's where you can find the code: http://drop.io/s28bu4t/asset/mydevice-hd-zip

    Read the article

  • Upon doing a XSL Transform to XML how do I remove white space from a Node's attribute or data?

    - by Randy
    I have a part's list built out in XML and each part is labeled as such: <division> <parts> <part number="123456 " drawing="123456 " cad="y"> <attribute> <header>Header</header> <list>2</list> </attribute> </part> And I need to get the data behind the number and drawing attributes without the white space. I tried xsl:strip-space on the specific elements, and across the board, but that only strips the content in between the tags. I unfortunately have no access to the back-end that's producing the XML, so removing the spaces there doesn't look like an option.

    Read the article

  • Why does UMN-Mapserver shows an ERDAS Image-File (.img) as white shape?

    - by Mnementh
    I want to render an ERDAS-Image-file (suffix .img) with the UMN-Mapserver. The data is rendered on the right position and with the correct shape, but all data is white instead of an raster-image. The Image contains many layers. My mapfile looks like this: MAP NAME "Test" WEB METADATA "wms_title" "test" "WMS_SRS" "epsg:31466 epsg:31467 epsg:31468 epsg:31469 epsg:4326 epsg:25832 epsg:3035" END LOG "test.log" IMAGEPATH "." END SHAPEPATH "." PROJECTION "init=epsg:32632" END LAYER NAME "testlayer" TYPE RASTER DATA "test.img" STATUS ON OFFSITE 0 0 0 END OUTPUTFORMAT NAME png DRIVER "GD/PNG" MIMETYPE "image/png" IMAGEMODE RGBA END END

    Read the article

  • white-label collaborative open-source development (e.g. github/sourceforge/google-code in a box) ?

    - by Justin Grant
    Does anyone have a recommendation for an open-source or paid (either packaged or SaaS) solution for integrating collaborative development features into your own website? Here's more details: We currently host an online plugin gallery for our product. Users can upload and download plugins. But users can't easily collaborate on a plugin's development, can't easily report and track bugs on a plugin, can't easily track a plugin's versions or roadmap, etc. Of course, contributors can host their plugin development on github, sourceforge, google code, codeplex, etc. But keeping users on our website has some advantages. For example: We can use single-sign-on to avoid yet another username/password required we can integrate end-user issue tracking into our existing online issue-tracking systems we can get integrated analytics so we can better meet the needs of top contributors as well as downloaders We can easily reward reputation points to committers just like we do for people who answer lots of questions Anyone know a good solution for white-label sites for open-source project developer collaboration?

    Read the article

  • ASP.NET MVC2 - Trim white space from form submits before server-side validation?

    - by David Lively
    If I add a validation attribute: public class ProductDownloadListModel { //xxxxx-xxxxx-xxxxx [Required] [StringLength(17)] public string PSN { get; set; } public DateTime PsnExpirationDate { get; set; } public DataTable Downloads { get; set; } } and the user enters a 17-character string but includes white space on the end, I get a validation error because the string is greater than that specified by the [StringLength(17)] attribute. How can I prevent this? I'd prefer not to have to have javaScript trim the string before submits.

    Read the article

  • Importing oracle dump file, getting error on stored procedures

    - by Paul Tomblin
    I export an oracle "schema" using exp userid=/ file=pt.dmp log=pt.log owner=FOO buffer=10000000 statistics=NONE direct=Y and then import it into a different schema on the same oracle instance on the same SID using imp userid=/ file=pt.dmp fromuser=FOO touser=paul When I try to access the stored procedures, I get ORA-29541: class PAUL.ESMQOracleStoredProc could not be resolved Any idea why one user can resolve this but another one can't?

    Read the article

  • Keep remoting into wrong account. Windows 7

    - by Paul
    I have a home theatre PC running with two users accounts on windows 7. The default account logs into locally. The account 'Paul' is present but is denied local log in so the default auto logs in locally. I am trying to remote into account Paul using RDC however it tries to log into the default account and I am presented with an an option to boot the present user off so I can log in. How do I specify which account I want to log into?

    Read the article

  • Keep remoting into wrong account. Windows 7

    - by Paul
    I have a home theatre PC running with two users accounts on windows 7. The default account logs into locally. The account 'Paul' is present but is denied local log in so the default auto logs in locally. I am trying to remote into account Paul using RDC however it tries to log into the default account and I am presented with an an option to boot the present user off so I can log in. How do I specify which account I want to log into?

    Read the article

  • Beware Sneaky Reads with Unique Indexes

    - by Paul White NZ
    A few days ago, Sandra Mueller (twitter | blog) asked a question using twitter’s #sqlhelp hash tag: “Might SQL Server retrieve (out-of-row) LOB data from a table, even if the column isn’t referenced in the query?” Leaving aside trivial cases (like selecting a computed column that does reference the LOB data), one might be tempted to say that no, SQL Server does not read data you haven’t asked for.  In general, that’s quite correct; however there are cases where SQL Server might sneakily retrieve a LOB column… Example Table Here’s a T-SQL script to create that table and populate it with 1,000 rows: CREATE TABLE dbo.LOBtest ( pk INTEGER IDENTITY NOT NULL, some_value INTEGER NULL, lob_data VARCHAR(MAX) NULL, another_column CHAR(5) NULL, CONSTRAINT [PK dbo.LOBtest pk] PRIMARY KEY CLUSTERED (pk ASC) ); GO DECLARE @Data VARCHAR(MAX); SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);   WITH Numbers (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master.sys.columns C1, master.sys.columns C2 ) INSERT LOBtest WITH (TABLOCKX) ( some_value, lob_data ) SELECT TOP (1000) N.n, @Data FROM Numbers N WHERE N.n <= 1000; Test 1: A Simple Update Let’s run a query to subtract one from every value in the some_value column: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; As you might expect, modifying this integer column in 1,000 rows doesn’t take very long, or use many resources.  The STATITICS IO and TIME output shows a total of 9 logical reads, and 25ms elapsed time.  The query plan is also very simple: Looking at the Clustered Index Scan, we can see that SQL Server only retrieves the pk and some_value columns during the scan: The pk column is needed by the Clustered Index Update operator to uniquely identify the row that is being changed.  The some_value column is used by the Compute Scalar to calculate the new value.  (In case you are wondering what the Top operator is for, it is used to enforce SET ROWCOUNT). Test 2: Simple Update with an Index Now let’s create a nonclustered index keyed on the some_value column, with lob_data as an included column: CREATE NONCLUSTERED INDEX [IX dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest (some_value) INCLUDE ( lob_data ) WITH ( FILLFACTOR = 100, MAXDOP = 1, SORT_IN_TEMPDB = ON ); This is not a useful index for our simple update query; imagine that someone else created it for a different purpose.  Let’s run our update query again: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; We find that it now requires 4,014 logical reads and the elapsed query time has increased to around 100ms.  The extra logical reads (4 per row) are an expected consequence of maintaining the nonclustered index. The query plan is very similar to before (click to enlarge): The Clustered Index Update operator picks up the extra work of maintaining the nonclustered index. The new Compute Scalar operators detect whether the value in the some_value column has actually been changed by the update.  SQL Server may be able to skip maintaining the nonclustered index if the value hasn’t changed (see my previous post on non-updating updates for details).  Our simple query does change the value of some_data in every row, so this optimization doesn’t add any value in this specific case. The output list of columns from the Clustered Index Scan hasn’t changed from the one shown previously: SQL Server still just reads the pk and some_data columns.  Cool. Overall then, adding the nonclustered index hasn’t had any startling effects, and the LOB column data still isn’t being read from the table.  Let’s see what happens if we make the nonclustered index unique. Test 3: Simple Update with a Unique Index Here’s the script to create a new unique index, and drop the old one: CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest (some_value) INCLUDE ( lob_data ) WITH ( FILLFACTOR = 100, MAXDOP = 1, SORT_IN_TEMPDB = ON ); GO DROP INDEX [IX dbo.LOBtest some_value (lob_data)] ON dbo.LOBtest; Remember that SQL Server only enforces uniqueness on index keys (the some_data column).  The lob_data column is simply stored at the leaf-level of the non-clustered index.  With that in mind, we might expect this change to make very little difference.  Let’s see: UPDATE dbo.LOBtest WITH (TABLOCKX) SET some_value = some_value - 1; Whoa!  Now look at the elapsed time and logical reads: Scan count 1, logical reads 2016, physical reads 0, read-ahead reads 0, lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.   CPU time = 172 ms, elapsed time = 16172 ms. Even with all the data and index pages in memory, the query took over 16 seconds to update just 1,000 rows, performing over 52,000 LOB logical reads (nearly 16,000 of those using read-ahead). Why on earth is SQL Server reading LOB data in a query that only updates a single integer column? The Query Plan The query plan for test 3 looks a bit more complex than before: In fact, the bottom level is exactly the same as we saw with the non-unique index.  The top level has heaps of new stuff though, which I’ll come to in a moment. You might be expecting to find that the Clustered Index Scan is now reading the lob_data column (for some reason).  After all, we need to explain where all the LOB logical reads are coming from.  Sadly, when we look at the properties of the Clustered Index Scan, we see exactly the same as before: SQL Server is still only reading the pk and some_value columns – so what’s doing the LOB reads? Updates that Sneakily Read Data We have to go as far as the Clustered Index Update operator before we see LOB data in the output list: [Expr1020] is a bit flag added by an earlier Compute Scalar.  It is set true if the some_value column has not been changed (part of the non-updating updates optimization I mentioned earlier). The Clustered Index Update operator adds two new columns: the lob_data column, and some_value_OLD.  The some_value_OLD column, as the name suggests, is the pre-update value of the some_value column.  At this point, the clustered index has already been updated with the new value, but we haven’t touched the nonclustered index yet. An interesting observation here is that the Clustered Index Update operator can read a column into the data flow as part of its update operation.  SQL Server could have read the LOB data as part of the initial Clustered Index Scan, but that would mean carrying the data through all the operations that occur prior to the Clustered Index Update.  The server knows it will have to go back to the clustered index row to update it, so it delays reading the LOB data until then.  Sneaky! Why the LOB Data Is Needed This is all very interesting (I hope), but why is SQL Server reading the LOB data?  For that matter, why does it need to pass the pre-update value of the some_value column out of the Clustered Index Update? The answer relates to the top row of the query plan for test 3.  I’ll reproduce it here for convenience: Notice that this is a wide (per-index) update plan.  SQL Server used a narrow (per-row) update plan in test 2, where the Clustered Index Update took care of maintaining the nonclustered index too.  I’ll talk more about this difference shortly. The Split/Sort/Collapse combination is an optimization, which aims to make per-index update plans more efficient.  It does this by breaking each update into a delete/insert pair, reordering the operations, removing any redundant operations, and finally applying the net effect of all the changes to the nonclustered index. Imagine we had a unique index which currently holds three rows with the values 1, 2, and 3.  If we run a query that adds 1 to each row value, we would end up with values 2, 3, and 4.  The net effect of all the changes is the same as if we simply deleted the value 1, and added a new value 4. By applying net changes, SQL Server can also avoid false unique-key violations.  If we tried to immediately update the value 1 to a 2, it would conflict with the existing value 2 (which would soon be updated to 3 of course) and the query would fail.  You might argue that SQL Server could avoid the uniqueness violation by starting with the highest value (3) and working down.  That’s fine, but it’s not possible to generalize this logic to work with every possible update query. SQL Server has to use a wide update plan if it sees any risk of false uniqueness violations.  It’s worth noting that the logic SQL Server uses to detect whether these violations are possible has definite limits.  As a result, you will often receive a wide update plan, even when you can see that no violations are possible. Another benefit of this optimization is that it includes a sort on the index key as part of its work.  Processing the index changes in index key order promotes sequential I/O against the nonclustered index. A side-effect of all this is that the net changes might include one or more inserts.  In order to insert a new row in the index, SQL Server obviously needs all the columns – the key column and the included LOB column.  This is the reason SQL Server reads the LOB data as part of the Clustered Index Update. In addition, the some_value_OLD column is required by the Split operator (it turns updates into delete/insert pairs).  In order to generate the correct index key delete operation, it needs the old key value. The irony is that in this case the Split/Sort/Collapse optimization is anything but.  Reading all that LOB data is extremely expensive, so it is sad that the current version of SQL Server has no way to avoid it. Finally, for completeness, I should mention that the Filter operator is there to filter out the non-updating updates. Beating the Set-Based Update with a Cursor One situation where SQL Server can see that false unique-key violations aren’t possible is where it can guarantee that only one row is being updated.  Armed with this knowledge, we can write a cursor (or the WHILE-loop equivalent) that updates one row at a time, and so avoids reading the LOB data: SET NOCOUNT ON; SET STATISTICS XML, IO, TIME OFF;   DECLARE @PK INTEGER, @StartTime DATETIME; SET @StartTime = GETUTCDATE();   DECLARE curUpdate CURSOR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR SELECT L.pk FROM LOBtest L ORDER BY L.pk ASC;   OPEN curUpdate;   WHILE (1 = 1) BEGIN FETCH NEXT FROM curUpdate INTO @PK;   IF @@FETCH_STATUS = -1 BREAK; IF @@FETCH_STATUS = -2 CONTINUE;   UPDATE dbo.LOBtest SET some_value = some_value - 1 WHERE CURRENT OF curUpdate; END;   CLOSE curUpdate; DEALLOCATE curUpdate;   SELECT DATEDIFF(MILLISECOND, @StartTime, GETUTCDATE()); That completes the update in 1280 milliseconds (remember test 3 took over 16 seconds!) I used the WHERE CURRENT OF syntax there and a KEYSET cursor, just for the fun of it.  One could just as well use a WHERE clause that specified the primary key value instead. Clustered Indexes A clustered index is the ultimate index with included columns: all non-key columns are included columns in a clustered index.  Let’s re-create the test table and data with an updatable primary key, and without any non-clustered indexes: IF OBJECT_ID(N'dbo.LOBtest', N'U') IS NOT NULL DROP TABLE dbo.LOBtest; GO CREATE TABLE dbo.LOBtest ( pk INTEGER NOT NULL, some_value INTEGER NULL, lob_data VARCHAR(MAX) NULL, another_column CHAR(5) NULL, CONSTRAINT [PK dbo.LOBtest pk] PRIMARY KEY CLUSTERED (pk ASC) ); GO DECLARE @Data VARCHAR(MAX); SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);   WITH Numbers (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master.sys.columns C1, master.sys.columns C2 ) INSERT LOBtest WITH (TABLOCKX) ( pk, some_value, lob_data ) SELECT TOP (1000) N.n, N.n, @Data FROM Numbers N WHERE N.n <= 1000; Now here’s a query to modify the cluster keys: UPDATE dbo.LOBtest SET pk = pk + 1; The query plan is: As you can see, the Split/Sort/Collapse optimization is present, and we also gain an Eager Table Spool, for Halloween protection.  In addition, SQL Server now has no choice but to read the LOB data in the Clustered Index Scan: The performance is not great, as you might expect (even though there is no non-clustered index to maintain): Table 'LOBtest'. Scan count 1, logical reads 2011, physical reads 0, read-ahead reads 0, lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.   Table 'Worktable'. Scan count 1, logical reads 2040, physical reads 0, read-ahead reads 0, lob logical reads 34000, lob physical reads 0, lob read-ahead reads 8000.   SQL Server Execution Times: CPU time = 483 ms, elapsed time = 17884 ms. Notice how the LOB data is read twice: once from the Clustered Index Scan, and again from the work table in tempdb used by the Eager Spool. If you try the same test with a non-unique clustered index (rather than a primary key), you’ll get a much more efficient plan that just passes the cluster key (including uniqueifier) around (no LOB data or other non-key columns): A unique non-clustered index (on a heap) works well too: Both those queries complete in a few tens of milliseconds, with no LOB reads, and just a few thousand logical reads.  (In fact the heap is rather more efficient). There are lots more fun combinations to try that I don’t have space for here. Final Thoughts The behaviour shown in this post is not limited to LOB data by any means.  If the conditions are met, any unique index that has included columns can produce similar behaviour – something to bear in mind when adding large INCLUDE columns to achieve covering queries, perhaps. Paul White Email: [email protected] Twitter: @PaulWhiteNZ

    Read the article

  • Convert color photos of documents to good black-and-white images?

    - by Norman Ramsey
    Since I don't have a copier or scanner, I'm using an 8 megapixel camera to copy documents. This works pretty well except they need a lot of processing afterward. I'd like to get from a photo to a bitmap, but using djpeg -grayscale -pnm photo.jpg | pgmtopbm -threshold -value XXX does not work so well, for two reasons: It's hard to guess what XXX should be, and XXX is different for different photos. Illumination varies, and sometimes a single threshold isn't what's right for the image. How can I do better? The ideal solution will be fully automatic command-line program that I can run on Linux. (I have already written a program to remove dark pixels from the edges of images.)

    Read the article

  • How to accept email *only* from white-listed addresses in Gmail? [migrated]

    - by Mawg
    I only want to accpet email from two addresses, the rest I want to delete immediately, unseen. I know how to make fileers and I can whitelist those two addresses. If I make 3 filter, in this order; 1) from [email protected] move to inbox, never mark as spam 2) from [email protected] move to inbox, never mark as spam 3) from *@*.* delete immediately, never move to trash can I be guaranteed that that will do what I want? For instance, can I be sure that the filters are executed in that order? I dont want to lose amy mail from those two adresses.

    Read the article

  • I trying to backreference using the sed command

    - by Paul
    I am relative new to shell scripting and sed. I need to substitute a pattern, globably, but I also need to remember (or save) part of the pattern and use it later in the same substitute command. The saved pattern will be varible, so I need to use a wild card. For example, input message=trt:GetAudioSourcesRequest/> and I want to end up with something like input message=trt:GetAudioSourcesRequest PAUL/GetAudioSourcesRequest/> but the function string "GetAudioSourcesRequest" will change (in length also) throughtout the file, so I need a wild card, e.g. sed -i "s/input message=trt:<wild card in here>/>/input message=trt:<print wild card> PAUL/<print wild card>/> I have managed to get the following command to nearly do what I want but it is too rigid. It only stores a 4 syllable pattern so if I have a function name such as GetProfileRequest, this doesn't work echo "input message=\"trt:GetAudioSourcesRequest\"/>" | sed 's/input message=\"trt:\([A-Z][a-z]*\)\([A-Z][a-z]*\)\([A-Z][a-z]*\)\([A-Z][a-z]*\).*/input message=\"trt:\1\2\3\4\ PAUL\/\1\2\3\4"\/\>/g' This outputs input message="trt:GetAudioSourcesRequest PAUL/GetAudioSourcesRequest"/> Which is ok but when I use GetProfileRequest this doesn't. I have come accross \W and [^[:alnum:]] or [[:alnum:]] but I don't how to use them Thanks in advance.

    Read the article

  • How to remove extra white spaces using javascript or jquery?

    - by Amr ElGarhy
    I got HTML element contains this: <!--Product Style--> <div style="float: right; padding-top: 4px; padding-bottom: 5px;"> P6C245RO </div> <div style="text-transform: uppercase; font-weight: bold; padding-top: 4px; padding-bottom: 5px;"> Style </div> <div style="clear: both; border-top: 1px solid rgb(216, 216, 216); padding-top: 4px;"> <!--Product Description--> <div style="font-size: 11px ! important;"></div> <div style="background: url(&quot;http://ii.armaniexchange.com/ArmaniExchange/images/en_US/global/globalgraphics/bullet.gif&quot;) no-repeat scroll 0pt 4px transparent; padding-left: 12px;">fine tonal striped fabric</div> <div style="background: url(&quot;http://ii.armaniexchange.com/ArmaniExchange/images/en_US/global/globalgraphics/bullet.gif&quot;) no-repeat scroll 0pt 4px transparent; padding-left: 12px;">epaulettes and sleeve tab</div> <div style="background: url(&quot;http://ii.armaniexchange.com/ArmaniExchange/images/en_US/global/globalgraphics/bullet.gif&quot;) no-repeat scroll 0pt 4px transparent; padding-left: 12px;">metal logo plate on the chest pocket</div> When i read it using jquery i get the .text() contains a lot of spaces and /n in between the text but without the html tags. How to remove all these white spaces and return the clean text using jquery or pure javascript?

    Read the article

  • Problem of Sprites and labels are displayed by white boxes.

    - by srikanth rongali
    I am writing a game in cocos2d. I am using a function restartDirector in AppDelegate class. -(void)restartDirector{ [[CCDirector sharedDirector] end]; [[CCDirector sharedDirector] release]; if( ! [CCDirector setDirectorType:CCDirectorTypeDisplayLink] ) [CCDirector setDirectorType:CCDirectorTypeDefault]; [[CCDirector sharedDirector] setPixelFormat:kPixelFormatRGBA8888]; [CCTexture2D setDefaultAlphaPixelFormat:kTexture2DPixelFormat_RGBA8888]; [[CCDirector sharedDirector] setAnimationInterval:1.0/60]; [[CCDirector sharedDirector] setDisplayFPS:YES]; [[CCDirector sharedDirector] setDeviceOrientation:CCDeviceOrientationLandscapeLeft]; [[CCDirector sharedDirector] attachInView:window]; } This function I called in one of the game Scene . -(void)PracticeMethod:(id)sender { [MY_DELEGATE restartDirector]; CCScene *endPageScene = [CCScene node]; CCLayer *endPageLayer = [DummyScene node]; [endPageScene addChild:endPageLayer]; [[CCDirector sharedDirector] runWithScene:endPageScene]; // [[CCDirector sharedDirector] replaceScene:endPageScene]; } When used the replaceScene, there is no problem in game but the memory of abject allocation is high(I checked in leaks tool). So I used runWithScene. But , while using these when the scene DummyScene is loaded the sprites, labels in it are displayed by white boxes. I cannot see the sprites and labels. If I am using replaceScene everything thing is working fine but the memory allocation is high. this is my problem. Thank you.

    Read the article

  • Converting image to Black & White image on iPhone SDK issues.

    - by KfirS
    Hello, I've used a familiar code to convert image to Black & White which I've founded on several forums. The code is: CGColorSpaceRef colorSapce = CGColorSpaceCreateDeviceGray(); CGContextRef context = CGBitmapContextCreate(nil, originalImage.size.width, originalImage.size.height, 8, originalImage.size.width, colorSapce, kCGImageAlphaNone); CGContextSetInterpolationQuality(context, kCGInterpolationHigh); CGContextSetShouldAntialias(context, NO); CGContextDrawImage(context, CGRectMake(0, 0, originalImage.size.width, originalImage.size.height), [originalImage CGImage]); CGImageRef bwImage = CGBitmapContextCreateImage(context); CGContextRelease(context); CGColorSpaceRelease(colorSapce); UIImage *resultImage = [UIImage imageWithCGImage:bwImage]; // This is result B/W image. CGImageRelease(bwImage); return resultImage; When I'm using this code with on Horizontal image it's work fine, but when I'm trying a to use this code on Vertical image, the result is disproportionate image and rotate 90 degree left. Can anyone know what could be the problem? Thanks, Kfir.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 11 12 13 14 15 16 17 18 19 20 21 22  | Next Page >