Viewing 15 posts - 1,501 through 1,515 (of 7,619 total)
Who t.h. is "Scoot", Jeft?
Hmm, so you allow ad-hoc tables with NO design process or thought, i.e. no data modeling, to be created and deployed? Interesting.
At any rate, how do...
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 18, 2021 at 8:15 pm
Actually they got the structure right for this table: the clustered index is what's critical, and it's not fragmented (presumably).
To help correct the PK but NONCLUSTERED index -- which again...
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 18, 2021 at 7:29 pm
And I still say you need business input before deciding on what to put in any table you create. I don't think you can properly model the data until then,...
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 18, 2021 at 7:23 pm
First, the history table should be clustered leading on OccurredDate. That's generally true for log tables. [Forget the stupid myth that "by default, every table should be clustered on IDENTITY"....
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 18, 2021 at 4:22 pm
Again, I'm still sticking with code, such as a function, for this rather than a physical table until I see an actual need for a table. The first digit alone...
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 18, 2021 at 3:59 pm
But any lookup table does NOT need to contain a list of all zip codes as originally stated. If the "slab" is what is significant, the lookup only needs to...
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 18, 2021 at 2:58 pm
Don't drop the existing table.
Create a new, test (dummy) Quality table UNIQUEly CLUSTERED on ( Quality_Date, Quality_ID ).
CREATE TABLE dbo.Quality_Test ( ... )
CREATE UNIQUE CLUSTERED INDEX QUALITY_Test__CL ON dbo.Quality_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".
June 18, 2021 at 2:51 pm
And again -- and for the last time, so as not to upset everyone -- you likely need to re-cluster the Quality table to get even more performance gains, 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".
June 17, 2021 at 9:09 pm
What almost always gives the best payback is to first do a review of all indexes on the table, particularly on the clustered indexes. Until you have the best clus...
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 17, 2021 at 9:07 pm
Why? If what you're saying is true, then just use the first char in the value as the category, that's less overhead than any kind of table lookup.
Because...
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 17, 2021 at 9:02 pm
According to your sample data and results, the "Answer" you marked is not correct. For example, you stated that '10000' should return a 1 -- "Anything between 00000...
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 17, 2021 at 6:51 pm
Hopefully this should improve things. As I noted before, it's very likely that re-clustering the Quality table would give you even more performance gains, as it would prevent a full...
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 17, 2021 at 6:48 pm
In the first query, you need to specify a specific db to look at, or include the db name in the results. The object_ids and index_ids are unique only within...
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 17, 2021 at 3:09 pm
According to your sample data and results, the "Answer" you marked is not correct. For example, you stated that '10000' should return a 1 -- "Anything between 00000 to 10000...
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 17, 2021 at 2:53 pm
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
Viewing 15 posts - 1,501 through 1,515 (of 7,619 total)