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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

XMLDML insert is dog slow for real inserts in SQL Server 2005

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
 
I put together a little sample after noticing my XML solution was taking more than three seconds every time I inserted something over 20 records with a reasonable number of attributes. XML updating is apparently not ready for production code in 2005. This is faster in SQL Server 2008, and might work for some applications, but you'd actually write the code a little differently there. In any case, it's far slower than writing out the string yourself. Anyhow, if anyone asks you why you're not using XML.modify('insert in SQL Server 2005, you can run this for them.  DECLARE @now DATETIME, @then DATETIME, @elapsed INT, @now_disp NCHAR(24); SET @now = GETDATE(); SET @now_disp = CONVERT(NCHAR(24), @now, 126); RAISERROR('starting timer at %s', 11, 1, @now_disp) WITH NOWAIT; DECLARE @root XML, @insert NVARCHAR(MAX), @counter INT, @child XML, @att NCHAR(3), @attdex tinyint; SET @root = '<root/>'; SET @counter = 24; WHILE @counter < 26 BEGIN SET @counter = @counter + 1 SET @child = CAST('<child/>' AS xml) SET @attdex = 0; WHILE @attdex < @counter BEGIN SET @attdex = @attdex + 1; SET @att = CHAR(96 + @attdex) + 'ow'; SET @insert = 'SET @child.modify(''insert attribute ' + @att + ' {"value"} into (/child)[1]'');'; EXEC sys.sp_executesql @insert, N'@child xml OUT', @child


View Complete Post


More Related Resource Links

error in insert hebrew data into sql server 2005

  
Hello, am trying to insert data in hebrew language into sql server 2005 but it appears like this ???????????? can i know how i can fix the problem plz???? thank u

Data Transfer in C# slow when targeting SQL Server 2005 using SQL Server SMO version 10 (distributed

  

 

Hello everyone,

I have a strange issue regarding SQL Server SMO Transfer. I am using the Transfer class to create a copy of a database. I will explain the different cases I have tried so far and maybe someone can help me figure out what's going on.

First case

I run my C# code from an assembly that references version 9.0.242.0 of SMO assemblies that are in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2005 database engine, it works perfectly and fast.

But of course, as soon as I try to target a Sql Server 2008 database engine, it complains that it is not compatible. No surprise there.

 

Second case

I run the exact same C# code from an assembly that references version 10.0.0.0 of SMO assemblies that are also present in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.Smo.Extended
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2008 database engine, it works perfectly and fast.

However, if I try to target the sam

Data Transfer in C# slow when targeting SQL Server 2005 using SQL Server SMO version 10 (distributed

  

 

Hello everyone,

I have a strange issue regarding SQL Server SMO Transfer. I am using the Transfer class to create a copy of a database. I will explain the different cases I have tried so far and maybe someone can help me figure out what's going on.

First case

I run my C# code from an assembly that references version 9.0.242.0 of SMO assemblies that are in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2005 database engine, it works perfectly and fast.

But of course, as soon as I try to target a Sql Server 2008 database engine, it complains that it is not compatible. No surprise there.

 

Second case

I run the exact same C# code from an assembly that references version 10.0.0.0 of SMO assemblies that are also present in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.Smo.Extended
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2008 database engine, it works perfectly and fast.

However, if I try to target the sam

Slow sql-server after upgrade from SQL-Server 2005 x64 standard to SQL-Server 2008 R2 x64 enterprise

  

We moved our databases to new hardware today (using copy database wizard)

 

The new hardware should be better than the old one.

We went from 28 GB ram to 128 GB.

We went from 16 cores to 32 cores (with even more cache etc.). We also moved from external SAN with 40 disks to intern disks (8 Samsung SS805 100gb ssd slc)

                             According to our own metrics we could get twice the IO using IO m

Why does SQL Server 2005 Express SP3 prompt to insert next disk when run from CD?

  

Question:

