SQL Server Performance Forum – Threads Archive

query optimization needed about rowcount

hi, i have 250.000 rows on a table. i need to page the resultsets so i use the rowcount method at this site: http://www.codeproject.com/aspnet/PagingLarge.asp</a> Entries Table ————- Id (pk) Entry text Time datetime Nick varchar Deleted int Topics Table ———— Id (pk) Topic varchar i use the paging sp in that page for this query (@Topic is the topic parameter): this is last version of my query (excluded unnecessary joins) SELECT e.Id AS Id, e.Topic AS Topic, e.Entry AS Entry, e.Nick AS Nick, e.Time AS Time, FROM Entries e WHERE (e.Topic = @Topic) AND (e.Deleted IS NULL OR e.Deleted = 0) AND (e.Accepted=-1) ORDER BY e.Id On Entries table (Id, Topic, Time) are clustered. On Topics table (Id, Topic) is non-clustered. The problem is that the query uses %100 cpu when there are about 50 ppl browsing the site. I can’t figure out why but please show the way i should go, before my hosting kicks me<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

behind this, it causes cxpackets. there is parallelism so the cause may be this? how could i fix?

to limit parallelism, put in OPTION (MAXDOP 1) at the end of the query for paging, suppose you want 10 rows displayed on your web page, consider bringing back 50 rows, and do the paging for this set entirely in your ASP code. for your query above, describe the execution plan, noting the components that show the highest cost, the estimated row count, number of executes, and the operation (index seek, bookmark lookup, table scan, join , etc) are you sure your query above is correct?
is Topic a column in the Entry table or Topics table, ie, why e.Topic = @Topic.
why isn’t e.Baslik or t.BaslikTr listed as columns what are the clustered and nonclustered indexes in each table

sorry i edited the query..

Hi I think that method o paging cause the problem. It absorbs too much resources. I suggest to change your code and return from query something like TOP 100. Inform users in your application that resultset is to big to show all records from database and they must change search conditions to find more precise resultset. Only method like this give us good response from queries like yours but ours are much, much more complicated. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

See if this helps:http://www.aspfaq.com/show.asp?id=2120
Otherwise, if you are already on SQL Server 2005 you can take advantage of the ROW_NUMBER() feature. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

very thanks for answers// i’ve tried "dynamic sql" method in aspfaq site for paging, and it helped to decrease use of cpu. i’ll inform you when my hosting reports me the results!

]]>