Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Are the "param1, param2, param3 ... param8" variables or columns in another table? In any event I would break it into chunks to get usage out of the indexes and slice down the work that needs to be done with each subsequent parameter check.</p> <p>You want to create a clustered index on resultnumber and only resultnumber and then create nonclustered indexes on resultnumber, param#min, param#max combinations. Should be a total of 9 indexes on [dbo].[MappingTable].</p> <pre><code>Create Clustered Index ix_rn On [dbo].[MappingTable] (resultnumber) Create NonClustered Index ix_p1 On [dbo].[MappingTable] (resultnumber, param1min, param1max) ... Create NonClustered Index ix_p8 On [dbo].[MappingTable] (resultnumber, param8min, param8max) </code></pre> <p>This will allow you to get performance gains from the code below. It should run extremely fast.</p> <pre><code>With param1 As ( Select resultnumber From [dbo].[MappingTable] Where @param1 Between param1min And param1max ), param2 As ( Select m.resultNumber From [dbo].[MappingTable] m Join param1 p1 On m.resultnumber = p1.resultnumber Where @param2 Between param2min And param2max ), param3 As ( ... ... ), param8 As ( Select m.resultNumber From [dbo].[MappingTable] m Join param7 p7 On m.resultnumber = p7.resultnumber Where @param8 Between param8min And param8max ) Select resultNumber From param8 </code></pre> <p>A table implementation would look something like this. It would still take awhile depending on the size of the table you're joining, but it should still run faster than your original query.</p> <pre><code>With param1 As ( Select m.resultnumber, w.tableIdentityValue From [dbo].[MappingTable] m Join whateverJoinTable w On w.param1 Between m.param1min And m.param1max ), param2 As ( Select m.resultNumber, w.tableIdentityValue From [dbo].[MappingTable] m Join param1 p1 On m.resultnumber = p1.resultnumber Join whateverJoinTable w On m.tableIdentityValue = w.tableIdentityValue And w.param2 Between m.param2min And m.param2max ), param3 As ( ... ... ), param8 As ( Select m.resultNumber, w.tableIdentityValue From [dbo].[MappingTable] m Join param7 p7 On m.resultnumber = p7.resultnumber Join whateverJoinTable w On m.tableIdentityValue = w.tableIdentityValue And w.param8 Between m.param8min And m.param8max ) Select resultNumber, tableIdentityValue From param8 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server - Index a Mapping table with min and max range
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEric J. Price
    1. USEric J. Price
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI'll give this a shot. Two quick questions, one, in your create indexes code example, do you mean to put Create Nonclustered index instead of Create Clustered index for the param indexs? Also, would the first select statement make use of the first param index because it doesn't use the resultnumber at all?
      singulars
      1. PO
      1. USuser1917038
    2. COYes, you're correct, the additional indexes should have been NonClustered; thanks for catching that typo. It would use the first param index because it is returning the resultnumber.
      singulars
      1. PO
      1. USEric J. Price
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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