Archive for September, 2008

Saving large numbers of entities in LightSpeed

Tagged as LightSpeed

We had a report in the forums recently of surprisingly slow performance when inserting large numbers of records using LightSpeed. The issue was resolved by adjusting some of LightSpeed’s performance tuning parameters, but the performance characteristics of those parameters weren’t initially obvious, so I thought it might be useful to write up a few notes on how to get the best performance out of LightSpeed if you’re doing mass inserts.

The first recommendation is to consider your IdentityMethod. This determines how a new entity gets its Id value. If you’re creating a lot of entities, you should use a method that allows IDs to be assigned efficiently. For numeric identities, avoid IdentityColumn, and prefer KeyTable or Sequence. IdentityColumn means that the ID is assigned by the database when LightSpeed inserts the record: that in turn means that LightSpeed has to insert records one at a time, and to query the database after each insert to find the ID that was assigned. That means two database round-trips for every entity. With KeyTable and Sequence, by contrast, LightSpeed grabs a block of IDs from the database, assigns them to entities client-side, and can then insert the entities in one go. That means two database round-trips total. (Well, sort of. See below.) For GUID fans, the Guid and GuidComb identity methods are also efficient because they assign IDs client-side and don’t incur any database queries for identity allocation. See Identity Generation in the LightSpeed user guide for more information.

The second recommendation is to consider your IdentityBlockSize. This determines how many IDs LightSpeed grabs in one go from a KeyTable. The default is 10. That means that for every 10 entities you create, you incur a database query for LightSpeed to grab another block of IDs. If your typical usage pattern is to save hundreds or thousands of entities at a time, you can reduce the number of queries by grabbing more IDs each time. The trade-off is that if you have to use the IDs or lose them. For example, if you specify an IdentityBlockSize of 1000 but you’re usually only inserting 5 or 10 records at a time, you’ll chew through the available range of ID numbers faster than necessary. The ideal is to use an IdentityBlockSize roughly equal to a typical insert size, so you’re keeping the number of identity block allocation queries low but making efficient use of the ID range.

The third recommendation is to consider your UpdateBatchSize. This determines how many entities LightSpeed saves (inserts or updates) in one go. If more entities than the UpdateBatchSize need saving, LightSpeed breaks up the save operation into multiple queries, each saving no more entities than the UpdateBatchSize. (The queries are all executed within a transaction, so the save operation remains atomic.) The default UpdateBatchSize is 10.

Now you might think that reducing the number of queries is good, and that bigger UpdateBatchSizes are therefore better. But that’s not always the case. The reason for this is that a bigger UpdateBatchSize means LightSpeed has to construct and send a bigger SQL statement. Up to a point, that’s no problem. But if the UpdateBatchSize is too high, the SQL statement gets really big, the number of parameters becomes huge, and the savings from reducing the number of queries are dwarfed by the cost of constructing such a vast query. Worse still, some providers can’t cope with really big batch sizes at all. For example, SQL Server has a limit of 2100 parameters per query. If you’ve got an entity with a dozen fields, and an UpdateBatchSize of 200, you’ll hit that limit.

So what should your UpdateBatchSize be? It depends on the characteristics of your entities, and more critically on your database and network characteristics. If hitting the database is fast — the database is on the local network — then the cost of multiple queries is low compared to the cost of constructing a single huge query. So an UpdateBatchSize of 10 or 20 is probably appropriate. If hitting the database is slow — for example, the database is on a WAN or VPN with low bandwidth and high latency — then the cost of multiple queries is higher, and you’ll want to get more work done in each query even if that means the queries take longer to build. So an UpdateBatchSize of 60 or 100 (or even higher) might be better.

As with any distributed system, you may need to do some experimenting to find the optimum for your entities, application usage patterns, database and network. Just remember that although reducing the number of round trips is important (hence KeyTable rather than IdentityColumn), other performance factors can come to dominate as the system scales. On the positive side, LightSpeed should help you to avoid having to optimise prematurely — the performance parameters can all be adjusted through configuration as you develop and test.

