Is this execution plan motivation for rethinking my core keys
When I entered the current (employer) company, a new database schema was developed and will be the basis for many future tools to be / will be created. Given my limited knowledge of SQL, I find the table to be pretty well designed. My only problem is that almost every table has a multipart primary key. Each table has at least a CustomerId and its own key. While they do define a specific entry, I feel that a few keys (we speak four times) are very ineffective.
I saw some unimaginable CPU usage today on a simple re-query that joins two tables, fetches one string field from the first, and highlights them.
select distinct(f.FIELDNAME) as fieldName
from foo f
inner join bar b
on f.id = b.fId
where b.cId = @id;
Checking the execution plan (I'm not an EP hero) I noticed that there are three main points of the CPU. Clear (as expected) and two are skewed above the scale. I personally think index lookups should be extremely fast, but they charge 18% of the cost of each. This is normal? Is this related to the (four) clustered indexes?
- UPDATE -
Query is used to create Lucene index. This is a one-time treatment that happens weekly (sounds contradictory, I know). I cannot reuse any of the results here as far as I can see.
a source to share
Could you please run the following queries and post their output:
SELECT COUNT(*), COUNT(DISTINCT fieldname)
FROM foo
SELECT COUNT(*), COUNT(DISTINCT cId), COUNT(DISTINCT fId)
FROM bar
This will help you assess which indexes best suit your needs.
In the meantime, make sure you have the following indices:
foo (FIELDNAME)
bar (cId, fId)
and rewrite your request:
SELECT DISTINCT(fieldname)
FROM foo f
WHERE EXISTS (
SELECT 1
FROM bar b
WHERE b.fId = f.id
AND b.cId = @id
)
This query should use index in f.FIELDNAME
to build the list DISTINCT
and index on bar
to filter out non-existent values.
a source to share
This kind of query looks familiar. I'm guessing here, but it's probably filling the internet / winform ui combo box which hits hard.
Perhaps you should cache the results on the application side so you don't run it often. In the worst case, you can cache this on the sql server side, but it is a massive kludge.
a source to share
Most databases do not use indexes unless the first column in the index is specified. You say customerId is part of every primary key, but you don't use it to join in your request. To answer your question correctly, we really need to see the output create table
for foo and bar, or at least show index from
.
However, your query might be faster if you change it like this:
select distinct(f.FIELDNAME) as fieldName
from foo f
inner join bar b
on f.id = b.fId
and f.cId = b.cId #Using this part of the key will speed it up
where b.cId = @id;
My comment assumes that your primary key is ordered as "cId, fId" Effectively, this would mean that your query doesn't have to check every cId, only those that are part of the index.
a source to share