My experiences and thoughts on [mostly] technology.

Author: Pablo (Page 2 of 3)

SQL Troubleshooting Queries (2)

Found this MSSQL query on StackOverflow for troubleshooting locks or not being able to access a database being taken offline.. posting for self-reference:

SELECT
        DB_NAME(l.resource_database_id) DB,
	l.resource_type,
	l.request_mode,
	l.request_status,
	l.request_session_id,
	r.command,
	r.status,
	r.blocking_session_id,
	r.wait_type,
	r.wait_time,
	r.wait_resource,
	request_sql_text = st.text,
	s.program_name,
	most_recent_sql_text = stc.text
FROM sys.dm_tran_locks l
LEFT JOIN sys.dm_exec_requests r
	ON l.request_session_id = r.session_id
LEFT JOIN sys.dm_exec_sessions s
	ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c
	ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
--WHERE l.resource_database_id = DB_ID('[Database Name]')
ORDER BY request_session_id;

Some additional helpful links:
Lock Modes
Wait Types; Additional wait Type Info
Locks System Table Info

Also, connection by session:

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = [SESSION ID]

SQL Troubleshooting Queries (Locks)

Some helpful queries used to do lock/running query diagnosis.

sp_who and sp_who2 system stored procedures returns some helpful information for diagnosing current connections; but it’s hard to filter. Here’s a quick query for dumping the results into a temp table for filtering. The key field is “status” to help you determine if the connection is hung.

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

DELETE FROM @Table
INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE DBName --...

In my case, we discovered it was a SQL Server Agent connection and it was running a job that was hanging. Some additional helpful queries to answer What is the Job Id? What are the current job statuses?

SELECT *
FROM msdb..sysjobs s 
LEFT JOIN master.sys.syslogins l 
      ONs.owner_sid = l.sid

EXEC msdb.dbo.sp_help_job @Job_name = '[..]'

Stop a job:

EXEC msdb.dbo.sp_stop_job @Job_name = '[..]'

Finally, a query to request last time a stored procedure was changed:

SELECT *
FROM sys.objects
WHERE type = 'P' 
AND NAME = '[Stored Procedure Name]'

3D Engine Update

So my pet project of writing a simple 3D engine basically was halted ever since my last “JDX Engine” post…. I’ve been busy; I got married, received a promotion, moved, and bought a house- so it’s been crazy. However, I think a big part of it was also that I let it stagnate and never came back to it. I also know I’m reinventing the wheel writing these kinds of things, and even though it’s mostly a project to learn how to deal with the challenges in the 3D arena, it can be a bit demotivating when it takes a long time to really have anything of substance to demo when you can just pick up something like Unity.

Having said that, I will always find 3D engines and their challenges intriguing. I’d love to eventually build a simple game with a 3D engine of my own design. I ended up deciding to try again now that I’m more settled and I wrote another 3D engine, mostly through the help of Frank Luna’s “3D Game Programming with DirectX 11” book. In spirit, it’s the successor to “JDX” in that it’s in C# and uses Slim DX.
I realize there’s a good chance this will be a lot like my last 3D engine project- I’ll make some progress and then I’ll get bogged down with something and it may not get worked on for months. Maybe I’ll never even touch it again. However, I enjoy building this kind of stuff, and I’m posting my code on GitHub, so maybe it will serve to help someone get started on their journey. Also open to feedback on it.
A lot like last time, it’s pretty simple. I don’t have support for the 3D Giles format like the previous engine, but I can’t even seem to find that application anymore so that’s probably for the better. I would like to find a good light mapper and then write support to import whatever its native format is so I have something that can start looking like a game. I’m thinking BSP seems most common but I haven’t decided yet.
Here’s what I do have:
  • DirectX 11 via SlimDX – most modern DirectX API.
  • Support for up to (currently) 8 dynamic lights – spot, directional, or point.
  • New framework of classes that should make using available objects much more simpler and easier.
    • Although still not full, much better support for MQO files.
    • Previous attempt only loaded triangulated meshed, this loads quads as well so you don’t have to do anything special to your mesh in Metasequoia.
    • Support for mirroring on the X-axis.
    • Loads all Metasequoia objects individually and can organize them by material type for faster rendering. (The engine doesn’t take advantage of this yet)
