Search Results

Search found 25852 results on 1035 pages for 'linq query syntax'.

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 184/1035 | < Previous Page | 180 181 182 183 184 185 186 187 188 189 190 191  | Next Page >

  • Is opening too many datacontexts bad?

    - by ryudice
    I've been checking my application with linq 2 sql profiler, and I noticed that it opens a lot of datacontexts, most of them are opened by the linq datasource I used, since my repositories use only the instance stored in Request.Items, is it bad to open too many datacontext? and how can I make my linqdatasource to use the datacontext that I store in Request.Items for the duration of the request? thanks for any help!

    Read the article

  • Helping linqtosql datacontext use implicit conversion between varchar column in the database and tab

    - by user213256
    I am creating an mssql database table, "Orders", that will contain a varchar(50) field, "Value" containing a string that represents a slightly complex data type, "OrderValue". I am using a linqtosql datacontext class, which automatically types the "Value" column as a string. I gave the "OrderValue" class implicit conversion operators to and from a string, so I can easily use implicit conversion with the linqtosql classes like this: // get an order from the orders table MyDataContext db = new MyDataContext(); Order order = db.Orders(o => o.id == 1); // use implicit converstion to turn the string representation of the order // value into the complex data type. OrderValue value = order.Value; // adjust one of the fields in the complex data type value.Shipping += 10; // use implicit conversion to store the string representation of the complex // data type back in the linqtosql order object order.Value = value; // save changes db.SubmitChanges(); However, I would really like to be able to tell the linqtosql class to type this field as "OrderValue" rather than as "string". Then I would be able to avoid complex code and re-write the above as: // get an order from the orders table MyDataContext db = new MyDataContext(); Order order = db.Orders(o => o.id == 1); // The Value field is already typed as the "OrderValue" type rather than as string. // When a string value was read from the database table, it was implicity converted // to "OrderValue" type. order.Value.Shipping += 10; // save changes db.SubmitChanges(); In order to achieve this desired goal, I looked at the datacontext designer and selected the "Value" field of the "Order" table. Then, in properties, I changed "Type" to "global::MyApplication.OrderValue". The "Server Data Type" property was left as "VarChar(50) NOT NULL" The project built without errors. However, when reading from the database table, I was presented with the following error message: Could not convert from type 'System.String' to type 'MyApplication.OrderValue'. at System.Data.Linq.DBConvert.ChangeType(Object value, Type type) at Read_Order(ObjectMaterializer1 ) at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader2.MoveNext() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) at Example.OrdersProvider.GetOrders() at ... etc From the stack trace, I believe this error is happening while reading the data from the table. When presented with converting a string to my custom data type, even though the implicit conversion operators are present, the DBConvert class gets confused and throws an error. Is there anything I can do to help it not get confused and do the implicit conversion? Thanks in advance, and apologies if I have posted in the wrong forum. cheers / Ben

    Read the article

  • Intersection() and Except() is too slow with large collections of custom objects

    - by Theo
    I am importing data from another database. My process is importing data from a remote DB into a List<DataModel> named remoteData and also importing data from the local DB into a List<DataModel> named localData. I am then using LINQ to create a list of records that are different so that I can update the local DB to match the data pulled from remote DB. Like this: var outdatedData = this.localData.Intersect(this.remoteData, new OutdatedDataComparer()).ToList(); I am then using LINQ to create a list of records that no longer exist in remoteData, but do exist in localData, so that I delete them from local database. Like this: var oldData = this.localData.Except(this.remoteData, new MatchingDataComparer()).ToList(); I am then using LINQ to do the opposite of the above to add the new data to the local database. Like this: var newData = this.remoteData.Except(this.localData, new MatchingDataComparer()).ToList(); Each collection imports about 70k records, and each of the 3 LINQ operation take between 5 - 10 minutes to complete. How can I make this faster? Here is the object the collections are using: internal class DataModel { public string Key1{ get; set; } public string Key2{ get; set; } public string Value1{ get; set; } public string Value2{ get; set; } public byte? Value3{ get; set; } } The comparer used to check for outdated records: class OutdatedDataComparer : IEqualityComparer<DataModel> { public bool Equals(DataModel x, DataModel y) { var e = string.Equals(x.Key1, y.Key1) && string.Equals(x.Key2, y.Key2) && ( !string.Equals(x.Value1, y.Value1) || !string.Equals(x.Value2, y.Value2) || x.Value3 != y.Value3 ); return e; } public int GetHashCode(DataModel obj) { return 0; } } The comparer used to find old and new records: internal class MatchingDataComparer : IEqualityComparer<DataModel> { public bool Equals(DataModel x, DataModel y) { return string.Equals(x.Key1, y.Key1) && string.Equals(x.Key2, y.Key2); } public int GetHashCode(DataModel obj) { return 0; } }

    Read the article

  • Problem with PHP/AS3 - Display PHP query results back to flash via AS3

    - by Dale J
    Hi, Ive made a query in PHP, and i'm trying to send the results back into Flash via AS3, but its throwing up this error Error: Error #2101: The String passed to URLVariables.decode() must be a URL-encoded query string containing name/value pairs. at Error$/throwError() at flash.net::URLVariables/decode() at flash.net::URLVariables() at flash.net::URLLoader/onComplete() Here is the relevant part of the PHP and AS3 code, including the query. The flash variable rssAdd is passed over to the PHP which it using it in the PHP query accordingly. $url = $_POST['rssAdd']; $query= SELECT title FROM Feed WHERE category = (SELECT category FROM Feed WHERE url =$url) AND url!=$url; $result = mysql_query($query); echo $query; Here is the AS3 code I've done so far. function recommendation(){ var request:URLRequest = new URLRequest("url"); request.method = URLRequestMethod.POST var recVars:URLVariables = new URLVariables(); recVars.rssAdd=rssAdd; request.data = recVars var loader:URLLoader = new URLLoader(request); loader.addEventListener(Event.COMPLETE, onComplete); loader.dataFormat = URLLoaderDataFormat.TEXT; loader.load(request); function onComplete(event:Event):void{ recommend.text = event.target.data; } } Any help would be most appreciated, thanks.

    Read the article

  • Overflow exception while performing parallel factorization using the .NET Task Parallel Library (TPL

    - by Aviad P.
    Hello, I'm trying to write a not so smart factorization program and trying to do it in parallel using TPL. However, after about 15 minutes of running on a core 2 duo machine, I am getting an aggregate exception with an overflow exception inside it. All the entries in the stack trace are part of the .NET framework, the overflow does not come from my code. Any help would be appreciated in figuring out why this happens. Here's the commented code, hopefully it's simple enough to understand: class Program { static List<Tuple<BigInteger, int>> factors = new List<Tuple<BigInteger, int>>(); static void Main(string[] args) { BigInteger theNumber = BigInteger.Parse( "653872562986528347561038675107510176501827650178351386656875178" + "568165317809518359617865178659815012571026531984659218451608845" + "719856107834513527"); Stopwatch sw = new Stopwatch(); bool isComposite = false; sw.Start(); do { /* Print out the number we are currently working on. */ Console.WriteLine(theNumber); /* Find a factor, stop when at least one is found (using the Any operator). */ isComposite = Range(theNumber) .AsParallel() .Any(x => CheckAndStoreFactor(theNumber, x)); /* Of the factors found, take the one with the lowest base. */ var factor = factors.OrderBy(x => x.Item1).First(); Console.WriteLine(factor); /* Divide the number by the factor. */ theNumber = BigInteger.Divide( theNumber, BigInteger.Pow(factor.Item1, factor.Item2)); /* Clear the discovered factors cache, and keep looking. */ factors.Clear(); } while (isComposite); sw.Stop(); Console.WriteLine(isComposite + " " + sw.Elapsed); } static IEnumerable<BigInteger> Range(BigInteger squareOfTarget) { BigInteger two = BigInteger.Parse("2"); BigInteger element = BigInteger.Parse("3"); while (element * element < squareOfTarget) { yield return element; element = BigInteger.Add(element, two); } } static bool CheckAndStoreFactor(BigInteger candidate, BigInteger factor) { BigInteger remainder, dividend = candidate; int exponent = 0; do { dividend = BigInteger.DivRem(dividend, factor, out remainder); if (remainder.IsZero) { exponent++; } } while (remainder.IsZero); if (exponent > 0) { lock (factors) { factors.Add(Tuple.Create(factor, exponent)); } } return exponent > 0; } } Here's the exception thrown: Unhandled Exception: System.AggregateException: One or more errors occurred. --- > System.OverflowException: Arithmetic operation resulted in an overflow. at System.Linq.Parallel.PartitionedDataSource`1.ContiguousChunkLazyEnumerator.MoveNext(T& currentElement, Int32& currentKey) at System.Linq.Parallel.AnyAllSearchOperator`1.AnyAllSearchOperatorEnumerator`1.MoveNext(Boolean& currentElement, Int32& currentKey) at System.Linq.Parallel.StopAndGoSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work() at System.Linq.Parallel.QueryTask.BaseWork(Object unused) at System.Linq.Parallel.QueryTask.<.cctor>b__0(Object o) at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of inner exception stack trace --- at System.Linq.Parallel.QueryTaskGroupState.QueryEnd(Boolean userInitiatedDispose) at System.Linq.Parallel.SpoolingTask.SpoolStopAndGo[TInputOutput,TIgnoreKey](QueryTaskGroupState groupState, PartitionedStream`2 partitions, SynchronousChannel`1[] channels, TaskScheduler taskScheduler) at System.Linq.Parallel.DefaultMergeHelper`2.System.Linq.Parallel.IMergeHelper<TInputOutput>.Execute() at System.Linq.Parallel.MergeExecutor`1.Execute[TKey](PartitionedStream`2 partitions, Boolean ignoreOutput, ParallelMergeOptions options, TaskScheduler taskScheduler, Boolean isOrdered, CancellationState cancellationState, Int32 queryId) at System.Linq.Parallel.PartitionedStreamMerger`1.Receive[TKey](PartitionedStream`2 partitionedStream) at System.Linq.Parallel.AnyAllSearchOperator`1.WrapPartitionedStream[TKey](PartitionedStream`2 inputStream, IPartitionedStreamRecipient`1 recipient, BooleanpreferStriping, QuerySettings settings) at System.Linq.Parallel.UnaryQueryOperator`2.UnaryQueryOperatorResults.ChildResultsRecipient.Receive[TKey](PartitionedStream`2 inputStream) at System.Linq.Parallel.ScanQueryOperator`1.ScanEnumerableQueryOperatorResults.GivePartitionedStream(IPartitionedStreamRecipient`1 recipient) at System.Linq.Parallel.UnaryQueryOperator`2.UnaryQueryOperatorResults.GivePartitionedStream(IPartitionedStreamRecipient`1 recipient) at System.Linq.Parallel.QueryOperator`1.GetOpenedEnumerator(Nullable`1 mergeOptions, Boolean suppressOrder, Boolean forEffect, QuerySettings querySettings) at System.Linq.Parallel.QueryOpeningEnumerator`1.OpenQuery() at System.Linq.Parallel.QueryOpeningEnumerator`1.MoveNext() at System.Linq.Parallel.AnyAllSearchOperator`1.Aggregate() at System.Linq.ParallelEnumerable.Any[TSource](ParallelQuery`1 source, Func`2 predicate) at PFact.Program.Main(String[] args) in d:\myprojects\PFact\PFact\Program.cs:line 34 Any help would be appreciated. Thanks!

    Read the article

  • SQL 2005 indexed queries slower than unindexed queries

    - by uos??
    Adding a seemingly perfectly index is having an unexpectedly adverse affect on a query performance... -- [Data] has a predictable structure and a simple clustered index of the primary key: ALTER TABLE [dbo].[Data] ADD PRIMARY KEY CLUSTERED ( [ID] ) -- My query, joins on itself looking for a certain kind of "overlapping" records SELECT DISTINCT [Data].ID AS [ID] FROM dbo.[Data] AS [Data] JOIN dbo.[Data] AS [Compared] ON [Data].[A] = [Compared].[A] AND [Data].[B] = [Compared].[B] AND [Data].[C] = [Compared].[C] AND ([Data].[D] = [Compared].[D] OR [Data].[E] = [Compared].[E]) AND [Data].[F] <> [Compared].[F] WHERE 1=1 AND [Data].[A] = @A AND @CS <= [Data].[C] AND [Data].[C] < @CE -- Between a range [Data] has about a quarter-million records so far, 10% to 50% of the data satisfies the where clause depending on @A, @CS, and @CE. As is, the query takes 1 second to return about 300 rows when querying 10%, and 30 seconds to return 3000 rows when querying 50% of the data. Curiously, the estimated/actual execution plan indicates two parallel Clustered Index Scans, but the clustered index is only of the ID, which isn't part of the conditions of the query, only the output. ?? If I add this hand-crafted [IDX_A_B_C_D_E_F] index which I fully expected to improve performance, the query slows down by a factor of 8 (8 seconds for 10% & 4 minutes for 50%). The estimated/actual execution plans show an Index Seek, which seems like the right thing to be doing, but why so slow?? CREATE UNIQUE INDEX [IDX_A_B_C_D_E_F] ON [dbo].[Data] ([A], [B], [C], [D], [E], [F]) INCLUDE ([ID], [X], [Y], [Z]); The Data Engine Tuning wizard suggests a similar index with no noticeable difference in performance from this one. Moving AND [Data].[F] <> [Compared].[F] from the join condition to the where clause makes no difference in performance. I need these and other indexes for other queries. I'm sure I could hint that the query should refer to the Clustered Index, since that's currently winning - but we all know it is not as optimized as it could be, and without a proper index, I can expect the performance will get much worse with additional data. What gives?

    Read the article

  • SQL Where Clause Against View

    - by Adam Carr
    I have a view (actually, it's a table valued function, but the observed behavior is the same in both) that inner joins and left outer joins several other tables. When I query this view with a where clause similar to SELECT * FROM [v_MyView] WHERE [Name] like '%Doe, John%' ... the query is very slow, but if I do the following... SELECT * FROM [v_MyView] WHERE [ID] in ( SELECT [ID] FROM [v_MyView] WHERE [Name] like '%Doe, John%' ) it is MUCH faster. The first query is taking at least 2 minutes to return, if not longer where the second query will return in less than 5 seconds. Any suggestions on how I can improve this? If I run the whole command as one SQL statement (without the use of a view) it is very fast as well. I believe this result is because of how a view should behave as a table in that if a view has OUTER JOINS, GROUP BYS or TOP ##, if the where clause was interpreted prior to vs after the execution of the view, the results could differ. My question is why wouldn't SQL optimize my first query to something as efficient as my second query?

    Read the article

  • Dynamic Linq Library Guid exceptions

    - by Adan
    I am having a problem with the Dynamic Linq Library. I get a the following error "ParserException was unhandled by user code ')" or ','". I have a Dicitionary and I want to create a query based on this dictionary. So I loop through my dictionary and append to a string builder "PersonId = (GUID FROM DICTIONARY). I think the problem is were I append to PersonId for some reason I can't seem to convert my string guid to a Guid so the dynamic library don't crash. I have tried this to convert my string guid to a guid, but no luck. query.Append("(PersonId = Guid(" + person.Key + ")"); query.Append("(PersonId = " + person.Key + ")"); I am using VS 2010 RTM and RIA Services as well as the Entity Framework 4. //This is the loop I use foreach (KeyValuePair<Guid, PersonDetails> person in personsDetails) { if ((person.Value as PersonDetails).IsExchangeChecked) { query.Append("(PersonId = Guid.Parse(" + person.Key + ")"); } } //Domain service call var query = this.ObjectContext.Persons.Where(DynamicExpression.ParseLambda<Person, bool>(persons)); Please help, and if you know of a better way of doing this I am open to suggestions.

    Read the article

  • My SQL query is only returning I need the parent aswell

    - by sico87
    My sql query is only returning the children of the parent I need it to return the parent as well, public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, parent, url, description, image FROM p_cat WHERE deleted = 0 AND parent is NULL ORDER BY position;"; $query = $this->db->query($sql) or die($this->db->error); }else{ //die($cat); $sql = "SET @parent = (SELECT c.itemID FROM p_cat c WHERE url = '".$this->sql($cat)."' AND deleted = 0); SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image, (SELECT c2.url FROM p_cat c2 WHERE c2.itemID = c1.parent LIMIT 1) as parentUrl FROM p_cat c1 WHERE c1.deleted = 0 AND c1.parent = @parent ORDER BY c1.position;"; $query = $this->db->multi_query($sql) or die($this->db->error); $this->db->store_result(); $this->db->next_result(); $query = $this->db->store_result(); } return $query; }

    Read the article

  • How to query the SPView object

    - by Hugo Migneron
    I have a SPView object that contains a lot of SPListItem objects (there are many fields in the view). I am only interested in one of these fields. Let's call it specialField Given that view and specialField, I want to know if a value is contained in specialField. Here is a way of doing what I want to do : String specialField = "Special Field"; String specialValue = "value"; SPList list = SPContext.Current.Site.RootWeb.Lists["My List"]; SPView view = list.Views["My View"]; //This is the view I want to query SPQuery query = new SPQuery(); query.Query = view.Query; SPListItemCollection items = list.GetItems(query); foreach(SPListItem item in items) { var value = item[specialField]; if(value != null) && (value.ToString() == specialValue) { //My value is found. This is what I was looking for. //break out of the loop or return } } //My value is not found. However, iterating through each ListItem hardly seems optimal, especially as there might be hundreds of items. This query will be executed often, so I am looking for an efficient way to do this. EDIT I will not always be working with the same view, so my solution cannot be hardcoded (it has to be generic enough that the list, view and specialField can be changed.

    Read the article

  • How To perform a SQL Query to DataTable Operation That Can Be Cancelled

    - by David W
    I tried to make the title as specific as possible. Basically what I have running inside a backgroundworker thread now is some code that looks like: SqlConnection conn = new SqlConnection(connstring); SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(Results); conn.Close(); sda.Dispose(); Where query is a string representing a large, time consuming query, and conn is the connection object. My problem now is I need a stop button. I've come to realize killing the backgroundworker would be worthless because I still want to keep what results are left over after the query is canceled. Plus it wouldn't be able to check the canceled state until after the query. What I've come up with so far: I've been trying to conceptualize how to handle this efficiently without taking too big of a performance hit. My idea was to use a SqlDataReader to read the data from the query piece at a time so that I had a "loop" to check a flag I could set from the GUI via a button. The problem is as far as I know I can't use the Load() method of a datatable and still be able to cancel the sqlcommand. If I'm wrong please let me know because that would make cancelling slightly easier. In light of what I discovered I came to the realization I may only be able to cancel the sqlcommand mid-query if I did something like the below (pseudo-code): while(reader.Read()) { //check flag status //if it is set to 'kill' fire off the kill thread //otherwise populate the datatable with what was read } However, it would seem to me this would be highly ineffective and possibly costly. Is this the only way to kill a sqlcommand in progress that absolutely needs to be in a datatable? Any help would be appreciated!

    Read the article

  • Run multiple MySQL queries based on a series of ifs

    - by OldWest
    I am just getting started on this complex query I need to write and was hoping for any suggestions or feedback regarding table structure and the actual query itself.. I've already created my tables and populated test data, and now just trying to sort out how and what is possible within MySQL. Here is an outline of the problem: End result: Listing of rates based on specific queried criteria (see below): Age: [ 27 ] Spouse Age: [ 25 ] Num of Children: [ 3 ] Zip Code: [ 97128 ] The problem I am running into is each company that provides rates has a unique way of dealing with the rate. And I am looking for the best approach for multiple queries based on the company (one query with results for each company more or less all combined into one result set). Here are some facts: - Each company deals with zip code ranges which assist in the query result. - Each company has a different method of calculating the rate based on the Applicant, Spouse, Num of Children: Example, a) Company A determines rate by: Applicant + Spouse + Child(ren) = rate (age is pertinent to the applicant within a range). b) Company B determines the rate by total number of applicants like: 1, 2, 3, 4, 5, 6+ = rate (and age is ignored). First off, what would I call this type of query? Multiple nested query? And should I intertwine php within it to determine the If()s ... I apologize if this thread lacks sufficient data, so please tell me anything you would like to see.

    Read the article

  • Getting Wrong Answer in range maximum query [on hold]

    - by user3186829
    I've just learnt range minimum and maximum queries using segment trees.But when I implemented it on my own I'm getting wrong answer.Logically I don't find any mistake in my code but if any one can point it out then I would be really thankful. Code in C++: #include<bits/stdc++.h> using namespace std; #define LL long long #define mp make_pair #define pb push_back #define gc getchar_unlocked #define pc putchar_unlocked #define LD long double #define MAXN 19999999 #define max(a,b) ((a)>(b)?(a):(b)) LL P[MAXN+15]; LL ST[2*MAXN+25]; long N,M,i,A,B,K; void build(long id,long L,long R) { long M=(L+R)>>1L; long LCT=id<<1L; long RCT=LCT+1L; if(L==R) { ST[id]=P[L]; return; } build(LCT,L,M); build(RCT,M+1,R); } //Range Update of segment tree void updateST(long id,long L,long R,long Q1,long Q2,long val) { long M=(L+R)>>1L; long LCT=id<<1L; long RCT=LCT+1L; if(L>Q2||R<Q1) { return; } if(L==Q1&&R==Q2) { ST[id]+=val; return; } if(Q2<=M) { updateST(LCT,L,M,Q1,Q2,val); } else if(Q1>M) { updateST(RCT,M+1,R,Q1,Q2,val); } else { updateST(LCT,L,M,Q1,M,val); updateST(RCT,M+1,R,M+1,Q2,val); } } //Query for finding maximum element in a given range[Q1,Q2] and 1<=Q1,Q2<=N LL query2(long id,long L,long R,long Q1,long Q2) { long M=(L+R)>>1; long LCT=id<<1; long RCT=LCT+1; if(L>Q2||R<Q1) { return 0; } if(L==Q1&&R==Q2) { return ST[id]; } if(Q2<=M) { return query2(LCT,L,M,Q1,Q2); } else if(Q1>M) { return query2(RCT,M+1,R,Q1,Q2); } else { LL G=query2(LCT,L,M,Q1,M); LL H=query2(RCT,M+1,R,M+1,Q2); LL RES=max(G,H); return RES; } } int main() { scanf("%ld %ld",&N,&M); build(1,1,N); for(i=0;i<M;i++) { scanf("%ld %ld %ld",&A,&B,&K); updateST(1,1,N,A,B,K); } //Finding maximum element in range[1,N]] cout<<query2(1,1,N,1,N); return 0; }

    Read the article

  • variables used in inner queries

    - by wcpro
    im trying to build a query that has something like this select id, (select top 1 create_date from table2 where table1id = t1.id and status = 'success') [last_success_date], (select count(*) from table2 where table1id = t1.id and create_date > [last_success_date]) [failures_since_success] from table1 t1 as you can see the [last_Success_Date] is not within the scope of the second query, and i was wondering how i could access that value in other queries without having to rerun it?

    Read the article

  • Why does calling IEnumerable<string>.Count() create an additional assembly dependency ?

    - by Gishu
    Assume this chain of dll references Tests.dll >> Automation.dll >> White.Core.dll with the following line of code in Tests.dll, where everything builds result.MissingPaths Now when I change this to result.MissingPaths.Count() I get the following build error for Tests.dll "White.UIItem is not defined in an assembly that is not referenced. You must add a reference to White.Core.dll." And I don't want to do that because it breaks my layering. Here is the type definition for result, which is in Automation.dll public class HasResult { public HasResult(IEnumerable<string> missingPaths ) { MissingPaths = missingPaths; } public IEnumerable<string> MissingPaths { get; set; } public bool AllExist { get { return !MissingPaths.Any(); } } } Down the call chain the input param to this ctor is created via (The TreeNode class is in White.Core.dll) assetPaths.Where(assetPath => !FindTreeNodeUsingCache(treeHandle, assetPath)); Why does this dependency leak when calling Count() on IEnumerable ? I then suspected that lazy evaluation was causing this (for some reason) - so I slotted in an ToArray() in the above line but didn't work. Update 2011 01 07: Curiouser and Curiouser! it won't build until I add a White.Core reference. So I add a reference and build it (in order to find the elusive dependency source). Open it up in Reflector and the only references listed are Automation, mscorlib, System.core and NUnit. So the compiler threw away the White reference as it was not needed. ILDASM also confirms that there is no White AssemblyRef entry. Any ideas on how to get to the bottom of this thing (primarily for 'now I wanna know why' reasons)? What are the chances that this is an VS2010/MSBuild bug? Update 2011 01 07 #2 As per Shimmy's suggestion, tried calling the method explcitly as an extension method Enumerable.Count(result.MissingPaths) and it stops cribbing (not sure why). However I moved some code around after that and now I'm getting the same issue at a different location using IEnumerable - this time reading and filtering lines out of a file on disk (totally unrelated to White). Seems like it's a 'symptom-fix'. var lines = File.ReadLines(aFilePath).ToArray(); once again, if I remove the ToArray() it compiles again - it seems that any method that causes the enumerable to be evaluated (ToArray, Count, ToList, etc.) causes this. Let me try and get a working tiny-app to demo this issue... Update 2011 01 07 #3 Phew! More information.. It turns out the problem is just in one source file - this file is LINQ-phobic. Any call to an Enumerable extension method has to be explicitly called out. The refactorings that I did caused a new method to be moved into this source file, which had some LINQ :) Still no clue as to why this class dislikes LINQ. using System; using System.Collections.Generic; using System.IO; using System.Linq; using G.S.OurAutomation.Constants; using G.S.OurAutomation.Framework; using NUnit.Framework; namespace G.S.AcceptanceTests { public abstract class ConfigureThingBase : OurTestFixture { .... private static IEnumerable<string> GetExpectedThingsFor(string param) { // even this won't compile - although it compiles fine in an adjoining source file in the same assembly //IEnumerable<string> s = new string[0]; //Console.WriteLine(s.Count()); // this is the line that is now causing a build failure // var expectedInfo = File.ReadLines(someCsvFilePath)) // .Where(line => !line.StartsWith("REM", StringComparison.InvariantCultureIgnoreCase)) // .Select(line => line.Replace("%PLACEHOLDER%", param)) // .ToArray(); // Unrolling the LINQ above removes the build error var expectedInfo = Enumerable.ToArray( Enumerable.Select( Enumerable.Where( File.ReadLines(someCsvFilePath)), line => !line.StartsWith("REM", StringComparison.InvariantCultureIgnoreCase)), line => line.Replace("%PLACEHOLDER%", param)));

    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

  • Multisystem Script won't work! "Syntax error:redirection unexpected" Worked 2 days ago?

    - by user74005
    this is my first question. I use Multisystem all of the time and have installed it on both Kubuntu and Ubuntu and have used it with no issues. I wiped my hard drive to try some new OSs I'm now using the exact same OS (Ubuntu 12.05) I used to load my USB stick to begin with and now I'm getting this ridiculous syntax error. I know the script is correct, I'm following the exact same steps I used to get to this point and I'm getting different results ?!?! I'm very confused by this. I have no clue how to begin addressing this issue. I get the same syntax error on Kubuntu now too, which did have multisystem installed. I run "sh install-depot-multisystem.sh" and get "Syntax error:redirection unexpected", this worked literally 2 days ago. The only thing that has changed is my face has grown some more facial hair and my head hurts from bangin it against the wall over this issue. The OS is exactly the same, the script is the same; but now it won't install. I'm lost and really hoping someone can help. Append Just to append to this a bit https://lists.ubuntu.com/archives/ub...er/000264.html I needed to do a chmod 777 on the script, I'm still getting a syntax error on Kubuntu...but it did install successfully. I'll mark this as resovled! Thanks anyway, I'll try to spruse up on my Linux skills.

    Read the article

  • Selenium IDE - Passing a URL string variable into a conditional 'gotoIf' statement throws a Syntax e

    - by user318568
    I am trying to store the current url (http://example.com)in a variable and compare it with another string as a condition in the gotoIf command (part of the gotoIf extension.js): storeLocation || url gotoIf || ${url}=="http://example.com" || label When I run this seleniun ide throws this error: [error] Unexpected Exception: message -> syntax error, fileName -> chrome://selenium-ide/content/tools.js -> file:///C:/Users/David%20Cunningham/Desktop/extensions_js/extensions.js, lineNumber -> 183, stack -> eval("http://example.com==\"http://example.com\"")@:0 ("http://example.com==\"http://example.com\"","label1")@chrome://selenium-ide/content/tools.js -> file:///C:/Users/David%20Cunningham/Desktop/extensions_js/extensions.js:183 ("http://example.com==\"http://example.com\"","label1")@chrome://selenium-ide/content/selenium/scripts/htmlutils.js:60 ([object Object],[object Object])@chrome://selenium-ide/content/selenium/scripts/selenium-commandhandlers.js:310 ()@chrome://selenium-ide/content/selenium/scripts/selenium-executionloop.js:112 (6)@chrome://selenium-ide/content/selenium/scripts/selenium-executionloop.js:78 (6)@chrome://selenium-ide/content/selenium/scripts/htmlutils.js:60 , name -> SyntaxError storeLocation should return a String so why am i getting this error, what is wrong with the syntax and how do I declare this command?

    Read the article

  • EL syntax error: Expression cannot start with binary operator

    - by auser
    Anyone have any creative ideas for how I can solve this warning? EL syntax error: Expression cannot start with binary operator caused by the following code: String.format("#{myController.deleteItemById(%d)}", getId()) My code looked like this before: "#{myController.deleteItemById(" + getId() + ")}" but this caused eclipse to generate the following warning: EL syntax error: String is not closed UPDATE: @ManagedBean(name = "myController") @ViewScoped public class MyController implements Serializable { private long id; private HtmlPanelGroup panel; public long getId() {return this.id; } private void getPanel() { /// bunch of code for programatically creating a form HtmlCommandButton deleteButton = new HtmlCommandButton(); deleteButton.setId(id); deleteButton.setValue(value); deleteButton.setActionExpression(/* EL expression used here */); } } <?xml version='1.0' encoding='UTF-8' ?> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:h="http://java.sun.com/jsf/html" xmlns:f="http://java.sun.com/jsf/core" xmlns:ui="http://java.sun.com/jsf/facelets"> <!-- some other elements removed for the sake of clarity --> <h:body> <h:panelGroup binding="#{myController.panel}" /> </h:body> </html>

    Read the article

  • gunit syntax for tree walker with a flat list of nodes

    - by Kaleb Pederson
    Here's a simple gunit test for a portion of my tree grammar which generates a flat list of nodes: objectOption walks objectOption: <<one:"value">> -> (one "value") Although you define a tree in ANTLR's rewrite syntax using a caret (i.e. ^(ROOT child...)), gunit matches trees without the caret, so the above represents a tree and it's not surprising that it fails: it's a flat list of nodes and not a tree. This results in a test failure: 1 failures found: test2 (objectOption walks objectOption, line17) - expected: (one \"value\") actual: one \"value\" Another option which seems intuitive is to leave off the parenthesis, like this: objectOption walks objectOption: <<one:"value">> -> one "value" But gunit doesn't like this syntax. It seems to result in a parse failure in the gunit grammar: line 17:20 no viable alternative at input 'one' line 17:24 missing ':' at 'value' line 0:-1 no viable alternative at input '<EOF>' java.lang.NullPointerException at org.antlr.gunit.OutputTest.getExpected(OutputTest.java:65) at org.antlr.gunit.gUnitExecutor.executeTests(gUnitExecutor.java:245) ... What is the correct way to match a flat tree?

    Read the article

  • Custumizing Syntax Highlighting in Vim

    - by sixtyfootersdude
    Hey I have defined a few custom file types for vim. I have done this like this: In vimrc: au BufWinEnter,BufRead,BufNewFile *.jak set filetype=jak And then in jak.vim syn match arrows /<-/ syn match arrows /->/ syn match arrows /=>/ syn match arrows /<=/ highlight arrows ctermfg=brown ... This works. (Formatting applies to any file opened with vim with file extension .jak) My question is how I can keep all the current formatting for a file type but add functionality. For example I would like to add this functionality for .vim files: syn keyword yellow yellow highlight yellow ctermfg=yellow ... (so that I can see how my terminal interpenetrates different colors before choosing them.) I have created ~/.vim/syntax/vim.vim (file only contains the above) and put this into my vimrc: au BufWinEnter,BufRead,BufNewFile *.vim set filetype=vim This has no effect. The word yellow is not colored yellow. I have also tried putting my vim.vim file into ~/.vim/after/syntax/vim.vim As suggested here This is the approach that I would like to take. Seems clean and easily maintainable.

    Read the article

  • javascript - catch SyntaxError and run alternate function

    - by ludicco
    Hello there, I'm trying to build something on javascript that I can have an input that can be everything like string, xml, javascript and (non-javascript string without quotes) as follows: //strings eval("'hello I am a string'"); /* note the following proper quote marks */ //xml eval(<p>Hello I am a XML doc</p>); //javascript eval("var hello = 2+2;"); So this first 3 are working well since they are simple javascript native formats but when I try use this inside javascript //plain-text without quotes eval("hello I am a plain text without quotes"); //--SyntaxError: missing ; before statement:--// Obviously javascript interprets this as syntax error because it thinks its javascript throwing a SyntaxError. So what I would like to do it to catch this error and perform the adjustment method if this occurs. I've already tried with try catch but it doesn't work since it keeps returning the Syntax error as soon as it tries to execute the code. Any help would be much appreciated Cheers :) Additional Information: Imagine an external file that javascript would read, using spidermonkey, so it's a non-browser stuff(I can't use HttpRequest, DOM, etc...)..not sure if this matters, but there it is. :)

    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 | 180 181 182 183 184 185 186 187 188 189 190 191  | Next Page >