Re: Help: Slow Query

mstowski@seas.gwu.edu (Mike Mstowski) write: > I having a problem with slow queries using Online 5.0 and HP/UX G50. > We have a table with 10,000 rows and each row has 300 bytes, with > 18 columns per row. If I do a serial select on one column, I can fetch > all the rows in about 10 seconds. If I select all the columns, > the select takes about 2 minutes. > I don't want to use indexes, because a user can query > on all 18 columns and I would need lots of indexes. > I thought I could use esql to fetch the rows one by one, and do > the pattern match myself, but this is also slow. > > Is there any way I could get this data in say, 10 seconds, allowing a > user to search on any column(s). A HP/UX G50 is supposed to support 200 > users, but with a number not even close to that, this response time is > too slow. > > Thanks in advance! > mstowski@seas.gwu.edu I have a table with >35000 rows and averaging 200 bytes with columns like: desc_id int desc1 varchar(255) desc2 varchar(180) desc3 varchar(75) actors varchar(80) director varchar(50) year_produced int My problem was that in processing 50,000 input rows a day I had to first check if the description was already there and if so then use its desc_id, otherwise I had to add a new one. At first my select statement looked like: select desc_id into did where desc1=$desc1 and desc2=$desc2 and desc3=$desc3 and actors=$actors and director=$director and year_produced=$year This was very slow. The solution which was a hugh improvement was to create the column: hash INTEGER {Also there is an index on this column} All of the descriptive columns are hashed into a 32 bit value which I use to lookup an existing record. Of course you still need to verify if/or which of the one or more rows returned actually match your data. Also be careful with the hash value of 0x80000000, because "where hash=$hash" will not work. Of course all you need to do is use "where hash is null" or just map this value to 0 so you don't have to deal with having two different where clauses. Greater than 99.9% of the time this query will produce exactly what you want and a small fraction of the time you will either get a false or multiple hash hits but these can be eliminated. -- Daniel Wood daniel@starsight.com The opinions expressed herein are those of myself, my employers, the carriers of this news message or any other messages for that matter, you the reader and any of your relations(whether by birth or chromosome count), all life forms and all inanimate objects.