CODEWITHSUNDEEP
sqlsql-server
Ankur Nigam
Ankur Nigam

Reputation: 83

Should I create View or write Query instead for better performance?

To help you understand, think like, I am working on some email system where email are saved in Db and can or can't be Draft.

Problem:

I have 2 tables with following structure

tblUsers
=========
Id - PK
Fname

.

tblMails
=========
Id - PK
UserId - F.K. tblUsers.Id
isDraft - Bit(boolean) field, default = 0

I need to search all mails and drafts in 'tblMails' for particular UserId saperately. To accomplish this, i have two ways:

  1. I can create 2 View - 'showMails' and 'showDraft' that select all records from 'tblMails' on the basis of 'isDraft' field. Thereafter, I can apply 'WHERE' condition on the View to fetch details for particular user.

  2. Instead of writing View, I apply 'isDraft=1' or 'isDraft=0' everywhere in my code.

Which of these should be more appropriate considering computational cycles required to fetch the records?

Upvotes: 0

Views: 419

Answers (2)

Sunil Tandon
Sunil Tandon

Reputation: 151

IMHO create a new field called draft and set default on it (say = 1). whenever its a draft = the default will set the value (=1) and in the other case explicity set draft to 0. Retrieving and reading the values wont have impact on the performance.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The logic for choosing between a draft and a regular email is pretty simple. I see no problem with including it in the code wherever it is needed. On the other hand, a view is a good way to encapsulate logic, particularly when the underlying data structures might change.

The issue of performance is subtle here. If you are using this for small queries in a transactional system that have to run really fast, then I think the view has a slight edge. Views tend to be compiled once, on first use, and then the query plan is used again. Other queries will have their plans cached, but the view is less likely to need recompilation.

On the other hand, this is also a downside. If the underlying data changes, the view might have a poor execution plan -- hurting everything.

To really determine the right approach (and there are others as well), you need to ask yourself some questions, such as:

  • How import is performance?
  • Are the queries supposed to have high throughput, with response times in the milliseconds?
  • How many people are working on the system?
  • Should "draft" and "regular" emails really be stored in the same table?
  • How big will the tables grow over time?
  • Will the underlying data structures be changing?

Upvotes: 2

Related Questions