SQL Server Indexing Strategy -


i'm building web application using sql server 2008 , having difficulty coming best indexing strategy given our use-case. example, of tables structured similar following:

create table jobs  (    id int identity(0, 1) not null,    cmpyid int not null default (0),    statusid int not null default (0),    name nvarchar(100) null,    isdeleted bit not null default (0),     constraint [pk_dbo.jobs]        primary key nonclustered (id asc))  create clustered index ix_jobs_cmpyidandid      on jobs (cmpyid, id)  create index ix_jobs_cmpyidandstatusid     on jobs (cmpyid, statusid)

in our application, users separated different companies results in queries looking similar following:

select *  jobs  cmpyid = @cmpyid , ...

additionally, jobs accessed statusid (canceled = -1, pending = 0, open = 1, assigned = 2, closed = 3), similar following:

select *  jobs  cmpyid = @cmpyid    , statusid >= 0    , statusid < 3

would better off using composite clustered index shown above, or should use default clustered index on id field , create separate index cmpyid?

for statusid column, correct in assuming filtered index way go?

i'm considering partitioning table cmpyid or statusid, not sure best (or if no partition best).

this kinda premature optimization. can spend lot of time worrying 1 net faster database, when live in production, when have best chance of optimizing indexes.

sql server has traces see queries being ran , taking longest. can test out different indexing strategies when live in production no risk. @ worst can slow application down.

i typically setup clustered indexes on primary key. , non clustered on important columns. works jvm stack used sql server. don't know bottle necks going without having data see it.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more