Why does SQL Server 2005 Express SP3 prompt to insert next disk when run from CD?

 

Replication Steps:

 

1. Download SQL Server 2005 Express with SP3 (SQLEXPR.EXE)

 http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

 

2. Extract the SQLEXPR.EXE using the "/X" cmd line parameter to c:\DBEngine\SQLSP3\

 

3. Burn to CD c:\DBEngine\SQLSP3\

 

4. Install a XP 32bit test machine with SQL Server 2005 Express with SP1 as the default instance with this command

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=11350b1f-8f44-4db6-b542-4a4b869c2ff1

setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=ALL SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=somepwd DISABLENETWORKPROTOCOLS=2

 

 

5. Attempt to upgrade the XP 32 bit test machine with the SP3 CD made above with the following command

setup.exe /qb INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine

 

Result:

During the upgrade the installat

Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


SQL 2005 Linked Server to Oracle 10g - Very Slow

  

Hi,

 

Anyone have some recommendations for a slow linked server from SQL 2005 to Oracle 10g?

 

Here is the situation.  We have an application that runs on SQL 2005 - Win 2003, and in certain forms it makes calls to an Oracle 10g database running on Sun Unix.  This database was upgraded from SQL 2000 Win 2000.  We have a test system mirroring production and have the same problems.

 

- All indexes are being used.

- Server configuration is correct.

- Changed the Oracle 10g driver from the main driver to the Instant Client - much smaller but same result.

- Read an old post about same issue but the person was connecting to 8i so they were using a different driver.

- I have been running profiler and the problem seems to be a remote scan on the Oracle side.  We are using the OPENQUERY call from the application.

- All other parts of the app are running fine, just the outside connection.

- We are using the Windows Firewall and all DCOM components are set as Microsoft recommends.  I don't think this is an issue because the test server is not using Windows Firewall and we have the same issue on that server.

 

Any suggestions would be greatly appreciated.  I have run into the wall :-)

 

thanks

Matt


SQL Server 2005 Try and Catch Exception Handling

  
I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:

sql server 2000 vs 2005

  

i would like to ask what the difference between sql server 2000 and 2005 


How to save Unicode in SQL Server 2005

  
I am using google transliteration to display the text in Hindi/Tamil
etc.

I tried to store the text appear as Hindi from textarea in an nvarchar
field of database (SQL Server 2005) , the text saved as "???????".

After retireving text from the same it display the text as "???????".

Can anyone help me how to store and retrieve the actual data from SQL
Server 2005?

I am using ASP.net,VB.Net and SQL Server 2005

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.

  

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?


sql server 2005 enterprise edition is required

  

sql serve 2005 enterprise edition is required to work with microsoft enterprise blocks

 


need help: HTMLEditor, Sql-Server 2005 and CrystalReport

  

Hi All,
i use the Ajax Control Toolkit HTMLEditor in VS 2008 web-project and save the formated text/content in Sql-Server 2005 - it works fine!

Now, i want print the formated text in a Report, with CrystalReport...??? is this possible?

How do i handle the process? i thing, i see the forest based on all the trees not more...

Can anyone help me? how to do?

thx
 
frank
 


SQL Server 2005: Regular Expressions Make Pattern Matching And Data Extraction Easier

  

Now you can perform efficient, sophisticated text analysis using regular expressions in SQL Server 2005.

David Banister

MSDN Magazine February 2007


SQL Server 2005: Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services

  

Custom report items in SQL Server 2005 Reporting Services address your needs for custom reports without the pain of doing it from scratch.

Teo Lachev

MSDN Magazine October 2006


Use The Source, Luke: Source Server Helps You Kill Bugs Dead In Visual Studio 2005

  

The latest releases of WinDBG and Visual Studio know exactly how to use source server, so its benefits are available to both .NET and native C++ developers. See why this is so important in tracking down bugs.

John Robbins

MSDN Magazine August 2006


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