Urgent help required !!!!!

Question

Post reply

Urgent help required !!!!!

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

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

tfifield SSCrazy Eights Points: 9655 More actions · Answer 1

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

Todd Fifield

samsql Hall of Fame Points: 3044 More actions · Answer 2

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

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

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

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

"Keep Trying"

peter.macdonald Newbie Points: 3 More actions · Answer 7

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.

samsql Hall of Fame Points: 3044 More actions · Answer 8

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

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

samsql Hall of Fame Points: 3044 More actions · Answer 9

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

WiltsDBA Say Hey Kid Points: 661 More actions · Answer 10

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

WiltsDBA Say Hey Kid Points: 661 More actions · Answer 11

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

WiltsDBA Say Hey Kid Points: 661 More actions · Answer 12

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

mcloney SSC Eights! Points: 885 More actions · Answer 13

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 15

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"