|
|
Posted by IanIpp on 10/02/28 11:45
That's interesting info Stu.
Erland,
>>I don't want to belittle, but I have a strong feeling that you still
have a lot to gain by tuning the application. Maybe you've past all
the simple ones: adding indexes, finding bad queries etc, and you
will now have to look for more structural issues. That is, how much
iterative processing (cursors and the like) do you have?
Virtually no cursors and iterative processing, due to all the problems
with them.
>>After all, the numbers Stu gave for his system were appalling better
than yours.
Erland, it's premature to judge one way or the other, since it's not
necessarily an apples to apples comparison.
Imagine this example. Two systems are both perfectly tuned and the
applications are perfectly designed. One is doing one query over and
over again: SELECT one_field FROM [SimpleTable] and that table is only
a few thousands rows. The second server is doing another query over
and over again: SELECT <lots of fields...> FROM [Table1] INNERJOIN
[Table2] INNERJOIN [Table3] INNERTJOIN [Table4] ... with tables that
each have a few million rows. In this example, system 1 will get a
signficantly higher # of TPS. This doesn't mean that you can jump to
the conclusion that system #2 is out of tune...it just means its job
requirements force it to do more work.
I'll give you a real life example. This is the most heavily used page
on the site (about 60% of the volume of traffic) and thus 60% of the
queries to the database:
http://www.rentacoder.com/RentACoder/misc/BidRequests/ShowBidRequests.asp?lngBidRequestListType=3&optSortTitle=2&lngBidRequestCategoryId=-1&txtMaxNumberOfEntriesPerPage=10&optBidRequestPhase=2&lngSortColumn=-6&blnModeVerbose=True&optBiddingExpiration=1
That page is actually called from a # of different places (newest bid
requests, my bid requests, search bid requests, browse bid request
category)...all of them lead to that page. But the end result is
always the same thing...show a list of bid requests. It seems simple
until you realize that there are over a million rows in our table of
registered people...and that must be joined to. We have half a million
bid requests and that table must be joined to. Connected each of these
is an average of 50 bids (x half a million=25 million rows) and this
table must be queried to produce some of the summary information. Etc,
etc.
Now maybe Stu's typical transaction is equally demanding. But without
asking him, we can't yet tell. (By the way Stu, do you know your
heaviest volume transaction and what kinds of tables sizes are
involved?)
Some other interesting things. The biggest killer of time on that page
is the fact that it involves paging. This means that:
a) Everyone expects you to provide a feature that say s Page # 1 of
<some #>...meaning you need to know how many total rows are in the
result set...even if you don't return them. So this requires doing a
COUNT (slow).
b) Paging is handled using a a great new SQL Server 2005 feature called
ROW_NUMBER(). That feature shaves off several orders of magnitude of
time versus in 2000 as you can see:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx.
Unfortuantely it doesn't work properly on a DISTINCT query (which is
understandable)...which requires structuring it as a ROW_NUMBER() of a
subquery. But there is a bug in 2005...it can do this...but as soon as
you add the BETWEEN clause or WHERE (which is what allows you to save
time via this method) it gives an error and won't run. I'm got a bug
report open with MSFT on this one too (I'm sure they love me...I have
way too many tickets open right now).
Ian
But my point is that back in the SQL 2000 days you didn't have this new
feature...and you just had to put up with the slowness if you were
doing paging.
Navigation:
[Reply to this message]
|