Some best practices involve something as simple as limiting when people can run reports. No matter how robust the hardware is, given enough users, enough load, the hardware and network can be brought to its knees.

With this in mind, I try to do as much munging on the server as it makes sense to do. How much or how little this is depends on your specific circumstances. I try to get as much on the server as I can. Then, I migrate munging to VFP if it is absolutely critical. Sometimes, the VFP language mandates the move since it is far easier to do the munging in VFP as opposed to SQL.

I hope this advice helps.




>Hi, John...
>
>Thanks for responding. I realize that a hundred users creating temp tables would affect performance. I have a follow-up question.
>
>Our app has a reporting piece where a user may report on thousands of accounts and products. The report might be summarized or detailed. The list of accounts/products they select will vary on every report run.
>
>We basically present a MOVER-style form, and allow the user to pick the accounts and items they want. So a user might pick 10 accts and 100 items. Our current 'prototype' (a VFP app) does an SQLEXEC to do a CREATE TABLE #TMPACCT and CREATE TABLE #TMPITEM. We return the result into a VFP cursor, do a little massaging, and then pass that data over to Crystal Reports.
>
>It then scans through the array of accts/items that the user selected in the MOVER form, and does an SQLEXEC INSERT into the temp tables.
>
>Finally, we call a stored proc that queries against the historical data, and the two TMP tables that were created.
>
>This 'works', though I don't know enough about 'best practices' to know if this is a good methodology that will work well with hundreds of users. (My gut tells me there's a better way to do this, but don't know what it would be.) That's pretty much the challenge I have now...I have a basic understanding of how to use the technology, but not enough of how to use it effectively.
>
>Any feedback you can give would be great.
>
>Thanks,
>Kevin