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


Post New Web Links

SQL insert performance

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

Hi all,

im taking a long period of time to do SQL insertion.

basically it takes ard 4ms for a record insert. I am using C++ to do my connection to SQL and then using recordsetPtr to open the database. I need to shorten this time taken as im doing some simulation work and real time analysis.

I am using scope_ident() to grab the primary key of a table and insert this primary key into another table. hence i don't think using bulk insert is possible.  

Pls help. thank you.




View Complete Post


More Related Resource Links

Read Uncommited (Bulk insert) and Performance

  

Hi all,

I have issues with Concurrency.

there has been times when updates happen on my fact table while some users read data from it at the same time, it takes a very long time and there is some blocking issues.

1.My inserts happen as Bulk inserts. I would like to know if I change my query to use NOLOCK will it still be blocked by the Bulk insert when i read? so does Bulk insert do an exclusive lock, so i cant read data?

2. changing my query to use NOLOCK is it benefical?

Please help me


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

  

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

SQL insert performance issue

  

Hi all,

im taking a long period of time to do SQL insertion.

basically it takes ard 4ms for a record insert. Using query analyzer, i used a for loop and insert into a table 10000 times. the result 42 secs. I need to shorten this time taken as im doing some simulation work and real time analysis.

I am using scope_ident() to grab the primary key of a table and insert this primary key into another table. hence i don't think using bulk insert is possible.  

im using SQL Server 2000.

Pls help. thank you.


SQL insert performance

  

Hi all,

im taking a long period of time to do SQL insertion.

basically it takes ard 4ms for a record insert. I am using C++ to do my connection to SQL and then using recordsetPtr to open the database. I need to shorten this time taken as im doing some simulation work and real time analysis.

I am using scope_ident() to grab the primary key of a table and insert this primary key into another table. hence i don't think using bulk insert is possible.  

Pls help. thank you.


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
begin
   insert into myTable (...) values (...)
   select @i = @i + 1
end

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


SQL Identity column / concurrent insert performance

  

How does sql handle concurrent insertion of rows into a table that has a integer identity column for a primary key?  Does it deadlock?  Does it just lock / block while inserts are performed one at a time?  My client app may be running twenty concurrent thread resulting in 20 concurrent inserts.  Is this a bad design from a performance perspective?  Is it better to use a different unique ID such as a GUID?

 

thanks

 


ASP.NET Best Practices for High Performance Applications

  
This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

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.

Top .NET Performance Problems and how to avoid them

  
Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

High-Performance .NET Application Development & Architecture

  
It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

How to improve the performance of ASP.NET MVC web applications

  
It is an in depth analysis of a Digg-like site and how it went from serving (on a test machine) 6 req/sec to 390 req/sec.

The biggest gain, 74 req/sec to 390 req/sec happened when he introduced data caching and another 25 req/sec where gained when he introduced the compilation of LINQ queries.

High Performance ASP.NET - Speeding Up ASP.NET Pages

  
have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

Improving Application Performance in .Net

  
Application performance has always been a concern for Web Application developers. This article contains guidelines and tips for maximizing application performance in ASP.NET.

Tips to Improve Your ASP.NET Web site performance

  
ASP.NET has been developed with lots of features which makes developer life easy. Start from ViewState, PostBack, Server Controls , User Controls and etc. All of them can make your web development experience fun and easy, but improper use of them can cause your site performance slowdown. Most of the controls generate overhead to the client and server. Therefore in this article, I I will list out about things you can do and don't to improve your ASP.NET website performance.

Insert Rows with a GridView

  
In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.

10 Tips for Writing High-Performance Web Applications

  
Writing a Web application with ASP.NET is unbelievably easy. So easy, many developers don't take the time to structure their applications for great performance. In this article, I'm going to present 10 tips for writing high-performance Web apps. I'm not limiting my comments to ASP.NET applications because they are just one subset of Web applications. This article won't be the definitive guide for performance-tuning Web applications-an entire book could easily be devoted to that. Instead, think of this as a good place to start.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

  
Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.
Categories: 
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