August 25, 2011 at 5:20 am
Hi,
I have a query which is used by all 500 users continuously. it is on it get data from View. In this query WHERE clause is generated dynamically. In view i have 20 fields, having '+' and CONVERT function used.
Here original query but details are not exact as it is in original query.
there are 150000-200000 records in my tables.
THIS TAKES TOO LONG TIME TO EXECUTE.
SELECT A.G, A.C , '(IA) ' + A.A + ' , (PDA)' + B.H AS A, '(O)' + ISNULL(A.P, '') + ', (R)' + ISNULL(A.PR, '')
+ ', (F)' + ISNULL(A.F, '') + ', (M)' + ISNULL(A.M, '') + ISNULL(A.N, '') + ', (CP)' + ISNULL(A.CN, '') AS Phone, A.EM,
B.Reg, B.Eng, B.Ch, B.Co, B.P, B.P, B.V, B.I, B.Eno, B.Nx,
B.PC, B.Ao, ISNULL(B.P, '') + '|' + ISNULL(B.C, '') AS chqno, CONVERT(varchar(11), B.ED, 113)
AS EDe
FROM dbo.Cp AS A LEFT OUTER JOIN
dbo.PE AS B ON A.G = B.G
WHERE Rg in ('A..A','A........','B.N....E','B.....','B.......R','B.....','B...........','C.........','C......','C.....','C....................','D....','G.........','G..','H....','H........','I.....','J.........','J.....','K.......','M.....','N....','P...','R.....','R.....','R..','S........','S....','T.........')
and Ic in ('A...','L...','T...','T......','O...','U...','U...','N....','M...','N...','R...','S...','M...','E...,','J...','H....','F...','E...','F... ','R...','S...','R...','R...','R...','R...','R...','R...','R...','R...','R...','R...')
and S in ('A...','D...','D....','D..','F...','F...','D...','D.....','E..........','F.......','F.......','H...','M.....','N......','T...','T..............','T.............','T.............','T..........','T.........','T.........','U..')
and and Ce like '%i%' and ( a.e in (select Srno from R where a in ('D') or replace(e1,' ','') ='D' or (replace(e2,' ','') ='D' and isnull(e1,'')='')) or (replace(e1,' ','') ='D' or replace(e2,' ','') ='D' or replace(e3,' ','') ='D')) Order by G, E
pls help me to optimize this query.
August 25, 2011 at 5:25 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 2:58 am
Hi,
Thanks for helping me.
Here the execution plan and definition of 3 tables which are used in my query. I don't know how can i give you index details.
August 26, 2011 at 3:20 am
Script the indexes the same way you scripted the tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 4:27 am
Can you please post the actual plan, and the view definition for ViewEntryDetails.
Cheers
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2011 at 2:35 am
here the plan.
August 27, 2011 at 3:04 am
Index definitions please (script the create index statements)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2011 at 3:54 am
I got the query how to get index details. here my index details
August 27, 2011 at 4:26 am
That is not what I asked for. The column names aren't in that and they are the most important part. I can't conclude anything from the name of an index
Generate and post the CREATE INDEX statements that define your index. From management studio, expand out object explorer until you see the indexes, right click - script - create
Also, Chris is still waiting for the definition of ViewEntryDetails.
Additionally, the execution plan you posted is not for the query in your initial post. It's for the following query
SELECT A.GroupCode, A.ClientName, '(IA) ' + A.Address + ' , (PDA)' + B.HolderAddress AS Address, '(O)' + ISNULL(A.PhoneO, '') + ', (R)' + ISNULL(A.PhoneR, '')
+ ', (F)' + ISNULL(A.Fax, '') + ', (M)' + ISNULL(A.Mobile, '') + ISNULL(A.NDNCMobile, '') + ', (CP)' + ISNULL(A.ContactNo, '') AS Phone, A.EMail,
B.RegistrationNo, B.EngineNo, B.ChasisNo, B.CoverNote, B.PolicyNo, B.ProductName, B.VehicleMake, B.InsCompany, B.EntryNo, B.NextStage,
B.PolicyCategory, B.ACChqNo, ISNULL(B.PendingAmtChqNo, '') + '|' + ISNULL(B.ChqNo, '') AS chqno, CONVERT(varchar(11), B.ExpiryDate, 113)
AS ExpiryDate, B.Region, B.Source, B.Executive1, B.Executive2, B.Executive3, B.Model
FROM dbo.ClientGroup AS A LEFT OUTER JOIN
dbo.PolicyEntry AS B ON A.GroupCode = B.GroupCode
which is radically different from what you posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2011 at 11:35 pm
HI,
Here i have attached index definitions as you have asked for.
I want to tell you that i have created index only for those fields which are used in my query.
I think i have created some confusions, sorry for that. let me clarify everything.
Structure is as under :
Main query is using view called 'ViewEntryDetails' and ViewEntryDetails uses PolicyEntry,Clientgroup tables.
'RenewalFollowUpAssigned' is used in main query.
here i am attaching :
index definitions of 3 tables ,
Execution Plans of main query and a view ,
Main query and Views.
Table definitions i have already posted.
sorry again, i will not make such mistake again.
August 29, 2011 at 11:57 pm
Pls help me
i am at critical situation.
August 30, 2011 at 3:04 am
Please post the actual plans, these are estimated plans.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2011 at 5:17 am
Hi Chris,
here i am attaching actual execution plan of View and Main query as you requested.
thanks for help me
August 30, 2011 at 7:35 am
Functions around columns in table dbo.PolicyEntry are causing table scans:
replace(executive1,' ','') = 'DINESHANJARA'
Change these to
[executive1] IN ('DINESHANJARA','DINESH ANJARA').
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2011 at 10:57 pm
Thanks a lot.
i did it as per your suggestion, it made a difference.
what if i change CompanyName,Region,Source to numeric ID instead of this long string ? does it make any difference ?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply