Picking Sheet slow to print after adding a UDF table from AR

[tpsiks]

Hi,

I need a little guidance on this issue that I could not figure out any other way.

I added a UDF field in Customer Maintenance called "Drop Ship", it's a check box. We use it for any customer that is on Drop Ship.

In Picking Sheet Printing (report: sowfba1.rpt)I added the table AR_90_UDF_ARCUSTOMER, which contains my Dropship I needed. I have to suppress certain fields in the report when a customer has "Drop Ship" checked (the table value is: "Y").

Here is the problem. It works, but it's slow, very slow. I've double check my linking (there's one way to link it anyways, by customer name). Sometime it takes 15 minutes or more to print a picking sheet. It compares the current customernumber of the sales order that was selected against the customernumber of the AR table for a match, which contains thousands of numbers. It takes couple seconds to print a normal picking sheet because it has all the data it needs to query after I select the sales order number, but with my added table, it has to compare with the big table to find a match.

Does anyone has a better way of doing this? My picking sheet for a Drop Ship customer cannot contain our company logo, name, address, Sold To has to say FROM, and many other fields that I have to suppress. I could have made a separate report for this but our Sales people do not know which customer is on Drop Ship to use the other report.

Thanks,

 

[dfeller]

You shouldn't be linking by customer name, you should link by division number and customer number.

Dawn

 

[tpsiks]

Opps Sorry,

I meant customer number.
AR_90_UDF_ARCUSTOMER does not have customer name anyways.
But I did not link it by division, that will probably cut down the query time by 50%.

Thanks for your response.

I will post the result.

 

[AndyMc]

tpsiks,

If you still find that it is too slow, I have had very good luck (speed-wise) adding fields from other tables on to forms with subreports. Subreports have the advantage of acting like a left-outer join, so if the UDF value does not exist for a customer - no problem.

Andy

 

[tpsiks]

Andy,

That's a very good sugestion. I use a lot of subreports on other reports, but I've tried it in this report, same result, because it's still looking at all the customer number in my database for a match(we have a lot). But I've made another link with the Division number, I don't know why I didn't do that in the first place. That speed it up a lot, because we have 6 divisions. So it first query the 6 divisions(doesn't take to query 6) and now it has 1/6 of the database to query for the same customer number.

So this is my fix for it. I've tested it and worked great for me, but I still have to wait for the sales people to use it for a week to see if it will actual work.

Thanks for all your help!

 

[dfeller]

Also check to make sure you have "use indexes or server for speed" turned OFF.

Dawn

 

[tpsiks]

WOW I would never know that having "indexes/server for speed" uncheck would speed it up. I always thought having it checked would do good. Do you leave this off for all your reports? or do you leave it off for certain applications? All of my reports seem to run pretty good with it ON, except this one that I missed an extra data field (duh).

I will try your suggestion.

Thanks for pointing that out.

 

[dfeller]

I usually turn it off.

Dawn