Indexing tables...

Hi everybody.
I noticed that most of my users go to P4310 and run querys with fields that are not included oin the primary key. I was wondering about creating a new index with all those fields so the query will take less time to be acomplished. From OMW I can regenerate the index and I been told that creating indexes from the DBMS (SQl server in my case) is not recommended by JDE/PeopleSoft/Oracle/Anything...

My questions ... Have you guys done this without further troubles in operation?

Thanks.

The main reason for not creating indexes outside the toolset is that they will just get blown away if you ever regenerate indexes. I would create the index through the tool set. Its not that hard.

Every time you add an index to a table the performance of your inserts and updates is affected. If you add a bunch of indexes, your searches could be fine but your inserts/updates could be very slow (especially during batches that updates/inserts multiple records).

The recommendation is to analyze the table usage and see what makes sense.

Pearl,

your statement "I was wondering about creating a new index with all those fields" made me think that you were considering creating 1 index with all of the fields that get queried included in it.

If so, don't. Only queries using the first field in the index is likely to benefit in such a case. To really be useful you would need to create an index for each of the most commonly used query fields ... See one of the other poster's response for some of the problems associated with that.

Now, do you really need to do this anyway? How long do user QBEs take in P4310? In our environment a wild-carded query against the description field (the slowest kind of query) takes only a couple of seconds.

If however you really have query performance problems you may need to perform database maintenance instead. (I'd be more specific as to what types of DB maintenance but you neglected to tell us anything about your system).

Another thing to consider is archiving old POs so they no longer clog up the main tables. Unless you have a LOT of rows in F4311 you probably don't need to do this however.

So, please tell us:
a) What your system/version/DBMS, etc are
b) What are the response times users see when executing QBEs
c) Number of rows in your proddta.f4311 table
d) What database maintenance activities are regularly performed on your system.

Hi all and thanks for your replies..

Larry


a) What your system/version/DBMS, etc are

Xe b7333, SQL Server 2000 Stanard Edition running on Windows 2000 Advanced Server.

b) What are the response times users see when executing QBEs

When things are REALLY faster the query take less than 30 seconds, if the server is slow (with another process running) the queries take up to 3 minutes to return thr records.

c) Number of rows in your proddta.f4311 table

469635 records.

d) What database maintenance activities are regularly performed on your system.

Only backup tasks.

Larry would you please give further information about archivng POs?

Thanks a lot for responsing.

The problem with doing it via the tool is lack on control concerning which filegroup your indexes get created in. Putting them with the data files is a drag on performance.

Paul Shearer
ACS