Bloated execution plans

I'm a newbie with lightspeed, so this may be a stupid question, although I could not find another post with a similar issue, except an older one (http://www.mindscapehq.com/forums/thread/4016).

We have a new service which acts as a proxy with an external system, this service retrieves information from a remote server, and saves it locally to db.

The code which stores the data (insert or update):

    public T SaveEntity<T>(T entity) where T : Entity
    {
        using (CommonUnitOfWork uow = _context.CreateUnitOfWork())
        {
            if (entity.EntityState == EntityState.New)
                uow.Add(entity);
            else 
                uow.Attach(entity);

            uow.SaveChanges();
        }

        return entity;
    }

After a few weeks this service was running on our production servers, the DBA (uh oh) noticed that this statement generates tons of execution plans in sql server, with further investigation he noticed that the insert statements are sent with the actual length of the strings instead of the length of the column. for example:

(@p0 nvarchar(6),@p1 smallint,@p2 nvarchar(4),@p3 int,@p4 nvarchar(6),@p5 nvarchar(6),@p6 nvarchar(7),@p7 nvarchar(3),@p8 nvarchar(16),@p9 nvarchar(2),@p10 tinyint,@p11 int,@p12 nvarchar(20),@p13 nvarchar(7))INSERT INTO Schema.Table  (    col1, col2, col3 ....  )  VALUES  (    @p0,    @p1,    @p2,    @p3,    @p4,    @p5,    NULL,    @p6,    @p7,    @p8,    @p9,    NULL,    @p10,    @p11,    @p12,    @p13,    @p13  );    

These insert statements are currently taking up 25% of total cached execution plans..

Is there something that can be done to control the way these statements are generated?

Thanks

Certainly not a stupid question :)

In short, this is not currently something which can be changed. As was mentioned on the linked thread at the time we create the parameters we create these with a name and value and not a size (which would help resolve this), however this isn't easily changed for the time being as at the time we create the parameters we dont have context about the originating schema to specify a size for the parameter which is based on the model.

We are aware of this though and we are looking to change this in the future, but for the time being this will be the behaviour you can expect.

Jeremy

Thank you Jeremy for your quick response.

Is there no manual way for us to specifically execute an insert query? that would be a solution I guess?

Otherwise I'm afraid I will have to remove lightspeed from this project.

Thanks,

Ron

Yes you can use stored procs to handle CRUD operations for entities which may be a solution here. See http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/CRUD-Stored-Procedures for details about how you set this up in the designer. Keep in mind you have to switch over in bulk (by selecting the access mode) and then if using stored procs for CRUD this does restrict you from using ad-hoc queries so it may not be a very feasible solution depending on your other requirements.

Jeremy