LightSwitch Data Freedom – Part 2

This article is part of a series of posts from Xpert360 Development Team to address real world scenarios and problems faced when designing, creating and implementing Visual Studio LightSwitch applications.

Walkthrough Shaping your Data

Connecting to disparate data sources is one of the key strengths of Visual Studio LightSwitch and with so much data and so many data sources to consume the possibilities seem endless.

In real-world scenarios it does come with a few limitations which will be addressed by this technique. Do you suffer from:

• My database is complex and has got hundreds of tables
• The database schema design does not conform to standards
• My tables have hundreds of columns
• Performance in LightSwitch design and runtime is awful
• It is no use as I am not allowed to change the database
• I only want a few bits of data
• LightSwitch will not import our database metadata

Design custom logical schemas for use by LightSwitch

The premise behind this is that we present to LightSwitch and Entity Framework our custom database schema which is to be used at design-time.

The custom schema needs to be compatible at runtime with the existing database schema as far as Entity Framework is concerned because at runtime we connect to the real database. For read-only access this is highly compatible but there are some caveats for updates, inserts and deletes. These caveats are the same as you would face designing a WCF RIA Service if you chose that route. If you try to break rules enforced in the database you will get errors, simple.

So it begins…

We start by matching our business requirements to the data entities in the target legacy database.

• Restrict the tables to only those we need in [SalesForce] schema:
  • Contact, Lead, Account
• Restrict the columns to only those required:
  • Done!
• Implement views to add business logic:
  • Only show active leads

With this LightSwitch friendly database schema we have a better experience consuming it within our applications. This technique is not restricted to LightSwitch applications per se as it really applies to the use of Entity Framework wherever it is used.

Script and Cut example

You can use Visual Studio SQL Server Object Explorer for this part but we chose to use SSMS (SQL Server Management Studio). We script the existing objects as we don’t like typing, besides deleting is easier.

Generate T-SQL database script in SSMS

We edit the generated script to change the database name from ‘CRM’ to ‘CRM_View’. We execute this and now have an empty database. At this point our security is all setup by default in our development environment.

Generate T-SQL script for Contact table in SSMS

We edit this generated script to change the database name and delete the columns that are not necessary for our LightSwitch application requirements. We execute this script and now our ‘CRM_View’ database has an empty ‘SalesForce.Contact’ table.

Then we repeat this to create the ‘SalesForce.Lead’ and ‘SalesForce.Account’ tables in the ‘CRM_View’ database.

That is the hardest part over.

Boot LightSwitch, Wire-up and Go

