Working with the company's vendor software, and one of the reports is coming across super-slow, about 10 minutes to retrieve about 150 items. Caught the code with Profiler, and... It's got a DISTINCT on a list of fields that's about 30 items long. Pluck the DISTINCT out, and the code drops from 10 minutes to run to three seconds, with identical results in all test cases.

Sadly, the report query isn't being held in a proc in the database; it's being assembled in the ASP.net front-end, and run from there. As far as I know of, I can't reach in there and rip the offending bit out. Is there some way of handling this, or should I introduce my forehead to my desk a few times?

So, the report is also an ASP .Net page (doesn't reference an SSRS or Crystal type report)?

Either way, it's a perfect example of evidence to beat on the vendor about. Take the evidence to them and tell them to fix it.

Yep, the report's an ASP.Net page too; there's a SSRS report base in the software, but it's unused.

And poking the vendor about the issue... *Shudder*. This company's had issues with the software almost from day 1 for all kinds of reasons. Their search functions are SQL-injectable, and we've reported it multiple times, but their response has always been "it's not a high priority". After digging in their code, it's riddled with NOLOCK, and uses READ UNCOMMITTED SNAPSHOT when they can't NOLOCK it (and then they NOLOCK the joins, whee!). Asked them if I could work with them to rewrite the NOLOCKs out, since the business has seen the negative side effects pretty regularly, and their general response was "it's our software, we wrote it this way, and if you don't like it, fine!". Slightly more verbose, but that was about it.

The unfortunate part of this is that we work in conjunction with several other agencies, and the software choice was basically passed down to us, so we don't really have a way out of it. I've been hired to get around some of it, like writing SSRS reports to replace their inefficient ones, but this particular case uses some logic that I can't quite crack. Maybe with a day or two of analyzing the structure, but it's a lot of joins between non-descriptive columns using criteria that don't have much tacit meaning.

Err... *Ahem*. Ok, ranting over . Back to trying to get some method to work here! I might fume over it, but if I apply enough elbow grease, it should work eventually!

Vendors are awesome.

We love vendors.

They do so many screwy things and they care so little about anything more than making a buck. There are only a handful of vendors that don't fall into this kind of category, but most fit the profile.

Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNR

Lynn Pettis (9/26/2013)I know most Threadizens know I am currently deployed in Afghanistan as a civilian contractor.

I was notified this evening that my position state side has been cut as part of company layoff. Unlike my fellow workers state side whose last day of employment is tomorrow, I still have a job here in Afghanistan. What I don't have is a job to come home to now. It looks like I will also be extending out here until February 2014 at this time as well (Yea me!! And yes, I am excited about this).

What this means is I am reaching out to start my job search now while I am still working. Yes, I would like to stay out here as long as possible. The money is really good and offers me the opportunity to put money aside should I come home to a continuing job search.

I will be reworking my resume over the next week or so. I would like to stay in the Colorado Springs area as I still have a daughter in High School (she is a sophomore now).

I telecommute position would be ideal.

With this news, I have some other news.

My ex-wife and I are getting back together. Yes, we are giving reconciliation a try. This deployment to Afghanistan opened a door and we both stepped through.

For me it started when she and my youngest daughter took me to the airport to start my journey to Afghanistan. She started to cry as we said good bye at the airport. I couldn't stop myself from telling her I loved her.

We have talked openly almost every day since then. It has been a blessed journey for me, and a confusing one for her.

It was during our talks I found out it started for her the night before I left while she was fixing my going away dinner. I stayed at her (our) house that night having moved out of my apartment that day so she and Kylie could take me to the airport the following morning.

That's totally awesome news all the way around! I'm excited for you both!

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

Working with the company's vendor software, and one of the reports is coming across super-slow, about 10 minutes to retrieve about 150 items. Caught the code with Profiler, and... It's got a DISTINCT on a list of fields that's about 30 items long. Pluck the DISTINCT out, and the code drops from 10 minutes to run to three seconds, with identical results in all test cases.

Sadly, the report query isn't being held in a proc in the database; it's being assembled in the ASP.net front-end, and run from there. As far as I know of, I can't reach in there and rip the offending bit out. Is there some way of handling this, or should I introduce my forehead to my desk a few times?

Heh... and now you truly understand the need for high velocity pork chops.

Like the others said... I love vendors.... I just can't cook and eat a whole one.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

hisakimatama (9/20/2013)*Huff* *Wheeze* I... I did it! I managed to read the entirety of The Thread! That was... Interesting . Been lurking around here, being generally amazed at the questions posted and everyone's unwavering insistence on being helpful despite the odds. The community here is great indeed.

I may chime in here a bit here and there, if you all won't mind, and if the rollercoaster of subjects strikes my fancy. Hopefully I won't eat a high-velocity pork chop or two in the near future

Are you still sane?

Well, after reading so many posts about kilts, pork chops, Cthulhu, DBCC TimeWarp, hamster poo, and considering my last post in the thread... Probably not

Wow - I'd forgotten all about those lonely hamsters.

Wow!!!! He really DID read the whole bloody thread!

Ah, hampster poo.... the ultimate fuel for DBCC TIMEWARP. I'm currently engaged in a project to make hampster poo look like pork chops... to feed to vendors, of course. The only real problem I've run into is that I also have to use DBCC TIMEWARP so the smell doesn't get to their nose until after I've fed it to them. I just can't incorporate it into the triple banded Wrist Rocket yet. I might give up on using DBCC TIMEWARP for that. Instead, I may have figure out how to "pulley" the bands so that I might be able to add a 4th or even 5th band. That would give me a range increase where the poo chop would get there long before the smell. Think of it as a one-two punch.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

If you need it peeled, find someone that deserves a little punishment. Kids are good for this, but the rogue developer who's introduced too many bugs works just as well. Deploy paring knife and well wishes.

If you want to hit something with a flying potato, throw it.

Thats how it started with pork chops until I burned out my rotator cuff... too many 3rd party vendors and developers.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013