Query tuning

Question

Post reply

Query tuning

Viewing 6 posts - 16 through 20 (of 20 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

PW (12/3/2007)
See yesterday's reply from the OP:

Thank you. I had not seen that thread and simply working based on SQL Serv OLTP side of the wall. I'll shut-up now. 😛

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

1) What possible need can there be for attempting to return 42M rows to the client?

2) Table scans are not necessarily a bad thing - especially in a reporting environment.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

TheSQLGuru (12/3/2007)
1) What possible need can there be for attempting to return 42M rows to the client?
2) Table scans are not necessarily a bad thing - especially in a reporting environment.

Well - considering there is no WHERE clause, it's going to be a scan of one kind or another. Of course, it should probably be an index scan (if the index covers and the overal table is much larger than the index) or a clustered index scan and not a table scan....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

California SSCommitted Points: 1888 More actions · Answer 4

Have some update to share:

The query is our financial quarter end report which our finance folks runs. This problem has been around for a while and never got noticed. i made a case to the management about the performane of this query and now we are pulling the vendor into the picture. I have provided all the info to the vendor and they are looking into it. Shall keep you posted about there findings on this problem.

But on the side note, I did tried couple of things what Antraes suggested. Unfortunately no luck. I made the fill factor of indexes to 100, ran index tuning advisor. but nothing productive came up.

Let's see, what the vendor has to say about this. I shall keep you guy's posted here.

Thanks again for all your contributions. This forum is awesome and you guys are tech guru's with wealth of knowledge.

Cali

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

I still want to know why it's necessary to return a 42 million row result set... THAT's the performance problem... especially if ANY of the "dim" tables are actually, ugh, views 😉

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

noeld SSC Guru Points: 96590 More actions · Answer 6

No matter how you slice it 42 million rows will KILL anything ( Client or Server). You should check is that is really all needed. All I can guess is that the query is actually missing a WHERE clause 😀

Cheers,

* Noel