Add your comments
Posted by Ivan Towlson on 18 September 2008

LightSpeed comparison to other O/R Mappers

Tagged as LightSpeed

Recently we have had a lot of queries about how LightSpeed compares to other object relational mapping frameworks that are available – notably LINQ to SQL and LINQ to Entities. To help better understanding of what LightSpeed provides we have now added a comparison page to the LightSpeed product pages.

Click here to view the LightSpeed O/R Mapper Comparison page.

We appreciate any feedback that you may have about this addition.

Hope this helps,

John-Daniel

Add your comments
Posted by John-Daniel Trask on 11 September 2008

Extending Visual Studio properties

Tagged as Visual Studio

We’ve been doing a bit of work with Visual Studio Extensibility recently through the LightSpeed designer and the VS File Explorer add-in, so I thought I’d start jotting down some of the stuff we’ve come across. We’ll start with a nice obscure one…

One of the features we’ve always had in mind for the LightSpeed designer is the ability to use your own custom templates. An example of why you might want to do this is if you want to have the property setters always call logging code. But how would you tell LightSpeed which custom template to use? Well, of course, it could be a model-level thing, but then if you’ve got multiple models in a project, you have to specify it on each one individually. More likely, you’ll want to choose the custom template at project or solution level because the reasons for needing custom code are likely to be at the project or solution level, e.g. design policy or coding standards. To support this, we would need to add a property to the Visual Studio project object, to be displayed in the Properties window when the user selected the project. But that object is defined by Visual Studio, not by us!

If you’re familiar with Windows Forms, you probably know that some controls (such as ToolTip and HelpProvider) have exactly the same problem — they want to add a pseudo-property to other controls on the form. Windows Forms solves this using an interface called IExtenderProvider. Visual Studio provides an analogous interface, also called IExtenderProvider though with slightly different methods.

So to add properties to a Visual Studio object, you implement IExtenderProvider and register your implementation using DTE.ObjectExtenders.RegisterExtenderProvider. For add-ins, do this in your connection method. For DSL Tools packages, you can do it in your package’s Initialize override. As part of registration, you have to say what kind of object you’re interested in extending (expressed as a CATID string for your old-skool COM nostalgia-inducing pleasure), so you may need to register multiple times (for example to extend both C# and Visual Basic projects). You may need to do a bit of poking around to find out what CATID to register with for any given selection, though the EnvDTE.Constants class and the VSLangProj namespace are good places to start.

When the user makes a selection, Visual Studio asks all suitably registered extender providers whether they are interested in this selection by calling IExtenderProvider.CanExtend. At this point you have access to the extendee object so you can make a more specific decision if required. For example we’d only want to display our custom property against projects that contain LightSpeed models.

If you return true from CanExtend, Visual Studio calls your GetExtender implementation. All you need to do from here is return an object with the properties you want added to the property grid. (Well, you need to do some bookkeeping as well, but this is described in the VS SDK documentation.) One thing we found is that the returned object needs to be COM-visible; otherwise, it’s just a plain old CLR object. Your returned object can use WinForms design-time attributes like CategoryAttribute, DisplayNameAttribute, DescriptionAttribute and EditorAttribute to customise the display.

Be aware that all this does is display the extension properties in the Properties window. Sometimes this is all you need: for example, the AnkhSVN add-in for Subversion displays read-only source control information using this technique. But in our case we would also need to save whatever the user enters into the “template file” extension property. How you do this depends on what you’re extending, but if you’re extending a project or solution, one simple solution is to have Visual Studio save the values as part of the project or solution file by using the Globals property of the Project or Solution interface, and setting VariablePersists to true.

As so often with Visual Studio, much of the battle is knowing what to do. You can see from the above that there’s not a lot of code to write to extend a Visual Studio object, but the documentation is ropey and the samples are often out of date. Hopefully this will provide a few pointers for the two other people on the planet who need to extend the VS properties window…

Add your comments
Posted by Ivan Towlson on 9 September 2008