Viewing 15 posts - 1,231 through 1,245 (of 7,619 total)
I agree with Phil. I would use a "holding" table to contain the data prior to it being summarized in 15-minute "packets" to be added to the main table.
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".
October 5, 2021 at 1:44 pm
Scott, fyi...I do have a clustered index defined for the column that is the primary key - an IDENTITY column.
[UserId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED (...
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".
October 5, 2021 at 1:34 am
I
[1] To log in, I request a User Name. Should I have a non-clustered index on that column?
[2] Also, in another table, I query on the email address, should...
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".
October 4, 2021 at 8:19 pm
Maybe something like this?:
ALTER TABLE dbo.your_table_name ADD REPORT_DATE AS CAST(DATEADD(DAY, CASE WHEN CREATE_TIME >= '18:00:00' THEN 1 ELSE 0 END, CREATE_DATE) AS date) PERSISTED;
...
WHERE REPORT_DATE = CAST(GETDATE() AS date)
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".
October 4, 2021 at 5:39 pm
Yep, there's not much you can do to speed it up. However, you can be sure to shrink only the specific file(s) you need to shrink, not the entire db. ...
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".
October 4, 2021 at 5:03 pm
Don't use a function against the column, that can make performance worse, potentially much worse.
WHERE CREATE_DATE = (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND CREATE_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".
October 4, 2021 at 4:53 pm
Database and index reviews cannot be automated. No software now does even a reasonably decent job of doing that. It must be done by a person.
Especially important is determining and...
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".
October 3, 2021 at 5:48 am
That's all the biggest parts of it.
Sometimes you can also need data cardinality info, that is, how many different values / row counts there are for certain columns.
For example, say...
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".
October 2, 2021 at 4:22 am
I have to admit, I really don't know how to debug "it doesn't like it." I'm not even 100% sure what that specifically means.
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".
October 2, 2021 at 4:19 am
ScottPletcher wrote:(Code reformatted but so that it can be read without scanning 50 feet to the right.)
50 feet? Is your browser set to Mr Magoo mode?
Hyperbole.
But, c'mon man, an...
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".
October 1, 2021 at 5:20 pm
Start with data modeling first, so not with "tables"/"columns" but with entities and attributes. Spend some time getting a full list of all data attributes (elements, "columns") that you need...
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".
October 1, 2021 at 4:18 pm
The key thing for performance is likely to be the indexing, and here specifically the clustered indexing.
Both IPS and V should be clustered on ( LocNum, InOrOut, AccountNumber ), 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".
October 1, 2021 at 3:46 pm
That code should return exactly the same datetime range as your original code. If not, please post what strings your current code is returning for a couple of rows against...
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".
September 30, 2021 at 7:35 pm
Purely from a performance perspective, I think you'd be better off using another table -- CustomerRanges -- to reduce each range to a single number for the final lookup 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".
September 30, 2021 at 7:33 pm
First, get rid of the functions against table columns in the WHERE clause. Those are always a terrible idea for performance. (Code reformatted but so that it can be read...
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".
September 30, 2021 at 4:44 pm
Viewing 15 posts - 1,231 through 1,245 (of 7,619 total)