10,734 reputation
23163
bio website
location Connecticut
age
visits member for 3 years, 3 months
seen 5 hours ago

I'm a dude.


2d
comment Deadlock when CODES table has 41900 rows
Step 1 - drop the index CODES_IS_COMM_IX - it gives you nothing that CODES_IS_TRANS_COMM_FLAG_IX doesn't already have, but both indexes need to be maintained
2d
comment Deadlock when CODES table has 41900 rows
One thing I see right away is you have redundant indexes which is not gonna help...
2d
comment Deadlock when CODES table has 41900 rows
On indexes you set "Allow Page Locks" and "Allow Row Locks" as options. If those are OFF then then entire index gets locked.
2d
comment Does a DELETE/UPDATE query based on a large SELECT query cause deadlocks and can it be improved?
What other approaches you can take depends entirely on your use case. Are you OK with a few milliseconds difference between the subquery and the delete? If so, you may be able to load your NOT IN list into a temp table and compare to that to reduce locking.
2d
comment Deadlock when CODES table has 41900 rows
Some things to check whenever there are deadlocks - what indexes are involved? What are your page and row locking options for affected indexes (including CI)? What's your isolation level?
Sep
15
reviewed Reject suggested edit on Duplicate login details per user in SQL Server 2008 R2 Error Log
Sep
11
comment Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements
I suspect you'll need to look at XML indexes to optimize this. These operations are VERY CPU-intensive otherwise.
Sep
8
comment A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?
Again, I don't know for Oracle. In SQL Server it can if you are inserting into the middle of the index like I assume you would have to be for non-time and non-id indexes. The DB has to maintain the b-tree for that whole index range which can be not-inexpensive.
Sep
8
comment A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?
Well reducing the rows shouldn't impact that TOO much, but I know very little about Oracle.
Sep
8
comment A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?
I think the answer will depend entirely on your query patterns and design of the table and indexes. If you run a lot of reports but they all filter on an indexed date column, you probably aren't paying a lot of overhead. However, you ARE paying to keep the indexes on those unused records for all eternity.
Aug
25
comment SQL select top X rows having sum limit
You could do this pretty easily with a recursive CTE...But honestly for running totals (which this is) a cursor is probably the best-performing solution.
Aug
21
comment How to back out of mass update in SQL Replication
I just went through this a few weeks ago with transactional replication. The solution is to reinitialize, unfortunately.
Aug
19
answered What is better? Delete and insert or update?
Aug
19
comment What is better? Delete and insert or update?
The performance difference could well be in how you are performing the DML statement. Is this running in a loop by any chance?
Aug
18
comment How do I ORDER BY typical software release versions like X.Y.Z?
This is confirmed by your fiddle...
Aug
18
comment How do I ORDER BY typical software release versions like X.Y.Z?
Do you need to convert these to numbers? Otherwise I would expect 10 to be between 1 and 2.
Aug
11
awarded  Popular Question
Aug
5
comment can we transfer sql database backup from one server to another server directly?
You need to take a backup, unless the database is readonly in which case you can copy the files directly.
Aug
4
comment How to write a query which finds all circular references when a table references itself? (II)
Wouldn't 7 still slip by?
Aug
1
awarded  Notable Question