Urgent help required !!!!!

  • Could you please post the actual query along with the table definitions.

    Todd Fifield

  • i m hving a clusterd index on the column which cantain some duplicate records may be more than 1000 ....table contain more thn 10 millions of records....

    when i execute a simple statement ....the table show 100% scan

    eg select * from table 1 where id ='saby123'

    1 more thiing table does not hav a unique record col...

    as it use to take less thn a sec now it is taking 5 to 7 secs

    what shud i do now i think i need to make some chages...

  • maruf24 (8/3/2008)


    i m hving a clusterd index on the column which cantain some duplicate records may be more than 1000 ....table contain more thn 10 millions of records....

    when i execute a simple statement ....the table show 100% scan

    eg select * from table 1 where id ='saby123'

    1 more thiing table does not hav a unique record col...

    as it use to take less thn a sec now it is taking 5 to 7 secs

    what shud i do now i think i need to make some chages...

    Is there an index on the ID column?

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

  • maruf24 (8/3/2008)


    i m hving a clusterd index on the column which cantain some duplicate records may be more than 1000 ....table contain more thn 10 millions of records....

    when i execute a simple statement ....the table show 100% scan

    eg select * from table 1 where id ='saby123'

    1 more thiing table does not hav a unique record col...

    as it use to take less thn a sec now it is taking 5 to 7 secs

    what shud i do now i think i need to make some chages...

    How many of the 10 million rows has the value of 'saby123'?

    I did a similar thing on a million row test table with a clustered index on the ID column like your table... it returned all 18 rows in 27 milli-seconds.

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

  • ...and, with NO indexes whatsoever on the table, it only took 289 milli-seconds...

    Are you sure that the "table" you're looking at isn't a view? And, again, how many rows are your returning? When were statistics last updated?

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

  • My point was to take away the "IN" clause and use a join or like some one else pointed out use "exists".

    "Keep Trying"

  • Have seen similar problems with customers running largish databases, with a fair amount of change during the day. Usually down to statistics not being updated regularly enough. Suggest you put in a maintenance plan to update statistics every night.

  • i m having a job which runs every week and it updates the statics...

    ........ but still no improvement has been made.

  • i hav used exists but there is no use same result...

    ...... shud i do 1 thing to check the appication.... some one might changed the coding as it run through java....

  • Maintenance plan??

    You can do that with a scheduled job easily enough.

    Maruf, several people, including me, have suggested you update your statistics and then run the query again. You haven't mentioned it, so have you done this? Did it make any difference?

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • Stats should be updated nightly, indexes rebuilt weekly (create a job that only updates stats that are above an arbitrary threshold, say, 75%).

    Can you try and update the stats on that table alone, then run the query immediately?

    Is there a lot of I/O on that table?

    Is it partitioned? Is it, as someone earlier suggested, an indexed view?

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • Another thought - someone hasn't found the "forceplan" option have they?

    I think that you are spot on to go check the application - or use profiler to drag out exactly the code that is being called

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • >>My point was to take away the "IN" clause and use a join or like some one else pointed out use "exists".

    I hate to be a "me too" kinda person, but I wanted to emphasize that an "EXISTS" solution doesn't always perform better than a "JOIN" solution, though I've read a lot recently that suggest that's true. Just last week I rewrote an "IN" clause as an "EXISTS" and a "JOIN" - the "JOIN" beat the "EXISTS" by a substantial margin.

    The moral of the story? Test, test test!

    Cheers,

    -m

  • mcloney (8/4/2008)


    >>My point was to take away the "IN" clause and use a join or like some one else pointed out use "exists".

    I hate to be a "me too" kinda person, but I wanted to emphasize that an "EXISTS" solution doesn't always perform better than a "JOIN" solution, though I've read a lot recently that suggest that's true. Just last week I rewrote an "IN" clause as an "EXISTS" and a "JOIN" - the "JOIN" beat the "EXISTS" by a substantial margin.

    The moral of the story? Test, test test!

    Cheers,

    -m

    1) If you get the time, any chance you can post the 3 queries (IN, EXISTS and JOIN)? Please give row counts for each involved table and relevant indexes too if possible.

    2) You didn't mention how the EXISTS compared to the IN performance wise.

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

  • mcloney (8/4/2008)


    >>My point was to take away the "IN" clause and use a join or like some one else pointed out use "exists".

    I hate to be a "me too" kinda person, but I wanted to emphasize that an "EXISTS" solution doesn't always perform better than a "JOIN" solution, though I've read a lot recently that suggest that's true. Just last week I rewrote an "IN" clause as an "EXISTS" and a "JOIN" - the "JOIN" beat the "EXISTS" by a substantial margin.

    The moral of the story? Test, test test!

    Cheers,

    -m

    can u give the details supporting these facts?

    "Keep Trying"

Viewing 15 posts - 16 through 30 (of 40 total)

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