I am looking for some advice on how to speed up response time when creating/using views. I have a view that has many millions of rows which is joined to a smaller table with, say, 12 million rows. A user is using this view by joining the view itself to a couple of small lookup tables, which are themselves views. The main table has aproximately 60 million records added to it a month, and is fragmented by date. We have noted a sudden large increase in the response time, so much that the view is now really unusable. The view also bombs out due to lack of tempspace. Any tips would be appreciated. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Performance of Informix views
- Thread starter LQS
- Start date
- Thread starter
- #3
Yes we have set explain on and updated statistics. It seems that the view is being completely materialized before it is being joined to the other lookup table. Therefore longer return times and the filling up of temporary disk space. I am not sure there is any way around this... Any thoughts ?
Is your temp space limited because of physical disk limitations?
Have you looked into using the fragment option(I think this is it) where when you create the table you place data with similar keys into its own dbspace. Ie say the key is a phone number, the area codes for 200 - 299 go to one, 300-399 to another etc.
Where is this select being processed? on the db machine or thru a net connection? Is it a program?
Look into the environment variable for Temp Space. You could maybe direct it to another disk or flat file. The system default is the temp dbspace. You may get some performance here.
Is it possible that this is a cartesian Join? So when it did work, was the data correct?
Have you looked into using the fragment option(I think this is it) where when you create the table you place data with similar keys into its own dbspace. Ie say the key is a phone number, the area codes for 200 - 299 go to one, 300-399 to another etc.
Where is this select being processed? on the db machine or thru a net connection? Is it a program?
Look into the environment variable for Temp Space. You could maybe direct it to another disk or flat file. The system default is the temp dbspace. You may get some performance here.
Is it possible that this is a cartesian Join? So when it did work, was the data correct?
- Thread starter
- #5
"Is your temp space limited because of physical disk limitations?" Yes I believe we are reaching capacity. But the point is that if the view wasn't materialized before the join to the other table, so much temp space wouldn't be needed.
"Have you looked into using the fragment option?" Yes. the data is fragmented by yyyy-mm, as this is a historical table.
"Where is this select being processed?" The query is processed on the DB machine, running interactively using DBAccess. Same problem when run in batch too.
"Is it possible that this is a cartesian Join?" No the query is not a cartesian join.
"So when it did work, was the data correct?" Yes it was. It seemed that the response tanked afer we reached an uknown "limit" to the number of records where this would run efficiently.
Thanks for your responses. They are very helpful for me in thinking this through.
"Have you looked into using the fragment option?" Yes. the data is fragmented by yyyy-mm, as this is a historical table.
"Where is this select being processed?" The query is processed on the DB machine, running interactively using DBAccess. Same problem when run in batch too.
"Is it possible that this is a cartesian Join?" No the query is not a cartesian join.
"So when it did work, was the data correct?" Yes it was. It seemed that the response tanked afer we reached an uknown "limit" to the number of records where this would run efficiently.
Thanks for your responses. They are very helpful for me in thinking this through.
- Status
Similar threads
- Locked
- Question