REORGANIZE is not meant to reduce total / overall I/O, rather it's meant to do it in chunks instead on in bulk, as REBUILD must do it.  You're expecting REORG...

I've found REORGANIZE is often useful when a lot of rows have been deleted / purged from a table.  In that situation, I don't want the other pages messed with...

You shouldn't routinely rebuild or reorganize indexes just because you can.

Far more important is to insure that you have the best clustered index on every existing table.  Naturally that requires...

For a single conditional column comparison, there's no reason to use multiple queries or procs, as it's unnecessarily overly complex.

As to using ISNULL(), that should simply never be used in...

No.

In particular, for example, often an EXISTS() check is more efficient that an INNER JOIN when you just to need to verify that a matching row exists in another table.

You'd also want to keep the closest building id in the table structure (with a datetime of when it was computed) and that distance.

The reason for that is to provide...

--if you just want to see if a dup key exists:
SELECT TOP (1) key_col1, key_col2, COUNT(*) - 1 AS duplicate_count
FROM dbo.table_name
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1

--if...

Any function against a table column in a WHERE clause is not a good idea and is potentially bad for performance, because it prevents index seeks for that comparison.

https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

Remember that...

SELECT * 
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter

Don't forget all the overhead bytes that SQL requires internally to manage the row.

You might have to move some of the values to a second table, with a 1-1 relationship...

We'd have to see the table DDL to find the exact cause of that.

Definitely force the varchar(max) data off row, as you have done.

You should also page compress the table...

You need to call the Google address parser/"splitter" function or use some other address parser that does this for you.  You will never be able to do this accurately by...

SELECT t1.ID2, SUM(t1.Amount1) AS Amount1, ISNULL(MAX(t2.Amount2), 0) AS Amount2
FROM dbo.table1 t1
LEFT OUTER JOIN (
    SELECT ID2, SUM(Amount2) AS Amount2
    FROM dbo.table2
...

Yeah, that's rather tricky.  All I can think of now is to create a stored proc with an " EXEC AS 'powerful_user_name' " clause, then give the ScriptRunner the authority...

MS has actually provided complex formulas for years.  But it's so difficult to come up with accurate numbers to go into the formulas, and the result is only as good...

• This reply was modified 5 years, 7 months ago by ScottPletcher.