|
|
Posted by Erland Sommarskog on 10/01/32 11:19
Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> c) There is no covering index for your query. This basically means that
> in this case, SQL-Server will have to scan the entire table, except for
> the cached pages. Let's assume that 1.5 GB of your internal memory
> already holds data of the tblVoIPTempCallDetails. This still leaves
> 585,000 pages to be read.
Actually, the index on vcdStartDt would only require 250000 reads. And,
assuming that vcdStartDt is correlated with vcdId, those 250000 reads
would yield a high cache-hit ratio. So...
> If all the assumptions above are correct (which is highly unlikely), and
> SQL-Server would really perform a clustered index scan, then the query
> would take some 9 minutes. From that perspective, 1 or 2 minutes doesn't
> sound slow at all...
There you have the answer why the query is as fast as it is.
As for the rest of the analysis, there is not much to add to Gert-Jan's
analysis when it comes to the performance part. By adding good indexes,
you can make enormous performance gains. I would go for the covered index:
> - You could add an index that covers the query, for example on
> tblVoIPTempCallDetails(vcdStartDt, vdsName, vdsDescription,
> vcdBillingSec). Of course, the downside of this is, that this extra
> index will impact Insert performance
I would also like to give some remark on the table design. Except for
the primary key, all columns are nullable. Does this really mirror
business rules? Can vcdStartDt really be NULL? Not that this affects
performance any in significant way, but when I see this table design,
I get the impression that this was created by someone left-handedly.
And data-modelling for a database this size is definitely a job for
both hands.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|