How can I analyze and optimize my sql query

Question

SELECT K.euclidNo,K.KlinikAdi,k.kisaAdi,
(SELECT COUNT(1) FROM Seanslar AS S
INNER JOIN Faturalar AS F ON F.fatura_id = S.refFatura_id
INNER JOIN BasilmisFaturalar AS BF ON BF.basilmisFatura_id = F.refBasilmisFatura_id
WHERE MONTH(S.tarihi)<MONTH(F.faturaTarihi) AND
S.refKlinik_id = K.klinik_id AND
S.durumuVT = 1 AND 
F.faturaTArihi BETWEEN '2009.06.01' AND '2009.06.30' AND
S.refFatura_id = F.fatura_id AND
F.iptalEdenKullanici_id is null
GROUP BY S.refKlinik_id) AS GecmisAydakiSeansSayisi,
(
SELECT kdvDahilToplamTutar  FROM BasilmisFaturalar AS BF
INNER JOIN Faturalar AS F ON F.refBasilmisFatura_id = BF.basilmisFatura_id
INNER JOIN Seanslar AS S ON S.refFatura_id = F.fatura_id
WHERE MONTH(S.tarihi)<MONTH(F.faturaTarihi) AND
S.refKlinik_id = K.klinik_id AND
S.durumuVT = 1 AND 
S.refFatura_id = F.fatura_id AND
F.faturaTArihi BETWEEN '2009.06.01' AND '2009.06.30' AND
BF.basilmisFatura_id = F.refBasilmisFatura_id AND 
F.iptalEdenKullanici_id is null
GROUP by kdvDahilToplamTutar
) as FaturaTutari,
(
    SELECT (COUNT(1)*KUA.fiyat) as t FROM Seanslar AS S
INNER JOIN Faturalar AS F ON F.fatura_id = S.refFatura_id
INNER JOIN BasilmisFaturalar AS BF ON BF.basilmisFatura_id = F.refBasilmisFatura_id
INNER JOIN KurumUrunAnlasmalari AS KUA ON KUA.urunAnlasma_id = S.refUrunAnlasma_id
WHERE MONTH(S.tarihi)<MONTH(F.faturaTarihi) AND
S.refKlinik_id = K.klinik_id AND
S.durumuVT = 1 AND 
F.faturaTArihi BETWEEN '2009.06.01' AND '2009.06.30' AND
S.refFatura_id = F.fatura_id AND
F.iptalEdenKullanici_id is null
GROUP BY S.refKlinik_id,KUA.fiyat
) AS SeansTutari
FROM Klinikler AS K
WHERE K.refKlinikGrup_id = 1

That query answering for 11 seconds. How can i optimize that. it must answering abot 3 or 4 seconds.

Thx for your helps.

Answer 1

The best way to get a good idea of where the problem is, is to run it in query analyzer, enabling the show Execution Plan option switched on.
(Where this is depends slightly depending on which version of SQL Server you're using, and if you're using the management tools or not.).

Once you've run that, look at the cost attribute of each item in the picture, and see which one is highest. That's your bottleneck. You can probably fix it by adding indexes, or by modifying your SQL.

Another attribute you might want to look at is the number of rows used in each operation of the query plan, as this can have a large impact on performance.

Answer 2

Replace your subqueries :

(
    SELECT (COUNT(1)*KUA.fiyat) as t FROM Seanslar AS S
INNER JOIN Faturalar AS F ON F.fatura_id = S.refFatura_id
INNER JOIN BasilmisFaturalar AS BF ON BF.basilmisFatura_id = F.refBasilmisFatura_id
INNER JOIN KurumUrunAnlasmalari AS KUA ON KUA.urunAnlasma_id = S.refUrunAnlasma_id
WHERE MONTH(S.tarihi)<MONTH(F.faturaTarihi) AND
S.refKlinik_id = K.klinik_id AND
S.durumuVT = 1 AND 
F.faturaTArihi BETWEEN '2009.06.01' AND '2009.06.30' AND
S.refFatura_id = F.fatura_id AND
F.iptalEdenKullanici_id is null
GROUP BY S.refKlinik_id,KUA.fiyat
)

with derived tables joined to KLINIKER on klinik_id, and the performance should increase massively.

eg :

SELECT K.euclidNo,K.KlinikAdi,k.kisaAdi, IsNull(DeriveOne.ValCount, 0) AS GecmisAydakiSeansSayisi,
....
FROM Klinikler AS K
LEFT JOIN (SELECT S.RefKlinik_id, Count(*) AS ValCount, 
    FROM Seanslar AS S
    INNER JOIN Faturalar AS F ON F.fatura_id = S.refFatura_id
    INNER JOIN BasilmisFaturalar AS BF ON BF.basilmisFatura_id = F.refBasilmisFatura_id
    WHERE MONTH(S.tarihi) < MONTH(F.faturaTarihi) AND
    S.durumuVT = 1 AND 
    F.faturaTArihi BETWEEN '2009.06.01' AND '2009.06.30' AND
    S.refFatura_id = F.fatura_id AND
    F.iptalEdenKullanici_id is null
    GROUP BY S.refKlinik_id) AS DeriveOne ON DeriveOne.RefKlinik_id = klinik_id
WHERE K.refKlinikGrup_id = 1

Answer 3

Query Plan and Execution plan helps a bit, but it can vary from development environment to production environment. I would suggest you split the query into pieces and performance tune each pieces. Some of the best practices you can add is

1. Prefixing the table calls
2. adding (NOLOCK) hint etc could help to

Hope I helped.

Answer 4

Here's a really easy way to find missing indexes on SQL Server (2005 or higher, I believe). The indexes at the top of your list will yield the highest benefit. If you're missing one that'll make a big difference, it should leap out at you.

SELECT 
    migs.avg_total_user_cost * 
        (migs.avg_user_impact / 100.0) * 
        (migs.user_seeks + migs.user_scans) AS improvement_measure, 
    'CREATE INDEX ' + UPPER(LEFT (PARSENAME(mid.statement, 1), 32)) + '_IXn'
        + ' ON ' + mid.statement 
        + ' (' + ISNULL (mid.equality_columns,'') 
        + CASE WHEN mid.equality_columns IS NOT NULL 
          AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
        + ISNULL (mid.inequality_columns, '')
        + ')' 
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') 
        AS create_index_statement, 
     migs.*, 
     mid.database_id, 
     mid.[object_id]
FROM 
    sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs 
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid 
        ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * 
      (migs.avg_user_impact / 100.0) * 
      (migs.user_seeks + migs.user_scans) > 10
ORDER BY 
    migs.avg_total_user_cost * migs.avg_user_impact * 
    (migs.user_seeks + migs.user_scans) DESC

Answer 5

in management studio run this command:

SET SHOWPLAN_ALL ON

then run your query.

It will give you a very detailed list of what SQL Server does to turn your query into a result set. Look over the output and try to learn what it means. I generally look for "SCAN", that is a slow part, and I try rewriting it so it uses an index.