January 21, 2009 at 6:03 pm
Thanks David,
I don't see any lookup in the execution plan. ?
January 21, 2009 at 7:30 pm
Odd, as I just opened the plan that you attached. I see Key Lookups on your Archive and your Live tables.
I'll poke a bit more in the AM. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 22, 2009 at 9:01 am
Something like the following on both the _LIVE and the _ARCHIVE table should help.
USE [IDX]
GO
CREATE NONCLUSTERED INDEX NI_TRANSAC_LIVE
ON [dbo].[TRANSAC_LIVE] ([PROC_ZZ],[SER_DT], [GROUP_ZZ],[PATIENT],[INVOICE])
INCLUDE ([POST_PD])
GO
As always, look at the execution plan to ensure that this is indeed the index that is needed.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 22, 2009 at 9:48 am
But in the execution plan , it's taking only clustered index seek. I am sending again attached execution plan.
I also ran the script to check the missing index on this query and found some missing index and I created but does not help.
Actually, the partition view is fine, it's taking 19 second when I run third time which is ok but when I ran same query first time in the morning, it's taking 7 minutes which very confusing.
January 22, 2009 at 9:57 am
I am sending you the attached text file which has statistics io on query when I run first time and second time.
If you have noticed that when I ran the same query first time in the morning it has lot more physical reads and logical read but when I ran the same query secound time physical read and logical reads reduces in big time.
January 22, 2009 at 9:58 am
The run time difference is due to the fact that is has to load so many data pages in order to get the information that you are seeking. My guess is that the table TRANSAC_* is a fairly wide table. Getting those pages into memory will take time and will therefore take longer the first run. Subsequent runs are going to take less time as there is no IO required to the disk.
I am still seeing a key lookup on the TRANSAC_* tables. So, I would still recommend trying that index that I recommended and seeing how things look.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 22, 2009 at 11:34 am
Created the index which you have recommended , it's really improved the performance. it is now taking 4 sec.
How you came up with this following index. I still cannot see lookup in the execution plan.
USE [IDX]
GO
CREATE NONCLUSTERED INDEX missing_index
ON [dbo].[TRANSAC_LIVE] ([PROC_ZZ],[SER_DT], [GROUP_ZZ],[PATIENT],[INVOICE])
INCLUDE ([POST_PD])
GO
Thanks a lot. you rock
January 22, 2009 at 11:43 am
Every once in a while I get a good one. 😛
Not sure what you were seeing in the Execution Plan but I was seeing the Key Lookup of the tables that were in the partitioned view. So I just took the columns that were seek predicates and the one column that was just an output (only in the select) and built the index off that. Glad that I was able to guess it right. I sometimes miss one.
Grant Fritchey has a great free ebook on Red-Gate on Execution Plans that might be a great tool to have. I know I really appreciated it and learned a bunch. http://www.red-gate.com/specials/Grant.htm?utm_content=Grant080623
No guarantees on the link. 🙂
Anyway, glad to help and hope this works better for you!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 22, 2009 at 11:54 am
Thanks man. I will go through this book.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply