|
|
Posted by Erland Sommarskog on 12/09/05 22:16
Xu, Wei (xuw@lucent.com) writes:
> I have wrote the following sql sentence.Do you have comments to
> improve the performance.I have created all the indexed. But it's still
> very slow.Thanks
I am afraid that is very difficult ot give much input given just the
query text. The fact that there are indexes, does not mean that they
are ussed, because they may not be the right indexes.
Normally, I would ask you to post CREATE TABLE and CREATE INDEX statements
for the query, but this is a very complex query, and I doubt that anyone
would actually analyse it to suggest better indexes or any other tweaks.
And it does not help, having no idea what the meaning of the query is.
There is one thing, though, that I can comment about the query:
> dbo.CreateFIDSort(Feat.FeatureID) as FIDSort,
I don't know how many rows your query returns, but if it returns many rows,
the call to the scalar UDF can be costly. You can always try to take it,
to see what the effect it has.
Apart from that, I can only advice you to look at the query plan and running
the query with SET STATISTICS IO ON, to get an idea of exactly what in the
query that is taking time.
It seems to me that the query plan should start with the condition
CPR_PATH.PROJ LIKE 'R26.0' (change to "=" if you can), provided that
that column is indexed. But this is only a guess on my part, that that
column is selective enough.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|