CR 8.5 Very slow access Pervasice DB

[mvhuia]

HI I AM A NEW USER OF CR

I HAVE A DRILL DOWN REPORT ACCESSING A PERVASIVE DATABASE WITH THE FOLLOWING SELECTION CRITERIA

{TransactionLines.tlGLCode} in [33110, 41060, 41061, 41062] and
{TransactionLines.tlCostCentre} in ["100", "120", "130", "140", "160", "600", "620", "640", "660", "661", "662", "663", "664", "665", "666", "667", "668", "669", "670", "725", "726", "740", "741", "742", "743", "810", "820"] and
{CostCentres.ccSubType} = "C" and
{CostCentres.ccRecType} = "C" and
{TransactionLines.tlRunNo} > 0 and
{TransactionLines.tlYear} = {?Financial Year} and
{TransactionLines.tlPeriod} = {?Financial Period}

THE DATABASE IS FOR EXCHEQUER ENTERPRISE ACCOUNTS V5.51

WHEN I REFRESH THE REPORT CR GOES "NOT RESPONDING" FOR 6 MINUTES BEFORE COMING UP WITH THE REPORT THERE IS APPROX 200K TRANSACTION LINES IN THE DATABASE

HAS ANYONE GOT ANY IDEAS??

 

[vidru]

Go to Database>Show SQL Query to see if your record selection formula is being passed to the server as the WHERE clause.

Check out synapsevampire's faq on this:
Optimizing SQL pass through using the Record Selection Formula (faq766-3826).

-dave

 

[FVTrainer]

Pervasive db's, particularly if they are based on old btrieve files, are very sensitive to the use of indexes. (And if you are using ddf files to access the btrieve files, the ddf's need to be designed to accurately reflect the btrieve indexes.) You can get incredibly fast performance if your report uses the btrieve indexes, but very bad performance if you don't. My experience in the past (I avoid btrieve whenever possible) was that the order in which you join the tables, as well as the order of fields in your WHERE clause is significant.

The best advice I can give is study your database, learn what indexes are available, and make sure that your record selection formula, if it has multiple criteria, filters first on all indexed fields. Only after you have filtered on indexed fields should you filter on non-indexed fields. In other words, if you need to filter on fields A, B, and C, and fields B and C are indexed, but A is not, your record selection would be:

WHERE B = x and C = y and A = z

Also, if you have multiple tables, you might want to try changing the order in which you are linking the tables. Try to make the first table a table that is being used in the record selection formula where the field(s) being used in the selection are indexed fields.

Good luck.

 

[mvhuia]

Thanks for that

The DB is Btrieve and even basic access seems slow could you tell me if this is slow or not.
With only 1 table "Transactionlines" and only using the "TlOurRef" field and no selection criteria it took 32.2 seconds to pull back 494934 records
Because of the following

"Due to the data structures used by Exchequer Enterprise most of the floating point numbers are not directly accessable using DDF's. To overcome this problem we have split the numbers into 2 fields, a 2-Byte Integer and a 4-Byte Integer, these are indicated by a '_1' and a '_2' after a shared field name. We have provided a function EntConvertInts in EntFuncs.Dll to convert these to a standard Double type which can be used in most programs."
I have to add the formula
Global NumberVar DocType := {TransactionLines.tlDocType};
If DocType in [0, 3, 6, 19, 17, 20] Then EntDouble ({TransactionLines.tlNetVal_1},{TransactionLines.tlNetVal_2})*-1
else EntDouble ({TransactionLines.tlNetVal_1},{TransactionLines.tlNetVal_2})

then the time increases to 47.6 seconds does this sound about right ??

 

[FVTrainer]

Well, if you are actually pulling back 494,934 records to the report, then 32 seconds is pretty good performance. Your performance sounds like it is the best it is going to get. You are suffering from a bad database design (from a Crystal perspective, at least).

 

[Smcmanus2001]

Which Version of BTrieve is it? if its called Btrieve its old, and version 6. Because after that they called it Pervasive. Version 6 was better at Native Calls then ODBC, 7+ was better at ODBC. If it is 7+ then you should get the same performance with a SQL Query as you do in Crystal. and that can let you optimise the Databse Selection. I have move some Selection criteria from the Select Side to the Report Side (Suppress on Formula) because some selections made it really slow.