Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
David Singleton
Starting Member
35 Posts |
 Posted - 2010-03-03 : 08:41:32
|
| I am trying to see if I can improve system performance using views. Basically the Front End Application that I work with only has the option to issue SELECT * FROM, even if I only need 3 or 4 fields out of 100 I still retrieve all. currently I am working on a query that pulls about 3.3 million rows. So my idea is to create a view on the table that only has the 3 key fields and 4 data fields that I actually need, then a SELECT * on the view will only pull the data I need.But are VIEWS comparable in performance to Tables?Basically its going to be about 2 days of programming to replace the Table with the view (its in a lot of places) so I don;t want to do this unless I can actually expect some performance boost. So I thought I would be a bit cheeky and ask everyone here for opinions and hopefully either save me wasting two days, or encorage me to go ahead and try it.PS I wrote a blog about this : http://dynamicsuser.net/blogs/singleton/archive/2009/03/14/select-from-and-how-to-do-it-better-in-nav.aspx as a possible solution. Microsoft did like the idea, but said it would be at least 2013 before they could implement this, and I need a solution sooner.Thanks in advance.David SingletonMicrosoft MVP Dynamics NAV |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-03 : 09:56:47
|
Isn't it possible to have a test without involving the front end app in first step?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-03-03 : 10:08:26
|
| Proper indexing could also help performance. If you're mostly selecting the same few columns from the table, a covering index would certainly help, and would take much less than 2 days to write!JimEveryday I learn something that somebody else already knew |
|
|
|
David Singleton
Starting Member
35 Posts |
Posted - 2010-03-03 : 10:35:30
|
quote:
Originally posted by webfred
Isn't it possible to have a test without involving the front end app in first step?
No, you're never too old to Yak'n'Roll if you're too young to die.
 of course. It never even occured to me, but it makes sense. Just create the view and run a query in SSMS and compare to the same against the table.David SingletonMicrosoft MVP Dynamics NAV |
|
|
|
David Singleton
Starting Member
35 Posts |
Posted - 2010-03-03 : 10:37:44
|
quote:
Originally posted by jimf
Proper indexing could also help performance. If you're mostly selecting the same few columns from the table, a covering index would certainly help, and would take much less than 2 days to write!JimEveryday I learn something that somebody else already knew
The two days was referring to modifying the front end app. But webfred's suggestion makes sense.you do bring up the other question that I will have later. I read in another post that views will use the indexes from the table if done correctly. Is there anything special I need to do to make that happen? Currently the Table has the indexes I need.David SingletonMicrosoft MVP Dynamics NAV |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-03 : 10:46:55
|
| As long as the query used to build the view uses the index(s) of the base table(s) then a query on the view will also use those index(s)part of that is obviously having a nice covering index to avoid key loop ups and to make sure that you comparisons are done in such a way that the index can actually be used. For instance comparing dates using > <= rather than DATEDIFF etc.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Performance of a view compared to a table.