Viewing 15 posts - 4,411 through 4,425 (of 7,613 total)
You need to use the actual semester number to record the grade. Year alone is not enough, nor is "odd/even" (bizarre!).
It really seems like this is some type of...
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 24, 2016 at 9:05 am
I think this is the (actual) logic you need, based on analyzing the original code:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]
@PolicyId int,
@AccountId int,
@PolicyNodeId int,
@output bit OUTPUT
AS
SET NOCOUNT...
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 23, 2016 at 4:04 pm
ChrisM@Work (2/23/2016)
spaghettidba (2/23/2016)
CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1,...
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 23, 2016 at 3:42 pm
SELECT
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
FROM (
SELECT r2.ID
FROM IMIS.dbo.vw_csys_registrations r2
WHERE r2.MEETING_TYPE in ('PADA', 'TSM') AND r2.MEMBER_TYPE 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".
February 23, 2016 at 3:40 pm
I think this will identity whether or not a row exist and will probably run much more quickly, since no massive join is involved, although a huge sort will be...
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 23, 2016 at 3:30 pm
hlsc1983 (2/23/2016)
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 23, 2016 at 1:39 pm
Below is a more fully sketched out design. Hopefully this is not a homework or project assignment :-D.
CREATE TABLE dbo.subjects (
subject_id int IDENTITY(1, 1)
...
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 23, 2016 at 11:34 am
Worse, all this query tuning is likely effectively wasted anyway since the clus index is not being correct first. As soon as another column is added to 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".
February 23, 2016 at 10:46 am
I think you should get check all NULLable columns for a given table in a single pass. It could hurt severely hurt performance to have to scan the same...
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 22, 2016 at 10:36 am
Hugo Kornelis (2/19/2016)
ScottPletcher (2/19/2016)
Log tables are almost always best clustered on a datetime, and virtually never on an identity value.
When it's an insert datetime, I think that this guess would...
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 19, 2016 at 9:56 am
Hugo Kornelis (2/19/2016)
ScottPletcher (2/19/2016)
You should cluster this table on upsrt_dttm instead.
How can you possibly determine that this should be a good clustering key based on seeing only one single query...
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 19, 2016 at 9:24 am
The "standard" performance problem when clustering on an identity column "by default". Trying to work around the wrong clustered index will generate lots of extra I/O.
You should cluster 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".
February 19, 2016 at 9:15 am
Make sure the sub-procs have their own CATCH blocks. You can use empty/dummy blocks if you don't care about errors there:
CREATE PROCEDURE sub_proc
AS
...
BEGIN CATCH
--ignore error here
END CATCH
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 17, 2016 at 3:51 pm
Try the RECOMPILE option. That time will be trivial compared to I/O time.
Also, do review the indexes on the table: getting the best clustered index on every table 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".
February 17, 2016 at 3:49 pm
In theory you could:
SET ANSI_WARNINGS OFF
But read up very, very carefully on that before you do it!
Or, you could create a stored proc and pass the values into the proc...
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 17, 2016 at 3:45 pm
Viewing 15 posts - 4,411 through 4,425 (of 7,613 total)