The tag has no wiki summary.

learn more… | top users | synonyms

2
votes
1answer
30 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
1
vote
1answer
81 views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
3
votes
3answers
142 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
0
votes
1answer
396 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
2
votes
5answers
3k views

Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

See first query below. Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK. How to make the first query compile? Interesting - if just one join or the other ...
6
votes
1answer
4k views

OPTION FORCE ORDER improves performance until rows are deleted

I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds. ...
4
votes
2answers
3k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
5
votes
2answers
417 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
1
vote
0answers
190 views

What overhead is there if I MERGE row by row?

I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users ...
1
vote
1answer
112 views

Should I use table hints with a process-keyed table?

I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table ...
2
votes
1answer
408 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
1
vote
1answer
8k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...