Here’s a few screenshot showcasing what I have so far.
Further support for MQO files:
Metasequoia’s sample “violin” (with Z-axis removed)
AH-64 Apache model
1 dynamic spot light; resource model from book.
3 dynamic spot lights

I think the main things that I need to add support for next are:

  • Sprites
  • Multi-textures.
  • Support to load some common level/map format that includes light maps
  • Optimization in rendering – this one will take a lot of work and would constantly be on-going.
    • Minimizing calls to set materials/textures
    • Scene graphs
    • Frustum Culling

ABCpdf for Reporting and Rendering Table Borders

I love using ABCpdf when working on a project that needs to generate PDF files. It is very powerful and flexible. One of the main reasons I love, have used, and convinced companies I work for to purchase ABCpdf is because it can render a PDF file from HTML- and not just as a big PDF image either.

Anway, the idea of HTML to PDF is extremely powerful- often times rather than use a reporting package that requires tons of work to modify a report to the exact custom request of a customer, I would much rather generate an HTML page and convert it to PDF or whatever reporting format is requried. (PDF seems to usually be the most popular) This is has become invaluable, as editing or creating new HTML pages that pull data is easy, straightforward, quick and powerful, and with the ability tocovert the output to PDF I can now send the PDFs as reports or have them available for download. I’ve frequently avoided nightmares when updating or adding to reports, and I’ve been able to modify reports very quickly using this model.

Rendering HTML Table Borders with ABCpdf

Recently I found a “bug” of sorts when rendering an HTML table border using ABCpdf. It’s a bit of an odd bug. Essentially the borders become different thickness and really distract from the table. Since reports often include tables, having tables show up correclty and properly formatted is crucial- after all, this is one of the main reasons I like to generate reports in HTML and then convert to PDF: aesthetic flexibility.

A picture is worth a thousand words- here is a screenshot of the browser’s rendering of a table border:

That’s perfect. Just what I want. Now convert it to PDF:

Ouch… whatever happened to those table borders? It’s odd- because if you zoom in on the PDF borders show up fine:
At first I thought it might be due to not enough pixel density, and that maybe I just needed to increase the “browser width” and image resolution settings on ABCpdf so I would have a sharper image to sample from. Then I realized ABCpdf is going to try to parse the table, since it’s not an image, so had to be a CSS issue. Obviously the borders were being created in some way that shows the right size at a certain zoom but is slightly off- enough to show when you use a fit-to-screen size.
Sure enough it was- and the fix was rather simple. I had a ‘style=”border 1px solid black”‘ on the table tag, and then the cells within the table also included inline styling data. For some reason ABCpdf is processing these borders that are defined twice- (first in the table as a whole and then in the cells as necessary)- as “overlapping”. Removing the inline styling information from the table tag and leaving it on the cells corrected the issue:
Yes, yes, I shouldn’t be using “inline” styles AND potentially this would not even have been a problem with a table-less div layout, but I was working with what I had and I wasn’t about to rewrite the page if there was a reasonable quick fix. I did however, remove most of the inline styles and replace them with CSS classes… you do what you can. 🙂

Debugging VBScript

Unfortunately, sometimes I still have to work with VBScript. It actually isn’t so bad for quickly whipping up a script that runs on a schedule to perform some sort of minor task. Sometimes it comes in handy to debug the script, and I’m making an entry on it for future reference. (A coworker showed me this)

1.) Make sure you set your VS settings as follows in Tools -> External Tools:
VSSettings.jpg
2.) Set your Internet Explorer settings accordingly:
IESettings.jpg

