Viewing 15 posts - 1,516 through 1,530 (of 7,620 total)
We need to see the DDL for the two tables, including all index definitions.
Btw, if the tables really were "properly indexed", you wouldn't be having big performance issues on this...
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".
June 16, 2021 at 4:21 pm
For the pure SQL part, technically this could theoretically perform better since it avoids using functions on the table columns:
WHERE (p.JCCo=@Company and p.Job=@Job AND Phase NOT LIKE...
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".
June 16, 2021 at 3:00 pm
So I'm looking for the most efficient way to pull the last 30 minutes of data from the table. This converts it to date\time I want to use DATEADD(s,t_stamp/1000,'1969-12-31...
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".
June 16, 2021 at 4:15 am
Found that I had to use '1969-12-31 20:00:00' to get the correct datetime.
Be careful with that! If you need UTC and "kludge" it this way instead, it may...
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".
June 16, 2021 at 4:14 am
To get a UTC calc, just substitute GETUTCDATE() for GETDATE() in my code.
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".
June 15, 2021 at 8:16 pm
Hmm, looks like UTC time.
Or something in the data -- i.e. it's not really ms since 1/1/70 -- or it's local time where the server is?!
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".
June 15, 2021 at 8:04 pm
I put no max time, so, yes, it would give you everything from then on.
If you want to limit to the then-current time, do this:
WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE())...
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".
June 15, 2021 at 7:52 pm
I'm unsure what value a leading '9' should yield as the slab#, it's not really clear from your OP.
SELECT
LEFT(ZIP, 1) + 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".
June 15, 2021 at 7:02 pm
WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE()) - (1000 * 60 * 30)
Note that, for important performance reasons, you do not want to use any function at all on the column...
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".
June 15, 2021 at 6:56 pm
IF EXISTS(
SELECT*
FROMsys.columns
WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
EXEC ('UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,''_'',''.''),2)
FROM dbo.[tbl_Reference]')
END
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".
June 15, 2021 at 1:38 pm
Create a "base" db with the schema, tables, etc. that you need.
When you need to create a new db, restore a backup of that db to the new db 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".
June 14, 2021 at 8:57 pm
SELECT
STUFF(NAME, 1, CASE WHEN LEFT(NAME, 5) = 'RECA-' THEN 5 ELSE 0 END, '') AS NAME
FROM dbo.table_name
A potential problem with REPLACE is...
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".
June 14, 2021 at 8:25 pm
Why are using the RTM version instead of applying patches? You'll have performance issues from that as well.
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".
June 14, 2021 at 3:05 pm
Actually, best is often to force max values out of row, for every row. That is generally my preference, although naturally there are a few specific exceptions.
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".
June 11, 2021 at 7:22 pm
Would have to see the query's use of col1 thru col8: in WHERE, JOINs, GROUP BY and even the SELECT itself.
SQL Server's missing index feature doesn't properly "understand" the importance...
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".
June 9, 2021 at 9:31 pm
Viewing 15 posts - 1,516 through 1,530 (of 7,620 total)