If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Too Many Joins?

Microsoft SQL Server 2000:

I am not a SQL programmer, but I am trying to use data collected in our Job Costing system to feed data to a crystal report that summarizes the current department name and the last date each job was scanned into a department.
I used aliases of the Process table to extract the max date for each department.

This statement works fine, but sometimes it hangs and locks my process table. I am not sure what event causes the lock, but I think it has to do with the users aborting the report during the SQL extract or multiple users trying to report at the same time (8am when they first arrive at work).

I showed this to a consultant and he showed me that the MAX statement in the 3rd to last join was eating most of the execution time. Also he told me I am using too many joins and should look into Table Views. The statement completes in 15 to 45 seconds, depending on the workload.

What can I do to improve the performance of this code and to avoid the locks?

I'd have to know at least a kazillion (thats a million, million gadzillions) more things about your database/server/clients/etc to give you an iron-clad answer. There are just too many variables for a "one size fits all" answer.

SQL Server can tolerate a lot of joins, and it does them pretty well in most cases. A view just "encapsulates" the joins, it doesn't actually get rid of them. I don't think you can improve much here, unless you can completely remove a table from the query

I'd run the query, using the Show Execution Plan option. Look at the output, especially the "fat" lines. Those are often the culprits in this kind of problem.

Each SELECT places a shared lock on a table that is in the FROM list. Crystal is using default (possibly configurable) query timeout value (I think it's 60 seconds, but may be wrong). Once your SELECT exceeds this value, - the client gets Timeout-ed, but Crystal may fail to notify the server with dbcancel-type flag that the result is no longer needed. So the server looks for invalid pipe (file) to write to, while still holding the shared locks. At this point other users are trying to run the same report and get timed out for the same reason. The problem continues until there is enough time for the server to stop looking for/writing to non-existing client and the locks to be released.

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."