What a pain in the @$$. I have to create a sortable, searchable view that has nearly 100 columns, with over 65 joins, 36 “pivot columns” and more. How do I make this giant monolithic thing perform well?
Select the whole doggone thing into a new table, which you actually use to view the data.
SELECT * INTO New_Table FROM Big_VIEW
This is ONLY ok if the data is pretty much read only, since you need to add a trigger to the tables to update the New_Table on update of the actual records.
Nasty, but you can only create indexed views on very specific requirements, and this puppy needed Functions that did lookups, as well as Left Outer Joins. Tough.
Just today I was tinkering with the 4.0 java dev tools for bb, and they have added enought integration points (finally) that a real enterprise app for these thing with true push technology is possible.
Mds server admin allows me to install an app from the server to the device and have it preload with data without the need to cradle, and all future updates can happen without having to sync manually.
It was a great development topic at the .net user group meeting in Missisauga last night.
The .Net Compact Framework combined with Visual Studio.net 2003 is far and away the best development platform for Enterprise handheld apps.
Sql Server CE is super easy to use for handheld data storage and web services and other http access is ridiculously easy to set up.
The only thing I was hoping to hear about and it is just not available, I suppose, is a server based “Push” architecture to get data to the handheld WITHOUT REQUIRING A SYNC.
The only server/device architecture that has this so far is Blackberry with the BES/MDS.
I suppose it would be a really cool C# app to write a web services based Push technology for the device that could be utilized by any app on the device. It would be a combination of server based service that could be called by apps wanting to push data to devices, as well as a service that the device calls on a pre-defined schedule for retrieving the data waiting for it and sending newly updated data down to the server.
This idea only works for wirelessly connected devices, since you don’t want to have to push “sync” on those, but since that is becoming the norm with 802.11 and GPRS/CDMA networks emerging.
I started this blog so that I could keep track of some work and personal stuff and maybe have some other people check it out.
Today is the first time I am going to the .Net User Group meeting. Should be cool.
Solved a wickedly cool problem with SQL Server performance yesterday by selecting a really slow view into a new table after data import and then updating the table from the view on a trigger. It works great for views with far to many joins to perform well, but need to be there.