3

I'm a software developer at a rather large company with a small team of software developers. I am the only remaining developer (.NET developer) in the "reports" team of the company. The rest of the developers (7 in total) are in the transactional part of the software. There are two "report analysts" with me.

This company has three full-time DBAs. But lately I've been receiving what, in my opinion, are too many tickets from the DBAs. Most of these tickets are complaints from the DBAs that our scripts are running "too slowly". They decided that 3 minutes should be enough for any script, and anything that takes more than 3 minutes needs fixing. The other team, the transactional team, basically just does CRUD operations (mostly C, we're into "tens of millions of rows" territory). The company has been bought by a larger company and dozens of people were laid off. There is no money for new servers (production is a '09 Xeon with 24GB RAM, and 10K SAS).

The problem seems to be that the DBA expect any script to run under 3 minutes, except of course, being the reports person, I have to sift through millions of records to get the required statistics (our reports are done nightly because of that reason). The transactional team has also broken down the database to textbook normal form. This means most scripts start off 10 INNER JOINs.

The DBAs messages aren't very helpful. I'm often required to look at query plans and make sure the indices are used. They expect the developers to worry about index creation and preformance. I'm not really sure that, as a software developer, it's my job to worry about indices.

I also have no production credentials, so I have no way of knowing if certain indices are present in production (often I get tickets back that "this table has more indices than data!!!!"). I also have no SP execution permissions (big no, developers have only SELECT permissions in production). And certainly not SHOWPLAN permissions.

The main issue seems that we don't have a copy of production to test our queries against. There is a mockup with a few records, and of course any script runs really quickly against a few records but runs slow on production. So basically I have to make guesses about SQL performance tuning, send the script out to the DBA to deploy (that seems to be their job description), and wait for their ticket next day to tell me in what new way the script has failed or made things worse.

Today I received a ticket with the following description: "X sp taking longer than 3 minutes. Replace CURSOR with WHILE", so this is why I'm asking:

Is this normal procedure for DBA operations? Am I wrong to assume that, if an SP is underperforming, it should be the DBA's job to optimize it? In case it's not the DBA's job, but mine, should I have more permissions to the production DB for the required traces, query plan, SP execution, etc? Is it normal not to have a copy of the production DB to test large queries against?

I would like to present my case to the boss here, but I want to be sure I'm not wrong about where my responsibilites as a .NET developer end.

Improve this question
2
  • 1
    Well they should be helping you if they say there is a problem especially if you have no way to test against production data or production data scale. As far as it being the DBA's job to optimize it you could also say it is the developers job to write a query correctly in the first place to minimize the need for DBA's to optimize it otherwise all they will be doing is optimizing other developers work.
    – Joe W
    Feb 4, 2019 at 20:44
  • 4
    That and "Replace CURSOR with WHILE" seems like a dumb piece of advice. Require that you get a test environment, with similar database/table sizes and characteristics as the production db. If not possible, create a sample db with roughly the same size as production. Different box may mean different plans but at least you'll get to experiment with various indexing and rewritings of your queries, in the same scale.
    – ypercubeᵀᴹ
    Feb 4, 2019 at 20:57

1 Answer 1

Reset to default
2

I agree with ypercube - the best course of action would be getting a test environment with a recent backup of production data. You say you have SELECT permissions in production so it doesn't seem as if information is being kept confidential from the dev team. If this is possible, you would be able to quickly test your procedures and find possible improvements.

I disagree with your assumption that it is the DBA's job to optimize an inefficient query. Part of the role of the developer is to write queries and/or code with performance in mind.

However, I also disagree with the DBAs that all queries should run in under 3 minutes with no exceptions. That is an absurd statement if you are writing complex reports against millions of records.

Sounds like there needs to be some give and take from both sides. You may be able to make your queries more efficient, but the DBAs need to give you the tools to assist with that - mainly a test version of current production data. Also, if the queries are indeed performing poorly then the DBAs should provide you with trace logs and other information to help you pinpoint any possible hangups with your query.

Improve this answer
3
  • Query time should depend on what data you are trying to get (and what needs to be done to get it) 3 minutes could be way to long for some queries or nowhere near long enough for others.
    – Joe W
    Feb 4, 2019 at 21:23
  • Absolutely. It greatly depends on the type of query. That's why I said that a 3 minute limit for ALL queries with no exceptions is ridiculous. 3 minutes could be way too long for a simple query, but far under the appropriate time limit for very detailed and complex queries. It is hard to say if 3 minutes is appropriate in this particular case without seeing OP's query and data structure.
    – Eric MPC
    Feb 4, 2019 at 21:27
  • FWIW, I'm not refusing to optimize my queries. But there are things outside my scope and I don't think a DBA can expect every developer to be as knowledgeable as him about fine tuning queries for performance. My point about the indices was that I suppose controlling use and status of indices in production is his role after all.
    – hjf
    Feb 5, 2019 at 12:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.