Sql-server – How to tune the below query

performancesql serversql-server-2008-r2

I am currently working on below table structure to pull the data/ generate any one time report.

Table ABC:

ID  Field       Value
1   Name        Nagarjuna
1   Gender      Male
1   Industry    Actor
2   Name        Sachin RT
2   Gender      Male
2   Industry    Sports
3   Name        Theresa
3   Gender      Female
3   Industry    Social Worker
4   Name        Saha
4   Gender      Female
4   Industry    Software Engineer
5   Name        Swe
5   Gender      Female
5   Industry    Doctor

In order to get the report in the format Name,gender,industry values, I am writing the below query

Select  
  vname.Value, vgender.value, vindustry.value 
from ABC [vname] with (nolock)   
join ABC [vgender] with (nolock)
  on vname.id = vgender.id  
join ABC [vindustry] with (nolock)
  on vgender.id = vindustry.id

where vname.field = 'Name' 
  and vgender.field = 'Gender'
  and vindustry.field = 'Industry'

I am currently working on the table which has around 500k of records and for each id type there would be around 10 – 30 entries, so if more records are there for one id type then more joins would be added up.

Because of this executing it is taking a lot of time.

Is there any way that we can reduce this time taken for execution and also this table is used by an website where there would be around 1000 to 50000 users.

Best Answer

This is your query:

Select vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock) join
     ABC [vgender] with (nolock)
     on vname.id = vgender.id join
     ABC [vindustry] with (nolock)
     on vgender.id = vindustry.id
where vname.field = 'Name' and
      vgender.field = 'Gender' and
      vindustry.field = 'Industry';

You can try indexes on this query. I would try ABC(id, field, value) and ABC(field, id, value).

However, you might also try the following:

select max(case when field = 'Name' then value end) as Name,
       max(case when field = 'Gender' then value end) as Gender,
       max(case when field = 'Industry' then value end) as Industry      
where field in ('Name', 'Gender', 'Industry')  
from ABC
group by id;

Related Solutions

Sql-server – CXPACKET Waits performance tune for SQL Server 2008

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else. When you see CXPACKET waits you need to look at the other threads of the SPID and see what other waits are besides CXPACKET. That other wait is the problem.

To your specific issue, the reason that the run times are so crazy is probably because the SQL Server is generating a different plan on some days because the statistics are out of date (making the job run long). Then you either manually update statistics (or via a job) or auto stats kicks in and then the plan gets better and the job runs quickly again.

Once you've solved the stats problem you can start looking at other reasons why the job is running slow. The fact that you only have a single disk is not helping any for sure.

SQL Server Query Performance – How to Optimize Queries

The optimizer can consider many plan alternatives (including ones with multiple seeks) but for disjunctions (OR predicates) it does not consider plans involving index intersections by default. Given the indexes:

CREATE CLUSTERED INDEX cx 
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1 
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

We can force index seeks (assuming SQL Server 2008 or later):

SELECT * 
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON 
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

FORCESEEK plan

Using your sample data, the seek plan costs at 0.0332551 units compared with 0.0068057 for the scan plan:

Scan plan

There are all sorts of possible query rewrites and hints we can try. One example of a rewrite to promote an option the optimizer does not consider for the original plan is:

SELECT * 
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

This execution plan does not seek the second index if it finds a match on the first:

APPLY TOP Plan

This may perform very slightly better than the default FORCESEEK plan.

Without adding any new indexes, we can also force a seek into the Dispatch table:

SELECT * 
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Seek 2

This may be better or worse than the first example depending on things like how many rows are in each of the tables. The APPLY + TOP improvement is still possible:

SELECT * 
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

Best Answer

Related Solutions

Sql-server – CXPACKET Waits performance tune for SQL Server 2008

SQL Server Query Performance – How to Optimize Queries

Related Question