3.) Load the script you wish to debug into Visual Studio… make sure it is VALID syntax or you’ll never even hit the break point.

4.) Set your break points.
5.) Go to Tools -> VBSCript_Debugger to begin debugging.
NOTE: When you hit “stop”, it will not actually stop script execution- only the debugging session- to actually stop the script from continuing, use the Immediate window to execute “wscript.quit”.
You can also start the debugger on a server that does not have Visual Studio running by starting up a command prompt and firing up the VBScript debugger manually:
wscript.exe //d c:/test.vbs
You’ll need to set a break point. (The command is “Stop” and you can add it anywhere in your script). This should automatically fire up the Microsoft Visual debugger if installed; if not you can download it from the Microsoft website.
As a final note, remember that often time errors in script are due to the account they are running under.  This one’s caused me to spin my wheels for linger that I wanted to until I realized my scheduled task was running under credentials preventing it from completing part of its job… it’s always the simple things. 😛

What Can’t be Measured Can’t be Improved: Investing in Measurement

For sure we have all heard the saying and know it well- but few of the times do we apply it- especially if measuring (or making measuring easy) is going to take some work up front. We like to think we can “cowboy” it and figure out where out performance-hitting bugs are ourselves.

Today, I worked on my 3D engine most of the day. I had a blast and a lot of good came from all the work I put into it. Even though I still have a ways to go, I felt like I had gotten to the point where it was time to start measuring performance.

Performance is a pretty important topic for a 3D application- and especially considering I’m just starting out learning the ins and outs of DirectX (and to add insult to injury, I’m using a managed language), I wanted to make sure I’m writing fairly efficient code. I’ve learned not to micro-optimize, and I felt like now that I had gotten the basic concepts down and working I would begin profiling.

At first I was going to just insert a few Diagnostics.Stopwatch calls here and there but after thinking it through I decided that as important as performance was for a 3D engine, it would most likely be in my best interest to create a class to make measuring the performance of the engine very easily. This is important because as you’re working, (if the use of your diagnostics class is easy enough), you will also be encouraged to add metrics to your application. I’ve realized that the time spent up-front to do this is invaluable, especially since I know I will use this class plenty in the future.

My approach was fairly simple- I knew it had to be something that was super easy to use or it would have a low ROI, but I also knew it had to provide valuable performance metrics and information, not just data.
Essentially it has four major timing calls:

  • public void BeginSingle()
  • public void EndSingle(string Name)
  • public void BeginSpecific(string Name) 
  • public void EndSpecific(string Name)

BeginSingle and EndSingle make timing a certain portion of code very easy. For example, say I want to measure a piece of code that calculates what should be hidden in a scene:

TimingObject.BeginSingle();
     [..code that calculates visibility …]
     TimingObject.EndSingle(“CalculatingHiddenObjects”);

The string passed to “EndSingle” will ensure that value it attached to some identifier. Now, if I need to nest between timings, I can use BeginSpecific and EndSpecific:

TimingObject.BeginSpecific(“TotalTime”);
     TimingObject.BeginSingle();
     [..code that calculates visibility …]
     TimingObject.EndSingle(“CalculatingHiddenObjects”);
     TimingObject.BeginSingle();
     [..other stuff …]
     TimingObject.EndSingle(“OtherCalculations”);
     TimingObject.EndSpecific(“TotalTime”);

This model has worked remarkably well. Additionally, built into the class I have code that stores the times in an array and averages them, as well as takes note of the max and min values. The class also has a function to output all values to a text file, and provide percentages based on the total frame and entity render times sorted by the most expensive- this was a very useful feature for me!

Results? I have improved my code to where it is over 5x faster! Huge difference! It came much more in line with what I was hoping for. I’m very happy, and I was able to make excellent use of my time while learning some great lessons on how to write my DirectX code more efficiently. In line with the 80/20 “rule”, indeed, about 80% of my bottle neck was in about 20% of my code. In the most complex scene I tested, I went from about 120 FPS, to about 680! That’s a 566% improvement after about 25 minutes of constantly optimizing by testing out different things and looking through the generated performance log.

