Re: Sysindexes - column defs

goldberg@sccsi.com (Steve Goldberg) wrote: :In article <500q6g$k82@nic.global-one.no>, Nils.Myklebust@ccmail.telemax.no (Nils Myklebust) says: :> :> :>When you ask like this there are too many variabls left for anyone to :>guess. :>I wouldn't be too much conserned about the meaning of the sysindexes :>columns. They sound like they have something to do with the size of :>the B-tree and whether the index is clustered and/or unique. :> :>As to your speed problem. Same search using the same index i take as :>meaning you have checked your sqlexplain.out file. (If not you can't :>have any idea at all what index(s), if any, is used.) :Yes, I have checked the sqexplain for both databases. They are exactly alike except :for the number of estimated rows returned and cost. The index chosen has :"street" as the first field. :>Do you select one or many rows? What is the timing difference? Have :>you dropped and recreated the indexes? Have you checked number of :>extents and fragmentation of them? Have you done anything about it? :>(Unload/load or alter index to cluster.) What else do you see? :The slow database is 1 min, fast database 7 secs. Both dbase has been :recently defrag'd so there is only 1 extent, they do not grow. Update :statistics has been run. Some differences are (from memory): : slow dbase fast dbase :time for search 1 min 7 sec :total size 560,000+ 190,000+ :num unique keys 2400 5400 (street name) :num rows selected 240 140 Even 7 seconds isn't particularly fast. (May be it's a slow machine.) But 1 minute we have only seen when some tuning is very badly done. I don't know much about OnLine 5, so you'll have to check your tuning manuals. Check everything about OnLine tuning and the relationship to the OS and tuning of that. When you have post your setup and the sqexplain output and may be someone else can help you. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company