August 2, 2008 at 3:21 pm
Could you please post the actual query along with the table definitions.
Todd Fifield
August 3, 2008 at 4:35 am
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...
August 3, 2008 at 9:50 am
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
August 3, 2008 at 4:53 pm
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
August 3, 2008 at 4:56 pm
...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
August 3, 2008 at 11:18 pm
My point was to take away the "IN" clause and use a join or like some one else pointed out use "exists".
"Keep Trying"
August 4, 2008 at 3:21 am
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.
August 4, 2008 at 5:59 am
i m having a job which runs every week and it updates the statics...
........ but still no improvement has been made.
August 4, 2008 at 6:02 am
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....
August 4, 2008 at 6:04 am
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
August 4, 2008 at 6:06 am
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
August 4, 2008 at 6:09 am
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
August 4, 2008 at 1:43 pm
>>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
August 4, 2008 at 6:46 pm
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
August 5, 2008 at 12:20 am
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