In closing, I felt like this was a successful approach I want to repeat again in other areas: first I learned how to write code that accomplished my goals without having to think about performance and I didn’t waste my time micro-optimizing. Then, when I was ready, I invested some time up front to write a tool that made generating, collecting, and reading metrics easily and used that to tackle optimization. The 30 minutes that I spent up-front writing my timing class was definitely worth it in the end, and I would dare say probably saved me plenty of time.

JDX Update: Limited Gile[s] Support

Another tool that I used to love working with was 3D Gile[s] . It’s a light mapper, and you can use it to build 3D scenes and light them. So I started working on support to import 3D Gile[s] scenes. Even though I’m still working through bugs and this scene import is not 100% yet, here’s a screen shot of my progress so far:

 

 

 

Also did some minor optimizations of the engine itself.  EDIT: [These minor optimizations were before the major optimizations I made by profiling]

3D Engine: DirectX, C++, C#, SlimDX and SharpDX

Programming Love & 3D Engines
I have an admission to make. My true love of programming comes from game development. Sure, I love a good web app- but honestly I just love cool technology. Networking is cool. Database internals are cool. But what is a cooler technology than a 3D Engine? now that’s awesome!

3D Engine and games are intriguing to me because of the technical challenges that must be addressed. Most business application address some in one way or another, but usually 3D Engines have to address all of them and be very good. Sorting? Check. Searching? Check. Drawing? Check. Complex Math? Check. AI? Sound? Hardware? Ect.. Additionally, a good 3D engine has to not only implement complex algorithms, but requires a strong architecture to manage its complexity while remaining efficient and still allowing great power. It needs to be a database, (of triangles, essentially), constantly read input, perform all sorts of logic and all these things must come together seamlessly to create a great game.

How I Started
My first experience was with LEGOMINSTORMS when I was about 12, I programmed the RCX (2.0) for cool little game with a light sensor and a moving piece of paper. Next, GameMaker, when I was 13- then 3D RAD, A5 Game Studio Engine, and finally Blitz3D. Over the last couple years I’ve wanted to skip all the middle ware and write a 3D engine using OpenGL or DirectX. At one point I wrote a very simple software render, but that was about as close as it got… I always had a hard time picking up DirectX or OpenGL- it seemed like SO MUCH was thrown at you just to draw a triangle… and that was fine, except I wanted to know what all that code did. On top of that I’m not super proficient in C++ which most tutorials are in.

At Last: Beginning DirectX
One weekend while I was visiting a friend in Houston, he convinced me to buy an old book he knew I was interested in. (Read it the whole time we were at at Half Price Books). It was on writing a managed 3D engine- just what I wanted! I slowly started on it and I actually started making progress. However, it wasn’t long before I realized I was working with Managed DirectX 9, a boat that had sunk and MS had abandoned- but not before I had made some good progress. Encouraged by the progress, I decided I’d push forward and try once again tackling DirectX through a managed interface or library like SlimDX. I had tried to use it in the past, but it had never worked out well, but in my search I also found SharpDX– which is essentially a library of “extern” C# calls to the DirectX API. I figured I might as well stay as close as possible, since most DirectX tutorials are for C++ which call the same API. Another major reason I chose it was because of SharpDX’s performance.

This turned out to be pretty great. The DirectX API is actually starting to make a lot more sense now, and I feel I could also even jump straight into C++, however, for my needs performance is already overkill- and since I can develop much faster in C# with the familiarity of the .NET framework, I’m going to stick to that. I’ve actually have gone far enough where I’ve implemented a simple object management system (for moving entities around relatively) and built a import function for  MQO models. (Still limited though) MQO is the format for the 3D Modeller Metasequoia. I know it is strange and probably obscure, little-known modeler- but I found it a few years ago through the FMS website and have found it to be an absolutely EXCELLENT simple, easy to use, and free 3D modeller. I LOVE it!

