Viewing 15 posts - 7,531 through 7,545 (of 7,612 total)
--CREATE VIEW viewname AS
SELECT
COALESCE(sel.id, defaults.id) AS Id,
COALESCE(sel.name, defaults.name) AS Name
FROM (
SELECT 10 AS id, 'NotAvail' AS Name
)...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2009 at 12:55 pm
I was reading the textual plan, not the graphical one, which at a quick look I thought was using IX_sysValidationList index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2009 at 1:13 pm
If you look at the exec plan, you'll notice it's a table scan on the lookup, not an index seek.
I never said it was doing a seek, I said it...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2009 at 8:21 am
Hmm, so a given item can't go to a different status *and back* on sequential weeks:
SELECT 'B100', 1, 'FFF', 1, '2009-04-04 00:00:00.000', '2009-04-10 00:00:00.000', 0 UNION ALL
SELECT 'B100', 1, 'FFF',...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 3:53 pm
In addition to the other suggestions, make sure that:
1) Join columns are indexed
2) Indexes have the proper freespace and are reorganized / rebuilt as often as needed to keep them...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 3:31 pm
It looks to me like SQL is using the non-clus index on the lookup table. It scans it once for each LOJ, building a hash table to match to...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 3:22 pm
select * from @tbl1 tbl1 join @tbl2 tbl2 on tbl1.VersionUsed+tbl1.QID = tbl2.VerOriginal+tbl2.QID
I agree about avoiding the concatenation. And it's definitely not needed here anyway:
SELECT *
FROM @tbl1 tbl1
INNER...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 2:35 pm
Add this column:
IND_ACTIVO
as an INCLUDEd column to your existing index.
Otherwise, the query plan looks reasonable.
It would be better if you didn't convert for the sort, but you will get...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 1:53 pm
Yes, definitely increase the RAM for the SQL server if you can.
Also, if possible isolate the physical db files on their own drives, and increase the read part of the...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 1:42 pm
Is there enough available space in the filegroup in which you intend to place this index? If not, you should probably allocate more space to it yourself beforehand rather...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2009 at 12:55 pm
Look at this in the execution plan...
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee
WHERE EmployeeID BETWEEN 100 AND 200
Notice how it rewrote the code? Is that what you're talking about?...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 16, 2009 at 2:36 pm
Excellent point about DISTINCT. I see it all the time.
Also, using HAVING to check for conditions that should be in a WHERE. That can have a serious impact...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2009 at 3:11 pm
Again, this code does work:
select isdate(col1), *
from #t
where isdate(col1) = 0
or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"
SQL will definitely short circuit when it knows it...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2009 at 2:30 pm
If SQL Server evaluated left to right, in the above query, it wouldn't have to evaluate the second condition if the first was false (short circuit) because it would not...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2009 at 2:10 pm
The sample temp table code posted doesn't prove what order SQL evaluated the expressions in. With "and" specified, it always has to evaluate both expressions.
Note that this code does...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2009 at 1:56 pm
Viewing 15 posts - 7,531 through 7,545 (of 7,612 total)