CODEWITHSUNDEEP
sql-serverperformancesql-server-2008foreign-keyssql-server-express
HugoRune
HugoRune

Reputation: 13819

increase db performance for 'foreign_key=x' queries

I have a database in ms-sql-server express 2008 which is accessed with linq-to-sql from the same pc. (not much concurrent access, but complex queries)

It has several tables and each table can get quite big, to the point where performance of queries, deletes, updates and inserts becomes too slow.

There is one main table projects and almost every other table has

Selects, deletes, updates and inserts always operate on a single project. I never need to update entries across different projects or select matching items from 2 projects etc.

Is there some way I can use this fact to increase the performance of the database?

I already have a non-clustered index over the foreign key project_ID wherever applicable.

Is there anything else I can do? Would partitioning help me, if it were available for sql-express?

edit:

examples of slow queries (paraphrased, most are linq-to-sql queries, for a few deletes i execute the sql directly):

  delete from items 
  where items.projectID=X 
  AND (items.prop1=a OR items.prop2=b OR items.prop3=c)
  (deletes a few 1000 items, fast when database is empty, slow when lots of other projects exist)

  select top 1 itemprops 
  from itemprops 
  inner join items on items.id = itemprops.itemid 
  inner join project on item.projectid=project.id 
  inner join modes on itemprops.modeId = mode.id
  where item.name = X and project.id = Y and mode.name = z
  (find a certain itemprop corresponding to an item and a mode)


  select top 1 * from foo where projectID=x and name=Y and type=z
  (nonclustered index on projectID + name + type exists)

The common point between all my queries is this: they all have a where projectID=XY in there somewhere

Upvotes: 0

Views: 106

Answers (2)

HugoRune
HugoRune

Reputation: 13819

All my attempts with indexes or various settings in the DB did not significantly improve performance.

Here is what worked for me in the end:

for this type of query:

 delete from items 
 where items.projectID=X 
 AND (items.prop1=a OR items.prop2=b OR items.prop3=c)

Instead of bulk deleting all items matching the criteria, I found a way tht is significantly faster, using ON DELETE CASCADE

  1. create a new dummy project
  2. update all items that should be deleted: update items set items.projectID=DummyProjectID where items.projectID=X AND (items.prop1=a OR items.prop2=b OR items.prop3=c)
  3. delete the dummy project. Since cascade deletes were enabled, this also deleted the items.

For some reason this is much faster than simply deleting the items. Creation of the new project and updating a few 1000 items happens almost instantly, and deleting the project is at least 10 times faster than deleting the items directly.

For these types of queries:

 select top 1 itemprops ...

it was much faster to load all itemprops for a project into a dictionary once, and then answer all queries from this local cache. It is not very elegant, and I have to remember to update the cache after each change, but it works.

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

For the first statement (delete), you can create a new, non-clustered index that includes both of your join fields:

CREATE NONCLUSTERED INDEX <MeaningfulIndexName>
ON Items (ProjectID)
INCLUDE (Prop1, Prop2, Prop3)

Same goes for the last SELECT query. The optimizer should realize that this index will result in a better plan and use it - check the query plan and if not, look into index hints. You might also remove the ORs and just do three separate delete queries.

On the last two, make sure you use an ORDER BY clause so the query knows what TOP you want. The middle one is tough: short of having an index on everything (again, check the query plan), you might want to see if you can avoid joining on that "name" field and try to use an ID instead. I know that's not always possible, but SQL is better at comparing numbers than strings.

Upvotes: 0

Related Questions