Credit Where Credit Is Due
I have to definitely give credit to the following sources for getting me where I am right now.

Introduction to 3D Game Engine Design Using DirectX 9 and C# – This was the book that helped to somehow “flip the switch” and help DirectX make sense after reading and coding only a couple chapters… (I skimmed through 3-4 of the others and never read the rest)
http://www.two-kings.de/ – Some help with the clear explanation/tutorials
http://zophusx.byethost11.com/tutorial.php?lan=dx9&num=0 – HUGE help. This guy does through DETAIL so that I could understand. Heavily considered switching to C++….
http://www.toymaker.info/Games/html/lighting.html – Helped my understanding of shaders.
http://www.rastertek.com/tutindex.html – Helped a LOT in understanding and writing shaders for multi-texturing and special effects.

And of course Wikipedia… (if you haven’t donated, but you use it, you should!) and the DirectX Documentation. It’s MUCH easier to read it now that I’ve grasped the major concepts though I undoubtedly have quite a bit to go. And finally thank God for the Internet and search engines…  if you’re persistent you’ll find what you need.

The JDX Engine
So that’s my newest passion- building this managed 3D engine- I couldn’t really think of a name and finally settled with “JDX”. When I’ve made a fair amount of progress, I actually want to build a Recoil clone (I played this game when I was 13 and I have fond memories of it), since that would be easy to do and not require much artistic skills. If it turns out well, I’d love to also make this 3D engine public for anyone who wants to be able to write a managed DirectX 3D game without being a DirectX expert. It will probably be fashioned somewhat after Blitz3D API, since I’ve always found it to be extremely intuitive.

I’ll probably give updates on JDX here and there when I can. Mostly I’m teaching myself, so I’m making a lot of mistakes. I’m very open to learning how to actually write an efficient and good D3D code base since a lot of examples *work* but there seems to be many different ways to do things in D3D and I want to not just perform the task but do so efficiently. Which brings me to one of my new major pet peeves: in most tutorials there seems to be very little out there as to how to actually best write the code. (E.g. do you store a vertex buffer for each object in your world or do you attempt storing them all in the same buffer? What about object parts? How do you apply the correct shaders when you do so? Ect…)

Until next time….

UPDATE: Bought another couple books from amazon on 3D Mathematics and DirectX 10; it’s helping lots. Engine is coming along well… here are someupdates.

NodeJS & Simple C# HTTP Server

