The downloadable version of this article is available here:
http://techrepublic.com.com/5138-3513-6032029.html
Are you ever really happy with the performance of your queries or are you merely content that it?s the best you can do? Is there another aspect of PostgreSQL you would like to see covered in a TechRepublic download?
Discussion on:
View:
Show:
missing here.
Performance !
Be very careful about selecting from views in particular unless you are extremely familiar with optimistation never join them. Server performance will drop like a stone in short order.
Watched Oracle 8i on a solaris box take 52 minutes to produce 50 records through selecting through three joined views. Busting the code out of the views and writing the requisite query took 15 minutes, and less than a second to execute.
File save as, took care of not having to write it again.
So be careful out there.
Performance !
Be very careful about selecting from views in particular unless you are extremely familiar with optimistation never join them. Server performance will drop like a stone in short order.
Watched Oracle 8i on a solaris box take 52 minutes to produce 50 records through selecting through three joined views. Busting the code out of the views and writing the requisite query took 15 minutes, and less than a second to execute.
File save as, took care of not having to write it again.
So be careful out there.
that. any query can be slow, if not coded right. combining 2 or more "views" is creating a new query, that will most likely not be coded right for the data set you are trying to get.
I would serously concider locking the views so they cannot be combined to avoid killing the performance.
make them come to me for a custom query to get the result set they want, so performance is still where it should be on a production server.
I would serously concider locking the views so they cannot be combined to avoid killing the performance.
make them come to me for a custom query to get the result set they want, so performance is still where it should be on a production server.
dangerous.
Oh you can treat them just like tables. Well true unoptimised, unrelated, unkeyed, unnormalised badly designed tables.
Another thing if you are going to use a view inside a stored procedure, check to see whether the sp will automatically be re-parsed if the view changes. Ran into that one in SQL Server 6.5. Changed the view, but it had pasted in the query that drove it so the sp was still working on the old design. Went mental 'til I figured that out, think that was the last time I used one as well.
Oh you can treat them just like tables. Well true unoptimised, unrelated, unkeyed, unnormalised badly designed tables.
Another thing if you are going to use a view inside a stored procedure, check to see whether the sp will automatically be re-parsed if the view changes. Ran into that one in SQL Server 6.5. Changed the view, but it had pasted in the query that drove it so the sp was still working on the old design. Went mental 'til I figured that out, think that was the last time I used one as well.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
