Situation critical. View performance is maxxed out, need alternate route to refresh application data.
-
I've got an Excel application that about 12 users hammer away at. The guy that developed the application has set it to refresh (that is, select * from vwLongRunningView) after any single cell is edited.
Not what I would have done, but it's somewhat out of my jurisdiction. The problem is that the view takes at least 1.5 seconds to run and usually more like 2-5 seconds. It's a monster view with about 15 tables referenced, a table valued function and most are left joins.
Users have been complaining about long wait times, and rightfully so. I've done as much as I can to bump the performance of the view, but I think we need to take a different approach at this point.
Materializing the view is out. Believe me. I've tried. My newest idea is to "manually materialize" the view by truncating/inserting it's resultset into a table everytime somebody makes an update. My hang up is that someone will make a change, then pull data from the manually materialized view before it's been truncated/refreshed thus not seeing their data.
Ideally I'd be able to do something entirely behind the scenes and not involve the Excel app developer, but this isn't mandatory.
Does anyone have any ideas? Have I provided enough info?
-
This may sound harsh, but
1 - Kill the excel developper (sorry but he simply doesn't know what he's doing)
2 - can you show us what the excel sheet looks like??
Maybe there's a way of creating a few sheets and split the view into multiple smaller parts.
3 - If you have time, recode the whole thing in access ADP (for fast developpement), or .net, whatever you preffer.
If there's only 15 tables, you should be able to knock this project out in only 1-2 days (assuming a lot here
). -
Remi is generally correct HOWEVER,
I would find a tall tower, and ...... contemplate life. Whichever you decide don't get stressed out. That will only make things worse. Take it one step at a time follow Remi's advice and you will be good to go.
Whatever you decide dont forget that computers are made of silicate which is a derivative of sand so WHY get frustrated about SAND?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Sorry about the outburst... was remembering an old post from Joe Celko that went along those lines
.Anyways, I'm in this situation here where a few guys from the Office followed a few months course at night (about 200 H worth of formation). And they think they know everything. It's their way or the highway. Now on the other end, I followed the same course they took. Only I took the full course (1500 hours formation + 1500 hours of homework + 3000 hours experience, not to mention that I finished over 25 points over the group average while they didn't have to pass any exam). So it comes a point where you got to say get the f*ck out of the system and let me do my job (especially with awfull design like this one).
Can you seriously tell me which programmer in its right mind would refresh the data (client side) for the WHOLE DB when he's simply changed 1 value in 1 field... from the same client??????
I mean this is a case where you got no choice but to take control of the application and make sure he doesn't come back in (except for specifications). Make sure you only hear about his needs and not what he thinks his the right design.
-
Thanks for the heartfelt opinions guys. I got the CTO's ear just before lunch and explained the situation. He had a few performance tweak ideas I hadn't thought about involving some tables that already have a lot of the information that the view derives on the fly, but the gist of the conversation was that it was poor form to do that much work for what amounts to a single cell refresh. He used the "sledge hammer for a thumb tack" analogy. There's just too much logic in there to run it so often and expect so much of it.
My guess is that this wasn't an issue when it was first put in place because of the amount of data we started with, but the db size has grown 20 fold since inception.
Again thanks. I suspected that the best solution would be to work the application side, and your convition convinced me to move on it.
greg
-
... and from my experience, and because of poorly coded application. I'd suggest you simply rebuild it instead of just tweaking it. You'll save plenty of time.
-
I agree with Remi 100%. We have had to "Tweak" several (ok, ok) ALL of the applications that one of our vendors has provided i.e. the application still looks the same and has the same name. Kind of like taking the shell off a car and driving another one under it.
I can empathize with the amount of UPFRONT work but, if done properly the LONGTERM benefits will FAR outway the shortterm cost.
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply