Hi

Thanks a lot.

I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read...

Hi

Thanks a lot.

I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read grows up for...

I've rewritten your query to use EXISTS instead of IN. Removed the UNION ALL and change the comma separate table names to INNER JOIN. I think it's a better starting...

Although the SQL online documentation shows an example of using WHERE with a comparison operator, I don't understand how it  is being used below.  I understand the notation "columnName"...

Why the not?:

if not exists ( select 0 from sys.sql_logins where name = @szlogin)

Also I think you might need:

EXECUTE AS LOGIN='sa';

before the line: if...

.

• This reply was modified 1 week, 1 day ago by  Jonathan AC Roberts.

You really need to look at the code. It could be, for example, that large temporary tables are being created for the processing. You could get around this by using...

Okay I will try creating these indexes and see if it benefits. Since, the query isn't completing in its entirety I have no clue of the optimizer suggesting an...

Thank you so much for all the help...

• This reply was modified 1 week, 1 day ago by  Jonathan AC Roberts. Reason: added PS:

You have an OUTER APPLY (aliased c2) in the query but in the WHERE clause of the main query you have

AND D.LiveTable = c2.TableName

So why is it an OUTER APPLY...

Yep, I agree with you Jonathan AC Roberts, but what if the number of columns is dynamic?  With version 1 it has 3, version 2 has 2, version 3...

Not sure that an index will help anything since there is no WHERE clause or ORDER BY.  No matter what index you put on there, it is going to...

The best way to get this answered is to post the actual SQL that is dynamically generated so we can see what the SQL is doing. (Just change exec(@SQL) to...

deleted answer

• This reply was modified 1 week, 4 days ago by  Jonathan AC Roberts.

1. Get the count of the number of rows on the table
2. Get the average size of all the columns on the index
3. Multiply the answers from 1. and 2....