Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 Performance: No Indexes vs Bad Indexes?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 Performance: No Indexes vs Bad Indexes?
    text
    copied!<p>i'm running into a strange problem in Microsoft SQL Server 2008. I have a large database (20 GB) with about 10 tables and i'm attempting to make a point regarding how to correctly create indexes. </p> <p>Here's my problem: on some nested queries i'm getting faster results <strong>without</strong> using indexes! It's close (one or two seconds), but in some cases using no indexes at all seems to make these queries run faster... I'm running a Checkpoiunt and a DBCC dropcleanbuffers to reset the caches before running the scripts, so I'm kinda lost.</p> <p>What could be causing this? I know for a fact that the indexes are poorly constructed (think one index per relevant field), the whole point is to prove the importance of constructing them correctly, but it should <strong>never</strong> be slower than having no indexes at all, right?</p> <p>EDIT: here's one of the guilty queries:</p> <pre><code>SET STATISTICS TIME ON SET STATISTICS IO ON USE DBX; GO CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO DBCC FREEPROCCACHE; GO SELECT * FROM Identifier where CarId in (SELECT CarID from Car where ManufactId = 14) and DataTypeId = 1 </code></pre> <p>Identifier table: - IdentifierId int not null - CarId int not null - DataTypeId int not null - Alias nvarchar(300)</p> <p>Car table: - CarId int not null - ManufactId int not null - (several fields followed, all nvarchar(100)</p> <p>Each of these bullet points has an index, along with some indexes that simultaneously store two of them at a time (e.g. CarId and DataTypeId). </p> <p>Finally, The identifier table has over million entries, while the Car table has two or three million</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload