Viewing 15 posts - 6,571 through 6,585 (of 7,613 total)
SQL_Enthusiast (4/25/2013)
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".
April 25, 2013 at 11:12 am
GilaMonster (4/25/2013)
Abu Dina (4/25/2013)
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.
Table variables don't have stats, but they certainly can have...
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".
April 25, 2013 at 11:10 am
There's no inherent reason the proc should perform so badly.
Instead of table variables, use temp tables and index the temp tables appropriately.
I suspect the time can be reduced significantly from...
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".
April 25, 2013 at 10:38 am
You may gain some performance by avoiding counting all the rows in inserted and deleted:
ALTER TRIGGER dbo.SystemInfoTrg
ON dbo.SystemInfo
AFTER INSERT, DELETE, UPDATE
AS
SET NOCOUNT ON;
DECLARE...
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".
April 25, 2013 at 10:35 am
I think something like this should at least be close:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
...
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".
April 25, 2013 at 10:29 am
Just in case, IF you already have an index on ( YearValue, MonthValue ), then this should be extremely fast:
SELECT
MAX(YearValue) AS YearValue, MAX(MonthValue) AS MonthValue...
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".
April 24, 2013 at 5:01 pm
Sorry, forgot I ended up using a database-specific function in the code (was trying to avoid it). This approach should be safer overall anyway:
EXEC sp_MSforeachdb N'
IF ''?'' IN (''master'',...
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".
April 24, 2013 at 9:00 am
SQL Server does not have an equivalent to that function or functionality :-(.
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".
April 23, 2013 at 4:24 pm
Yeah, that's the official story. But I don't 100% buy it.
With that many different fragments, if I were you, I'd run contig.exe on that(those) file(s) anyway.
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".
April 23, 2013 at 4:22 pm
Here's something more detailed. Btw, I avoided using I_S.SCHEMATA for the schema names because of the associated warnings in Books Online -- it's best to avoid using I_S views...
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".
April 23, 2013 at 11:19 am
Dird (4/23/2013)
Evil Kraig F (4/22/2013)
There's no cursor in his solution.
Is there any documentation/book which proves this? Oracle would be running implicit cursors here; I have a hard time...
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".
April 23, 2013 at 9:53 am
Dird (4/22/2013)
ScottPletcher (4/22/2013)
unless you use cursors, and nobody wants thatAnd you think that solution isn't implicitly using cursors?
Edit: But yeah, it's a better way of doing it 😛
Dird
I know my...
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".
April 22, 2013 at 3:32 pm
SQL Server triggers only fire once per statement, no matter how many rows are INSERTed or UPDATEd (or DELETEd).
Therefore, it's not safe to use variables to get column data (unless...
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".
April 22, 2013 at 1:56 pm
I suggest adding a computed column to the table to determine the value: then the definition is only one place, and is thus very easy to change everywhere:
ALTER TABLE dbo.tablename...
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".
April 22, 2013 at 1:47 pm
The code's not really ready to be used in dev or QA either.
NO reason to search the same table multiple times, esp. not once per column.
Instead, should do a single...
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".
April 17, 2013 at 2:48 pm
Viewing 15 posts - 6,571 through 6,585 (of 7,613 total)