Help me to optimize hard logic on SQL?

I think this should be about the same: SELECT ev. Rectime, ev. Event, ev2.

Value FROM EventView AS ev INNER JOIN Incident I ON PATINDEX('%' + ev. Col + '%', i. SQL) > 0 LEFT JOIN EventView ev2 ON ev.

Col = ev2. Col AND ev. Rectime BETWEEN ev2.

Rectime AND ev2. ChangedDate The thing is, you are finding your complex events using event names, then you are extracting those very names from the complex events found, and finally you are using the extracted names in the last CTE to compare against themselves. So, it seemed to me that the extracting part was completely unnecessary.

And without it the resulting query turned out to be quite simple (in appearance at least).

Sad but query executing still being about 7 minutes on test data - that is very slow. – nCdy Mar 22 '11 at 5:13 But this is 10-20% faster then my query. – nCdy Mar 22 '11 at 5:20 and... extracting part is necessary.

– nCdy Mar 22 '11 at 5:37 I updated first question – nCdy Mar 22 '11 at 5:40 I think I just need to use JOIN instead of LEFT JOIN ... – nCdy Mar 22 '11 at 5:46.

Well one of the most basic concepts of relational data storage is to store the data in a normalized way and use the relational database to store the data, but do not parse/process it etc. Use the application layer to do that. That should be the first thing you do and then you may move to the next level of optimizing the queries, joins, making indices etc.

Parse part is not the slowest one. There are weirds joins... – nCdy Mar 21 '11 at 11:22.

I think the slowest part is originating from EventView definiton: SELECT ... ( 'Alarm' + CAST(ID as nvarchar(MAX)) ) AS Col, ... Joining with such calculated field causes nasty performance hit. Can't you: record (Col=)Alarm+ID directly to Changes table or update Alarm+ID by trigger or use indexed view for calculating Alarm+ID or use temporary table for storing Alarm+ID or at least not use nvarchar(MAX), but something like nvarchar(10) (if this changes query plan)?

Thank you but that is not slowest part, slowest part was I making JOIN by RecTime field - it's awesome slow process. – nCdy Mar 23 '11 at 8:40.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.