Record Select

[gaus]

Hi,
We have Crystal ver 7.0, and have an issue with a query a vendor wrote in the report, but in order to fix it they want $$$ (big surprise). They have told me when I remove one of the Group Bys, in order to avoid running out of TEMP space, to add a '"Record Select" clause to the query stating 1=0". Does anybody know what that means? I have never heard of such a thing.
Thanks,
Gary

 

[Naith]

1=0 is a numerical way of saying the boolean 'False' - that is to say, less (if it's part of a subclause) or no rows will be returned.

Post your query up and stipulate the specifics of what problems you're having with it.

Naith

 

[gaus]

Ok, here is the query. My apologies - it is the removal of some of the 'Order Bys' that causes us to run out of TEMP space. Full table scans then occur, even though I added the required fields as indexes to the DB (worked with the DBA). They call this Crystal from a VB program which has several selection criteria controls (mostly drop-down list boxes), in which many items can be entered to narrow the selection. I have no idea of what additionally is taking place within the VB rpogram, but my guess is they are 'undoing' the tuning I am attempting to perform. How and where would I incorporate the suggested Record Select clause?

SELECT
DEDUCTIONS."DEDID", DEDUCTIONS."DEDDATE", DEDUCTIONS."DEDREFERENCE", DEDUCTIONS."DEDPRODUCTCODE", DEDUCTIONS."DEDDATERESOLVED",
CUSTOMERS."CUSTNUMBER", CUSTOMERS."CUSTOMERNAME", CUSTOMERS."CUSTADDRESS1", CUSTOMERS."CUSTCITY", CUSTOMERS."CUSTSTATE", CUSTOMERS."CUSTZIP",
DEDSETTLEITEM."DSISETTLETYPE", DEDSETTLEITEM."DSIAPPLYAMOUNT", DEDSETTLEITEM."DSIREASONCODE",
ACTIONPROFILES."ACTDEDUCTTYPE", ACTIONPROFILES."ACTDEDUCTREASON",
BUSINESSDISTRICTS."SDCDESCRIPTION",
BUSINESSREGIONS."SRCDESCRIPTION",
BUSINESSTERRITORIES."STCDESCRIPTION",
BUSINESS."BUSINESSNAME"
FROM
"DEDUCTIONS" DEDUCTIONS,
"CUSTOMERS" CUSTOMERS,
"DEDSETTLEITEM" DEDSETTLEITEM,
"ACTIONPROFILES" ACTIONPROFILES,
"BUSINESSREGIONS" BUSINESSREGIONS,
"BUSINESSDISTRICTS" BUSINESSDISTRICTS,
"BUSINESSTERRITORIES" BUSINESSTERRITORIES,
"BUSINESS" BUSINESS
WHERE
DEDUCTIONS."DEDCUSTCOUNT" = CUSTOMERS."CUSTCOUNT" AND
DEDUCTIONS."DEDCOUNT" = DEDSETTLEITEM."DSIDEDCOUNT" AND
DEDUCTIONS."DEDTYPE" = ACTIONPROFILES."ACTIONCOUNT" AND
DEDUCTIONS."DEDSALESDISTRICT" = BUSINESSDISTRICTS."SALESDISTRICTCOUNT" AND
DEDUCTIONS."DEDSALESREGION" = BUSINESSREGIONS."SALESREGIONCOUNT" AND
DEDUCTIONS."DEDSALESTERR" = BUSINESSTERRITORIES."SALESTERRITORYCOUNT" AND
CUSTOMERS."CUSTBUSID" = BUSINESS."BUSINESSID" AND
DEDUCTIONS."DEDUSERSTATUS"=260
ORDER BY
BUSINESS."BUSINESSNAME" ASC,
BUSINESSREGIONS."SRCDESCRIPTION" ASC,
BUSINESSTERRITORIES."STCDESCRIPTION" ASC,
BUSINESSDISTRICTS."SDCDESCRIPTION" ASC,
CUSTOMERS."CUSTNUMBER" ASC


Thanks very much,
Gary

 

[Naith]

I would need to see your Record Selection criteria too, as I can't see the relevance of 1=0 just by looking at the sql.

However, with reference to the sql, when you say that you created indices, did you create multi-key indexes (comprised of as many fields in the select as possible per table), or single key indexes?

Naith

 

[gaus]

Hi,
I'll get the record selection info for you. I created single field indices - those that were in the WHERE clause (the associations). This is basically because the association between the tables is 1 column to 1 column. E.G - BUSINESSDISTRICTS."SALESDISTRICTCOUNT" is the primary key for the BUSINESSDISTRICTS table (a number incremented via a Before insert trigger with sequence), and the DEDUCTIONS."DEDSALESREGION" is just a column in the DEDUCTIONS table. I created an index for DEDUCTIONS."DEDSALESREGION".
Thanks,
Gary

 

[Naith]

You don't need to create single key indices on primary keys. But if your dba is willing to let you try implementing some indices to troubleshoot the performance of this query, I would create the following:

Index1: DEDUCTIONS."DEDID", DEDUCTIONS."DEDDATE", DEDUCTIONS."DEDREFERENCE", DEDUCTIONS."DEDPRODUCTCODE", DEDUCTIONS."DEDDATERESOLVED"

Index2: CUSTOMERS."CUSTNUMBER", CUSTOMERS."CUSTOMERNAME", CUSTOMERS."CUSTADDRESS1", CUSTOMERS."CUSTCITY", CUSTOMERS."CUSTSTATE", CUSTOMERS."CUSTZIP"

Index3: DEDSETTLEITEM."DSISETTLETYPE", DEDSETTLEITEM."DSIAPPLYAMOUNT", DEDSETTLEITEM."DSIREASONCODE"

If the Action Profiles and Business tables are pretty small in comparison, I wouldn't worry about indexing those - but use your judgement.

Also, suggest to your dba that you switch between optimisers and see what the impact is. (If you're on Oracle, you're probably defaulting to cos based optimisation.)

Naith

 

[gaus]

Well you're obviously much more knowledgeable in this area than I. I wasn't even aware you could switch optimisers, nor did the DBA offer. Indeed, the other 2 tables you mentioned are much smaller and would not need indexing. I'll give this a try. Thanks!!!
Gary

 

[synapsevampire]

Perhaps Naith is using an old version of Oracle, but I doubt that the indexes are much of a problem.

More likely the problem is the result of a poorly constructed record selection criteria (it isn't passing the criteria in the pass through SQL to the database), so the net effect is that you're pulling in far too many rows and processing them within CR (the temp space issue is the same problem, an index won't change that much).

Trap the SQL being passed from the Crystal Report and examine it (you can turn on ODBC tracing or ask your DBA to trap it).

The best way to construct a Record Selection criteria I've found thus far is (replace the <text>):

(
{MyTable.MyDate} <= <some static date>
and
{MyTable.MyDate} >= <some static date>
)

and

(
{MyTable.MyID} = <some static number.
)

and

(
if {MyTable.MyText} <> &quot;&quot; then
{MyTable.MyText} in {?MyTextParm}
else
if {MyTable.MyText} = &quot;&quot; then
true
)


I gave 3 examples, the latter using a parameter and disqualifying the data, and note the parens used throughout. This has become the standard suggestion here for constructing a Record Selection criteria as it nets the most consistent results.

BTW, If you have single bitmap indexes in Oracle you'll typically get good performance in CR, compound indexes don't lend themselves to flexibility (fields added/omitted, compound indexes are great for OLTP applications, but aren't ideal for flexible querying).

Also, creating an index for all of the fields in the select isn't as crucial as adding the fields that are in the joins, group, order and the where clause.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

I have Oracle 8i and 9i. However, the version of Oracle is irrelevant - Temp tablespace does the same thing in all versions of Oracle; that is - store and process temporary objects.

It's obvious to see what's passing to the database, because the SQL has already been posted. If it's the removals of the ORDER BYs which is creating the performance issue, then it's certainly an Oracle issue you have - and not, as SynapseVampire is telling you, a record selection issue. Crystal does not pass ordering to the database.

As an Oracle DBA myself, I would support the claim about single key indices is true for flexibility and the like, but for purposes of troubleshooting this query, I would still create a multi key index. Once you ascertain whether the performance is affected by their creation, the potential problem area will be narrowed down for your DBA to scrutinise.

Indexing is a very important place to start troubleshooting, if as you say, your query is doing full table scans, as it's almost 100% guaranteed that the fact that you have full table scans isn't doing your TEMP tablespace usage any favours (especially if your tables are large). I honestly have absolutely no idea why SynapseVampire told you that indices aren't worth checking out in the situation you're experiencing.

Additionally, get your DBA to do an explain plan on the query with and without the ORDER BY clause. There's a possibility that taking the ORDER BY out is making Oracle decide to use a much less efficient optimisation method in the absense of being hinted otherwise in the query.

You might want to bring this to the Oracle forum, because I don't think this is a Crystal issue. There may be numerous reasons for what you're experiencing, but the fix for any one of them in Oracle is usually not a difficult operation.

Naith

 

[Naith]

Apologies for the typo: &quot;Crystal does not pass ordering to the database.&quot; Crystal does, of course, pass ordering to the database.

The point made should instead have been that ordering forcing your query to do full-table-scans makes your issue related to that of Oracle processing.

Naith

 

[gaus]

Thanks to both of you. I have passed this information on to the DBA group. I will work with them to get this resolved. I may take this to OraFans and see what turns up. Thanks very much!
Gary

 

[gaus]

I found no record selection within the Crystal Select Expert. How would one incorporate the 1=0 in the selection within Crystal or within the query as the vendor recommends?
Thanks,
Gary

 

[synapsevampire]

You may be right, Naith, but I'm cautiously skeptical.

&quot;I honestly have absolutely no idea why SynapseVampire told you that indices aren't worth checking out in the situation you're experiencing.&quot;

The issue is Group Bys being removed I think, not order bys, removing an order by shouldn't degraqde performance...

Gaus referenced that it was Group Bys in one area, and Order Bys in another, so I'm going by what makes sense, not the mixed signals.

I hadn't realized that you were an Oracle dba, I'm a PL/SQL (and T-SQL) programmer, though I might function as a primitive DBA on occasion, my skills and interests are geared towards SQL, specifically optimizing.

I agree that an index may help with temp space problems, but I think that the bigger picture is that they aren't passing the SQL correctly. Note that they aren't passing the Group By's (which was the issue I think), just the ordering, and only one field has a criteria on it in the SQL, though they apparently have several prompts in the VB app. <light bulb coming on?>

Gaus: See if you can get the DBA's to build a Stored Procedure for you, that will yield the best performance, and they should optimize it. Otherwise, make sure that you turn on the perform grouping on server and use indexes for speed in Crystal.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

Some time the query just isn't doing what you think it is. The addition and/or removal of single fields/order bys/group bys etc can cause huge changes in the query plans.

I am currently dealing with a number of situations dealing with this sort of thing myself.

I would sit down and run the querys outside of crystal, generating a SQL plan for each, changing one single item at a time. I.e. if you want to remove a Group by, remove the group by only, then for the next iteration remove the output field. Don't worry so much about getting &quot;correct&quot; data. I usually try to limit my output to 100-1000 lines. Its enough so that I can see any significant speed changes for &quot;by eye&quot; speed comparisons, but not so many records that I have to wait an hour to get my data if its a bad run. If that is too many records speed wise.. knock it down farther.

If that fails to point things out, start from the other direction and start with one table, one field etc.

It can get tedious either way. If you have a DBA who can read and understand query plans it would help to have them work closely with you if you can.

Oh, as for the &quot;add a 0=1&quot; to the select statment from your (hopefully ex) consultants.. it is probably somebodies idea of a bad joke. It will, as previously noted, immediately cause your report to stop returning any rows. Someone who isn't familiar with SQL (ie in their eyes someone who should be paying them instead of trying themselves) wouldn't recognize that very quickly.... like I said.. a bad joke

Lisa

 

[gaus]

Thanks to everyone for all of the ideas. I did clarify in my second post that it was an 'Order By' as opposed to a 'Group By' (sorry for the confusion). However, would that not also affect performance (or space, which is the main problem)? In order to 'Order By', would a grouping not need to take place as well? How can it order until all items of the same data have been identified and placed together? I just changed the Crystal Report group to keep all groups together to see what happens. I am also grouping on the server. But I noticed Crystal ties the grouping and ordering directly together.

 

[lyanch]

Crystal ties lots of things together that don't necessarily need to be. As you may have noticed, Crystal will add a &quot;where&quot; field (not joins) to the &quot;select&quot; field as well, even when it sends the &quot;where&quot; to the server correctly.

Thats one of the reasons that I like to optimize &quot;problem children&quot; outside of crystal. Once I have it running correctly then I recreate it in crystal. If crystals &quot;additions&quot; cause the optimization to fail, then I know I need to go to a stored proc for that report.

All the &quot;perform grouping on server&quot;, &quot;select distinct&quot; etc can just as easily be turned on or off in the SQL itself.

Lisa

 

[synapsevampire]

No, you don't need to Group By to Order By.

A group does what it sounds like, for instance if you want to return the number of Orders per State, you would select State, count(Ordernumbers) from MyTable Group By State and possibly add an Order By State, although most databases will automatically Order By State, some will not (Timesten is an example).

If you wanted a list of OrderNumbers by State, you would select state, ordernumber from MyTable order by state, ordernumber

Hope this helps to clarify.

Again, request the DBA's to build an SP, it will ALWAYS give the best performance, and they will (hopefully) optimize it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com