Viewing 15 posts - 21,796 through 21,810 (of 22,232 total)
Something along the lines of:
SELECT c.State, h.HotelName, h.MinPrice
FROM City c
INNER JOIN Hotel h
ON c.City = h.City
AND h.Range = (SELECT MIN(Range)
FROM Hotel h2
WHERE h2.City = h.City
and h2.Hotel = h.Hotel
...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2007 at 11:30 am
The estimated plan can't be trusted because statistics & data can get out of sync. I had to come up with a contrived situation to display it. Silly, I'll grant...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2007 at 9:38 am
With some help from a person, here's one that works (by works I mean we get two different plans).
IF
EXISTS (...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2007 at 8:55 am
The short answer is "as often as possible." What this translates to varies widely based on system, space, etc.. The reason is pretty much as you state. You need to...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2007 at 4:36 am
It's funny you say that. The reason I posted an XML plan was that the graphical plans were acting all wonky with that script. There's a mystery I'm going to...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2007 at 4:29 am
Basically, yes. I'm working on something and rather than simply caution that differences could arise, I'd like to show one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2007 at 10:21 am
Crud. Now parameter sniffing is taking part. If I take away the constant values & substitute a parameter I get clustered scans for everything...
**^& %^*%&% *%&$^& $*^%& ##%#!
And I...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2007 at 8:31 am
I'm a little spacy this morning (production problems overnight), so maybe I'm doing something stupid here.
In desperation to try to find an answer this question, I hit up a...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2007 at 8:13 am
Two identical plans:
<
ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood" Author of:
- Theodore Roosevelt
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2007 at 7:07 am
Ha!
Well, I'm not going for messed up, but it's worth a shot. I'll let you know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 9:59 am
Everything the Guru said and, check the use of transactions in your process. Something seems to be doing some sort of lock escalation within the transaction because blocking, by itself, doesn't...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 8:53 am
OK. Weak theory completely destroyed by reality. You can't drop statistics created by indexes and the other statistics just don't seem to be affecting the plan much. More ideas needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 6:41 am
Something like this?
UPDATE Receipts
SET DateEnd = u.ExpiryDate
FROM UserRoles u
INNER JOIN Receipts r
ON u.UserId = r.UserId
WHERE u.RoleId = 5
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 6:39 am
I'm thinking I'll try dropping the statistics on a table and see what happens. I'm trying to get a different plan, not just subtle differences in the plan (different row counts...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 6:18 am
Hey,
I saw the test you'd outlined so I tried it on my laptop:
WHILE LOOP TEST...
1000000 The final count (just to show work was done)
970 Duration in milliseconds
------------------------------------------------------------------------------
SET BASED...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 6, 2007 at 5:19 am
Viewing 15 posts - 21,796 through 21,810 (of 22,232 total)