Search Results

Search found 7240 results on 290 pages for 'natural join'.

Page 55/290 | < Previous Page | 51 52 53 54 55 56 57 58 59 60 61 62  | Next Page >

• Translate an IQueryable instance to LINQ syntax in a string

  - by James Dunne

  I would like to find out if anyone has existing work surrounding formatting an IQueryable instance back into a LINQ C# syntax inside a string. It'd be a nice-to-have feature for an internal LINQ-to-SQL auditing framework I'm building. Once my framework gets the IQueryable instance from a data repository method, I'd like to output something like: This LINQ query: from ce in db.EiClassEnrollment join c in db.EiCourse on ce.CourseID equals c.CourseID join cl in db.EiClass on ce.ClassID equals cl.ClassID join t in db.EiTerm on ce.TermID equals t.TermID join st in db.EiStaff on cl.Instructor equals st.StaffID where (ce.StudentID == studentID) && (ce.TermID == termID) && (cl.Campus == campusID) select new { ce, cl, t, c, st }; Generates the following LINQ-to-SQL query: DECLARE @p0 int; DECLARE @p1 int; DECLARE @p2 int; SET @p0 = 777; SET @p1 = 778; SET @p2 = 779; SELECT [t0].[ClassEnrollmentID], ..., [t4].[Name] FROM [dbo].[ei_ClassEnrollment] AS [t0] INNER JOIN [dbo].[ei_Course] AS [t1] ON [t0].[CourseID] = [t1].[CourseID] INNER JOIN [dbo].[ei_Class] AS [t2] ON [t0].[ClassID] = [t2].[ClassID] INNER JOIN [dbo].[ei_Term] AS [t3] ON [t0].[TermID] = [t3].[TermID] INNER JOIN [dbo].[ei_Staff] AS [t4] ON [t2].[Instructor] = [t4].[StaffID] WHERE ([t0].[StudentID] = @p0) AND ([t0].[TermID] = @p1) AND ([t2].[Campus] = @p2) I already have the SQL output working as you can see. I just need to find a way to get the IQueryable to translate into a string representing its original LINQ syntax (with an acceptable translation loss). I'm not afraid of writing it myself, but I'd like to see if anyone else has done this first.

  Read the article

• Fun with Aggregates

  - by Paul White

  There are interesting things to be learned from even the simplest queries.  For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten. One possible query to meet that specification is: SELECT p.Name FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON p.ProductID = th.ProductID GROUP BY p.ProductID, p.Name HAVING COUNT_BIG(*) < 10; That query correctly returns 23 rows (execution plan and data sample shown below): The execution plan looks a bit different from the written form of the query: the base tables are accessed in reverse order, and the aggregation is performed before the join.  The general idea is to read all rows from the history table, compute the count of rows grouped by ProductID, merge join the results to the Product table on ProductID, and finally filter to only return rows where the count is less than ten. This ‘fully-optimized’ plan has an estimated cost of around 0.33 units.  The reason for the quote marks there is that this plan is not quite as optimal as it could be – surely it would make sense to push the Filter down past the join too?  To answer that, let’s look at some other ways to formulate this query.  This being SQL, there are any number of ways to write logically-equivalent query specifications, so we’ll just look at a couple of interesting ones.  The first query is an attempt to reverse-engineer T-SQL from the optimized query plan shown above.  It joins the result of pre-aggregating the history table to the Product table before filtering: SELECT p.Name FROM ( SELECT th.ProductID, cnt = COUNT_BIG(*) FROM Production.TransactionHistory AS th GROUP BY th.ProductID ) AS q1 JOIN Production.Product AS p ON p.ProductID = q1.ProductID WHERE q1.cnt < 10; Perhaps a little surprisingly, we get a slightly different execution plan: The results are the same (23 rows) but this time the Filter is pushed below the join!  The optimizer chooses nested loops for the join, because the cardinality estimate for rows passing the Filter is a bit low (estimate 1 versus 23 actual), though you can force a merge join with a hint and the Filter still appears below the join.  In yet another variation, the < 10 predicate can be ‘manually pushed’ by specifying it in a HAVING clause in the “q1” sub-query instead of in the WHERE clause as written above. The reason this predicate can be pushed past the join in this query form, but not in the original formulation is simply an optimizer limitation – it does make efforts (primarily during the simplification phase) to encourage logically-equivalent query specifications to produce the same execution plan, but the implementation is not completely comprehensive. Moving on to a second example, the following query specification results from phrasing the requirement as “list the products where there exists fewer than ten correlated rows in the history table”: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID HAVING COUNT_BIG(*) < 10 ); Unfortunately, this query produces an incorrect result (86 rows): The problem is that it lists products with no history rows, though the reasons are interesting.  The COUNT_BIG(*) in the EXISTS clause is a scalar aggregate (meaning there is no GROUP BY clause) and scalar aggregates always produce a value, even when the input is an empty set.  In the case of the COUNT aggregate, the result of aggregating the empty set is zero (the other standard aggregates produce a NULL).  To make the point really clear, let’s look at product 709, which happens to be one for which no history rows exist: -- Scalar aggregate SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = 709;   -- Vector aggregate SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = 709 GROUP BY th.ProductID; The estimated execution plans for these two statements are almost identical: You might expect the Stream Aggregate to have a Group By for the second statement, but this is not the case.  The query includes an equality comparison to a constant value (709), so all qualified rows are guaranteed to have the same value for ProductID and the Group By is optimized away. In fact there are some minor differences between the two plans (the first is auto-parameterized and qualifies for trivial plan, whereas the second is not auto-parameterized and requires cost-based optimization), but there is nothing to indicate that one is a scalar aggregate and the other is a vector aggregate.  This is something I would like to see exposed in show plan so I suggested it on Connect.  Anyway, the results of running the two queries show the difference at runtime: The scalar aggregate (no GROUP BY) returns a result of zero, whereas the vector aggregate (with a GROUP BY clause) returns nothing at all.  Returning to our EXISTS query, we could ‘fix’ it by changing the HAVING clause to reject rows where the scalar aggregate returns zero: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID HAVING COUNT_BIG(*) BETWEEN 1 AND 9 ); The query now returns the correct 23 rows: Unfortunately, the execution plan is less efficient now – it has an estimated cost of 0.78 compared to 0.33 for the earlier plans.  Let’s try adding a redundant GROUP BY instead of changing the HAVING clause: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY th.ProductID HAVING COUNT_BIG(*) < 10 ); Not only do we now get correct results (23 rows), this is the execution plan: I like to compare that plan to quantum physics: if you don’t find it shocking, you haven’t understood it properly :)  The simple addition of a redundant GROUP BY has resulted in the EXISTS form of the query being transformed into exactly the same optimal plan we found earlier.  What’s more, in SQL Server 2008 and later, we can replace the odd-looking GROUP BY with an explicit GROUP BY on the empty set: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () HAVING COUNT_BIG(*) < 10 ); I offer that as an alternative because some people find it more intuitive (and it perhaps has more geek value too).  Whichever way you prefer, it’s rather satisfying to note that the result of the sub-query does not exist for a particular correlated value where a vector aggregate is used (the scalar COUNT aggregate always returns a value, even if zero, so it always ‘EXISTS’ regardless which ProductID is logically being evaluated). The following query forms also produce the optimal plan and correct results, so long as a vector aggregate is used (you can probably find more equivalent query forms): WHERE Clause SELECT p.Name FROM Production.Product AS p WHERE ( SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () ) < 10; APPLY SELECT p.Name FROM Production.Product AS p CROSS APPLY ( SELECT NULL FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () HAVING COUNT_BIG(*) < 10 ) AS ca (dummy); FROM Clause SELECT q1.Name FROM ( SELECT p.Name, cnt = ( SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () ) FROM Production.Product AS p ) AS q1 WHERE q1.cnt < 10; This last example uses SUM(1) instead of COUNT and does not require a vector aggregate…you should be able to work out why :) SELECT q.Name FROM ( SELECT p.Name, cnt = ( SELECT SUM(1) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID ) FROM Production.Product AS p ) AS q WHERE q.cnt < 10; The semantics of SQL aggregates are rather odd in places.  It definitely pays to get to know the rules, and to be careful to check whether your queries are using scalar or vector aggregates.  As we have seen, query plans do not show in which ‘mode’ an aggregate is running and getting it wrong can cause poor performance, wrong results, or both. © 2012 Paul White Twitter: @SQL_Kiwi email: [email protected]

  Read the article

• Fun with Aggregates

  - by Paul White

  There are interesting things to be learned from even the simplest queries.  For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten. One possible query to meet that specification is: SELECT p.Name FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON p.ProductID = th.ProductID GROUP BY p.ProductID, p.Name HAVING COUNT_BIG(*) < 10; That query correctly returns 23 rows (execution plan and data sample shown below): The execution plan looks a bit different from the written form of the query: the base tables are accessed in reverse order, and the aggregation is performed before the join.  The general idea is to read all rows from the history table, compute the count of rows grouped by ProductID, merge join the results to the Product table on ProductID, and finally filter to only return rows where the count is less than ten. This ‘fully-optimized’ plan has an estimated cost of around 0.33 units.  The reason for the quote marks there is that this plan is not quite as optimal as it could be – surely it would make sense to push the Filter down past the join too?  To answer that, let’s look at some other ways to formulate this query.  This being SQL, there are any number of ways to write logically-equivalent query specifications, so we’ll just look at a couple of interesting ones.  The first query is an attempt to reverse-engineer T-SQL from the optimized query plan shown above.  It joins the result of pre-aggregating the history table to the Product table before filtering: SELECT p.Name FROM ( SELECT th.ProductID, cnt = COUNT_BIG(*) FROM Production.TransactionHistory AS th GROUP BY th.ProductID ) AS q1 JOIN Production.Product AS p ON p.ProductID = q1.ProductID WHERE q1.cnt < 10; Perhaps a little surprisingly, we get a slightly different execution plan: The results are the same (23 rows) but this time the Filter is pushed below the join!  The optimizer chooses nested loops for the join, because the cardinality estimate for rows passing the Filter is a bit low (estimate 1 versus 23 actual), though you can force a merge join with a hint and the Filter still appears below the join.  In yet another variation, the < 10 predicate can be ‘manually pushed’ by specifying it in a HAVING clause in the “q1” sub-query instead of in the WHERE clause as written above. The reason this predicate can be pushed past the join in this query form, but not in the original formulation is simply an optimizer limitation – it does make efforts (primarily during the simplification phase) to encourage logically-equivalent query specifications to produce the same execution plan, but the implementation is not completely comprehensive. Moving on to a second example, the following query specification results from phrasing the requirement as “list the products where there exists fewer than ten correlated rows in the history table”: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID HAVING COUNT_BIG(*) < 10 ); Unfortunately, this query produces an incorrect result (86 rows): The problem is that it lists products with no history rows, though the reasons are interesting.  The COUNT_BIG(*) in the EXISTS clause is a scalar aggregate (meaning there is no GROUP BY clause) and scalar aggregates always produce a value, even when the input is an empty set.  In the case of the COUNT aggregate, the result of aggregating the empty set is zero (the other standard aggregates produce a NULL).  To make the point really clear, let’s look at product 709, which happens to be one for which no history rows exist: -- Scalar aggregate SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = 709;   -- Vector aggregate SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = 709 GROUP BY th.ProductID; The estimated execution plans for these two statements are almost identical: You might expect the Stream Aggregate to have a Group By for the second statement, but this is not the case.  The query includes an equality comparison to a constant value (709), so all qualified rows are guaranteed to have the same value for ProductID and the Group By is optimized away. In fact there are some minor differences between the two plans (the first is auto-parameterized and qualifies for trivial plan, whereas the second is not auto-parameterized and requires cost-based optimization), but there is nothing to indicate that one is a scalar aggregate and the other is a vector aggregate.  This is something I would like to see exposed in show plan so I suggested it on Connect.  Anyway, the results of running the two queries show the difference at runtime: The scalar aggregate (no GROUP BY) returns a result of zero, whereas the vector aggregate (with a GROUP BY clause) returns nothing at all.  Returning to our EXISTS query, we could ‘fix’ it by changing the HAVING clause to reject rows where the scalar aggregate returns zero: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID HAVING COUNT_BIG(*) BETWEEN 1 AND 9 ); The query now returns the correct 23 rows: Unfortunately, the execution plan is less efficient now – it has an estimated cost of 0.78 compared to 0.33 for the earlier plans.  Let’s try adding a redundant GROUP BY instead of changing the HAVING clause: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY th.ProductID HAVING COUNT_BIG(*) < 10 ); Not only do we now get correct results (23 rows), this is the execution plan: I like to compare that plan to quantum physics: if you don’t find it shocking, you haven’t understood it properly :)  The simple addition of a redundant GROUP BY has resulted in the EXISTS form of the query being transformed into exactly the same optimal plan we found earlier.  What’s more, in SQL Server 2008 and later, we can replace the odd-looking GROUP BY with an explicit GROUP BY on the empty set: SELECT p.Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () HAVING COUNT_BIG(*) < 10 ); I offer that as an alternative because some people find it more intuitive (and it perhaps has more geek value too).  Whichever way you prefer, it’s rather satisfying to note that the result of the sub-query does not exist for a particular correlated value where a vector aggregate is used (the scalar COUNT aggregate always returns a value, even if zero, so it always ‘EXISTS’ regardless which ProductID is logically being evaluated). The following query forms also produce the optimal plan and correct results, so long as a vector aggregate is used (you can probably find more equivalent query forms): WHERE Clause SELECT p.Name FROM Production.Product AS p WHERE ( SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () ) < 10; APPLY SELECT p.Name FROM Production.Product AS p CROSS APPLY ( SELECT NULL FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () HAVING COUNT_BIG(*) < 10 ) AS ca (dummy); FROM Clause SELECT q1.Name FROM ( SELECT p.Name, cnt = ( SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID GROUP BY () ) FROM Production.Product AS p ) AS q1 WHERE q1.cnt < 10; This last example uses SUM(1) instead of COUNT and does not require a vector aggregate…you should be able to work out why :) SELECT q.Name FROM ( SELECT p.Name, cnt = ( SELECT SUM(1) FROM Production.TransactionHistory AS th WHERE th.ProductID = p.ProductID ) FROM Production.Product AS p ) AS q WHERE q.cnt < 10; The semantics of SQL aggregates are rather odd in places.  It definitely pays to get to know the rules, and to be careful to check whether your queries are using scalar or vector aggregates.  As we have seen, query plans do not show in which ‘mode’ an aggregate is running and getting it wrong can cause poor performance, wrong results, or both. © 2012 Paul White Twitter: @SQL_Kiwi email: [email protected]

  Read the article

• SQL SERVER – Weekly Series – Memory Lane – #037

  - by Pinal Dave

  Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. 2007 Convert Text to Numbers (Integer) – CAST and CONVERT If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function. List All Stored Procedure Modified in Last N Days If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If a stored procedure was created but never modified afterwards modified date and create a date for that stored procedure are same. Count Duplicate Records – Rows Validate Field For DATE datatype using function ISDATE() We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format. 2008 Find Space Used For Any Particular Table It is very simple to find out the space used by any table in the database. Two Convenient Features Inline Assignment – Inline Operations Here is the script which does both – Inline Assignment and Inline Operation DECLARE @idx INT = 0 SET @idx+=1 SELECT @idx Introduction to SPARSE Columns SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause). SP_CONFIGURE – Displays or Changes Global Configuration Settings If advanced settings are not enabled at configuration level SQL Server will not let user change the advanced features on server. Authorized user can turn on or turn off advance settings. 2009 Standby Servers and Types of Standby Servers Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. BLOB – Pointer to Image, Image in Database, FILESTREAM Storage When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community. 2010 Upper Case Shortcut SQL Server Management Studio I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available. The Self Join – Inner Join and Outer Join Self Join has always been a noteworthy case. It is interesting to ask questions about self join in a room full of developers. I often ask – if there are three kinds of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. However, the reality is very different. Parallelism – Row per Processor – Row per Thread – Thread 0  If you look carefully in the Properties window or XML Plan, there is “Thread 0?. What does this “Thread 0” indicate? Well find out from the blog post. How do I Learn and How do I Teach The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the same. 2011 SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31 What are Different Types of Locks? What are Pessimistic Lock and Optimistic Lock? When is the use of UPDATE_STATISTICS command? What is the Difference between a HAVING clause and a WHERE clause? What is Connection Pooling and why it is Used? What are the Properties and Different Types of Sub-Queries? What are the Authentication Modes in SQL Server? How can it be Changed? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31 Which Command using Query Analyzer will give you the Version of SQL Server and Operating System? What is an SQL Server Agent? Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible? What is Log Shipping? Name 3 ways to get an Accurate Count of the Number of Records in a Table? What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF? What is the Difference between a Local and a Global Temporary Table? What is the STUFF Function and How Does it Differ from the REPLACE Function? What is PRIMARY KEY? What is UNIQUE KEY Constraint? What is FOREIGN KEY? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 What is CHECK Constraint? What is NOT NULL Constraint? What is the difference between UNION and UNION ALL? What is B-Tree? How to get @@ERROR and @@ROWCOUNT at the Same Time? What is a Scheduled Job or What is a Scheduled Task? What are the Advantages of Using Stored Procedures? What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for? Can SQL Servers Linked to other Servers like Oracle? What is BCP? When is it Used? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31 What Command do we Use to Rename a db, a Table and a Column? What are sp_configure Commands and SET Commands? How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables? What is Difference between Commit and Rollback when Used in Transactions? What is an Execution Plan? When would you Use it? How would you View the Execution Plan? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31 What is Difference between Table Aliases and Column Aliases? Do they Affect Performance? What is the difference between CHAR and VARCHAR Datatypes? What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes? What is the Difference between VARCHAR and NVARCHAR datatypes? Which are the Important Points to Note when Multilanguage Data is Stored in a Table? How to Optimize Stored Procedure Optimization? What is SQL Injection? How to Protect Against SQL Injection Attack? How to Find Out the List Schema Name and Table Name for the Database? What is CHECKPOINT Process in the SQL Server? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31 How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away? How to Find the List of Fixed Hard Drive and Free Space on Server? Why can there be only one Clustered Index and not more than one? What is Difference between Line Feed (\n) and Carriage Return (\r)? Is It Possible to have Clustered Index on Separate Drive From Original Table Location? What is a Hint? How to Delete Duplicate Rows? Why the Trigger Fires Multiple Times in Single Login? 2012 CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis Shortcut key is CTRL+SHIFT+]. This key can be very useful when dealing with multiple subqueries, CTE or query with multiple parentheses. When exercised this shortcut key it selects T-SQL code between two parentheses. Monday Morning Puzzle – Query Returns Results Sometimes but Not Always I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me. Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video Effect of Case Sensitive Collation on Resultset Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround. Switch Between Two Parenthesis using Shortcut CTRL+] Earlier this week I wrote a blog post about CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• More CPU cores may not always lead to better performance – MAXDOP and query memory distribution in spotlight

  - by sqlworkshops

  More hardware normally delivers better performance, but there are exceptions where it can hinder performance. Understanding these exceptions and working around it is a major part of SQL Server performance tuning.   When a memory allocating query executes in parallel, SQL Server distributes memory to each task that is executing part of the query in parallel. In our example the sort operator that executes in parallel divides the memory across all tasks assuming even distribution of rows. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.   In reality, how often are column values evenly distributed, think about an example; are employees working for your company distributed evenly across all the Zip codes or mainly concentrated in the headquarters? What happens when you sort result set based on Zip codes? Do all products in the catalog sell equally or are few products hot selling items?   One of my customers tested the below example on a 24 core server with various MAXDOP settings and here are the results:MAXDOP 1: CPU time = 1185 ms, elapsed time = 1188 msMAXDOP 4: CPU time = 1981 ms, elapsed time = 1568 msMAXDOP 8: CPU time = 1918 ms, elapsed time = 1619 msMAXDOP 12: CPU time = 2367 ms, elapsed time = 2258 msMAXDOP 16: CPU time = 2540 ms, elapsed time = 2579 msMAXDOP 20: CPU time = 2470 ms, elapsed time = 2534 msMAXDOP 0: CPU time = 2809 ms, elapsed time = 2721 ms - all 24 cores.In the above test, when the data was evenly distributed, the elapsed time of parallel query was always lower than serial query.   Why does the query get slower and slower with more CPU cores / higher MAXDOP? Maybe you can answer this question after reading the article; let me know: [email protected].   Well you get the point, let’s see an example.   The best way to learn is to practice. To create the below tables and reproduce the behavior, join the mailing list by using this link: www.sqlworkshops.com/ml and I will send you the table creation script.   Let’s update the Employees table with 49 out of 50 employees located in Zip code 2001. update Employees set Zip = EmployeeID / 400 + 1 where EmployeeID % 50 = 1 update Employees set Zip = 2001 where EmployeeID % 50 != 1 go update statistics Employees with fullscan go   Let’s create the temporary table #FireDrill with all possible Zip codes. drop table #FireDrill go create table #FireDrill (Zip int primary key) insert into #FireDrill select distinct Zip from Employees update statistics #FireDrill with fullscan go  Let’s execute the query serially with MAXDOP 1. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --First serially with MAXDOP 1 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 1) goThe query took 1011 ms to complete.   The execution plan shows the 77816 KB of memory was granted while the estimated rows were 799624.  No Sort Warnings in SQL Server Profiler.  Now let’s execute the query in parallel with MAXDOP 0. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --In parallel with MAXDOP 0 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 0) go The query took 1912 ms to complete.  The execution plan shows the 79360 KB of memory was granted while the estimated rows were 799624.  The estimated number of rows between serial and parallel plan are the same. The parallel plan has slightly more memory granted due to additional overhead. Sort properties shows the rows are unevenly distributed over the 4 threads.   Sort Warnings in SQL Server Profiler.   Intermediate Summary: The reason for the higher duration with parallel plan was sort spill. This is due to uneven distribution of employees over Zip codes, especially concentration of 49 out of 50 employees in Zip code 2001. Now let’s update the Employees table and distribute employees evenly across all Zip codes.   update Employees set Zip = EmployeeID / 400 + 1 go update statistics Employees with fullscan go  Let’s execute the query serially with MAXDOP 1. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --Serially with MAXDOP 1 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 1) go   The query took 751 ms to complete.  The execution plan shows the 77816 KB of memory was granted while the estimated rows were 784707.  No Sort Warnings in SQL Server Profiler.   Now let’s execute the query in parallel with MAXDOP 0. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --In parallel with MAXDOP 0 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 0) go The query took 661 ms to complete.  The execution plan shows the 79360 KB of memory was granted while the estimated rows were 784707.  Sort properties shows the rows are evenly distributed over the 4 threads. No Sort Warnings in SQL Server Profiler.    Intermediate Summary: When employees were distributed unevenly, concentrated on 1 Zip code, parallel sort spilled while serial sort performed well without spilling to tempdb. When the employees were distributed evenly across all Zip codes, parallel sort and serial sort did not spill to tempdb. This shows uneven data distribution may affect the performance of some parallel queries negatively. For detailed discussion of memory allocation, refer to webcasts available at www.sqlworkshops.com/webcasts.     Some of you might conclude from the above execution times that parallel query is not faster even when there is no spill. Below you can see when we are joining limited amount of Zip codes, parallel query will be fasted since it can use Bitmap Filtering.   Let’s update the Employees table with 49 out of 50 employees located in Zip code 2001. update Employees set Zip = EmployeeID / 400 + 1 where EmployeeID % 50 = 1 update Employees set Zip = 2001 where EmployeeID % 50 != 1 go update statistics Employees with fullscan go  Let’s create the temporary table #FireDrill with limited Zip codes. drop table #FireDrill go create table #FireDrill (Zip int primary key) insert into #FireDrill select distinct Zip       from Employees where Zip between 1800 and 2001 update statistics #FireDrill with fullscan go  Let’s execute the query serially with MAXDOP 1. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --Serially with MAXDOP 1 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 1) go The query took 989 ms to complete.  The execution plan shows the 77816 KB of memory was granted while the estimated rows were 785594. No Sort Warnings in SQL Server Profiler.  Now let’s execute the query in parallel with MAXDOP 0. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --In parallel with MAXDOP 0 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 0) go The query took 1799 ms to complete.  The execution plan shows the 79360 KB of memory was granted while the estimated rows were 785594.  Sort Warnings in SQL Server Profiler.    The estimated number of rows between serial and parallel plan are the same. The parallel plan has slightly more memory granted due to additional overhead.  Intermediate Summary: The reason for the higher duration with parallel plan even with limited amount of Zip codes was sort spill. This is due to uneven distribution of employees over Zip codes, especially concentration of 49 out of 50 employees in Zip code 2001.   Now let’s update the Employees table and distribute employees evenly across all Zip codes. update Employees set Zip = EmployeeID / 400 + 1 go update statistics Employees with fullscan go Let’s execute the query serially with MAXDOP 1. --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --Serially with MAXDOP 1 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 1) go The query took 250  ms to complete.  The execution plan shows the 9016 KB of memory was granted while the estimated rows were 79973.8.  No Sort Warnings in SQL Server Profiler.  Now let’s execute the query in parallel with MAXDOP 0.  --Example provided by www.sqlworkshops.com --Execute query with uneven Zip code distribution --In parallel with MAXDOP 0 set statistics time on go declare @EmployeeID int, @EmployeeName varchar(48),@zip int select @EmployeeName = e.EmployeeName, @zip = e.Zip from Employees e       inner join #FireDrill fd on (e.Zip = fd.Zip)       order by e.Zip option (maxdop 0) go The query took 85 ms to complete.  The execution plan shows the 13152 KB of memory was granted while the estimated rows were 784707.  No Sort Warnings in SQL Server Profiler.    Here you see, parallel query is much faster than serial query since SQL Server is using Bitmap Filtering to eliminate rows before the hash join.   Parallel queries are very good for performance, but in some cases it can hinder performance. If one identifies the reason for these hindrances, then it is possible to get the best out of parallelism. I covered many aspects of monitoring and tuning parallel queries in webcasts (www.sqlworkshops.com/webcasts) and articles (www.sqlworkshops.com/articles). I suggest you to watch the webcasts and read the articles to better understand how to identify and tune parallel query performance issues.   Summary: One has to avoid sort spill over tempdb and the chances of spills are higher when a query executes in parallel with uneven data distribution. Parallel query brings its own advantage, reduced elapsed time and reduced work with Bitmap Filtering. So it is important to understand how to avoid spills over tempdb and when to execute a query in parallel.   I explain these concepts with detailed examples in my webcasts (www.sqlworkshops.com/webcasts), I recommend you to watch them. The best way to learn is to practice. To create the above tables and reproduce the behavior, join the mailing list at www.sqlworkshops.com/ml and I will send you the relevant SQL Scripts.   Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Hands-on Workshop in London, United Kingdom during March 15-17, 2011, click here to register / Microsoft UK TechNet.These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.   Disclaimer and copyright information:This article refers to organizations and products that may be the trademarks or registered trademarks of their various owners. Copyright of this article belongs to R Meyyappan / www.sqlworkshops.com. You may freely use the ideas and concepts discussed in this article with acknowledgement (www.sqlworkshops.com), but you may not claim any of it as your own work. This article is for informational purposes only; you use any of the suggestions given here entirely at your own risk.   Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Hands-on Workshop in London, United Kingdom during March 15-17, 2011, click here to register / Microsoft UK TechNet.These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.   R Meyyappan [email protected] LinkedIn: http://at.linkedin.com/in/rmeyyappan  

  Read the article

• JPA : Add and remove operations on lazily initialized collection behaviour ?

  - by Albert Kam

  Hello, im currently trying out JPA 2 and using Hibernate 3.6.x as the engine. I have an entity of ReceivingGood that contains a List of ReceivingGoodDetail, and has a bidirectional relation. Some related codes for each entity follows : ReceivingGood.java @OneToMany(mappedBy="receivingGood", targetEntity=ReceivingGoodDetail.class, fetch=FetchType.LAZY, cascade = CascadeType.ALL) private List<ReceivingGoodDetail> details = new ArrayList<ReceivingGoodDetail>(); public void addReceivingGoodDetail(ReceivingGoodDetail receivingGoodDetail) { receivingGoodDetail.setReceivingGood(this); } void internalAddReceivingGoodDetail(ReceivingGoodDetail receivingGoodDetail) { this.details.add(receivingGoodDetail); } public void removeReceivingGoodDetail(ReceivingGoodDetail receivingGoodDetail) { receivingGoodDetail.setReceivingGood(null); } void internalRemoveReceivingGoodDetail(ReceivingGoodDetail receivingGoodDetail) { this.details.remove(receivingGoodDetail); } @ManyToOne @JoinColumn(name = "receivinggood_id") private ReceivingGood receivingGood; ReceivingGoodDetail.java : public void setReceivingGood(ReceivingGood receivingGood) { if (this.receivingGood != null) { this.receivingGood.internalRemoveReceivingGoodDetail(this); } this.receivingGood = receivingGood; if (receivingGood != null) { receivingGood.internalAddReceivingGoodDetail(this); } } In my experiements with both of these entities, both adding the detail to the receivingGood's collection, and even removing the detail from the receivingGood's collection, will trigger a query to fill the collection before doing the add or remove. This assumption is based on my experiments that i will paste below. My concern is that : is it ok to do changes on only a little bit of records on the collection, and the engine has to query all of the details belonging to the collection ? What if the collection would have to be filled with 1000 records when i just want to edit a single record ? Here are my experiments with the output as the comment above each method : /* Hibernate: select receivingg0_.id as id9_14_, receivingg0_.creationDate as creation2_9_14_, ... too long Hibernate: select receivingg0_.id as id10_20_, receivingg0_.creationDate as creation2_10_20_, ... too long removing existing detail from lazy collection Hibernate: select details0_.receivinggood_id as receivi13_9_8_, details0_.id as id8_, details0_.id as id10_7_, details0_.creationDate as creation2_10_7_, details0_.modificationDate as modifica3_10_7_, details0_.usercreate_id as usercreate10_10_7_, details0_.usermodify_id as usermodify11_10_7_, details0_.version as version10_7_, details0_.buyQuantity as buyQuant5_10_7_, details0_.buyUnit as buyUnit10_7_, details0_.internalQuantity as internal7_10_7_, details0_.internalUnit as internal8_10_7_, details0_.product_id as product12_10_7_, details0_.receivinggood_id as receivi13_10_7_, details0_.supplierLotNumber as supplier9_10_7_, user1_.id as id2_0_, user1_.creationDate as creation2_2_0_, user1_.modificationDate as modifica3_2_0_, user1_.usercreate_id as usercreate6_2_0_, user1_.usermodify_id as usermodify7_2_0_, user1_.version as version2_0_, user1_.name as name2_0_, user2_.id as id2_1_, user2_.creationDate as creation2_2_1_, user2_.modificationDate as modifica3_2_1_, user2_.usercreate_id as usercreate6_2_1_, user2_.usermodify_id as usermodify7_2_1_, user2_.version as version2_1_, user2_.name as name2_1_, user3_.id as id2_2_, user3_.creationDate as creation2_2_2_, user3_.modificationDate as modifica3_2_2_, user3_.usercreate_id as usercreate6_2_2_, user3_.usermodify_id as usermodify7_2_2_, user3_.version as version2_2_, user3_.name as name2_2_, user4_.id as id2_3_, user4_.creationDate as creation2_2_3_, user4_.modificationDate as modifica3_2_3_, user4_.usercreate_id as usercreate6_2_3_, user4_.usermodify_id as usermodify7_2_3_, user4_.version as version2_3_, user4_.name as name2_3_, product5_.id as id0_4_, product5_.creationDate as creation2_0_4_, product5_.modificationDate as modifica3_0_4_, product5_.usercreate_id as usercreate7_0_4_, product5_.usermodify_id as usermodify8_0_4_, product5_.version as version0_4_, product5_.code as code0_4_, product5_.name as name0_4_, user6_.id as id2_5_, user6_.creationDate as creation2_2_5_, user6_.modificationDate as modifica3_2_5_, user6_.usercreate_id as usercreate6_2_5_, user6_.usermodify_id as usermodify7_2_5_, user6_.version as version2_5_, user6_.name as name2_5_, user7_.id as id2_6_, user7_.creationDate as creation2_2_6_, user7_.modificationDate as modifica3_2_6_, user7_.usercreate_id as usercreate6_2_6_, user7_.usermodify_id as usermodify7_2_6_, user7_.version as version2_6_, user7_.name as name2_6_ from ReceivingGoodDetail details0_ left outer join COMMON_USER user1_ on details0_.usercreate_id=user1_.id left outer join COMMON_USER user2_ on user1_.usercreate_id=user2_.id left outer join COMMON_USER user3_ on user2_.usermodify_id=user3_.id left outer join COMMON_USER user4_ on details0_.usermodify_id=user4_.id left outer join Product product5_ on details0_.product_id=product5_.id left outer join COMMON_USER user6_ on product5_.usercreate_id=user6_.id left outer join COMMON_USER user7_ on product5_.usermodify_id=user7_.id where details0_.receivinggood_id=? after removing try selecting the size : 4 after removing, now flushing Hibernate: update ReceivingGood set creationDate=?, modificationDate=?, usercreate_id=?, usermodify_id=?, version=?, purchaseorder_id=?, supplier_id=?, transactionDate=?, transactionNumber=?, transactionType=?, transactionYearMonth=?, warehouse_id=? where id=? and version=? Hibernate: update ReceivingGoodDetail set creationDate=?, modificationDate=?, usercreate_id=?, usermodify_id=?, version=?, buyQuantity=?, buyUnit=?, internalQuantity=?, internalUnit=?, product_id=?, receivinggood_id=?, supplierLotNumber=? where id=? and version=? detail size : 4 */ public void removeFromLazyCollection() { String headerId = "3b373f6a-9cd1-4c9c-9d46-240de37f6b0f"; ReceivingGood receivingGood = em.find(ReceivingGood.class, headerId); // get existing detail ReceivingGoodDetail detail = em.find(ReceivingGoodDetail.class, "323fb0e7-9bb2-48dc-bc07-5ff32f30e131"); detail.setInternalUnit("MCB"); System.out.println("removing existing detail from lazy collection"); receivingGood.removeReceivingGoodDetail(detail); System.out.println("after removing try selecting the size : " + receivingGood.getDetails().size()); System.out.println("after removing, now flushing"); em.flush(); System.out.println("detail size : " + receivingGood.getDetails().size()); } /* Hibernate: select receivingg0_.id as id9_14_, receivingg0_.creationDate as creation2_9_14_, ... too long Hibernate: select receivingg0_.id as id10_20_, receivingg0_.creationDate as creation2_10_20_, ... too long adding existing detail into lazy collection Hibernate: select details0_.receivinggood_id as receivi13_9_8_, details0_.id as id8_, details0_.id as id10_7_, details0_.creationDate as creation2_10_7_, details0_.modificationDate as modifica3_10_7_, details0_.usercreate_id as usercreate10_10_7_, details0_.usermodify_id as usermodify11_10_7_, details0_.version as version10_7_, details0_.buyQuantity as buyQuant5_10_7_, details0_.buyUnit as buyUnit10_7_, details0_.internalQuantity as internal7_10_7_, details0_.internalUnit as internal8_10_7_, details0_.product_id as product12_10_7_, details0_.receivinggood_id as receivi13_10_7_, details0_.supplierLotNumber as supplier9_10_7_, user1_.id as id2_0_, user1_.creationDate as creation2_2_0_, user1_.modificationDate as modifica3_2_0_, user1_.usercreate_id as usercreate6_2_0_, user1_.usermodify_id as usermodify7_2_0_, user1_.version as version2_0_, user1_.name as name2_0_, user2_.id as id2_1_, user2_.creationDate as creation2_2_1_, user2_.modificationDate as modifica3_2_1_, user2_.usercreate_id as usercreate6_2_1_, user2_.usermodify_id as usermodify7_2_1_, user2_.version as version2_1_, user2_.name as name2_1_, user3_.id as id2_2_, user3_.creationDate as creation2_2_2_, user3_.modificationDate as modifica3_2_2_, user3_.usercreate_id as usercreate6_2_2_, user3_.usermodify_id as usermodify7_2_2_, user3_.version as version2_2_, user3_.name as name2_2_, user4_.id as id2_3_, user4_.creationDate as creation2_2_3_, user4_.modificationDate as modifica3_2_3_, user4_.usercreate_id as usercreate6_2_3_, user4_.usermodify_id as usermodify7_2_3_, user4_.version as version2_3_, user4_.name as name2_3_, product5_.id as id0_4_, product5_.creationDate as creation2_0_4_, product5_.modificationDate as modifica3_0_4_, product5_.usercreate_id as usercreate7_0_4_, product5_.usermodify_id as usermodify8_0_4_, product5_.version as version0_4_, product5_.code as code0_4_, product5_.name as name0_4_, user6_.id as id2_5_, user6_.creationDate as creation2_2_5_, user6_.modificationDate as modifica3_2_5_, user6_.usercreate_id as usercreate6_2_5_, user6_.usermodify_id as usermodify7_2_5_, user6_.version as version2_5_, user6_.name as name2_5_, user7_.id as id2_6_, user7_.creationDate as creation2_2_6_, user7_.modificationDate as modifica3_2_6_, user7_.usercreate_id as usercreate6_2_6_, user7_.usermodify_id as usermodify7_2_6_, user7_.version as version2_6_, user7_.name as name2_6_ from ReceivingGoodDetail details0_ left outer join COMMON_USER user1_ on details0_.usercreate_id=user1_.id left outer join COMMON_USER user2_ on user1_.usercreate_id=user2_.id left outer join COMMON_USER user3_ on user2_.usermodify_id=user3_.id left outer join COMMON_USER user4_ on details0_.usermodify_id=user4_.id left outer join Product product5_ on details0_.product_id=product5_.id left outer join COMMON_USER user6_ on product5_.usercreate_id=user6_.id left outer join COMMON_USER user7_ on product5_.usermodify_id=user7_.id where details0_.receivinggood_id=? after adding try selecting the size : 5 after adding, now flushing Hibernate: update ReceivingGood set creationDate=?, modificationDate=?, usercreate_id=?, usermodify_id=?, version=?, purchaseorder_id=?, supplier_id=?, transactionDate=?, transactionNumber=?, transactionType=?, transactionYearMonth=?, warehouse_id=? where id=? and version=? detail size : 5 */ public void editLazyCollection() { String headerId = "3b373f6a-9cd1-4c9c-9d46-240de37f6b0f"; ReceivingGood receivingGood = em.find(ReceivingGood.class, headerId); // get existing detail ReceivingGoodDetail detail = em.find(ReceivingGoodDetail.class, "323fb0e7-9bb2-48dc-bc07-5ff32f30e131"); detail.setInternalUnit("MCB"); System.out.println("adding existing detail into lazy collection"); receivingGood.addReceivingGoodDetail(detail); System.out.println("after adding try selecting the size : " + receivingGood.getDetails().size()); System.out.println("after adding, now flushing"); em.flush(); System.out.println("detail size : " + receivingGood.getDetails().size()); } Please share your experience on this matter ! Thank you !

  Read the article

• Scala puts precedence on implicit conversion over "natural" operations... Why? Is this a bug? Or am

  - by Alex R

  This simple test, of course, works as expected: scala var b = 2 b: Int = 2 scala b += 1 scala b res3: Int = 3 Now I bring this into scope: class A(var x: Int) { def +=(y:Int) { this.x += y } } implicit def int2A(i:Int) : A = new A(i) I'm defining a new class and a += operation on it. I never expected this would affect the way my regular Ints behave. But it does: scala var b:Int = 0 b: Int = 0 scala b += 1 scala b res29: Int = 0 scala b += 2 scala b res31: Int = 0 Scala seems to prefer the implicit conversion over the natural += that is already defined to Ints. That leads to several questions... Why? Is this a bug? Is it by design? Is there a work-around (other than not using "+=")? Thanks

  Read the article

• Atomic UPSERT in SQL Server 2005

  - by rabidpebble

  What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists, INSERT otherwise) in SQL Server 2005? I see a lot of code on SO (e.g. see http://stackoverflow.com/questions/639854/tsql-check-if-a-row-exists-otherwise-insert) with the following two-part pattern: UPDATE ... FROM ... WHERE <condition> -- race condition risk here IF @@ROWCOUNT = 0 INSERT ... or IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0 -- race condition risk here INSERT ... ELSE UPDATE ... where will be an evaluation of natural keys. None of the above approaches seem to deal well with concurrency. If I cannot have two rows with the same natural key, it seems like all of the above risk inserting rows with the same natural keys in race condition scenarios. I have been using the following approach but I'm surprised not to see it anywhere in people's responses so I'm wondering what is wrong with it: INSERT INTO <table> SELECT <natural keys>, <other stuff...> FROM <table> WHERE NOT EXISTS -- race condition risk here? ( SELECT 1 FROM <table> WHERE <natural keys> ) UPDATE ... WHERE <natural keys> (Note: I'm assuming that rows will not be deleted from this table. Although it would be nice to discuss how to handle the case where they can be deleted -- are transactions the only option? Which level of isolation?) Is this atomic? I can't locate where this would be documented in SQL Server documentation.

  Read the article

• Can PetaPoco populate an object using a stored procedure with a join clause?

  - by Mark Kadlec

  I have a stored procedure that does something similar to: SELECT a.TaskId, b.CompanyCode FROM task a JOIN company b ON b.CompanyId = a.CompanyId; I have an object called TaskItem that has the TaskId and CompanyCode properties, but when I execute the following (which I would have assumed worked): var masterDatabase = new Database("MasterConnectionString"); var s = PetaPoco.Sql.Builder.Append("EXEC spGetTasks @@numberOfTasks = @0", numberOfTasks); var tasks = masterDatabase.Query<Task>(s); The problem is that the CompanyCode column does not exist in the task table, I did a trace and it seems that PetaPoco is trying to select all the properties from the task table and populating using the stored procedure. How can I use PetaPoco to simply populate the list of task objects with the results of the stored procedure?

  Read the article

• how to join a set of XElements to the values of a struct?

  - by jcollum

  Let's say I have a struct that contains local environments: public struct Environments { public const string Dev = "DEV"; public const string Qa1 = "SQA"; public const string Prod1 = "PROD"; public const string Prod2 = "PROD_SA"; public const string Uat = "UAT"; } And I'd like to pull a set of XElements out of an xml doc, but only those elements that have a key that matches a value in a struct. this.environments =(from e in settings.Element("Settings").Element("Environments") .Elements("Environment") .Where( x => x.HasAttribute("name") ) join f in [struct?] on e.Attribute("name") equals [struct value?]).ToDictionary(...) How would I go about doing this? Do I need reflection to get the values of the constants in the struct?

  Read the article

• Rails - How do i update a records value in a join model ?

  - by ChrisWesAllen

  I have a join model in a HABTM relationship with a through association (details below). I 'm trying to find a record....find the value of that records attribute...change the value and update the record but am having a hard time doing it. The model setup is this User.rb has_many :choices has_many :interests, :through => :choices Interest.rb has_many :choices has_many :users, :through => :choices Choice.rb belongs_to :user belongs_to :interest and Choice has the user_id, interest_id, score as fields. And I find the ?object? like so @choice = Choice.where(:user_id => @user.id, :interest_id => interest.id) So the model Choice has an attribute called :score. How do I find the value of the score column....and +1/-1 it and then resave? I tried @choice.score = @choice.score + 1 @choice.update_attributes(params[:choice]) flash[:notice] = "Successfully updated choices value." but I get "undefined method score"......What did i miss?

  Read the article

• Join DELETE in MySql? How to?

  - by Camran

  I have this: $query="DELETE FROM classified, $sql_table WHERE classified.ad_id = '$id' AND classified.classified_id = $sql_table.classified_id AND classified.poster_password='$pass'"; I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE classified.ad_id = 'Bmw_M3_E46_Full_29920' AND classified.cla' at line 1 Any help? As you can see the $sql_table is linked to the classifieds table with the fields classified_id I need to JOIN DELETE somehow. Basically classified table is the main table, then every category has its own tables with vehicle data. classified table has a field called classified_id which is the same as the Here is the full query echoed: DELETE FROM classified, vehicles WHERE classified.ad_id = 'Bmw_M3_E46_410811305' AND classified.classified_id = vehicles.classified_id AND classified.poster_password='some_password' Why isn't this working, Should it be so hard to delete from multiple tables? Thanks

  Read the article

• How to perform a join with CodeIgniter's Active Record class on a multicolumn key?

  - by Scott Southworth

  I've been able to make this code work using CodeIgniter's db->query as follows: $sql = 'SELECT mapping_code,zone_name,installation_name FROM installations,appearances,zones WHERE installations.installation_id = zones.installation_fk_id AND appearances.installation_fk_id = installations.installation_id AND appearances.zone_fk_id = zones.zone_id AND appearances.barcode = ? '; return $this->db->query($sql,array($barcode)); The 'appearances' table throws a 'not unique table' error if I try this using the Active Record class. I need to join appearances on both the zone and installations tables. How can I do this?

  Read the article

• Can I join between two MySQL tables stores on separate machines?

  - by CuriousCoder

  I have a relatively light query that needs information from a local MySQL table along with another MySQL table which is stored on a physically separate machine (on the same network). I'm keen to avoid setting up replication just to facilitate this light query that only needs executed once a day. Is there any way that I can join with a table on a remote machine using one query? Or run a SELECT INTO into a local table. Notes I'm using C# & .NET 4.

  Read the article

• Can I join 2+ styles together into a superstyle?

  - by Kurru

  Hi I was looking to join 2 styles together to make a super style for easy use and customisation of my page. Is it possible to define something like this? (if so how) .bold { font-weight: bold;} .color1 {color: white;} .boldColor {.bold; .color1;} where .boldColor is effectively .boldColor {font-weight:bold; color:white;} I want this so that I can have styles thoughout the page and be able to easily change the colors in many places in 1 place. I'm currently using <p class="bold color"> but some of my class defs are becoming long so I'd like to be able to use <p class="boldColor"> Thanks

  Read the article

• In which layer should I join 2 entities together?

  - by William

  I use Spring MVC and a regular JDBC. I've just learned that I should separate business process into layers which are presentation layer, controller layer, service layer, and repository/DAO layer. Now suppose that I have an Entity called Person that can have multiple Jobs. Job itself is another entity which have its own properties. From what I gathered, the repository layer only manages one entity. Now I have one entity that contains another entity. Where do I "join" them? The service layer? Suppose I want to get a person whose job isn't known yet (lazy loading). But the system might ask what the job of that particular person is later on. What is the role of each layer in this case? Please let me know if I need to add any detail into this question.

  Read the article

• Can anyone help me with a complex sum, 3 table join mysql query?

  - by Scarface

  Hey guys I have a query and it works fine, but I want to add another table to the mix. The invite table I want to add has two fields: username and user_invite. Much like this site, I am using a point system to encourage diligent users. The current query which is displayed below adds the up votes and down votes based on the user in question: $creator. I want to count the number of entries for that same user from the invite table, and add 50 for each row it finds to the current output/sum of my query. Is this possible with one query, or do I need two? "SELECT *, SUM(IF(points_id = \"1\", 1,0))-SUM(IF(points_id = \"2\", 1,0)) AS 'total' FROM points LEFT JOIN post ON post.post_id=points.points_id WHERE post.creator='$creator'"

  Read the article

• How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way?

  - by EugeneP

  How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? As I see it returns a list of Objects. yes, it is tricky and only you who write the query know what should the query return (what objects). But are there ways to simplify things, so that it returned specific objects with no need in casting Object to a specific class according to its position in the query ? Maybe Spring can simplify things here? It has the similar functionality for JDBC, but I don't see if it can help in a similar way with Hibernate.

  Read the article

• Any Way to Use a Join in a Lambda Where() on a Table<>?

  - by lush

  I'm in my first couple of days using Linq in C#, and I'm curious to know if there is a more concise way of writing the following. MyEntities db = new MyEntities(ConnString); var q = from a in db.TableA join b in db.TableB on a.SomeFieldID equals b.SomeFieldID where (a.UserID == CurrentUser && b.MyField == Convert.ToInt32(MyDropDownList.SelectedValue)) select new { a, b }; if(q.Any()) { //snip } I know that if I were to want to check the existence of a value in the field of a single table, I could just use the following: if(db.TableA.Where(u => u.UserID == CurrentUser).Any()) { //snip } But I'm curious to know if there is a way to do the lambda technique, but where it would satisfy the first technique's conditions across those two tables. Sorry for any mistakes or clarity, I'll edit as necessary. Thanks in advance.

  Read the article

• Not your usual "The multi-part identifier could not be bound" error

  - by Eugene Niemand

  I have the following query, now the strange thing is if I run this query on my development and pre-prod server it runs fine. If I run it on production it fails. I have figured out that if I run just the Select statement its happy but as soon as I try insert into the table variable it complains. DECLARE @RESULTS TABLE ( [Parent] VARCHAR(255) ,[client] VARCHAR(255) ,[ComponentName] VARCHAR(255) ,[DealName] VARCHAR(255) ,[Purchase Date] DATETIME ,[Start Date] DATETIME ,[End Date] DATETIME ,[Value] INT ,[Currency] VARCHAR(255) ,[Brand] VARCHAR(255) ,[Business Unit] VARCHAR(255) ,[Region] VARCHAR(255) ,[DealID] INT ) INSERT INTO @RESULTS SELECT DISTINCT ClientName 'Parent' ,F.ClientID 'client' ,ComponentName ,A.DealName ,CONVERT(SMALLDATETIME , ISNULL(PurchaseDate , '1900-01-01')) 'Purchase Date' ,CONVERT(SMALLDATETIME , ISNULL(StartDate , '1900-01-01')) 'Start Date' ,CONVERT(SMALLDATETIME , ISNULL(EndDate , '1900-01-01')) 'End Date' ,DealValue 'Value' ,D.Currency 'Currency' ,ShortBrand 'Brand' ,G.BU 'Business Unit' ,C.DMRegion 'Region' ,DealID FROM LTCDB_admin_tbl_Deals A INNER JOIN dbo_DM_Brand B ON A.BrandID = B.ID INNER JOIN LTCDB_admin_tbl_DM_Region C ON A.Region = C.ID INNER JOIN LTCDB_admin_tbl_Currency D ON A.Currency = D.ID INNER JOIN LTCDB_admin_tbl_Deal_Clients E ON A.DealID = E.Deal_ID INNER JOIN LTCDB_admin_tbl_Clients F ON E.Client_ID = F.ClientID INNER JOIN LTCDB_admin_tbl_DM_BU G ON G.ID = A.BU INNER JOIN LTCDB_admin_tbl_Deal_Components H ON A.DealID = H.Deal_ID INNER JOIN LTCDB_admin_tbl_Components I ON I.ComponentID = H.Component_ID WHERE EndDate != '1899-12-30T00:00:00.000' AND StartDate < EndDate AND B.ID IN ( 1 , 2 , 5 , 6 , 7 , 8 , 10 , 12 ) AND C.SalesRegionID IN ( 1 , 3 , 4 , 11 , 16 ) AND A.BU IN ( 1 , 2 , 3 , 4 , 5 , 6 , 8 , 9 , 11 , 12 , 15 , 16 , 19 , 20 , 22 , 23 , 24 , 26 , 28 , 30 ) AND ClientID = 16128 SELECT ... FROM @Results I get the following error Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1021.ComponentName" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1011.Currency" could not be bound. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2454'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2461'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2491'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2490'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2482'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2478'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2477'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Col2475'. EDIT - EDIT - EDIT - EDIT - EDIT - EDIT through a process of elimination I have found that following and wondered if anyone can shed some light on this. If I remove only the DISTINCT the query runs fine, add the DISTINCT and I get strange errors. Also I have found that if I comment the following lines then the query runs with the DISTINCT what is strange is that none of the columns in the table LTCDB_admin_tbl_Deal_Components is referenced so I don't see how the distinct will affect that. INNER JOIN LTCDB_admin_tbl_Deal_Components H ON A.DealID = H.Deal_ID

  Read the article

• TSQL -- Make it better

  - by user319353

  Hi: -- Very Narrow (all IDs are passed in) IF(@EmpID IS NOT NULL AND @DeptID IS NOT NULL AND @CityID IS NOT NULL) BEGIN SELECT e.EmpName ,d.DeptName ,c.CityName FROM Employee e WITH (NOLOCK) JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID WHERE e.EmpID = @EmpID END -- Just 2 IDs passed in ELSE IF(@DeptID IS NOT NULL AND @CityID IS NOT NULL) BEGIN SELECT e.EmpName ,d.DeptName ,NULL AS [CityName] FROM Employee e WITH (NOLOCK) JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID WHERE d.deptID = @DeptID END -- Very Broad (just 1 ID passed in) ELSE IF(@CityID IS NOT NULL) BEGIN SELECT e.EmpName ,NULL AS [DeptName] ,NULL AS [CityName] FROM Employee e WITH (NOLOCK) JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID WHERE c.CityID = @CityID END -- None (Nothing passed in) ELSE BEGIN SELECT NULL AS [EmpName] ,NULL AS [DeptName] ,NULL AS [CityName] END Question: Is there any better way (OR specifically can I do anything without IF...ELSE condition?

  Read the article

• SQL Loop over a family tree

  - by simon831

  Using SQL server 2008. I have a family tree of animals stored in a table, and want to give some information on how 'genetically diverse' (or not) the offspring is. In SQL how can I produce sensible metrics to show how closely related the parents are? Perhaps some sort of percentage of shared blood, or a number of generations to go back before there is a shared ancestor? AnimalTable Id Name mumId dadId select * from AnimalTable child inner join AnimalTable mum on child.[mumId] = mum.[Id] inner join AnimalTable dad on child.[dadId] = dad.[Id] inner join AnimalTable mums_mum on mum.[mumId] = mums_mum.[Id] inner join AnimalTable mums_dad on mum.[dadId] = mums_dad.[Id] inner join AnimalTable dads_mum on dad.[mumId] = dads_mum.[Id] inner join AnimalTable dads_dad on dad.[dadId] = dads_dad.[Id]

  Read the article

• DB function failed with error number 1 in joomla admin panel

  - by sabuj

  When i access joomla article manager or module manager then i had faced the bellow output: 500 - An error has occurred! DB function failed with error number 1 Can't create/write to file '/tmp/#sql_57c0_0.MYD' (Errcode: 17) SQL=SELECT c.*, g.name AS groupname, cc.title AS name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM jos_content AS c LEFT JOIN jos_categories AS cc ON cc.id = c.catid LEFT JOIN jos_sections AS s ON s.id = c.sectionid LEFT JOIN jos_groups AS g ON g.id = c.access LEFT JOIN jos_users AS u ON u.id = c.checked_out LEFT JOIN jos_users AS v ON v.id = c.created_by LEFT JOIN jos_content_frontpage AS f ON f.content_id = c.id WHERE c.state != -2 ORDER BY section_name , section_name, cc.title, c.ordering LIMIT 0, 20

  Read the article

• Access is refusing to run an query with linked table?

  - by Mahmoud

  Hey all i have 3 tables each as follow cash_credit Bank_Name-------in_date-------Com_Id---Amount America Bank 15/05/2010 1 200 HSBC 17/05/2010 3 500 Cheque_credit Bank_Name-----Cheque_Number-----in_date-------Com_Id---Amount America Bank 74835435-5435 15/05/2010 2 600 HSBC 41415454-2851 17/05/2010 5 100 Companies com_id----Com_Name 1 Ebay 2 Google 3 Facebook 4 Amazon Companies table is a linked table when i tried to create an query as follow SELECT cash_credit.Amount, Companies.Com_Name, cheque_credit.Amount FROM cheque_credit INNER JOIN (cash_credit INNER JOIN Companies ON cash_credit.com_id = Companies.com_id) ON cheque_credit.com_id = Companies.com_id; I get an error saying that my inner Join is incorrectly, this query was created using Access 2007 query design the error is Type mismatch in expression then i thought it might be the inner join so i tried Left Join and i get an error that this method is not used JOIN expression is not supported I am confused on where is the problem that is causing all this

  Read the article

• Are these 2 sql queries equivalent in all respects (e.g. estimated and actual execution plan)?

  - by Xerion

  Are query 1) == 2) in terms of estimated query plan AND actual plan? (can statistics affect the actual plan here, ever?) declare @cat int -- input param from prc ... 1) select * from A as a join B as b on b.id = a.id on b.cat = @cat join C as c on c.fid = b.fid on c.cat = @cat where a.cat = @cat 2) select * from A as a join B as b on b.id = a.id on b.cat = a.cat join C as c on c.fid = b.fid on c.cat = b.cat where a.cat = @cat It seems to me that these should logically be equivalent and the execution plan should always be the same regardless of actual difference in tables. And adding more conditions either in join, or where, or add more tables to join shouldn't change this. Are there cases this is not true?

  Read the article

< Previous Page | 51 52 53 54 55 56 57 58 59 60 61 62  | Next Page >