WARNING: The Geek Content of this post has been rated EXTREMELY HIGH...please avoid this post unless you're into programming and databases.
In the "who knew? probably a lot of people except me" category....
Being a programmer with some basic database knowledge, but not database-proficient by any stretch, I am often surprised by the inner workings of databases. Today was one of those days. I knew that in addition to having database tables, you can have "views", which are logical views over data. I think in my mind, a database view was storing pointers to data. Well, it turns out that it's pretty much just a stored query or a "macros" that's executed afresh whenever you query the view.
Now here comes the really surprising thing. I was querying a particularly slow view...it has a lot of business logic built into it. It's also against a huge table. So my query:
SELECT * FROM MY_VIEW WHERE MY_FOREIGN_KEY = 42was taking about 5 minutes. I decided to try and speed up the query by limiting the number of rows that were being returned, just in case it was the data transfer or creation of the row set that was taking so long:
SELECT * FROM MY_VIEW WHERE MY_FOREIGN_KEY = 42 and rownum <= 100This did speed things up, but not very much: It took about 3 minutes. So, I decided to try an inner query: Just chop off the first 100 rows of the view and then query against them thusly:
SELECT * FROM (SELECT * FROM MY_VIEW WHERE rownum <= 100) WHERE MY_FOREIGN_KEY = 42I expected this query to execute more quickly. Instead, it took FOREVER. (I stopped counting at an hour.)
This leads me to believe that in the first and second cases, my WHERE clause criteria was just getting added on to the where clause of the view's query, but in the third case, the entire view was being created in some temp space, then the first 100 rows were taken.
Definitely not what I expected.
I'm going to try to get this view converted to a Materialized View, which is physically stored and then updated as the "underlying" tables change.
| JiffNotes |
|---|
| Blah blah blah database blah blah blah. |
3 comments:
I should've paid attention to the warning. :/
Ya need an index on "my foreign key" column or reduce the number of columns that you're bringing back (select * brings back all of them)
Why yes, I understood everything you wrote. I build big databases for a living :)
I'm a software engineer too. I have the same situation as you.. use SQL, but hardly a 'deep database' type. I always expect the SQL drivers to be much smarter at optimizing my queries. And it all depends on the database you're using as to what's fastest just to confuse the issue even more.
Post a Comment