Create a new LightSwitch project, in our example a HTML (Visual C#) template variety. Then run the attach data source wizard and point it at our empty ‘CRM_View’ database.

Connect to our empty database

The whole process to achieve this and create a LightSwitch HTML application took less than 5 minutes as I type fast. So now we have some  T-SQL scripts, an empty ‘CRM_View’ database with three tables and a LightSwitch HTML application in need of a screen.

Create screens to consume the data

Not much to see here, if you know LightSwitch, click-click-clickety-click and five minutes later our first application version with Tabs, Tile Views and detail screens looks like this:

VS2012 Solution Explorer

Robert is your mother’s brother, that F5 moment arrives, but not before switching databases. Locate the server project ’Web.config’ file and edit the connection string to point at the real database with the data.

Switch databases in Web.config

It works! It performs well at design-time with our light-weight database schema. It performs well at runtime as we only transfer a subset of the contact list data that we need.

LightSwitch HTML Home Page

Even more smashing!

NEXT UP:

LightSwitch Data Freedom – Part 3: Pre-filter the data

PREVIOUS:

LightSwitch Data Freedom – Part 1: The chains of legacy databases

LightSwitch Data Freedom – Part 1

This article is part of a series of posts from Xpert360 Development Team to address real world scenarios and problems faced when designing, creating and implementing Visual Studio LightSwitch applications.

Freedom from the chains of legacy databases

Connecting to disparate data sources is one of the key strengths of Visual Studio LightSwitch. With so much data and so many data sources to consume the possibilities seem endless.

Attach Data Source Wizard

LightSwitch’s attach data source wizard has built-in connectivity to four main types of providers: Database, SharePoint, OData Service, WCF RIA Service.

It does come with a few real and perceived limitations as far as implementation and consumption of data are concerned, but are these limitations just self-imposed? Perhaps they are not as real as they seem.

Big Database, Big Data, Big Problems

By definition if a database exists then it is a legacy database. Yes, even that shiny new one you created yesterday. They come in all sizes, all shapes and comply to various degrees with established design patterns (or not!).

Some real and perceived difficulties:

• My database is complex and has got hundreds of tables
• The database schema design does not conform to standards
• My tables have hundreds of columns
• Performance in LightSwitch design and runtime is awful
• It is no use as I am not allowed to change the database
• I only want a few bits of data
• LightSwitch will not import our database metadata

You can create your own WCF RIA data service to address some of these concerns but there is another approach that you can try without the need to write any code.

Design custom logical schemas for use by LightSwitch

You can use this technique to great effect with surprisingly fast results, true to the principles of LightSwitch RAD. It does not answer all concerns and problems, in particular, poorly designed databases, but it is a technique well worth considering and adding to your LightSwitch tool bag.

You (or a colleague) will need some basic T-SQL skills to successfully use this technique so you can rule yourself in or out. If you are out then it really is the time to get some basic T-SQL skills.

If you shape your data source and data in a way that is more LightSwitch friendly then you will have a better experience consuming it within your LightSwitch applications.

To cut a long story short let us see an example

Here is a simple but often real scenario

• Legacy CRM database
• 200+ tables
• 10,000+ columns
• We just need a list of contacts with a subset of the columns

Conceptually we need to map the data across to our data view for LightSwitch.

Map the existing database schema across to a subset

The whole process to achieve this and create a LightSwitch HTML application to consume it took less than 5 minutes. So now we have:

Simple home page showing contact list

We did some T-SQL script generation, we made some simple name changes, but we did not write a line of code in LightSwitch!

It works! The legacy database contained unsupported data types that caused LightSwitch to fail to import the metadata at all before.

It performs well at design-time as the big, nasty database schema is no more.

It performs well at runtime as we only transfer a subset of the contact list data that we need.

Smashing!

NEXT UP:

LightSwitch Data Freedom – Part 2: Walkthrough shaping the data

LightSwitch Data Freedom – Part 3: Pre-filter the data

Windows Phone 7 – WP7 LOB App Dev

Don’t you love all the new acronyms! Well maybe not for everyone. Interested in LOB then also look at #TEE10 MEAP, MEAP! Sounds like the roadrunner cartoon.

With all this Windows Phone 7 goodness around all those blogs and videos can be pretty overwhelming. At heart we are games developers but in reality we are LOB application developers and for some odd reason we never came across Rob Tiffany’s blog until this week. We think it needs a plug.

It is so full of WP7 goodness that I thought it was worth re-blogging links to his series of articles. The articles ‘do what they say on the tin’, enough said.

Rob Tiffany
Thoughts on Mobility, Wireless, Entrepreneurship, Writing, Speaking, Wine, Strategy and Architecture

20-JUL-2010 Windows Phone 7 Line of Business App Dev :: The Overview Video

27-SEP-2010 Windows Phone 7 Line of Business App Dev :: Building a WCF REST + JSON Service

15-OCT-2010 Windows Phone 7 Line of Business App Dev :: Moving your WCF REST + JSON Service to Windows Azure

27-OCT-2010 Windows Phone 7 Line of Business App Dev :: Consuming an Azure WCF REST + JSON Service

05=-NOV-2010 Windows Phone 7 Line of Business App Dev :: Working with an In-Memory Database

Hope you like them too. Many thanks to Rob for sharing them…

Using Table Value Functions to Replace ‘Get’ Stored Procedures?

I learnt so many useful techniques, tricks and pointers at this SQLBits conference in York: tally tables, common table expressions (CTEs), cross applies, query tuning techniques, parallelism, data visualisation, to name but a few. However, the most useful technique I learnt was divulged in the first couple of hours during the training session given by Rob Farley.

In amongst the banter, jokes and excursions, a la Geordie comedian Ross Noble, was a complete gem which will make a huge difference to our existing and future projects.

Put simply, never use stored procedures for simple retrieves, but replace these with Inline Table-Value Functions (TVF).

Given our love of stored procedures, this is a difficult pill to swallow at first, but the benefit can be seen through the following example…

Suppose we have written a stored procedure that returns a dataset to the business layer of the application. In our case, we will have delivered the dataset via a WCF service.

Our stored procedure might be something like:
CREATE PROCEDURE dbo.usp_MyTable_Get (@FilterParms…)
AS
BEGIN
SELECT T.Column1, T.Column2, T.Column3, LT.Column2, …
FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable AS LT ON LT.IDCol = T.Column4
WHERE T.ColumnN = @FilterParm1
…
END

Now suppose that the application development team ask for an additional column to be included in the returned dataset. This may be because the dataset returns a foreign key identifier and the developers need to see the lookup description in the returned dataset. To return the lookup value we need to include an additional join on the lookup table.

Before going ahead, we’d confirm that the developer hasn’t or can’t get the lookup description from content held in memory; we’d explain that we’ll need to create a new stored procedure just for this situation, or, we’ll need to include it in the existing one which will (unnecessarily) adversely impact performance for instances where the service is used but the additional column is not required.

We have seen this situation many times and it is frustrating that an optimum one-size-fits-all solution hasn’t presented itself – until now…

The solution is to replace the stored procedure with an inline TVF which includes every possible join for foreign key lookups and every possible output column, and then build up SQL SELECTs on this TVF with the required columns relevant to each individual situation, letting the Query Optimizer exclude joined tables where necessary.
So, the above stored procedure is replaced with:

WCF Service
public List GetMyTable(dtoRequiredColumns, dtoParameters, dtoFilter)
{
List dtoOut = new List()
string strSQL = ""

foreach (col in dtoRequiredColumns)
{
if (col.IsRequired) strSQL += ", " + col.Name
}

strSQL = "SELECT" + strSQL.Substring(2) + " FROM dbo.tvfMyTableGet"

// add any WHERE and ORDER BY clause

SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(strSQL, SQLConnection)
DataTable dtResult = new DataTable()
SqlDataAdapter.Fill(dtResult)

foreach (DataRow drRow in dtResult.Rows)
{
// add row to dtoOut
}

SqlDataAdapter.Dispose()

return dtoOut
}


and the TVF definition is something like the following:

CREATE FUNCTION dbo.tvfMyTable
RETURNS TABLE
RETURN
SELECT T.every column…,
LT1.everycolumn…,
LT2.everycolumn…,
LT3.everycolumn…,
…
FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable1 AS LT1
ON LT1.IDColumn = T.ForeignKeyID1
INNER JOIN dbo.MyLookupTable2 AS LT2
ON LT1.IDColumn = T.ForeignKeyID2
INNER JOIN dbo.MyLookupTable3 AS LT3
ON LT1.IDColumn = T.ForeignKeyID3
…
GO
So the function includes every column in MyTable, and is joined to every table that provides a lookup for foreign keys.

And here’s the clever thing, at run-time the optimizer looks at which columns are included in the SELECTed column list and excludes those JOINs on tables that are not required for that particular query. In addition, query plans are cached (however, note that different length string parameters will generate additional cached plans).

The objections might go something like – underlying data is made more secure by placing these behind a stored procedure ‘firewall’. Of course, just as with stored procedures, we can grant execute permission to the function and not the underlying tables or views. And, this looks like dynamic SQL, which is bad! But, we are not allowing any injection to take place here – the listed columns are defined within the dtoRequiredColumns object and full control of the ‘dynamic SQL’ is maintained.

Here at last is the one-size-fits-all solution, so thanks go to Rob Farley for this.

Read more from Rob Farley’s Blog here .

T-SQL Stored Procedures – who needs them?

The answer to this question may seem obvious – for a secure, well-performing application, of course we all need them!

But what about all those great legacy applications that are littered with embedded SQL? What if we are asked to bring these into the brave new world of rich interfaces and mobile apps (plus those still glued to your traditional PC)? I have seen plenty of applications with MSAccess and MySQL back-ends. When scaling these up, do we really have to create a plethora of stored procedures when all that embedded SQL blinks winningly up at us to be used as-is? This question can be equally posed for a new application, where developers with SQL experience but lacking the know-how to use stored procedures, gamely rush into creating the SQL while coding. And what about dynamic SQL? How do we deal with that?

OK, so let’s rewind… we know that stored procedures bring enormous benefits:

1. Increased performance – the SQL is pre-compiled, unlike embedded or dynamic SQL
2. More secure – execute access given to the stored procedures only – direct access to tables and views are denied.
3. Better separation – the database access is contained within one layer, rather than scattered throughout the application UI code.
4. Easier maintenance – wholesale change is easier to apply to a number of stored procedures rather than searching through reams of application code.
5. Easier performance tuning – with the separation described above, it is easier to examine the performance of a stored procedure in isolation from UI code and fine-tune it as necessary.
6. Easier testing – stored procedures expose an additional area of testing thereby catching bugs in a more timely manner.

And the drawbacks?

The main drawback is the increased development time and the increased skill required to build the queries (but is this a bad thing for your important application?).

As for dynamic SQL – there certainly are situations where we may want this, e.g. building a complex filter from the UI, which don’t fit the pattern of simple parameters for stored procedures.  But we can still ensure the database access is encapsulated within the stored procedure by parsing strings or using data (e.g. in table variables) and I would argue this is best practice.

So it looks like we should always aim to build our data access layer using stored procedures. But what about those drawbacks?

OK, it’s time to get real – stored procedures are not complicated – In fact they are just the same as your embedded SQL but embedded in a server-side procedure instead. That means there’s more code to write. In fact quite a lot more code as we can add auditing, error handling and security checking, amongst other very useful things. And we need a version for each CRUD action.

But, after having written many stored procedures it becomes apparent that there is an enormous amount of repetition. In fact stored procedures settle into a very definite and predictable pattern which is pretty irritating if you have to code them individually from scratch, so much so that you’d think you were missing a trick. And you would be, because let’s face it, you can generate the procedures directly from the underlying data model (i.e. the table definitions and the relationships).

In fact there is often code to generate dynamic SQL from the underlying schema within applications. The leap is to change this dynamic code such that it generates the stored procedures as part of the overall development process.

Future reading:

The stored procedure CRUD pattern laid bare (Coming Soon!)

Other (more) entertaining musings on generating stored procedures

My Experience using Entity Framework for WCF/SQL Server Layered Application

This post is a little late coming , as I first starting using Entity Framework 4 when it was on CTP 1 at the start of the year. I used it up to CTP2 in March/April 2010.  Forgive me if there have been changes since and please do visit the MSDN site here and do read up on it (I read Julia Lermans book and her blog here)

First let me start by saying EF4 is a great product. It has the potential to not only generate code and classes for communication with database but also now has the ability to generate the database schema based on your code. This is great for developers who are not confident with databases or T-SQL, or do not have a lot of time to create a working system.

My background is primarily as a sql dba and developer, perhaps this is why I had a few problems with EF.

I have always believed that a good database layer is the best starting point for a good application design.  I realise there are times when this is not always possible …

However, I like using stored procedure when writing real applications.

1. They have the ability to protect the application from schema changes making it easier to change code without redeploying your front end.
2. They can protect the database from unscrupulous client developers who like writing badly performing inline sql (you know who you are …!)
3. They generally have the capability to perform well (of course they have to also be written well!)
4. They can be used to protect from direct table access and permissions can be applied both externally and programatically within the procedure.
5. They can enforce atomic updates to your data across multiple tables.
6. Most importantly for me.  They can remove a lot of complexity from the application and can allow you to create a normalised data schema, but protect the developers from the complexity of multi-tabled updates.

So why is this a problem I hear your ask.  Entity framework can be used with stored procedures can’t they?

Well yes and no …

Of course you can map your insert/update/delete portion of the entity to your stored procedures.  But they have to be written in a certain way and they must return key values in a select statement (no output parameters allowed here).

No out of the box support for the Get data process being a stored procedure, this needs to be a table or a view, unless you like editing the xml files.  I found this side of the entity framework to be incredibly painful, in terms of setting an exact entity that could use a view for the Get and stored procedures for the rest.  Let alone when you want to add a column and have to start all over again.

However what I did find useful in the entity framework was the ability to knock up a working demo system in an incredibly short amount of time. I had a WPF/WCF/SQL 2008 system working with a single table process in a matter of days.  But what I found was that I couldn’t take it any further without wasting a lot of time remapping schema changes to the entities.  I also decided to take a look at some of the generated SQL statements from the entity sql and linq sql. It wasn’t the worst I’ve seen but boy was it verbose !

My conclusion is … use the EF if you want create quick proof of concepts or prototypes but when it comes to writing real database applications stick to the stored procedures and real T-SQL, your DBA will thank you for it when he has to take it over and support it.  Microsoft seem to be approaching the whole code generation tools from the front end.  What I really need is the reverse, I want a really good database design, with standard stored procedures for data updates and tool to help me connect it to a front end without me writing all the code!

Please try it for yourself and do read the material at the start of the post  – make up your own mind!

SQLBits V Session Review : Practical Performance Tuning and Monitoring For SQL Server with Jasper Smith

So its obviously not the most exciting subject when it comes to SQL Server (how many years of running sp_who2 for blocking, profiler runs, deadloack trace flags etc etc…).

But it is possibly one of the more useful subjects especially when you’re up at 3am watching your SQLAgent Batch process run for over an hour longer than it did yesterday and not knowing when (or if!) it will finish so you can go back to bed.

I didn’t really ‘get’ real performance tuning till a few years back when a colleague of mine and I started lunchtime training sessions with each other trying to learn all the new features in SQL 2005.

I would say that Jasper did a very good job with what is actually quite a dry topic. If like a lot of SQL DBA’s and Developers you’ve read the MS Press books on Tuning then there was obviously a lot of overlap. But what you don’t get in the books is the sense of realism based on actual examples that were used. It also a lot easier to follow when someone is taking you through it in SSMS!

What was covered

The first half of the day was based mainly around ‘Core Concepts’ including the three main hardware aspects of performance and how SQL uses them.

 

• CPU
• IO
• Memory

We spent some time on Schedulers, CPU Affinity, Difference between NUMA and SMP configurations. Talked about CLR Processing and how SQL can force CLR performance under certain conditions. Jasper also discussed his usage of WSRM (Windows Server Resource Manager) and how he had very good results on multi-instance machines using it to assign resources and how he always left at least 1-2GB memory free for the OS (Remote Desktop Connections etc).

Next we covered Memory usage differences between 32 and 64 bit OS. Jasper then brought up the TokenAndPermUserStore cache issues he had experienced in the past.

The main empasis was placed on setting up a Monitoring Database on each server to hold collected data. (SQL 2008 does this on a per instance basis but the data can be moved to a central server).

Following this, Jasper discussed Disk IO , and the way database files should be stored (mdfs on one drive, ldfs on another, tempDB files split by core. We went in great detail about SQL Read Ahead and differences in version (EE with read and Extent ahead until it reaches max then will match Standard and read 8k chunks). We briefly touched on partition alignment with the disk subsystems and the performance gains that can be achieved through setting the disks up correctly.

(I’m looking forward to the day when the SQL DBA is actually asked what they need setup rather than asked to fix the performance issues on a badly configured server (” raid? whats raid configuration got to do with SQL Performance? Just fix it …”)

Although I enjoyed the morning session, I found the afternoon a lot more interesting. It was based around four performance killers that Jasper had come across.

Parameterised Queries

This was related to the way SQL deals with parameter changes and selectivity related to statistics. Jasper covered the FORCE RECOMPILE options available and how parameterising query plans helped this. The real eye openeer for me was the ADO.Net comment about how ADO.Net parameters are passed as variable length data types and frequently cause both implicit conversions in the stored procedures and many different versions of essentially the same query plan. (I’ll be revisiting a C# helper class in a BizTalk orchestration which loads SAP Customers into SQL next week to sort this one out!!!)

 

UDF in a View

A silent performance killer that does not show up in a query plan. A simple case statement in a scalar function kills performance due to the way the optimiser uses it.

 

Dynamic Search

This was a good one. Jasper showed us 4 different ways to run the normal dynamic search type stored proc.

 

1. Dynamic SQL
2. x = ISNULL(@value,x)
3. (x is null or x = @x)
4. and the coalesce version of ISNULL.

This one was an eye opener for me as I have done a fair bit of Reporting Services Over the past years and frequently find myself using the ISNULL check for dynamic searches. It turns out is was a bad choice compared to Dynamic Sql (by far the fastest but a nightmare to maintain) and (x is null or x = @x) which performed well but has issue with forced recompiles (some issues here with SQL 2008 SP1 CU4/5). Not sure what I will do in future yet!

Covering Indexes

This one I have been through many times in indexing situation but was covered well and as with all of Jaspers training was literally proven by him during the session.

 

Summary

A very good start to SQL Bits V and as its my first I was very impressed. Good trainer(many thanks Jasper!) , nice hotel, good food and coffee. Sign me up for next year ! Part 2 Tomorrow …