So… I’ve read quite a bit about NodeJS and was very early introduced to it by one of my good friends, Dominic ( http://dominicbarnes.us/ ). He’s a huge JavaScript fan and so NodeJS was a big hit. I like Javascript, but admittedly I am nowhere near as proficient as he is nor do I understand fully the functional paradigm needed to use it to its full extent. (He’s great at pretty much everything open source/other side of the MS/Windows fence, so if you need someone like that go hire him and pay him lots of money – you won’t regret it.) NodeJS got attention with it’s claims to efficiency with its non-blocking programming style- the functional JavaScript would in theory make writing such code easy.

The Bleeding-edge Event Model in NodeJS?
This was a couples months ago and I was fascinated by the potential NodeJS performance gains and decided I would try writing a simple server in C# with the same model. I figured since C# is compiled, it may be at the least somewhat quicker than the Windows version of NodeJS. Of course, now it all seems silly having learned what I did about IIS through the process. I never realized most of the comparisons for performance were against Apache, and IIS already performs better than Apache anyway. Of course, that seems obvious now- what was I expecting? I suppose at times we can all be suckers for the success stories of the underdog coming out on top, but in practice that is generally not the case. Regardless, it was a fun learning experience. My proof-of-concept server did turn out to be incredibly fast, (and considerably faster than the Windows NodeJS at the time) but that doesn’t mean much considering it offered limited functionality. It was a simple test; I used .NET’s HTTP Request classes and didn’t build my own implementation to keep simple. (which would have been a huge part of the effort) It was really fun trying to think of ways to optimize my little server. (request handling/caching/reading from disk/ect.)

A C# Server Like NodeJS
Basically it is a C# application that only has a task bar tray icon for an interface (I never actually got far enough to turn it into a service and separate the UI from the server service) and sits around waiting for requests. A main thread is the one that just sits around listening to the HTTP port[s] all the time and whenever a requests comes through hands it off to a worker thread. The worker thread checks if the request is cached in memory and if so returns it, if not depending on the extension it will either return a static file, image, or compile the script page, add it to the cache, and return it. (or just run the script page’s compiled code if cached)

Having one thread completely devoted to listening for requests and passing the actual request handling to a thread pool allowed my server to respond to requests extremely quickly- this was the concurrency I was after. The cached compiled scripts would run quickly once the compilation for the page script was cached, as it was literally like running a function that returned a string. (Plus obviously the JIT will also compile to native code once the function is called for the first time)

Yes, it compiles on demand! But that wasn’t as big a deal as I thought it would be. It was much easier than I thought since .NET comes with compiler libraries for C#. All my server does is some string parsing on the requested file to look for ‘@{‘ and ‘}@’ symbols to know where the C# code begins and ends. (C# “script”, anyone?) As I mentioned, compiled methods are kept in memory so that subsequent requests are extremely fast.

I realize NodeJS operates a bit differently. NodeJS listens on a single thread (main event loop) and when a request comes in, it immediately processes it. For requests what would require “blocking” functions, (such as File I/O… though technically any function call is blocking by definition), a callback is given and the “blocking” function is queued in a thread pool- this way the main even loop thread goes right back to processing and listening to requests. When the “blocking” function completes, it calls the callback function on the main event loop and the request is finished on that main thread. (As I thought through this I began to realize some potential shortcomings)

In my case, I simply created the event loop to only listen for requests, and then hand off request handling to the thread pool. This is because I couldn’t be guaranteed that the page script wouldn’t perform a “blocking” operation. However, had I continued with the project and had my intention been to imitate NodeJS exactly, I would have probably needed to build a library of functions that page scripts could have called to handle “blocking” functions. This is where the power or ease of the functional programming style of JavaScript would have been nice. Doing this in C# would have been ugly, but it would be much easier to adopt Javascript’s call-back functional style to help segregate those “blocking operations” that should be executed in the thread pool from those that should run in the main event loop. (Technically I didn’t have a “main event loop” since all mine did was handle and hand off the requests, but you get the point) In the end, it didn’t really matter that I didn’t go through all the lengths to simulate that, because for my tests I wrote a page script that didn’t do any file I/O or any other “blocking” operations. (Which I suppose makes for poor tests, but good enough for what I needed)

Realizations and Some Final Thoughts on NodeJS & IIS
Throughout the whole time I was researching more about the IIS pipeline and began to realize IIS does pretty much the same thing as my server did as far as listening, handling off requests, and working with a thread pool to process them. (of course, it does it a whole lot better) Eventually I stopped development; the code is here if curious. (Since I abandoned the project, the “scripting” support is very limited- it supports C# since it uses the .NET compiler but the page script code doesn’t have access to any server variables like POST/GET, ect making it close to useless)

So NodeJS/IIS thoughts. I think it is a cool technology and I’ve still got a lot to learn on the subject, but I’ve researched enough where I feel I have a fair opinion. I think IIS does a pretty darn good job and NodeJS model isn’t exactly ground-breaking here… it’s actually been around for a long time. Apache is the big web server it always seems to be compared to, and I suppose that’s where there’s a big win performance-wise is since Apache spawns a new thread for every request. (Maybe the just need to implement a thread pool in their pipeline?) My thoughts are you’d be hard pressed to get test results (and not just mass concurrent request tests) where an equivalent MVC.NET page written well and using IIS under-performs it’s equivalent NodeJS page.

In closing, here are a couple articles I agree with, though I think he is rather harsh/offensive to the NodeJS community, but he seems to hit the nail on the head as far as analyzing performance and the NodeJS model:
http://teddziuba.com/2011/10/node-js-is-cancer.html
http://teddziuba.com/2011/10/straight-talk-on-event-loops.html

Part 2: SqlBulkCopy Class (MS SqlServer and .NET)

At work we’re making an effort to contribute technical knowledge to a centralized IT wiki. I like that. Writing encouraged a good understanding of the technology and it’s benefits. I didn’t think I’d have much to write but I’m surprised how some things I take for granted as simple, others didn’t know and vice versa. This two part series is from those entries.

​Introduction

Normally, inserting rows into SQL server is quick and easy and done through a simple INSERT SQL statement. This is fine when saving data to one, two, or even a few rows. However, when it is necessary to insert larger sets of data, this method becomes not only functionally inadequate, but slow and clunky. In this entry (part two of a two-part series) I wanted to write about the second option we will look at for inserting large sets of data: using .NET’s SQLBulkCopy class.

The SqlBulkCopy ClassWhen it is necessary to insert more than about a 1000 rows of data, a TVP​ now begins to reach the limits of its performance gain. If we are using the TVP’s only for inserts, we can move up and dramatically increase performance by using .NET’s SqlBulkCopy class. In addition to providing the functionally for large inserts, the SQLBulkCopy class can also be used to copy large amounts of data between tables. With SQLBulkCopy we can deal with millions of rows if need be. Here is an amazing whitepaper on the SqlBulkCopy class’ performance:http://www.sqlbi.com/LinkClick.aspx?fileticket=svahq1Mpp9A%3d&tabid=169&mid=375

The SqlBulkCopy Class
Using the SQLBulkCopy class if fairly simple. The dataset you use must match the columns on the table. If the order or column names are a bit different, that’s okay since that can be handled with the SqlBulkCopy class’ ColumnMapping property which is just for that. Here’s a .NET sample of using the SqlBulkCopy class to update a table named “tblSIWellList” from a table name “MyData” within a DataSet:

Using objConnection As System.Data.SqlClient.SqlConnection = GetSQLConnection()

objConnection.Open()

 

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(objConnection)

bulkCopy.DestinationTableName = “dbo.tblSIWellList”

With bulkCopy.ColumnMappings

.Add(New SqlBulkCopyColumnMapping(“ID”, “ID”))

.Add(New SqlBulkCopyColumnMapping(“EPD”, “EPDate”))

.Add(New SqlBulkCopyColumnMapping(“Comments”, “Comments”))

.Add(New SqlBulkCopyColumnMapping(“Date”, “Date”))

.Add(New SqlBulkCopyColumnMapping(“RTP_Date”, “RTPDate”))

End With

bulkCopy.WriteToServer(ds.Tables(“MyData”))

End Using

End Using

Because SqlBulkCopy class is designed to copy/insert a large number of rows, transactions are handled in batches. It is possible to specify how large each batch is (e.g. 5000 rows at a time) but by default a single transaction (“batch”) is used for all rows. When committing transaction in batches, a failed batch will only roll back the last active transaction in the batch. (This may not necessarily be all rows if a previous batch was successfully committed)

Considerations when using .NET’s SlqBulkCopy class

There are a few “gotcha”’s to keep in mind when using the SqlBulkCopy class:

  • When the source and destination table data types are different, SqlBulkCopy will attempt to convert to the destination data type where possible but this will incur a performance hit.
  • By default, PK’s are assigned by destination and are not preserved.
  • By default, constraints are not checked and triggers are not fired. Also row-level locks are used. Changing these setting may affect performance.​
« Older posts Newer posts »

© 2023 Pablo Aizpiri

Theme by Anders NorenUp ↑