Forum Replies Created
Viewing 15 posts - 526 through 540 (of 7,613 total)
-
Reply To: How to query performance baseline
Broadly speaking, SQL Server performance comes down to indexes (assuming you don't have major issues with RAM, disk I/O, other basics).
Therefore, first I'd suggest reviewing your indexes for performance. Unfortunately,...
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".
-
Reply To: Using A CTE to exclude rows from the main Select statement
That code is not valid. There is no way to tell which 'type' column is being referenced in the WHERE clause.
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".
-
Reply To: Hints for searching text?
You have a some options at least to help speed things up. I'm sure someone can think of others. (Entries are numbered only to ease possible discussion of them, 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".
-
Reply To: Coalesce performance issue
You're doing (the equivalent of) a CROSS JOIN on the WORKC and PLNT_WORKC tables (JOIN ON 1 = 1). That could generate a huge number of rows. You then filter...
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".
-
Reply To: Fetch dynamic columns in join condition
You don't need a temp table, you can just create a view of the existing table. The really good this is the view can be created using dynamic SQL, so...
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".
-
Reply To: LOJ followed By LOJ - What is this and is it best practices
You can defer the ON clause for a JOIN until after other JOINs. Normally you wouldn't do that unless it's absolutely necessary.
LEFT OUTER JOIN TABLEC CC ON (CC.hFKey4 = CHG.hPKey...
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".
-
Reply To: PIVOT, is there a better way to do this?
Jonathan AC Roberts wrote:ARPRINCE wrote:Thank you all!
Sorry but I made a mistake on my original posting. Revised DATA representation is below
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
...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".
-
Reply To: sql2012 equivalant
WHERE t_stamp >= (CAST(DATEDIFF(SECOND, '19691231 20:00:00', GETDATE()) AS bigint) * 1000) - (1000 * 60 * 30)
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".
-
Reply To: PIVOT, is there a better way to do this?
ARPRINCE,
Have you looked at the query plans for the two queries? The LEFT JOIN method is doing a full table scan per join. That could be a (big) performance issue,...
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".
-
Reply To: PIVOT, is there a better way to do this?
SELECT
ID,
MAX(CASE WHEN Referrence = 'OrderNo' THEN Value END) AS OrderNo,
MAX(CASE WHEN Referrence = 'OrderDate'...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".
-
Reply To: PIVOT, is there a better way to do this?
Here's the "standard" cross-tab method:
SELECT
ID,
MAX(CASE WHEN Referrence LIKE 'ON%' THEN Referrence END) AS OrderNo,
MAX(CASE...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".
-
Reply To: "ID" primary key from two separate tables as a foreign key in a new table
RonKyle wrote:The use of the prefixed "TBL_" is a design error so bad it has a name; it's called the tibble.
This is not a design error. It does not violate...
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".
-
Reply To: Are the posted questions getting worse?
I use
;WITH
just to help prevent possible errors. Sure, I could be technically correct and blame someone else when there was a problem with the code, but I prefer to avoid...
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".
-
Reply To: "ID" primary key from two separate tables as a foreign key in a new table
Jonathan AC Roberts wrote:ScottPletcher wrote:RonKyle wrote:I would not prefix a view with tbl. In any case I rarely use views in an OLTP environment. I do make extensive use of them in...
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".
-
Reply To: "ID" primary key from two separate tables as a foreign key in a new table
RonKyle wrote:I would not prefix a view with tbl. In any case I rarely use views in an OLTP environment. I do make extensive use of them in my OLAP...
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".
Viewing 15 posts - 526 through 540 (of 7,613 total)