An Urgent Ad Hoc Report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Yakov Shlafman » An Urgent Ad Hoc Report

39 posts, Page 2 of 4

««1 2 3 4 »»»

An Urgent Ad Hoc Report

Rate Topic

Display Mode

Topic Options

Author

Message

GSquared

Posted Wednesday, March 17, 2010 8:53 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 15,441, Visits: 9,570

l543123 (3/17/2010)

"First of all, you run a script to find Contractor and Expense tables across all databases on you production server"

sorry if I am wrong, but why are you searching all databases and if you dont know the tables then what are you searching Smile

also I think its a lot of code for something like,
select contractor name, max of expense and date from contractor
inner join expense on expense.contract_id = contrator.id"

That won't work. What if the highest expense is on a different date than the one you want? Max gives the highest value in the column for the rows defined in the Group By clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #884748

l543123

Posted Wednesday, March 17, 2010 8:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:27 AM
Points: 42, Visits: 170

ok thanks for the reply :)

Post #884755

Steve Jones - SSC Editor

Posted Wednesday, March 17, 2010 9:09 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,416, Visits: 13,730

It's easy if you control all the tables. What if you're being asked for a report from something like JD Edwards OneWorld, which is a large ERP package. The tables are named "A7230" and "E23"

Good luck figuring out the database diagram for that one. I've had to go through it and it's a mess.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #884778

SQLRNNR

Posted Wednesday, March 17, 2010 9:27 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Steve Jones - Editor (3/17/2010)

Yuck - I hate supporting those kinds of databases. Not a fan of that kind of design and it is no fun trying to create any query from them in an efficient manner.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #884806

Trey Staker

Posted Wednesday, March 17, 2010 9:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:52 PM
Points: 1,379, Visits: 2,626

Thanks for the article. It brought back a lot of memories of times when I've had such request.

I agree with Steve's comments. Most of the time when I've been asked to run these types of query with this little notice it is for an urgent business need and usually from a 3rd party database like of of the ones he listed.

I've also had request like this happen to data platforms that I have limited experience in like mysql. Sometimes it is to verify something that a business analyst is saying to an Executive. Sometimes they go to the DBA because of a relationship of trust that has been built up over time and they need a 2nd opinion.

The only caution here is that if they keep comming to the DBA for AD-hoc queries they stop relying on the B/As and your job could turn from being a DBA to being a Hybrid DBA/analyst. Knowing how and when to say no is a learned skill. If you always just say no, which a lot of DBAs do, then there may be missed opportunities for you and for the company. If you say yes too often then they'll come to you every day at 4pm and you'll be working past midnight. IMHO.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Post #884814

yakov shlafman-228008

Posted Wednesday, March 17, 2010 9:44 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 05, 2010 2:07 PM
Points: 17, Visits: 281

Thanks SSC Veteran.
I think you have a really good idea for an article - when to say Yes and when to say No. This is an ART.

Post #884825

WHug

Posted Wednesday, March 17, 2010 10:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:18 PM
Points: 30, Visits: 239

I like the story, its a good example of how we SQL junkies get it done.

I would have refused the work. Almost every time I have had to crank out a last minute ad-hoc, it has been wrong somehow, usually due to a data issue. Sometimes the Business Owner can help you QA the data, but that still isn't going to be right by 5pm. Usually the supervisor/manager doesn't have a real grasp on what the BO wants because he doesn't know the data either so the bulk of your effort is spent running the wrong direction. The only situation where this works is when a data warehouse exists where you can trust that what you are putting out is accurate.

Saying NO directly could get me canned, so my approach is to go directly to the requester (I don't care what their label is) and discuss it directly. At least then if I have to run around with my head cut off it is in the right direction, and the BO and I can come to an understanding of how correct the data will be and how long it will take to get done. I can update my manager and trust is built all-around.

The art of saying NO:
http://www.impactfactory.com/gate/assertiveness_skills_training_saying_no_too_nice/fungate_1741-4102-18850.html

Post #884866

timothyawiseman

Posted Wednesday, March 17, 2010 10:52 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

This was a rather interesting article. But it made me think of two things I think are worth noting.

First, when dealing with complicated queries over large datasets that do not have indexes, it is often faster to create the indexes you need then it is to run the query without the indexes, not to mention that the indexes will then be available for use in the future. This is not universal of course, but I have very frequently found it to be the case.

Next, it struck me as strange that they banned the use of cursors and recursion for an ad hoc query. Of course I recommend avoiding them in all cases where it is practical, but when you say that something is ad hoc and will be run precisely once and you are on a tight timeline so developer time is likely much more valuable than processor time that is one case where it might be worth using one. Even there, I would look for a non-cursor non-recursion solution first, but if I could generate a solution with a cursor faster than I could a truly set based solution then it may be worth considering in a situation like this.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #884878

SQLRNNR

Posted Wednesday, March 17, 2010 11:52 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

whug (3/17/2010)

I like the story, its a good example of how we SQL junkies get it done.

I would have refused the work. ...
The art of saying NO:
http://www.impactfactory.com/gate/assertiveness_skills_training_saying_no_too_nice/fungate_1741-4102-18850.html

I would have done the same thing. After a certain time in the day, with no requirements, no thought out process - it's just a recipe to set you up for disaster.

Post #884929

Michael Valentine Jones

Posted Wednesday, March 17, 2010 12:20 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 2,944, Visits: 10,500

CirquedeSQLeil (3/17/2010)

whug (3/17/2010)

I would have done the same thing. After a certain time in the day, with no requirements, no thought out process - it's just a recipe to set you up for disaster.

You can turn the "no requirements" to your advantage. Just send an email requesting answers to about twenty questions and head for the door.

Post #884947

« Prev Topic | Next Topic »

39 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions