Post reply

Query tuning

  • December 3, 2007 at 9:13 am

    #756527

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

  • December 3, 2007 at 9:34 am

    #756540

    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

  • December 3, 2007 at 9:38 am

    #756541

    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?

  • December 3, 2007 at 12:02 pm

    #756610

    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

  • December 4, 2007 at 6:25 am

    #756898

    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)
    Intro to Tally Tables and Functions

  • December 4, 2007 at 10:42 am

    #757065

    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

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

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