[Solved] SQL Server different design query performance

EverSQL Database Performance Knowledge Base

SQL Server different design query performance

Database type:

I have been trying to check the performance of different types of database designs, but I am not sure if the results I get are correct.

I have two databases, with the different tables, but meant to store the same information.

  1. In my first design I put all my fields in one table.
  2. In second design I have multiple tables and want to use joins to find my records.

See the image for more detailed information:

enter image description here

When I run the query below against first database it executes in 0.03 sec.

SELECT 
    a.[idPerson], a.[Id], a.[Description], a.[LastName], a.[nme], 
    a.[Email], a.[Phone], a.[grp] 
FROM 
    [Student] a 
WHERE 
    a.[grp] = 'R3PU56' 
    AND a.[nme] = 'tZv5oxqSDEoXPnU' 
    AND a.[Email] = 'gyRpWWCopv'

When I run the query below against second database it executes in 0.03 sec too.

SELECT 
    a.[Id], a.[grp], b.[Email], b.[Phone], 
    c.[Description], c.[LastName], c.[nme] 
FROM 
    [Student2] a 
JOIN 
    [AdvancedPerson2] AS b ON (a.[Id] = b.[Id]) 
JOIN 
    [Person2] AS c ON (a.[Id] = c.[Id]) 
WHERE 
    a.[grp] = 'R3PU56' 
    AND b.[Email] = 'gyRpWWCopv'
    AND c.[nme] = 'tZv5oxqSDEoXPnU'

I was expecting the second query will take much more than first query because of joins. My question is, why both quesries are takingsame amount of time? If they are the same which database strucure should I use? Some people might say that second structure is complicated because of multiple tables, but I don't care. I am using Telerik ORM and both seems the same in C# code.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX student_idx_grp_nme_email ON Student (grp,nme,Email);
The optimized query:
SELECT
        a.[idPerson],
        a.[Id],
        a.[Description],
        a.[LastName],
        a.[nme],
        a.[Email],
        a.[Phone],
        a.[grp] 
    FROM
        [Student] a 
    WHERE
        a.[grp] = 'R3PU56' 
        AND a.[nme] = 'tZv5oxqSDEoXPnU' 
        AND a.[Email] = 'gyRpWWCopv'

Related Articles



* original question posted on StackOverflow here.