Viewing 15 posts - 991 through 1,005 (of 7,613 total)
Would help greatly to see the DDL for the tables, including all indexes.
The query could also be rewritten to help performance, but without sample data there's no way to test...
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 14, 2022 at 8:24 pm
Ouch.
Based on my past experience, you'll continue to have trouble until you move all non-SQL-Server apps to different boxes. Not just CPU could be an issue, you could easily run...
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 14, 2022 at 8:02 pm
My guess would be that the log file needed to grow dynamically, and that is very slow. That partly would depend on the specific UPDATEs that were done and how...
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 14, 2022 at 7:27 pm
You didn't provide any sample data so I can't test this at all:
SELECT TP.Location,
TE.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 11, 2022 at 2:57 pm
Your second table, CTR_PanelMembers, is defined as a heap.
Instead, it should have a clustered index on:
( CTRseqNo, RecordID )
I don't see anything wrong with the FK definition in the second...
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 10, 2022 at 2:17 pm
I assumed that the only value after 'Receiver' was the id. If more string can follow, the code below will need changed. I was not able to test the 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".
February 9, 2022 at 4:42 pm
No, adding a COMMIT will not speed it up, the associated explicit BEGIN TRAN will likely slow it down slightly. SQL will (auto)commit after the statement finishes anyway.
Be sure you...
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 8, 2022 at 10:52 pm
You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block. Checking XACT_STATE in 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".
February 7, 2022 at 4:43 pm
You need to step back and do proper data modeling on all your entities. That is the most important step in proper design.
Then, as you convert the entities to physical...
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 7, 2022 at 4:41 pm
SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'". Google the syntax of the command.
You'll need to use dynamic SQL if you want to feed in a FROM file...
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 2, 2022 at 11:29 pm
I'm guessing you don't already have a row_num.
;WITH cte_add_row_num AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
...
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 1, 2022 at 5:39 pm
Would need to see the tables' DDL and the actual query plan. Can't troubleshoot air.
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 1, 2022 at 1:02 am
Yeah, really should have mentioned that Pervasive thing to begin with :-). That means there are multiple rows being returned for each lookup. Which one you get will be random,...
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 31, 2022 at 9:06 pm
Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...
IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip,...
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 31, 2022 at 6:37 pm
--...
ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [DBNameToRestoreHere]...
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 31, 2022 at 4:41 pm
Viewing 15 posts - 991 through 1,005 (of 7,613 total)