How do you Turbo Charge your Server? (SQL7)
-
Well, I did get a chance to look at alternate ways of getting the same type of results with the query you gave and found no improvement that I can readily see. Also looking at the change I suggested you may want to switch back, first time I have ever seen a change like that cause it to swap to table scans from clustered index. Also, if he doesn't do much with the database in you were talking about have him at least try
Rebuild indexes
Run sp_update stats
Run DBCC UPDATEUSAGE
as his may not be on the mark anymore depending on when these things last occurred.
Finaly thought, at least until something else hits me is you might see a minor improvement by changing these lines from
DateDiff (dd, Case
When NotesStatus.NotesEntryTime is NULL Then IWstatus.ccDateTime
Else NotesStatus.NotesEntryTime
END, @CurrDate) InActiveDays,CONVERT(CHAR(12), AssignedStatus.ccDateTime,110) as AssignedON,
Case
When NotesStatus.NotesEntryTime is NULL Then CONVERT(CHAR(12), IWstatus.ccDateTime, 110)
Else CONVERT(CHAR(12),NotesStatus.NotesEntryTime, 110)
END as LastActionOn
to
DateDiff (dd, ISNULL(NotesStatus.NotesEntryTime, IWstatus.ccDateTime), @CurrDate) InActiveDays,
CONVERT(CHAR(12), AssignedStatus.ccDateTime,110) as AssignedON,
CONVERT(CHAR(12), ISNULL(NotesStatus.NotesEntryTime, IWstatus.ccDateTime), 110) as LastActionOn
The reasoning is the case when x IS NULL then y ELSE x END
means that x is processed to be evaluated then processed for display if needed.
ISNULL(x,y) will give the same results but x i processed and evaluted for null which if not then the data from the processing is output and not reprocessed again.
Also if you note I do the convert on the outside of the evaulation in the secound time, this saves code typing is all in the case of a case statement but in the isnull funtion it saves processing as the evaluation takes place first then the output from that is converted.
If anything else pops into my head or all of a sudden I see a change that may improve things I will pass along.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I notice table scans and bookmarked lookups in your execution plan. Try to take care of these, and you'll definately see improvement. Table scans are obvious, and you can eliminate bookmarked lookups with covered indexes.
Edited by - Scorpion_66 on 10/18/2002 07:17:07 AM
-
OK, new plan, I went into all the CCID columns for cc_Status cc_Notes cc_Assigned and made them clustered.. it seems as if it took and this is my plan now...
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([].[ContactName] ASC, [CONTACT1].[Company] ASC, [Expr1027] ASC))
|--Compute Scalar(DEFINE:([Expr1026]=datediff(4, [Expr1004], [@CurrDate]), [Expr1027]=datediff(4, [Expr1008], [@CurrDate]), [Expr1028]=datediff(4, If ([Expr1011]=NULL) then [Expr1002] else [Expr1011], [@CurrDate]), [Expr1029]=Convert([Expr1008]
|--Hash Match(Left Outer Join, HASH:([tbl_ccStatus].[ccID], [].[ContactID])=([tbl_ccNotes].[ccID], [].[ContactID]), RESIDUAL:([tbl_ccNotes].[ccID]=[tbl_ccAssigned].[ccID] AND [].[ContactID]=[].[ContactID]))
|--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join)
| | |--Nested Loops(Inner Join)
| | | |--Sort(ORDER BY:([A].[ccID] ASC, [A].[AssignedID] ASC))
| | | | |--Nested Loops(Inner Join)
| | | | |--Merge Join(Inner Join, MERGE:([tbl_ccAssigned].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([tbl_Complaints].[ccID]=[tbl_ccAssigned].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([tbl_ccAssigned].[ccID]) DEFINE:([Expr1008]=MAX([tbl_ccAssigned].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid]), ORDERED)
| | | | | |--Merge Join(Inner Join, MERGE:([tbl_ccStatus].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([tbl_Complaints].[ccID]=[tbl_ccStatus].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([tbl_ccStatus].[ccID]) DEFINE:([Expr1002]=MAX([tbl_ccStatus].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID]), ORDERED)
| | | | | |--Merge Join(Inner Join, MERGE:([A].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([A].[ccID]=[tbl_Complaints].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([A].[ccID]) DEFINE:([Expr1004]=MIN([A].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), WHERE:([A].[StatusID]=2) ORDERED)
| | | | | |--Sort(DISTINCT ORDER BY:([tbl_Complaints].[ccID] ASC, [CONTACT1].[Company] ASC, [CONTHIST].[ONDATE] ASC, [CONTHIST].[OnTime] ASC))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1024]), OBJECT:([TeleMarket].[dbo].[CONTACT1]))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1022]), OBJECT:([TeleMarket].[dbo].[CONTHIST]))
| | | | | | |--Nested Loops(Inner Join)
| | | | | | |--Index Scan(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints]))
| | | | | | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTHIST].[CNHRECID]), SEEK:([CONTHIST].[RECID]=[tbl_Complaints].[RECID]) ORDERED)
| | | | | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTACT1].[CONTACC]), SEEK:([CONTACT1].[AccountNo]=[CONTHIST].[AccountNo]) ORDERED)
| | | | |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid] AS [A]), SEEK:([A].[ccID]=[tbl_Complaints].[ccID]), WHERE:([A].[ccDateTime]=[Expr1008]) ORDERED)
| | | |--Filter(WHERE:([A].[StatusID]=5 OR [A].[StatusID]=2))
| | | |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), SEEK:([A].[ccID]=[tbl_ccStatus].[ccID]), WHERE:([A].[ccDateTime]=[Expr1002]) ORDERED)
| | |--Index Seek(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints] AS [A]), SEEK:([A].[ccID]=[tbl_ccStatus].[ccID]) ORDERED)
| |--Index Spool(SEEK:([].[ContactID]=[A].[AssignedID]))
| |--Remote Scan(SELECT ContactID, ContactName FROM DEV_REDe.dbo.tbl_Contacts)
|--Hash Match(Inner Join, HASH:([A].[UserID])=([].[ContactID]), RESIDUAL:([].[ContactID]=[A].[UserID]))
|--Hash Match Root(Inner Join, HASH:([tbl_ccNotes].[ccID])=([A].[ccID]), RESIDUAL:([Expr1011]=[A].[ccDateTime]))
| |--Hash Match Team(Aggregate, HASH:([tbl_ccNotes].[ccID]) DEFINE:([Expr1011]=MAX([tbl_ccNotes].[ccDateTime])))
| | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID]))
| |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID] AS [A]))
|--Remote Scan(SELECT ContactID, ContactName FROM DEV_REDe.dbo.tbl_Contacts)
-Francisco
-Francisco -
What effect did it have on your performance?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Tuning such stored procedures is what I do quite often for my customers. Looking at the stored procedure and its execution plan, I would do the following:
1. run sp_updatestats (already suggested by Antares)
2. change the second select statement
from:
SELECT IWStatus.CCID, A.RecID, IWStatus.ccDateTime, IWStatus.StatusID FROM
(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)
WHERE A.StatusID IN (2,5))
to:
SELECT IWStatus.CCID, A.RecID, IWStatus.ccDateTime, IWStatus.StatusID FROM
(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))
--the difference is in the last line
3. As a test, move the tbl_Contacts to your local server and use it not by means of a view but as a normal table. I suspect that this will help you the most - there is a remote scan in the execution plan and there is a condition on one of columns from remote server (furthere more it's a view and not a table).
BTW, does tbl_Contacts contains many records?
Let me know if this helped so I could give you some advise what to do with this remote view.
Regards,
Marek
-
Thanks for your indept reply
I made the initial changes and in process of moving the table.. (later today) The table only contains 245 rows. How does this plan look? btw, I'm also curios at what you guys read out of these.. I know cost and time is a factor but I have a hard time determening my next step in reading these... obviously bigger %'s make me look at that section.. but what do you guys do?-Francisco
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([].[ContactName] ASC, [CONTACT1].[Company] ASC, [Expr1027] ASC))
|--Compute Scalar(DEFINE:([Expr1026]=datediff(4, [Expr1004], [@CurrDate]), [Expr1027]=datediff(4, [Expr1008], [@CurrDate]), [Expr1028]=datediff(4, If ([Expr1011]=NULL) then [Expr1002] else [Expr1011], [@CurrDate]), [Expr1029]=Convert([Expr1008]
|--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([tbl_ccStatus].[ccID], [].[ContactID])=([tbl_ccNotes].[ccID], [].[ContactID]), RESIDUAL:([tbl_ccNotes].[ccID]=[tbl_ccStatus].[ccID] AND [].[ContactID]=[].[ContactID]))
|--Nested Loops(Inner Join)
| |--Sort(ORDER BY:([tbl_ccStatus].[ccID] ASC, [A].[AssignedID] ASC))
| | |--Hash Match(Inner Join, HASH:([A].[AssignedID])=([].[ContactID]), RESIDUAL:([A].[AssignedID]=[].[ContactID]))
| | |--Nested Loops(Inner Join)
| | | |--Nested Loops(Inner Join)
| | | | |--Merge Join(Inner Join, MERGE:([tbl_ccStatus].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([tbl_Complaints].[ccID]=[tbl_ccStatus].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([tbl_ccStatus].[ccID]) DEFINE:([Expr1002]=MAX([tbl_ccStatus].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID]), ORDERED)
| | | | | |--Merge Join(Inner Join, MERGE:([tbl_ccAssigned].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([tbl_ccAssigned].[ccID]=[tbl_Complaints].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([tbl_ccAssigned].[ccID]) DEFINE:([Expr1008]=MAX([tbl_ccAssigned].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid]), ORDERED)
| | | | | |--Merge Join(Inner Join, MERGE:([A].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([A].[ccID]=[tbl_Complaints].[ccID]))
| | | | | |--Stream Aggregate(GROUP BY:([A].[ccID]) DEFINE:([Expr1004]=MIN([A].[ccDateTime])))
| | | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), WHERE:([A].[StatusID]=2) ORDERED)
| | | | | |--Sort(DISTINCT ORDER BY:([tbl_Complaints].[ccID] ASC, [CONTACT1].[Company] ASC, [CONTHIST].[ONDATE] ASC, [CONTHIST].[OnTime] ASC))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1024]), OBJECT:([TeleMarket].[dbo].[CONTACT1]))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1022]), OBJECT:([TeleMarket].[dbo].[CONTHIST]))
| | | | | | |--Nested Loops(Inner Join)
| | | | | | |--Index Scan(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints]))
| | | | | | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTHIST].[CNHRECID]), SEEK:([CONTHIST].[RECID]=[tbl_Complaints].[RECID]) ORDERED)
| | | | | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTACT1].[CONTACC]), SEEK:([CONTACT1].[AccountNo]=[CONTHIST].[AccountNo]) ORDERED)
| | | | |--Index Seek(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints] AS [A]), SEEK:([A].[ccID]=[tbl_Complaints].[ccID]) ORDERED)
| | | |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid] AS [A]), SEEK:([A].[ccID]=[tbl_Complaints].[ccID]), WHERE:([A].[ccDateTime]=[Expr1008]) ORDERED)
| | |--Remote Scan(SELECT ContactID, ContactName FROM DEV_REDe.dbo.tbl_Contacts)
| |--Filter(WHERE:([A].[StatusID]=5 OR [A].[StatusID]=2))
| |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), SEEK:([A].[ccID]=[tbl_ccStatus].[ccID]), WHERE:([A].[ccDateTime]=[Expr1002]) ORDERED)
|--Sort(ORDER BY:([tbl_ccNotes].[ccID] ASC, [].[ContactID] ASC))
|--Hash Match(Inner Join, HASH:([A].[UserID])=([].[ContactID]), RESIDUAL:([].[ContactID]=[A].[UserID]))
|--Hash Match Root(Inner Join, HASH:([tbl_ccNotes].[ccID])=([A].[ccID]), RESIDUAL:([Expr1011]=[A].[ccDateTime]))
| |--Hash Match Team(Aggregate, HASH:([tbl_ccNotes].[ccID]) DEFINE:([Expr1011]=MAX([tbl_ccNotes].[ccDateTime])))
| | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID]))
| |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID] AS [A]))
|--Remote Scan(SELECT ContactID, ContactName FROM DEV_REDe.dbo.tbl_Contacts)
-Francisco
-Francisco -
I usually look at the graphical representation of the execution plan available in Query Analyzer not only because I'm lazy but also because it displays more information than in the text represeptation. For example you get estimated number of rows that will be retrieved and cost of each of operations. Please check the following topic in BOL: "Graphically Displaying the Execution Plan Using SQL Server Query Analyzer". In any case (with text or graphical representation) I look for operations that may be costly. In the case of your procedure I see "Remote Scan" (even twice - why it is twice?), I see "Nested Loops" which may be costly depending on the size of tables involved, and I see "Many to many" join. Please display the graphical version of the execution plan of the stored procedure and check where the cost is the highest. You may try to exclude tables that cause high cost (by comenting out parts of the query) and check the improvment.
I assume that the size of your database is not to big - the reason for performance problems must be in the way how the procedure is written. This may be a very good oportunity for you to learn how to tune server performance 🙂 by building queries in a proper way.
Marek
-
quote:
I usually look at the graphical representation of the execution plan available in Query Analyzer not only because I'm lazy but also because it displays more information than in the text represeptation. For example you get estimated number of rows that will be retrieved and cost of each of operations.Sorry to debunk, it is prettier and that is all. If you use SET SHOWPLAN_ALL then you get all the same details (this is what the graphical version uses behind the scenes). SHOWPLAN_TEXT is generally better to use for quick information.
quote:
In the case of your procedure I see "Remote Scan" (even twice - why it is twice?),If you read back thru the post you will see he references a view twice for the same table on a remote server. Now this can be costly or not depending on how it is handled and how big the table read from is. He stated he was using openquery so all the data is read from the remote server and transfered into local memory storage. This can be improved if you can at least copy the data locally. If it doesn't change often then you can schedule as needed removing network contention and table maintaintence issues on the remote server from your query.
Bookmark lookups can be an issue but are better than table scans, you have several bookmark lookups and these relate to subqueries you have in the cases I see. Unfortunately I set up a test DB with similar queries to the ones you are doing and I cannot find an alternate query pattern to provide some of the same results, you may want to verify what you get is what you want.
For example
quote:
SELECT IWStatus.CCID, A.RecID, IWStatus.ccDateTime, IWStatus.StatusID FROM(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)
WHERE A.StatusID IN (2,5)) IWStatus INNER JOIN
HAAScc.dbo.tbl_Complaints A ON (A.ccID = IWStatus.ccID)) AS IWstatus
This query takes a very innner query of the ccID and the max ccDateTime. Then joining those results to the outer subquery it finds of those only the ones with StatusID 2 an 5 so some items do not output at all. Make sure this is what is intended, if so then this is the best.
However something that may bennefit you is to setup a table to store this information from innermost subquery. By storing you can index and join much faster for the same results. Also, you can setup a trigger on the table tbl_ccStatus for UPDATE, INSERT, and DELETE that when run it keeps the value up to date in the tbl_ccStatusMaxes table that I am suggesting this table can also containt the StatusID of those records so the outer subquery is almost completely done and you tie the table in regularly. There are a lot of the subqueries you can do this way to get rid of the bookmark lookups to speed things along even if query is otherwise optimal. This is should be worth exploring.
Big things to look for is table scans. If you get these on large tables or subqueries then you need to look at why indexes or bookmark lookups are not occurring.
Another thing to look at is the indexes being used, they are reported in the output of the execution plan and the query manager may have made a bad decision which an index hint, stats update, or reevaluation of the indexes can correct.
This article http://www.sql-server-performance.com/jc_sql_server_quantative_analysis1.asp is extremely helpfull but reading the execution plans is best learned by looking at them everytime you work on a query, mak adjustments and relook at the plan. You will notice what things change and start to better understand why as you do this.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks for the pointers that you guys have provided... I guess it's just a trial and error type of learning... What I know about writting SQL has been all self taught. I've taken a New Horizon's class on SQL administration which covered sql performance tunning, and I've drilled through the examples there and also those chapters on the website... but it's all classroom stuff.
quote:
If you read back thru the post you will see he references a view twice for the same table on a remote server. Now this can be costly or not depending on how it is handled and how big the table read from is. He stated he was using openquery so all the data is read from the remote server and transfered into local memory storage. This can be improved if you can at least copy the data locally. If it doesn't change often then you can schedule as needed removing network contention and table maintaintence issues on the remote server from your query.The vw_Contacts view refrences an OpenQuery request to a linked 2000 server where the contacts table is maintained... I have not moved this over as a stale table as 'advised' on an earlier post simply because I've been pressed for time on another project
.I forgot that I was referencing the view twice!!.. I've removed it now... and will post up the execution plan from that.. btw the current execution time is avg 24 seconds..
quote:
This query takes a very innner query of the ccID and the max ccDateTime. Then joining those results to the outer subquery it finds of those only the ones with StatusID 2 an 5 so some items do not output at all. Make sure this is what is intended, if so then this is the best.I have the database set up in a star table set up...
tbl_Complaints (main table that holds the recID pointer to the Goldmine database...)
(the remaining tables accept inserts modifications are never made, only inserts thus a status can go from 0, 1, 2, 5, 6,7 and back to 2 depending on the path of the complaint.
tbl_ccAssigned
tbl_ccFailCode
tbl_ccNotes
tbl_ccStatus
thus my reasoning was that I had better collect the MAX date of the CCID in question thus requiring the bookmark lookup.. is this the wrong approach? what other approach can I use?
quote:
Also, you can setup a trigger on the table tbl_ccStatus for UPDATE, INSERT, and DELETE that when run it keeps the value up to date in the tbl_ccStatusMaxesThis is terretory that I wish to explor and have not had the time to do. The books I have do not cover Triggers very well, at least not well enough for me to catch on.. do you have any links here on this site or elsewhere where I can look? I happen to agree that moving all the latest MAX ccid's from tbl_ccStatus to a StatusMaxes table would optimize the query probably by a huge leap since it is having to do this bookmark lookup for a total of over 134 records... 134 bookmark lookups probably = slow performance... I won't go into the Hardware setup... which I don't think can be optimized anymore because it's in production...
C:D: are on a Raid 5, C: has the OS (win2k server) D: has the the OS page file, SQL executables, database files, and log files. My wish? to have 3 logical channels C: scsi drive0 on channel 1 w/ OS and SQL executables w/ page file, D: on channel 2 Raid 5 w/ database files and the E: another scsi drive w/ the log files. But the system is IN production... during the week M-F we cannot have it go down... what other options do I have? live with it?
-Francisco
Edited by - fhtapia on 10/23/2002 10:23:37 AM
-Francisco -
Here is the query plan after I took out the second vw_Contacts table refrence...
|--Sort(ORDER BY:([].[ContactName] ASC, [CONTACT1].[Company] ASC, [Expr1025] ASC))
|--Compute Scalar(DEFINE:([Expr1024]=datediff(4, [Expr1004], [@CurrDate]),
[Expr1025]=datediff(4, [Expr1008], [@CurrDate]),
[Expr1026]=datediff
(4, If ([Expr1011]=NULL) then [Expr1002] else [Expr1011], [@CurrDate]), [Expr1027]=Convert([Expr1008]), [E
|--Hash Match(Right Outer Join, HASH:([tbl_ccNotes].[ccID], [A].[UserID])=([tbl_ccAssigned].[ccID], [].[ContactID]), RESIDUAL:([tbl_ccNotes].[ccID]=[tbl_ccAssigned].[ccID] AND [A].[UserID]=[].[ContactID]))
|--Hash Match Root(Inner Join, HASH:([tbl_ccNotes].[ccID])=([A].[ccID]), RESIDUAL:([A].[ccDateTime]=[Expr1011]))
| |--Hash Match Team(Aggregate, HASH:([tbl_ccNotes].[ccID]) DEFINE:([Expr1011]=MAX([tbl_ccNotes].[ccDateTime])))
| | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID]))
| |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccNotes].[IX_tbl_ccNotes_ccID] AS [A]))
|--Hash Match(Inner Join, HASH:([A].[ccID])=([tbl_Complaints].[ccID]))
|--Stream Aggregate(GROUP BY:([A].[ccID]) DEFINE:([Expr1004]=MIN([A].[ccDateTime])))
| |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), WHERE:([A].[StatusID]=2) ORDERED)
|--Hash Match(Inner Join, HASH:([A].[AssignedID])=([].[ContactID]), RESIDUAL:([A].[AssignedID]=[].[ContactID]))
|--Nested Loops(Inner Join)
| |--Merge Join(Inner Join, MERGE:([tbl_ccAssigned].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([tbl_ccAssigned].[ccID]=[tbl_Complaints].[ccID]))
| | |--Stream Aggregate(GROUP BY:([tbl_ccAssigned].[ccID]) DEFINE:([Expr1008]=MAX([tbl_ccAssigned].[ccDateTime])))
| | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid]), ORDERED)
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([A].[ccID])=([tbl_Complaints].[ccID]), RESIDUAL:([A].[ccID]=[tbl_Complaints].[ccID]))
| | |--Nested Loops(Inner Join)
| | | |--Nested Loops(Inner Join)
| | | | |--Stream Aggregate(GROUP BY:([tbl_ccStatus].[ccID]) DEFINE:([Expr1002]=MAX([tbl_ccStatus].[ccDateTime])))
| | | | | |--Clustered Index Scan(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID]), ORDERED)
| | | | |--Filter(WHERE:([A].[StatusID]=5 OR [A].[StatusID]=2))
| | | | |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), SEEK:([A].[ccID]=[tbl_ccStatus].[ccID]), WHERE:([A].[ccDateTime]=[Expr1002]) ORDERED)
| | | |--Index Seek(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints] AS [A]), SEEK:([A].[ccID]=[A].[ccID]) ORDERED)
| | |--Sort(DISTINCT ORDER BY:([tbl_Complaints].[ccID] ASC, [CONTACT1].[Company] ASC, [CONTHIST].[ONDATE] ASC, [CONTHIST].[OnTime] ASC))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1022]), OBJECT:([TeleMarket].[dbo].[CONTACT1]))
| | |--Nested Loops(Inner Join)
| | |--Bookmark Lookup(BOOKMARK:([Bmk1020]), OBJECT:([TeleMarket].[dbo].[CONTHIST]))
| | | |--Nested Loops(Inner Join)
| | | |--Index Scan(OBJECT:([HAAScc].[dbo].[tbl_Complaints].[PK_tbl_Complaints]))
| | | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTHIST].[CNHRECID]), SEEK:([CONTHIST].[RECID]=[tbl_Complaints].[RECID]) ORDERED)
| | |--Index Seek(OBJECT:([TeleMarket].[dbo].[CONTACT1].[CONTACC]), SEEK:([CONTACT1].[AccountNo]=[CONTHIST].[AccountNo]) ORDERED)
| |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccAssigned].[IX_tbl_ccAssigned_ccid] AS [A]), SEEK:([A].[ccID]=[tbl_Complaints].[ccID]), WHERE:([A].[ccDateTime]=[Expr1008]) ORDERED)
|--Remote Scan(SELECT ContactID, ContactName FROM DEV_REDe.dbo.tbl_Contacts)
-Francisco
Edited by - fhtapia on 10/23/2002 10:30:46 AM
-Francisco -
I have still two ideas how you can improve the performance of the query.
1. Use the tbl_Contacts locally. You can do it by copying it to a local temporary table (in the beginning of you SP):
SELECT ContactID, ContactName
INTO #loc_tbl_Contacts
FROM DEV_REDe.dbo.tbl_Contacts
and then replace all further references to tbl_Contacts with #loc_tbl_Contacts. It may help although the table has no indexes but I think that it's quite small so this shouldn't be a problem.
2. The idea with gathering MAX date in another table (using triggers) is not bad. Of course it depends on the number of records in the table. You haven't tell us what are the numbers of rows in tables used by the stored procedure. Are these several thousands or several millions?
You can test this idea by again selecting maximum date to a temporary table and using this table further in the stored procedure. In both cases (temporary table or permanent table with triggers) it's not more work than for let's say 1 hour. It is worth trying.
-
quote:
1. Use the tbl_Contacts locally. You can do it by copying it to a local temporary table (in the beginning of you SP):SELECT ContactID, ContactName
INTO #loc_tbl_Contacts
FROM DEV_REDe.dbo.tbl_Contacts
and then replace all further references to tbl_Contacts with #loc_tbl_Contacts. It may help although the table has no indexes but I think that it's quite small so this shouldn't be a problem.
Just one note here. There are known locking issues with inserting data to a temp table this way. It is suggested to instead create a temp table first thru CREATE TABLE then use INSERT instead of SELECT..INTO... and remmeber to DROP TABLE at the end to ensure cleanp is done properly.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Here is a listing of what my table rows are like (sorry I misunderstood the first few posts I thought you ment how many rows were being returned (doh!)
tbl_ccAssigned 947 Rows
tbl_ccFailCode 611 Rows
tbl_ccNotes 1708 Rows
tbl_ccStatus 3074 Rows
tbl_Complaints 578 Rows
Goldmine Tables that I access
ContHist 797177 rows
Contact2 336263 rows
Contact1 335437 rows
-Francisco
-Francisco -
Francisco,
I'm not sure whether the stored procedure that you posted before is still valid but it seems to me that it's not. In the procedure, the TELEMARKET part is commented out but this information is still in the execution plan. What worries me more is the vw_Contacts (view?). Is it defined on one of tables Contact1 or Contact2?
Can you check (either with graphical execution plan or with SET SHOWPLAN_ALL ON command) how many rows are returned by each of subqueries? I have a feeling that somewhere in your query there are 300000 records involved.
I'm working with a little bigger tables (up to 7 millions records) and I get very upset when the execution time for a stored procedure (which are quite simmilar to the one you wrote or even more complex) is more that 8 seconds. Since most probably I cannot have a backup of your database(s), my advise it to do what I usually do in such cases - I start writing such queries from the beginning, from the most inner select statement and check execution plan and performance of every statement. Doing so, I'm sure you will find the operation that causes poor performance. For me it can be one of Contact tables or vw_Contacts view.
-
Marek,
Thanks for posting and for providing details about how long a similar procedure would run on more records... yikes!!! you have many more records than what I'm working with and that is what worries me, because the Telemarket database is growing quite rapidly due to the efforts of the company trying to reach out to more and more customers...
quote:
Francisco,I'm not sure whether the stored procedure that you posted before is still valid but it seems to me that it's not. In the procedure, the TELEMARKET part is commented out but this information is still in the execution plan. What worries me more is the vw_Contacts (view?). Is it defined on one of tables Contact1 or Contact2?
Can you check (either with graphical execution plan or with SET SHOWPLAN_ALL ON command) how many rows are returned by each of subqueries? I have a feeling that somewhere in your query there are 300000 records involved.
I'm working with a little bigger tables (up to 7 millions records) and I get very upset when the execution time for a stored procedure (which are quite simmilar to the one you wrote or even more complex) is more that 8 seconds. Since most probably I cannot have a backup of your database(s), my advise it to do what I usually do in such cases - I start writing such queries from the beginning, from the most inner select statement and check execution plan and performance of every statement. Doing so, I'm sure you will find the operation that causes poor performance. For me it can be one of Contact tables or vw_Contacts view.
Below is the stored procedure, I'm currently working on creating some temp tables (in between other projects) and dumping the MAX of the ccStatus, ccAssigned, ccNotes tables into so that there are less lookups..
I'll post the sproc and results then 😀 (probably tonight or tomorrow)
Declare @CurrDate as datetime
Select @CurrDate = GetDate()
Select IWstatus.ccID, callComplaint.Company, AssignedStatus.ContactName as AssignedTo,
DateDiff(dd,DaysInCyle.ccDateTime,@CurrDate) DaysInCycle,
DateDiff (dd,AssignedStatus.ccDateTime, @currDate) as Days_INBIN,
DateDiff (dd, Case
When NotesStatus.NotesEntryTime is NULL Then IWstatus.ccDateTime
Else NotesStatus.NotesEntryTime
END, @CurrDate) InActiveDays,CONVERT(CHAR(12), AssignedStatus.ccDateTime,110) as AssignedON,
Case
When NotesStatus.NotesEntryTime is NULL Then CONVERT(CHAR(12), IWstatus.ccDateTime, 110)
Else CONVERT(CHAR(12),NotesStatus.NotesEntryTime, 110)
END as LastActionOn
From (
--HAAScc STATUS
SELECT IWStatus.CCID, A.RecID, IWStatus.ccDateTime, IWStatus.StatusID FROM
(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
INNER JOIN
(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) as DaysInCyle
ON (DaysInCyle.ccID = IWstatus.ccid)
INNER JOIN
--********************--TELEMARKET INFO--********************************************
vw_TelemarketView as CallComplaint
----*********************************************************************************
ON (IWstatus.ccid = CallComplaint.ccid) Inner join
--AssignedStatus
(Select B.CCid, ctc.ContactName , ctc.ContactID ,B.ccDateTime From tbl_ccAssigned A
inner Join (Select ccID, MAX(ccDateTime) as ccDateTime From
tbl_ccAssigned
Group by ccID) B On (A.ccID = B.ccID and A.ccDateTime = B.ccDateTime) Inner Join
vw_Contacts Ctc on (ctc.ContactID = A.AssignedID)) AssignedStatus
ON (AssignedStatus.ccID = IWstatus.ccID)
--NotesEntries
Left Join
(Select B.CCid, A.Notes, A.UserID, B.ccDateTime as NotesEntryTime From tbl_ccNOTES A
INNER JOIN (Select ccID, MAX(ccDateTime) as ccDateTime From tbl_ccNOTES Group by ccID) B
ON (A.ccID = B.ccID and A.ccDateTime = B.ccDateTime))NotesStatus
ON (NotesStatus.ccID = AssignedStatus.ccID and NotesStatus.UserID = AssignedStatus.ContactID )
Order by AssignedTO,Company, Days_INBIN-Francisco
-Francisco
I made the initial changes and in process of moving the table.. (later today) The table only contains 245 rows. How does this plan look? btw, I'm also curios at what you guys read out of these.. I know cost and time is a factor but I have a hard time determening my next step in reading these... obviously bigger %'s make me look at that section.. but what do you guys do?
.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply