posts - 230, comments - 423, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server

ClearTrace 2008.34

It seems like all I post anymore is ClearTrace releases.  I guess that’s not a bad thing.

The next build is available. This is the full release of the multi-user functionality.  In earlier versions all I cared about was performance.  I did everything I could to make it as fast as possible.  I had just discovered SqlBulkCopy and it was my new hammer.  And all of ClearTrace looked like a nail.  The application would use SqlBulkCopy to load a summary of each trace file as it was read into memory.  All the dimensions I created (application, login, SQL statement, etc.) were loaded after all the trace files were loaded.  Also using SqlBulkCopy of course.  It was fast!  It was also pretty horrible if two people were processing traces at the same time into the same database.  The same dimensions ended up in the database multiple times with different values or the program just errored out.  If you happened to run a query while a trace was processing you got “interesting” results as the joins would partially fail.  It also forced me to take some design shortcuts in my database.

Build 34 is very understanding that multiple people might be loading and querying the repository at the same time.  Dimensions are cached locally but the database remains the official copy of the truth.  Dimensions are stored in the database as they are read from the trace file.  You should now be able to query a trace while someone else (or the command-line version) is loading it.  The performance hit turned out to be small enough that I couldn’t measure it with the second hand on my watch. 

I also improved performance for large trace files.  An early, early version of ClearTrace stored each trace row in the database instead of storing just summary information.  When I removed that code I didn’t get everything completely removed.  There was still an object being created for each trace row but never used.  It wasn’t much of a big deal until reading traces over 1GB.  At that point the application would slow down and possibly throw memory errors.  Thanks to Jérôme for pointing this out.  Actually what he did was take my binary, disassemble it, find the code in error, patch the binary, test it and then tell me the exact line that needed fixing.  I wish all my bug reports were this good!  Keep using ClearTrace Jérôme!

Print | posted on Wednesday, May 27, 2009 7:04 AM | Filed Under [ ClearTrace ]



# re: ClearTrace 2008.34

If you put this out as open source I would have added to the code base. I had also written a tool similar to ClearTrace but wasn't as accurate but is very fast. I have to process hundreds of gigabytes of files to process so speed is important. I would be more than happy to work on ClearTrace and I'm sure others would too.
5/27/2009 9:32 AM | Wes Brown

# re: ClearTrace 2008.34

SQLBulkCopy is pretty cool. I did some performance testing of it last year, trying to match BCP adn BULK INSERT's speed with it. Could only get about 50% as fast because of the amount of CPU time with was spending in type conversions.

Turns out that both the .Net and the default SQL Server text-to-numeric (and text-to-datetime) conversion routines have a lot of overhead, I suspect to handle the zillions of different text-numeric formats. Since I knew exactly what my text-numeric formats were I hand coded my own type-conversion routines (in VB no less!) and almost doubled its speed. I was curious if you had noticed the same thing?
5/27/2009 9:52 AM | RBarryYoung

# re: ClearTrace 2008.34

Wes, I've been thinking seriously about putting this out as open source. I really need to clean up the code some first and get rid of all the junk in it. There are a bunch of projects in the solution that really need to go away.

Barry, I haven't done any testing at the level of detail. Your results don't surprise me though.
5/27/2009 9:56 AM | Bill Graziano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET