query perf issue

Question

Post reply

query perf issue

siri_28

Valued Member

Points: 64
More actions
May 21, 2022 at 3:20 pm

#4036798

Hi Everyone,
Good morning.
I am new to Sql administration.Have one question.
We work for a govt organization and most of the end users are non-technical users.
Many a times I feel difficult to tell them in simple terms or a story the query performance is based on different factors.
They say, why the simple select query which returns 5 rows is taking 15-20 mins to retrieve data. I tell them in technical terms but they don't understand. blocking or waits etc... I also tell them that retrieving 5 rows from 10 rows table is different from 5 rows from a 2-3 table join which is scanning around 25 million rows.
My question is that, is there any analogies or metaphors to explain in a manner they understand what I am saying.
Thanks,
Siri

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80323 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 2

Let's see this simple query that you're talking about. See the article at the second link in my signature line below.

As for all the excuses you gave for their queries, why not take a look at the query and fix it and then use that as a tool to help the users learn?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

siri_28 Valued Member Points: 64 More actions · Answer 3

not like that. if the user comes back will provide the data.

But in general, it is obvious that when data is less and return rows from it will take less time and fetching data from large datasets is my question.

ratbak Ten Centuries Points: 1390 More actions · Answer 4

One of the best analogies I've seen on scanning vs. seeking -- for those old enough to even know what a phone book -- is:

A seek is like when you know both last name and first name, and can find the phone number by going directly to that last name, first name listing.

A bad scan is when you know the address, and have to look at every listing in the phone book until you find that address.

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 5

siri_28 wrote:

not like that. if the user comes back will provide the data.
But in general, it is obvious that when data is less and return rows from it will take less time and fetching data from large datasets is my question.

That's just not true. Having more rows of data in the source tables may not (and usually doesn't) have anything to do with performance for a given set of data. Giving users that impression also just tends to perpetuate the myth that the bigger the data in the tables, the longer it should take.

I'd have asked the user for the query they were running and figured out what was wrong with it. Then, I'd hunt the user down and explain the how and why it was slow and what you did to speed it up. The user would know better what to look for in future queries and, who knows, might even pass on what they know to others in the company.

To say it in blunt terms, BIG DATA <> SLOW DATA and you should stop perpetuating the myth that they are equal. Take the time turn such interaction into the opportunity to mentor. It'll also help you a ton in the rest of your job as well as being a strong point on your resume.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)