Views and Queries 1

[SQLScholar]

Hey all,

I am kinda new to SQL, but picking it up quite quickly.

Just curious - do views run quicker then queries?? If not what is the point of them??

I am basically trying to optimise the database, and i am not sure quite where to start - anyone got any basic tips??

Regards

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[vongrunt]

> Just curious - do views run quicker then queries??

Basically - no. Unless we are talking about indexed/materialized views, which are brand new topic.

> If not what is the point of them??

a) less redundant code. Instead of writing the same (or similar) query all over the database, write view once than use it many times.
b) security mechanism. Suppose you have large table. Some users may see only columns ABCD, other only EFGH. Build two views, set their permissions and security is solved at database level.
c) hiding physical database design from other (all?) applications. Someone changes database structure and application that uses altered table crashes. Throw view between, alter it's definition and output will remain the same. This is useful for linking with external/legacy applications.

> I am basically trying to optimise the database, and i am not sure quite where to start - anyone got any basic tips??

It depends whether you design database from scratch or optimize existing one, and do you want to change physical database structure or not. For the beginning, print database diagram and use profiler.

 

[SQLScholar]

OK - that sounds great. I am at the moment, just diving for techniques, unfortunately i cant get my hand on the database till abit later.

Thanks STAR

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLSister]

Stored procedures are the fastest way most of the time for your code to process.

Things to think about in terms of optimizing:

Check indexes to see if you have the best ones.

Replace any cursors or while loops that you can. These are performance killers and most of the time are unnecessary.

Avoid using dynamic SQL. It is slower thatn a stored procedure without it and it is much harder to adequately test.

Joins are often faster than subqueries. But this is not always the case, it can pay to try both methods if you are experiencing a problem.

Run profiler to see what is actually happening vice what you think is happening. One time I thought a particular stored procedure was being called once by the GUI and it turned out it was being called multiple times to write data tot he same page. A rewrite fixed that so all the information was sent one time, speeding up the application considerably.

There's lots more on performance tuning, but you'll need to look at the books wrtten on the subject to really learn it.

Questions about posting. See faq183-874

 

[john76]

Just thought I'd add my 2 bits.

(Normal) Views do not run faster than a query, as vongrunt said. However, if you can write a stored procedure that accomplishes the same task, that does run faster than a view/query. The reason is that the stored procedure is compiled on the first use, a query execution plan is put together, then unless the stored procedure is altered SQL Server will reuse the compiled code and execution plan for each subsequent call to the sp. This makes for a faster executing query.

HTH,
John