Fastest way to insert object graph on to sql azure?

Fastest way to insert object graph on to sql azure?

Old forum URL: forums.lhotka.net/forums/t/10992.aspx


DanielFowler posted on Saturday, December 17, 2011

Hi, I'm currently developing an application that centers around dynamic questionnaires.

The object graph looks like this Questionnaire > Sections > Questions.  Questions has a relationship with its self so many questions can have many questions of their own which can go however deep it needs to go.

What is the fastest possible way i could have the questionnaire inserted into SQL Azure? I have went down the route of passing a Stringbuilder down through the object graph to build up a query. Each parent generates a unique SQL parameter "@Parent1, @Parent2" etc then stores the Scope_Identity() to this sql variable and passes it down to the FieldManager.UpdateChildren().

Using this way it takes a good 5-10 seconds to save a questionnaire which, i feel, is too long a delay for the user.

It runs nice and fast on the local machine but deployed to azure it has this delay because the Database server is held in a different location from the hosting machine.

Is there some sort of other batch query that could be written that doesnt require so many SQL variables?

Hope i've explained well enough!

 

Thanks!

RockfordLhotka replied on Saturday, December 17, 2011

I would think one answer is to use a Web Role in Azure to host the data portal, so the SQL Azure interactions are "local" within the Azure cloud.

DanielFowler replied on Saturday, December 17, 2011

Thanks Rocky,

We do have the ASP web project thats hosting the Silverlight app within a web role, but im guessing its slow because, although SQL Azure and The web role are both on the cloud, i imagine they could be a distance from one another, making it slow.

RockfordLhotka replied on Saturday, December 17, 2011

Ahh, good point.

Have you tried using EF? Maybe they are able to do some optimizations you aren't doing?

DanielFowler replied on Monday, December 19, 2011

Nope we havn't tried using Entity Framework.

 

But I found a solution, thought i'd share incase anyone is having the same problem. I decided to use the new SQL Server datatype called HeirarchyId to represent a relationship between Form>Sections>Questions this meant i didnt have to work about ForeignKeys atall so I could build up a heirarchyid without touching the server (well once, to get a unique root id).

Then instead of building a query i built up two data table, one for sections, one for questions.

 

I then used SqlBulkCopy to send it to the server.

 

All of this has cut the time down to save an application from 16 seconds to 3.

 

 

 

Copyright (c) Marimer LLC