Viewing 15 posts - 14,236 through 14,250 (of 14,953 total)
Nope. My idea didn't work.
The only way I can get it to use only Index Seeks, without Scans, is to separately query each index, build a temp table for...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 2:10 pm
Actually, there is a partial way around it, which is using Union statements instead of Or statements.
For example:
select Col1, Col2
from dbo.Table1
where Col1 = @Col1_in
union
select Col1, Col2
from dbo.Table1
where Col2 = @Col2_in
The...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:57 pm
If you're using NewID as part of the clustered PK, you should also add an incrementing field to it. A transaction time would work. Otherwise, since NewID isn't...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:54 pm
What you're looking for is "left outer join". Look it up in Books Online and you'll get exactly what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:35 pm
Is there a technical reason to do the pivot in the database?
Would it be possible to move the pivot to a front-end application?
The reason I ask is that Excel, Report...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:30 pm
Multiple concurrent selects shouldn't cause a deadlock. Are you sure that's what's causing it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:27 pm
There isn't a way around that. At least not one I'm aware of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:24 pm
I used your temp table and test data on 9000 rows, 1000 IDs in #t.
Here's the query I wrote, based on the description you gave in the original post:
;with CTE...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 12:38 pm
I didn't realize the whole thing was being done on a temp table. Sorry about that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 12:09 pm
Good on finding a solution. You're welcome (not that I actually helped much).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 11:58 am
Those definitions are the differences.
Transactional Replication copies on a transaction-by-transaction basis. It's near real-time (mainly depending on network latency).
Log shipping sends a backup of the log over and restores...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 11:51 am
Yeah. I had found the thing with begin and commit/rollback in different procs, and had assumed that batches would work the same way. Found out I was wrong...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 8:11 am
Matt Miller (4/11/2008)
rbarryyoung (4/11/2008)
Michael Earl (4/7/2008)
The semi-colon you have included is also a batch...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:49 am
I could probably help, if you provide the table structure, including indexes, some sample data, and the query you're using.
Without that, I would hazard a guess that the difference in...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:23 am
Gail, I checked that concept out as follows (beginning and ending transactions in separate procs):
create table TranTest (
ID int identity primary key,
Val char(5))
go
create proc TranTest2
as
insert into dbo.trantest (val)
select 'test2'
commit
go
create proc...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:17 am
Viewing 15 posts - 14,236 through 14,250 (of 14,953 total)