How do you Turbo Charge your Server? (SQL7)

Question

How do you Turbo Charge your Server? (SQL7)

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Markus2006 SSChasing Mays Points: 616 More actions · Answer 1

Francisco,

I'm not sure about the correctness of the query you posted. Take a look at the statement:

Select Distinct A.ccID, Min(A.ccDateTime) as ccDateTime, max(A.StatusID) as StatusID From HAAScc.dbo.tbl_ccStatus A Where A.StatusID = 2 Group by A.ccID

Why do you select here max(A.StatusID)? There is a condition that A.StatusID = 2

so the maximum will be also equal 2.

Later, the StatusIS is not used (from this subquery). Most probably "Distinct" clause is also not needed. I have a feeling that business requirement are not reflected correctly in this part of the stored procedure.

There is also a general problem that I see in the query: you do not limit amount of data you retrieve. You just group all data and then you select what is required. The more data you will have in your tables, the slower query will run. Would it be possible to limit information to let's say two last months?

fhtapia SSC Eights! Points: 994 More actions · Answer 2

Marek,

Thanks for responding, Let's see



Select Distinct A.ccID, Min(A.ccDateTime) as ccDateTime, max(A.StatusID) as StatusID From HAAScc.dbo.tbl_ccStatus A Where A.StatusID = 2 Group by A.ccID

I'm Maxing the StatusID so that I wouldn't get multiple values for that column... instead what I get is for example...

For CCID 1111 the Min of ccDateTime: 1/1/2002, and Max of StatusID would be 0, 2, 5, 6 or 7. I thought of using the max so that I wouldnt declare it in the groupby but it looks like I'm forcing an uneeded calculation since I'm looking for the first instance of 2 in that table for that ccid...

Would rewriting it as



Select Distinct A.ccID, Min(A.ccDateTime) as ccDateTime, A.StatusID From HAAScc.dbo.tbl_ccStatus A Where A.StatusID = 2 Group by A.ccID, A.StatusID

be more efficient?

quote:
Later, the StatusIS is not used (from this subquery). Most probably "Distinct" clause is also not needed. I have a feeling that business requirement are not reflected correctly in this part of the stored procedure.
There is also a general problem that I see in the query: you do not limit amount of data you retrieve. You just group all data and then you select what is required. The more data you will have in your tables, the slower query will run. Would it be possible to limit information to let's say two last months?

I see what you are saying I may need to drop the StatusID completely from the group by... I will try that and see what my results are... I Do limit the amount of data that is retreive if you look at the top portion of the sproc..



(Select B.CCid, B.ccDateTime, A.StatusID From tbl_ccStatus A   INNER JOIN (Select ccID, MAX(ccDateTime) as ccDateTime From tbl_ccStatus Group by ccID) B  ON (A.ccID = B.ccID and A.ccDateTime = B.ccDateTime AND A.StatusID IN (2,5))) IWStatus INNER JOIN   HAAScc.dbo.tbl_Complaints A ON (A.ccID = IWStatus.ccID)) AS IWstatus

This may be too subnested and possibly need rewritting.. but I'm looking for the records in the system that meet the statusID of 2 or 5

I cannot just simply ignore data that is prior (or stale) from beyond that amount of time because there are some (few but there are) records that are over 90 days old... thes are customer complaints that require longer follow up cycles for intermitten problems that require a follow up.....

I will try your suggesting of removing the MAX(A.StatusID) from that statement and report back the query plan at that time along with time performance... I've already applied creating a maxtable that is created at the beginning of the process and has improved response times... I've also improved the GUI portion to only query when a change is made or every hour which ever comes first.. so at the very least they will see new updates on the hour... I added refresh capability ala F5 which is refresh in all other windows applications too. If you think of any more updates I can make to improve this sproc.. I'm all ears as I said I learned all this from books and being on lists so I appreciate any feedback.

-Francisco

Markus2006 SSChasing Mays Points: 616 More actions · Answer 3

Francisco,

This is what I suggested but I'm still afraid that this query does not meet your business requierements. Isn't it that you need to retrieve all records that had status = 2 at least once in the past and now their last status is either 2 or 5 (so the status hasn't change or changed to 5)?

If yes, I think that neither the first nor the last version of the query is correct.

Other possible suggestion is to move remote table to local database and see the performance gain (this was already suggested).

I also assume that according to Antares advise statistics in your database are updated.