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

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

CURSOR performance on multiple delete/insert and maybe poor table design

Posted By:      Posted Date: October 17, 2010    Points: 0   Category :Sql Server

We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

View Complete Post

More Related Resource Links

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

Help required regarding audit (Update/INSERT/DELETE) a table in Database.


Hi all


Help required regarding audit (Update/INSERT/DELETE) a table in Database.



I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.


Now I want find that who is doing this


Which table design is good for performance?


hi All,

I have situation to create table with datetime column for OLAP (more select stmt.) database with following conditions,

1.       Need to insert only date (no need time)

2.       Only SQL Server 2005 or less can be used.

3.       More than 10 million records capacity of the table.


use tempdb
drop table T1;
drop table T2;
create table T1(col1 int, colD tinyint, colM tinyint, colY smallint);
create table T2(col1 int, colDate datetime);
insert in

Updating multiple Indexes on a table - Performance issue



I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated


insert performance on sql server using multiple connections


Hi all,

I have a c++ code using ADO to do connection to sql server 2000. i want to find out whether using multiple connections can improve the insert performance. it seems like it does not improve any insertion performance, when i say performance i mean the time taken to insert into the database. I am current able to insert about 1000 entries / sec into the database. using 2 connections, i was expecting something like 2000 entries / sec. however the performance still stood at 1000 entries / sec.

using query analyzer, i did a simple test.

declare @i int
select @i = 1
while @i <= 10000
   insert into myTable (...) values (...)
   select @i = @i + 1

this took 40sec. but opening another session, the total time taken to insert 20000 entries in total took 80 sec.

are there any multi threading at SQL server??

Pls help. thanks

Single insert/delete takes more than 5 sec on merge replicated table.


Dear experts,

Environment: MSSQL standard edition – 2008 / Windows server 2008 (64 bit)

I have lots of tables with merge publication (filtered) on windows 2008 server and it takes few seconds to insert/ delete single row on that database.


This is what execution plan shows.

Delete single row: 7 sec. It shows 47 queries.

Insert single row (2 columns): 4 sec. 35 queries.


What I can see it MSmerge_past_partition_mappings and MSmerge_current_partition_mappings table take 15%-30% of cost.


Please help to to resole this issue. I can send execution plan if anyone interested. 

How to archieve the field template that can update or insert multiple field value in database table,



How to archieve the field template that can update or insert multiple field value in database table,please? 

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Under the Table: How Data Access Code Affects Database Performance


In this article, the author delves into some commonly used ways of writing data access code and looks at the effect they can have on performance.

Bob Beauchemin

MSDN Magazine August 2009

Performance r/w xml and dealing with multiple access.



I've working on a chat type project at the moment. I was originally using a DB but decided to move to XML which has improved speed a lot and also made the control more portable. I have two related issues that I was hoping you guys could pass comment on.

The first is dealing with multiple users either reading or trying to write to the xml file when someone else already has it open. I've had to be an Exception loop around the write process to 'queue' the writes from different users. This means that sometimes there is now an issue with speed of response.

The second issue is to do with the speed at which my code read and writes xml. Speeding it up would help my app generally and but would also help with issue one.

Here is my code. Just so you are aware, when someone makes a post it does Savemessage and then Getmessages. Getmessages also runs every 3 seconds regardless:

public List<Chat.Message> Getmessages(int numberOfMessages, int lastMsgID)
        List<Chat.Message> res = new List<Chat.Message>();

        DataSet xmlDB = new DataSet("xmlMSG");        

        if (xmlDB.Tables.Count > 0)
            // I only want a maximum of the last

Need to add value to table on insert



I'm fairly new to .NET.  I need to add values CreatedBy, CreatedDate, ModifiedBy, ModifiedDate to the record before inserting it to the database. 

Please see code below. 

     [MetadataType (typeof(GRIPSMetaData))]
    public partial class SchoolDistrict
        internal void OnSaving(ChangeAction changeAction)
            if (changeAction == ChangeAction.Insert)
              this.CreatedBy = 5;
              this.CreatedDate = DateTime.Now;
            else if (changeAction == ChangeAction.Update)
                this.CreatedBy = 5;

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

How to setup db diagram to automatically insert username into table?

 I have a table setup in the database for users who are basically going to create a classified listing.  How can I setup a diagram that would easily create a table relationship with the Users table, so that it automatically inserts the users username when they submit a new ad?  If so, how?  I've tried adding a UserName row to my table and creating a relationship with the Users table "UserName" row, but it wasn't accepting the relationship when I tried to save the diagram...apparently because the UserName row in the Users table isn't a primary key?Also, one this is setup, what's the best way to provide the user a way to see a list of their ads and have the ability to edit them?

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

Very poor performance from SQL/CLR

I have been developing with SQL/CLR recently, by creating stored procedures in C# and installing these in SQL Server 2008. It has gradually emerged that performance of the code when run in SQL Server is far far worse than when run as an ordinary .Net app. I have benchmark C# code that runs in 0.5 seconds in a console app, but takes around 8 or 9 seconds when installed and run as a stored proc in SQL Server 2008. We are running the 64-bit edition of SQL Server on 64-bit Vista. Now I just read this: "Managed code may incur additional overhead necessary to maintain the execution environment and perform security checks. This is the case, for example, when running inside SQL Server and numerous transitions from managed to native code are required (because SQL Server needs to do additional maintenance on thread-specific settings when moving out to native code and back). Consequently, extended stored procedures can significantly outperform managed code running inside SQL Server for cases in which there are frequent transitions between managed and native code." and this worries me, because our app uses a library that does a great deal of calling into some native API, so I know the code is transitioning to/from native code a great deal. I have to say that I am astonished at the degree of slow down, I need to find out if there is anything we can do to reduce this excessiv
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