duplicate

• This reply was modified 2 years, 8 months ago by Jonathan AC Roberts.

I would initially try creating the temporary tables (#potential_dups and #person_list) before you insert into it and create the CLUSTERED indexes inx_potential_dups_temp1 and index idx_person_1 instead of NONCLUSTERED, and create...

Thanks Jonathon.

Thanks Brian - yeah that table gets massive inserts and updates so I guess indexed view is a bad option.

Tried using exists - same run time:

            
January 4, 2024 at 7:54 pm
            
#4356873

            
            
                                                
        

To resolve this issue, you need to ensure that the ANSI_NULLS option is set to ON for the table 'eligibility'. You can modify the table definition to set the ANSI_NULLS...

• This reply was modified 2 years, 8 months ago by Jonathan AC Roberts.

I would just shrink the data files and rebuild the indexes including the clustered indexes.

If you want to make the query you've given as fast as possible to return the rows you could use a schema bound view:

-- Create the view...

Indexes on [dbo].[eligibility]([member_id]) INCLUDE ([eligibility_id])

and [memberphonelookup]([member_id])

Is it really returning all the rows in the table? If so then why has the OP got a where clause?

How many rows does the query return?

An obvious solution is to create an index on Final_date and include columns (column1, column2... column18)

You might be able to get away with just...

I expected that if I use pagination it will only load 1st page for me, which should be very quick. And load the rest pages only if users request...

Instead of applying the recompile at the procedure level, you can apply it at the query level within the stored procedure using the OPTION (RECOMPILE) hint. This way, only specific...

Are you creating a temp table i.e. one that begins with a hash (#) or are you using a permanent table name?

Yes, that's a bit shorter and generally more efficient. I'm wondering if there were a lot more that 12 items per customer and the right indexes on the...

;WITH CTE AS
(
    SELECT DISTINCT CustomerCode
      FROM myTable
)
SELECT B.*
  FROM CTE A
 CROSS APPLY(SELECT TOP(12) *
...

;WITH CTE AS
(
    SELECT DISTINCT CustomerCode
      FROM myTable
)
SELECT B.*
  FROM CTE A
 CROSS APPLY(SELECT TOP(12) *
  ...