Lightswitch is slow, ADO.NET Entity Framework/Domain Service/WCF RIA Service

Question

I'm developing an Lighswitch application in VS2012 with an external SQL Server 2008. I'm using a WCF RIA Service to fetch my data, using these techniques: http://www.c-sharpcorner.com/UploadFile/raj1979/how-to-use-wcf-ria-service-in-lightswitch-2012/.

My main table contains some columns with static data and about 5 columns with foreign keys. The static data columns loads instant but all the foreign properties takes 3-5 secs to load on a screen with ~100 rows. This is not acceptable..

The problem for me is that I don't know where the bottleneck might be. Any ideas where I could start looking? The only thing I could think of so far is that my ADO.NET model uses Lazy Loading, and it does.

All the foreign key properties only has two columns with a PK and some text, so they're not data heavy.

Any help appreciated, thanks!

Potassium

Answer 1

I don't know if this is the exact answer to your speed problem, but that article you quoted suggests that you add a new ADO.NET Data Model to the class library containing the RIA Service.

There's absolutely no reason to generate a second data model, as LightSwitch already generates one when you attach the external data source (your SQL Server database). You may as well re-use it. An EF data model takes time to construct, so I suppose that using two EF data models could explain the slowness you're experiencing, but it could also be something else entirely.

Have a look at this article (the original RIA Service/LightSwitch article, & still the best), which among several other techniques, Eric will show you how to re-use the data model that LightSwitch creates for your data source.

How Do I: Display a Chart Built On Aggregated Data

In this article, the RIA service is created to specifically aggregate data to display in a chart, but you can do whatever you need to do in it. It doesn't have to aggregate data. I use RIA services with an attached data source (a SQL database) extensively, to provide data for lists, reports, as well as lookup data for combo boxes.

Hamamatsu

Thanks for your answer @YannDuran. I have seen Eric's guide and tried to implement it, but I didn't get it to work, therefore I used the technique in my first link. As far as I understand Eric's solution doesn't support editing out-of-the-box, how do I make that possible? I also did some analysis on the data flow and it turns out related data isn't fetched and returned in the same response, which should be possible to fix. Is this anything you have experience in? (social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/…) – Potassium

Believe it or not, this is one of the things that I use RIA services for. That way, I can return only the columns I need (I'm dealing with a legacy database, where tables sometimes have dozens of columns), plus any related data. This speeds up the grids substantially. As for editing, yes you can, you just have to add 3 methods with Add, Edit & Delete attributes (or automatic if you name them according to a convention). – Hamamatsu

Sweet! I've successfully switched to Eric's method and added update functionality according to point 2 in this guide harshgupta71.blogspot.se/2013/03/…. However I new problem emerged with loading related data, I've searched but couldn't find anything, I've asked a new question here goo.gl/R16zC. – Potassium

I'm glad to hear that Eric's article was helpful, & that you got it working, but as I've noted in a comment for the other article that you referenced, it is a direct copy of Michael Washington's article, originally published 8/1/2011. – Hamamatsu

Do you have indices on the foreign keys? you don't get an index by default when you declare an FK -- just a check constraint. if your interface layer is making calls after retreiving the fk's then you may be table scanning the db constantly. do you have any experience running sql server profiler? you could point it at your database and see what requests are generated... – Entrain

Answer 2

I am almost a beginer at this new art, but as Yan says in his book, instead a very good one, lightswitch is directed to the screen. I dare to ask you if you need to have 100 lines in your screen. You can't even see them all. If you reduce that number to 20 you will reduce your waiting time to 1/5th. I've had the same problem and now I use the least possible number of rows in screen. For that to be possible without disturbing the business performance, I choose to open screens with the maximum number of parameters, not required, which I send when I open them. If one of the parameters is not the option in the case I mention it as "Nothing". And so, I can have a minimum number of lines in the grid, fullfiling the purpous of teh screen, the program performance and the business performance.

Crimpy

This isn't really answering his question... Improving performance should not be by displaying less data – Hebdomadary