February 5, 2008 at 3:50 pm
Hi all.
I want to use the following query in a sp to enable paging using ObjectDataSource.
The problem (being EXTREMELEY slow) arises when I add these joins and where statements.
SELECT r.RID AS ReqID, r.Name AS ReqName, r.Family AS ReqFamily
,t3.Name AS DistName, t4.Name AS RurName,t5.Name AS VilName, n.Name+' '+n.Family AS NazerName
,ROW_NUMBER() over (order by r.Family) AS RowRank
FROM Requests r
LEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID
LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID
LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID
LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID
LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID
LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.ID
WHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end)
and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end)
and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end)
and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end)
and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end)
**there are 1million rows in [Requests] table ,200000 rows in [t5villages], and about total 5000 rows in other tables.
As you can see, this is for a GridView showing list of people requesting a loan allowing users to make alternative searches based on Name, Familyname ,ID ,...
would you please help me optimize and make fast this query.
Many thanks..
February 5, 2008 at 4:07 pm
Could you provide the DDL for the entire sp, not just the query it is running?
thanks
February 5, 2008 at 11:28 pm
Also the table schemas and index definitions would help.
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
February 6, 2008 at 10:25 am
WHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end)
and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end)
and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end)
and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end)
and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end)
try to eliminate the case usage like so:
WHERE
r.stateid= isnull(nullif(@StateID,''),r.stateid)
and r.provid = isnull(nullif(@provID,''),r.provid)
and r.rID = isnull(nullif(@ReqID,''),r.rID)
and isnull(r.nazerID,'') = isnull(nullif(@nazerID,''),isnull(r.nazerID,''))
and (r.name+' '+r.family) like '%'+ isnull(@ReqName,'')+ '%'
if all three of these parameters are optional (@StateID, @provID, @ReqId ), at a minimum you'll need indexes to support searching only by stateid and provid (assuming rID is an indexed primary key).
LEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID
LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID
LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID
LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID
LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID
LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.ID
the geography tables could be combined in a view (especially if you use them frequently in other queries) or a derived table.
LEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID
LEFT OUTER JOIN
(SELECT {desired fields named for specific use, e.g.: t1.ID as StateID,
t2.ID as ProvID, etc.}
FROM t1States
JOIN t2Provinces t2 ON t1.ID = t2.StateID
JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID
JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID
JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID
) as V ON V.StateID = r.StateID and V.ProvID = r.ProvID and V.DistID = r.DistID and V.RurID = r.RurID and V.VilID = r.VilID
February 6, 2008 at 1:47 pm
Hi,
Couple of points on yout query:
1) The No.Of Outer joins you have very high.
2) You are using isnull operation this avoids usage of Index.
3) Dont have case statements in WHERE condition.
As you are planning to have it in stored procedure try to make use of temprary tables to store the intermediate result.
If you are in OLAP try to denormalize the tables so that you will less joins.
Try thinking about partitioning the tables.
Post the explain plan and Indexs you have on the table?
Thanks -- Vj
February 8, 2008 at 4:17 am
Hi all, I have an idea... Why don´t you use views. For example 3 views with 2 outer join each.. I think it will be more faster.. What do you think?
Byee!!
February 8, 2008 at 6:15 am
Federico Zajur (2/8/2008)
Hi all, I have an idea... Why don´t you use views. For example 3 views with 2 outer join each.. I think it will be more faster.. What do you think?
It's not likely to be faster. When SQL runs a query that contains views, it expands the views out into their definitions, inlines those into the query and runs the resulting statement. Views don't store the data, they're just saved select statements.
Indexed views are a completely different story.
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
February 8, 2008 at 6:23 am
but a view would making writing and understanding that query a lot easier.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply