SQL query taking long time to execute in SQL Server

Check the execution plan of your view or query to identify whether any indexes can be created for better performance or not.
Also check whether you can exclude any set of records before the PIVOT so that your view can return records faster..
Kindly request you to share the view script and your final query along with execution plans to suggest better..

Please post your code.

Can you do search before PIVOT, as that will help organizing table indexes better, imho. Create indexes, based on your WHERE conditions, and check execution plans as someone already pointed out.

Also set maintenance plans, e.g. sp_updatestats etc in schedules.

Regards,
Valliappan AN.

A search in a PIVOT should never be faster than a search in the normalized tables in a good relational model as we're working with a RDBMS. The pivot would only be "faster", when you materialize it using the appropriate indices. Which means basically replicating the data under the hood.

Thus you should invest the time to your original situation. The first thing you need to clarify is your terminology:

What does "a column based table" mean? Cause every table in a RDBMS "is based on columns". Your link does not give further glues on that. But my database senses are tingling.. do you talk about an EAV model?
Also in this sense, what does "I am trying to search a column based table around 150 million rows." mean? We all search columns.. Reading it literally: You have 150m row table and want to search in only one column? Then create an index for this column. Depending on the use-case and data type of this column, a maybe it should be the clustered key. If you're talking about doing pattern matching, wild card searches and more on this column - assuming it is NVARCHAR() - then consider using a FULLTEXT INDEX on this column.

But besides that, what kind of process leads to 150m rows? Is the table properly implemented? Did you chose the optimal clustered key? Did you examine a partitioned table? Are the indices optimal?

Valliappan, Thank you for your response.  Due to company's policy, I will not be able to post the code.  To see the sample data, please the attachment in the link below.

ste5an, Thank you for your response.  Column based table meaning, instead of storing the data in single row, we use columns.  Example, employee data stored as below
Field Id         Field Value
-----------        ----------------
     1               NNN
     2               XXX
     3               YYY
     4               ZZZ
      .
      .

Field ID   Field Name
-----------   -----------------
     1         Employee ID
     2         First Name
     3         Last Name
     4         Middle Name
      .
      .
Please see the attachment in the link for further clarification.
https://www.experts-exchange.com/questions/29106205/Convert-columns-to-rows-in-SQL.html

So, it is in fact an EAV-model. You should have posted this as first information. Also in your other post..

Why do you have 150m rows? EAV is only used for sparse attributes. For everything else we use entity modeled tables. So I consider this to be the wrong choice of architecture for your data storage.

This is imho already your answer: normalize your model.

Or at least use a normalized model for reporting. E.g. using an entitiy Employee will reduce already the table rows around the factor 3. Depending on the other attributes, this number can shrink drastically.
btw, this is equally to materialize your "PIVOT" approach. But a proper normalized model here is better index-able.

Otherwise use filtered indices.

How do the users / consumers of the View interact with it ?

Challenge with a view is that the query is pre-formed so all you can really do is SELECT ??? FROM VIEW WHERE ???

Well, there are other things of course, but essentially the view is coping with those 150 million rows every time.

Depending on how the consumers of that data interact, you would probably be better off creating it as a function or stored procedure.

That way, you can filter the results via parameters before formatting (pivot) your query.

Now, with the PIVOT operator you will not be able to create a clustered index on that view, so you are stuck between a rock and a hard place.

Thats where a Function, or a Stored Procedure would be more beneficial, because you can introduce select criteria inside the first select subquery

Let me know if you want to follow up on a function or a stored proc and how people will be interacting with / requesting the data.

Hi Radhakrishna,

I get what you are saying.

1) Create a clustered index on FieldID and /or other included RowID excetera included columns.

2) if you have multiple WHERE clauses, do the filter first on the FieldID/FieldValue combinations, and then PIVOT.

3) And then PIVOT, either use a CTE or a Temp Table, based on performance for the same.

HTH.

I have written an Article about creating a Procedure to perform a PIVOT

Its focus was the use of dynamic SQL, but it does have a few insights, and would be easy to build in WHERE clauses etc....

https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html

Now, in terms of EAV models, that Article also touches on EAV as a Pivot.

And as far as EAV modelling, here is one (of many) with example PK and Indexing

This is one which I have very quickly and rudely adapted for your structures/sample data - it is not the best, but demonstrates some of the discussions above

Create table Entity ([Entity_ID] int, [Entity_Name] varchar(100))
insert Entity values (1,'Employees')

Create table E_Attributes ([Entity_ID] int, [Attribute_ID] int, [Attribute_Name] varchar(100))
insert E_Attributes values (1,1,'Employee ID' )
insert E_Attributes values (1,2,'First Name' )
insert E_Attributes values (1,3,'Last Name' )

Create table EA_Values ([Entity_ID] int, [Attribute_ID] int, [Attribute_Value] varchar(100))
insert EA_Values values (1,1,'NNN' )
insert EA_Values values (1,2,'XXX' )
insert EA_Values values (1,3,'YYY' )


ALTER TABLE Entity ADD CONSTRAINT PK_ENTITY primary key Clustered ([Entity_ID])
create index IDX_Entity on Entity ([Entity Name])

ALTER TABLE E_Attibute ADD CONSTRAINT PK_E_Attribute primary key Clustered ([Entity_ID],[Attribute_ID])
create index IDX_E_Attribute on E_Attribute ([Attribute Name])

ALTER TABLE EA_Values ADD CONSTRAINT PK_EA_Values primary key Clustered ([Entity_ID],[Attribute_ID])
create index IDX_EA_Values on EA_Values ([Attribute_Value])




SELECT [Entity_ID], [Employee ID], [First Name], [Last Name]
FROM
    (SELECT E.[Entity_ID], A.[Attribute_Name], V.[Attribute_Value]
     FROM Entity E
     INNER JOIN E_Attributes A on E.[Entity_ID] = A.[Entity_ID]
     INNER JOIN EA_Values V on A.[Entity_ID] = V.[Entity_ID] and A.[Attribute_ID] = V.[Attribute_ID]  
	 WHERE [Entity_Name] = 'Employees'
	 AND [Attribute_Name] in ('Employee ID','First Name','Last Name') ) AS Src
PIVOT
    (MAX([Attribute_Value]) FOR [Attribute_Name] IN ([Employee ID], [First Name], [Last Name])) AS pvt

WHERE [First Name] = 'xxx'

Select allOpen in new window

Hope that helps demonstrate some of the concepts for you.

Thank you everyone for your help.  In this scenario, Raja Jegan R reply helped me the most.  Thank you!

>> In this scenario, Raja Jegan R reply helped me the most.

By saying what others said before him ?

By not showing how to turn the view into a SP ?

For what it is worth, you can also create an inline table valued function which is also very quick, and you can also select from a function so essentially the only change is using the function name (with parameters) instead of the view name (with where clause).

It was offered, but you didnt respond... And if SP is the way to go, there were examples provided for converting a pivot into a SP.

Frustrating....

>> In this scenario, Raja Jegan R reply helped me the most.  Thank you!

RadhaKrishnaKiJaya, just a request..
While many experts are trying to help you out, if you value some solution better than others, then please accept the solution you preferred as Best solution and share some points to other experts to value their time and effort as Assisted solutions..
I understand that you might have tried my suggestion since I've helped the linked question but in all cases please evaluate the suggestions provided by all experts to derive into a better solution..

>> Frustrating....

Sorry Mark, no offenses pls..
You have dedicated yourself a long time in EE to help lots of people and request you to reassign the points with mine as Best Solution(since author preferred for this scenario) and share points to other experts with valuable suggestions..

Cheers Raja,

But the Asker has  spoken :)

And I probably said too much ;)