Newly converted SQL database has no indexes

[dgillz]

I just converted a customer from pervasive to SQL and we had horrible performance on any report or inquiry that used order history tables.

In the past, when I have run into performance issues after a conversion, my practice has been to run the mslsyscheck.exe utility to verifiy the database integrity, more specifically to see if the indexes are OK. I did this, and virtually every table is missing its indexes. The MSL directory is there and is properly populated.

I rebuilt a couple of files as a test, and the rebuild worked, and the tables in question fall off of the report generated by mslsyscheck.exe.

Does anyone know what causes this? Do I have to rebuild the entire frickin' database or reconvert? The coversion log shows nothing unusual by the way.

Ideas welcomed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[keain]

If the conversion log doesn't show any errors, I can't say why it happened, but it isn't too hard to correct.

As for rebuilding the indexes, call support. We used to have a script that could be run in Query Analyzer that would (re)create all the indexes. I don't know if they still send it out, but it can't hurt to ask/cajole/threaten.

If you're running the same version, you could generate the script yourself in Enterprise Manager. Unfortunately, I'm on the road and don't have a machine with SQL server on it, so I can't give you the exact steps. (No pun intended). I want to say it's right click on the DB -- all task -- generate index or something similar.

The script would be somewhat version dependant, in that if there's been a table change between a version, a script for the older version would not reflect that table change.

 

[NEmacGuy]

Keain, was right on the target. I have only had the conversion utility not generate indexs one time. The client ended up having duplicate records in a table that was indexed as no duplicated allowed. When you manually tried to index the file that would even fail. In the end had to create a blank table, export the data in btrieve company and import in the sql company. I beleive once one table failed, the entire process dumped.

 

[dgillz]

I have the script for 76200 and betting that the indexes did not change, I used it and it cleared up 98% of my problems. The other 2%, I handled with the export, initialize, import method.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[keain]

It's usually a safe bet that the indexes do not change between most version upgrades.

Here's a little horror story for you, though. I remember one tech thought he was creating the script for 7.6.300 and sent it to the BP. The BP called in about an hour later because he could't get into that company (the live company) after running the script.

He sent the script back in to support and I took a look at it. Rather than dropping and recreating the indices, it was dropping the tables.

 

[macoladan]

Problem is frequently caused by failed indexes in the IMINVTRX file or the JOBFILE. Export both files (using the export function); initialize and import the data. This has fixed the problem where it involves MCPosting or OE issues (anything that may hit the IMINVTRX file)

 

[dgillz]

macoladan,

Sorry, but export, initialize and import of those 2 tables will fix those 2 tables only, not the other 500+ tables.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[kscheeler]

Don,

Does the conversion log from the first workstation show errors at the end where it creates the indexes?

Kevin Scheeler