x

Optimize EAV based SQl server Query

I have a code that will return about 1 million records in about 15 seconds on a normal PC.

   select joined.* , eset.PageNo from (select * from
 ( 
  select e.EntityTypeID ,  a.EntityID , a.AttributeValue , ea.AttributeName
  from
 (SELECT      T1.EntityID,
         T1.AttributeID,
         STUFF(( SELECT    ', ' + T22.OptionText
                 FROM   [POC].[dbo].[EntityAttributeOptionValues] t2
                   inner join EntityAttributeOptions t22 on 
                   t22.ID = t2.OptionValueID
                   
                 WHERE     T2.AttributeID = T1.AttributeID
                 AND       T2.EntityID = T1.EntityID
                 
               --  ANd t2.EntityID between 5060 and 56598
                 
                 FOR XML PATH('')
               ), 1, 2, '') [AttributeValue]
   FROM  [POC].[dbo].[EntityAttributeOptionValues] t1
   inner join EntityAttributeOptions t11 on 
   t11.ID = t1.OptionValueID
   
   --where t1.EntityID between  5060 and 56598
  
   
   GROUP BY    T1.EntityID, T1.AttributeID
   ) a
    inner join Entities e on e.ID =  a.EntityID
    inner join EntityAttributes ea on ea.ID = a.AttributeID

    --where e.EntityTypeID = 9


    ) options
 

      union all
     (select EntityAttributes.EntityTypeID, 
     EntityAttributeValues.EntityID as  'EntityID' ,
      EntityAttributeValues.AttributeValue,
     EntityAttributes.attributeName from EntityAttributeValues
       inner join EntityAttributes 
     on EntityAttributes.ID = EntityAttributeValues.AttributeID

      )) joined

     inner join Entity_Paged_View_With_Set eset on eset.ID = joined.EntityID 


This query will return me five columns

"EntityTypeID" , "EntityID" , "AttributeValue" , "AttributeName" , "PageNo".

EntityTypeID EntityID AttributeValue AttributeName PageNo
1 5790 Musics, Paintings Hobbies 286
1 11673 Musics, Paintings Hobbies 580
1 30595 Males Gender 1527
1 31860 No Want Bus Facility 1590
1 39755 Males Gender 1985
1 41020 No Want Bus Facility 2048
1 47599 Males Gender 2377
1 48864 No Want Bus Facility 2440

Now when i just try to get about 100 records it will take about 4 seconds. I want to filter this query by like

pageNo = 1 and EntityTypeID = 1
or
pageNo = 1 and EntityTypeID = 3
or
pageNo = 5632 and EntityTypeID = 1
and so on.

Can you please specify where should i apply where condition. Even order by clause drastically reduce performance.

The upper query before union all is slow. but the later is fast.

Where should i apply where conditions to optimize the query such that it will take less than a second to execute

more ▼

asked Nov 01, 2011 at 04:28 AM in Default

avatar image

Kamal Deep Singh Saini
25 3 3 6

Can you please pass the DDL statements for the tables involved. Thanks.

Nov 01, 2011 at 04:48 AM Usman Butt

@Usman Butt My problem is solved, actually i havent apply indexing for the tables thats the issue

Nov 17, 2011 at 10:41 PM Kamal Deep Singh Saini

Please add an answer to your own question to show everyone else how it was solved. The concept of this site is to get help and to help. :)

Nov 17, 2011 at 11:45 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2029
x1148

asked: Nov 01, 2011 at 04:28 AM

Seen: 971 times

Last Updated: Nov 18, 2011 at 01:57 AM

Copyright 2017 Redgate Software. Privacy Policy