.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

problem with merge statement query and parallelism plan

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

View Complete Post

More Related Resource Links

Query execution plan problem

Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

Weird query plan problem where total is over 100%

Hi All, I am looking at the query plan for a stored procedure and I am seeing things like 1400% on the execution plan, the stored procedure I believe is called within a loop quite a lot of times, but I don’t quite understand why the percentages will be over 100 for one section of the execution plan. Any explanation for this ? Thanks in advance.

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Need help to merge all the code into only one query

Hi guys,   Can anyone help me, please to merge all the queries below into one query? Thanks in advance, Aldo.   DECLARE @i smalldatetime, @FirstDate smalldatetime, @LastDate smalldatetime, @SortGroupStart INT, @SortGroupEnd INT, @TargetSales REAL; SET @FirstDate = '2008-10-01T00:00:00' SET @LastDate = '2008-10-31T00:00:00' SET @SortGroupStart = 3000; SET @SortGroupEnd = 3999; SET @TargetSales = 300000;   if object_id('MonthlySalesIncrement_20081002000_Calendar') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000_Calendar'); CREATE TABLE MonthlySalesIncrement_20081002000_Calendar (CalendarDate smalldatetime PRIMARY KEY) WHILE @FirstDate <= @LastDate BEGIN INSERT INTO MonthlySalesIncrement_20081002000_Calendar (CalendarDate) VALUES (convert(smalldatetime, @FirstDate, 121)) SET @FirstDate = DateAdd(dd, 1, @FirstDate); END   if object_id('MonthlySalesIncrement_20081002000') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000'); SELECT convert(smalldatetime, Stock.VALUEDATE, 120) AS 'Stock.VALUEDATE', StockMoves.DOCUMENTID AS 'StockMoves.DOCUMENTID', StockMoves.SUPPLYQUANTITY AS 'StockMoves.SUPPLYQUANTITY', StockMoves.PRICE AS 'StockMoves.PRICE', StockMoves.RATE AS 'StockMoves.RATE', StockMoves.DISCOUNTPRC AS 'StockMoves.DISCOUNTPRC', Stock.DISCOUNTPRC AS 'Stock.DISCOUNTPRC', RATES.RATE AS 'RATES.RATE' INTO MonthlySalesIn

ODBC Select if Statement in MAS90 query

Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Problem with Content Query Webpart and Custom Userfields

Dear Community, I am using Sharepoint 2007's content query webpart to access content types from 2 lists each contained in a different sitecollection. To achieve this, I used this tutorial: http://www.heathersolomon.com/blog/articles/CustomItemStyle.aspx. The tutorial worked great. By using the property CommonViewFields and modifing the XML Stylesheets accordingly, i could access almost every field. Even User Fieldtypes like editor and author were accessible by adding (editor,User) or (author,User) to the CommonViewFields property. But when I tried to access the custom made userfield "Username" in the same way (adding Username,User to the property), the webpart threw an error and couldn't be displayed anymore. When I changed it to Username,Text , no error occured but it naturally didn't show up, since it is the wrong fieldtype. I hope some of you can help me, since all my researches on this topic only brought up some more people with the same problem, but no solution to it. Thanks in advance, SoundofSilence

Merge Statement Error

I'm getting the error: Attempting to set a non-NULL-able column's value to NULL. And I can't figure why the error is occurring. Code for replication in SSMS using temp tables: Yes, it's somewhat redundant, it's all dummy tables mimicing our actual setup, this is one of several similar merge statements in an SP, however only this one throws any errors. As for testing: the first pass works fine, but on a second run of just the section marked for testing it will throw the above error when it hits the update section, specifically the phoneNumberOrder seems to be causing it. Interestingly, if you comment out that block and try rerunning, it will throw the same error but it now appears to stem from the INSERT statement's phoneNumberOrder block. Commenting that out will allow the statement to run just fine. The two problem sections are indicated by comments. Any insight is greatly appreciated. -------------------------------------------------Run Once CREATE TABLE #User_PhoneNumbers(     [phoneNumberID] [bigint] IDENTITY(1,1) NOT NULL,     [userID] [bigint] NOT NULL,     [phoneNumberOrder] [int] NOT NULL,     [countryCode] [nvarchar](6) NOT NULL,     [areaCode] [nvarchar](3) NOT NULL,     [localCode] [nvarchar](3) NOT NULL,     [lineNumber] [nvarchar](4) NOT NULL,   

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Merge Replication and "Field size too large" problem

Hi,I have a single table database for tests configured on my server, I create another database using the same script that I use on my server on another server. I made a several inserts on the first database and I configure a merge replication between the two server and on the table article configure 2 integer columns to not be replicated and configure @pre_creation_cmd to none, because if I use another configuration on @pre_creation_cmd , when the snapshot applied it must recreated my table without the columns and I need then on another server. When I set @pre_creation_cmd  to none I need to create a rowguid column on the another server. My server is the Publisher and my another server is the publisher. I create the snapshot and when I sinconize the databases, the sinconization return that error:=================================================================================================The process could not bulk copy into table '"dbo"."CONSTS"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)Get help: http://help/MSSQL_REPL20037 Field size too large (Source: MSSQLServer, Error number: 0)Get help: http://help/0 To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options. (Source:

Query help to write case statement in Where clause?

Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

Sharepoint 2010 FullTextSqlQuery query problem with where condition

Hello, i´m developing a webpart under Sharepoint 2010, i need to launch some different query´s to filter data but i´m having problems. First of all, i launch this query and i get all the users; SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') ORDER BY "Rank" DESC with that results i fill different dropdownlist with each of the columns, then I can filter for each column. For example: I filter by PreferredName, Lastname or WorkEmail, with this query SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("LastName" Like '%MOSS%')) ORDER BY "Rank" DESC But when i try to use the columns Department, OfficeNumber and Jobtitle every query returns 0 rows. SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("Department" Like '%Dirección%')) ORDER BY "Rank" DESC The main thing is if i get all the data i see that these columns (officeNumber, Jobtitle, Department) are not empty. Some of them have values some of them don´t. All the columns have the property "Reduce storage recquirement for text propert
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend